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

[Luuk]

SELECT id, int1 
FROM testInt 
WHERE int1 IN (1,2,346,596,597)
ORDER BY int1, id
 
SELECT id, int1 
FROM testInt 
WHERE int1 IN (1,597)
ORDER BY int1, id
 
SELECT id, int1 
FROM testInt 
WHERE id in (5152426,6301653,1308712,5518275,6121956,7410050)
ORDER BY int1, id
 

this gives next results

id          int1
----------- -----------
5152426     2
6301653     2
1308712     346
5518275     346
6121956     596
7410050     596
 
(6 rows affected)
 
 
 
(1 row affected)
id          int1
----------- -----------
 
(0 rows affected)
 
 
 
(1 row affected)
id          int1
----------- -----------
5152426     2
6301653     2
1308712     346
5518275     346
6121956     596
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

CREATE TABLE [dbo].[testInt](
    [id] [int] NOT NULL,
    [description] [nvarchar](50) NULL,
    [int1] [int] NULL,
    [int2] [int] NULL,
 CONSTRAINT [PK_testInt] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20190309-125532] ON [dbo].[testInt]
(
    [int1] ASC
)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]
GO
 
 

[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.

[Luuk]

Indeed

with tmp as (
    SELECT 1 as tmp
    union
    select 2
    union 
    select 346
    union
    select 596
    union 
    select 597
)
SELECT tmp.tmp, id, int1 
FROM tmp
LEFT OUTER JOIN testInt on int1 = tmp.tmp
ORDER BY int1, id
 

shows:

tmp    id    int1
1    NULL    NULL
597    NULL    NULL
2    5152426    2
2    6301653    2
346    1308712    346
346    5518275    346
596    6121956    596
596    7410050    596