We have found in our a system an SQL query which looks like this:
SELECT *, tid FROM x WHERE tid IN (213, 214, 215, 216, -1)
The actual numbers come from somewhere else, but we always append -1 at the end of the query so I tried removing it. After some investigation why it broke the system I narrowed the problem down to the database:
SELECT tid FROM x WHERE tid IN (213) -- 213
SELECT tid FROM x WHERE tid IN (213, 214) -- 214
SELECT tid FROM x WHERE tid IN (213, 214, 215) -- 214, 215
SELECT tid FROM x WHERE tid IN (213, 214, 215, -1) -- 213, 214, 215
SELECT tid FROM x WHERE tid IN (5000) -- 5000
SELECT tid FROM x WHERE tid IN (213, 5000) -- 5000
SELECT tid FROM x WHERE tid IN (215, 214, 213, 2147000000) -- 215, 214
SELECT tid FROM x WHERE tid = 1 OR tid = 2 -- 2
It seems that if in-list has more than one element, Ingres is ignoring the smallest of them. It happens only when querying on tid, it doesn't happen with any other columns. It also doesn't matter when it is in-list or just multiple ORs, the lowest value is always ignored. Finally, it behaves like this only in this SELECT, for DELETE it works fine.
Any idea why the Ingres may be doing it?
I only tested this quickly on the nearest Ingres version to hand (10.2) but I didn't get this behaviour, I always got all the matching rows.
I'd say this is a bug and I would raise it with Actian Support if I were you.
(I work for Actian)