468,291 Members | 1,666 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,291 developers. It's quick & easy.

strange query result, (i must be doing something wrong)

1,043 Expert 1GB
Expand|Select|Wrap|Line Numbers
  1. SELECT id, int1 
  2. FROM testInt 
  3. WHERE int1 IN (1,2,346,596,597)
  4. ORDER BY int1, id
  5.  
  6. SELECT id, int1 
  7. FROM testInt 
  8. WHERE int1 IN (1,597)
  9. ORDER BY int1, id
  10.  
  11. SELECT id, int1 
  12. FROM testInt 
  13. WHERE id in (5152426,6301653,1308712,5518275,6121956,7410050)
  14. ORDER BY int1, id
  15.  
this gives next results
Expand|Select|Wrap|Line Numbers
  1. id          int1
  2. ----------- -----------
  3. 5152426     2
  4. 6301653     2
  5. 1308712     346
  6. 5518275     346
  7. 6121956     596
  8. 7410050     596
  9.  
  10. (6 rows affected)
  11.  
  12.  
  13.  
  14. (1 row affected)
  15. id          int1
  16. ----------- -----------
  17.  
  18. (0 rows affected)
  19.  
  20.  
  21.  
  22. (1 row affected)
  23. id          int1
  24. ----------- -----------
  25. 5152426     2
  26. 6301653     2
  27. 1308712     346
  28. 5518275     346
  29. 6121956     596
  30. 7410050     596
As you can see
- There is no record for int1 with value =1
- Why does the first query return two records with value=2?
- There is no record for int1 with value =597
- Why does the first query return two records with value=596?


The create script for this table is
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [dbo].[testInt](
  2.     [id] [int] NOT NULL,
  3.     [description] [nvarchar](50) NULL,
  4.     [int1] [int] NULL,
  5.     [int2] [int] NULL,
  6.  CONSTRAINT [PK_testInt] PRIMARY KEY CLUSTERED 
  7. (
  8.     [id] ASC
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  10. ) ON [PRIMARY]
  11. GO
  12. CREATE NONCLUSTERED INDEX [NonClusteredIndex-20190309-125532] ON [dbo].[testInt]
  13. (
  14.     [int1] ASC
  15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  16. GO
  17.  
  18.  
Mar 9 '19 #1

✓ answered by Rabbit

It seems to me that the int1 values were inserted multiple times with the same value. Since the additional index created on int1 doesn't specify unique, nothing prevents someone from doing that.

2 2160
Rabbit
12,512 Expert Mod 8TB
It seems to me that the int1 values were inserted multiple times with the same value. Since the additional index created on int1 doesn't specify unique, nothing prevents someone from doing that.
Mar 11 '19 #2
Luuk
1,043 Expert 1GB
Indeed
Expand|Select|Wrap|Line Numbers
  1. with tmp as (
  2.     SELECT 1 as tmp
  3.     union
  4.     select 2
  5.     union 
  6.     select 346
  7.     union
  8.     select 596
  9.     union 
  10.     select 597
  11. )
  12. SELECT tmp.tmp, id, int1 
  13. FROM tmp
  14. LEFT OUTER JOIN testInt on int1 = tmp.tmp
  15. ORDER BY int1, id
  16.  
shows:
Expand|Select|Wrap|Line Numbers
  1. tmp    id    int1
  2. 1    NULL    NULL
  3. 597    NULL    NULL
  4. 2    5152426    2
  5. 2    6301653    2
  6. 346    1308712    346
  7. 346    5518275    346
  8. 596    6121956    596
  9. 596    7410050    596
Mar 15 '19 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Chiller | last post: by
6 posts views Thread by Michael Sparks | last post: by
9 posts views Thread by Randy | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by Teichintx | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.