0

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?

Jaroslaw Pawlak
  • 5,538
  • 7
  • 30
  • 57
  • Odd. Can you show us the table definition, any index? – jarlh Aug 17 '15 at 12:02
  • 1
    What is the type of `tid`? – Gordon Linoff Aug 17 '15 at 12:03
  • @GordonLinoff tid is a tuple-id, intended for internal use. like a rowid, rownumber, etc. @ OP : maybe the `-1` was a work-around for the observed behaviour? – wildplasser Aug 17 '15 at 12:09
  • are your values coming from tables in 'IN clause'. if yes then you can do like this select your_field from table_name where your_field > 0 – Hardik Parmar Aug 17 '15 at 12:38
  • @jarlh tid is Ingres specific thing, it doesn't have anything to do with table definition – Jaroslaw Pawlak Aug 17 '15 at 13:12
  • @GordonLinoff type of tid is bigint but our largest value there is about 6 million, no negative values – Jaroslaw Pawlak Aug 17 '15 at 13:12
  • @wildplasser yes, it is a workaround - we add a new smallest value (for which we definitely do not have the entry) so Ingres can ignore this one instead of other significant one... – Jaroslaw Pawlak Aug 17 '15 at 13:13
  • @HardikParmar the values are actually sent by the client because we use them as identifiers (using a normal key for this was drastically decreasing performance). Greater/lower than will not help, because this list will contain up to a few hundred random numbers. However, what is interesting 'SELECT tid FROM x WHERE tid >= 2043395 AND tid <= 2043396' returns both values, however the performance was terrible (a few seconds). – Jaroslaw Pawlak Aug 17 '15 at 13:15

1 Answers1

1

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)

PaulM
  • 446
  • 2
  • 12