[DERBY-6017] IN lists with mixed types may return wrong results

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.9.1.0
• Fix Version/s: 10.8.3.3, 10.9.2.2, 10.10.1.1
• Component/s: SQL
• Issue & fix info: Repro attached
• Bug behavior facts: Wrong query result
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Created: 18/Dec/12 13:27
• Updated: 07/Aug/13 14:52
• Resolved: 04/Mar/13 13:47

Description

Given this table:

ij> connect 'jdbc:derby:memory:db;create=true';
ij> create table t(x bigint);
0 rows inserted/updated/deleted
ij> insert into t values 9223372036854775805, 9223372036854775806, 9223372036854775807;
3 rows inserted/updated/deleted

A query that uses an IN list that contains all the three values actually stored in the table, returns all three rows as expected:

ij> select * from t where x in (9223372036854775805, 9223372036854775806, 9223372036854775807);
X
--------------------
9223372036854775805
9223372036854775806
9223372036854775807

3 rows selected

However, if we add a value whose type precedence is higher, like a DOUBLE value, and that value happens to be equal to the approximation of the other values in the IN list when they are cast from BIGINT to DOUBLE, only one row is returned:

ij> select * from t where x in (9223372036854775805, 9223372036854775806, 9223372036854775807, 9.223372036854776E18);
X
--------------------
9223372036854775805

1 row selected

I believe this query should return all three rows too.

Issue Links

• is related to: DERBY-6080 Cast to same type changes result with IN subquery
• is related to: DERBY-6289 Derby 10.8 backport issue (summer 2013)
• relates to: DERBY-2256 Wrong Results: Use of decimal values in an IN-list with INTEGER left operand can lead to extra rows.