Knut Anders Hatlen created DERBY-6017:
-----------------------------------------
Summary: IN lists with constants may return wrong results
Key: DERBY-6017
URL: https://issues.apache.org/jira/browse/DERBY-6017
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.9.1.0
Reporter: Knut Anders Hatlen
Assignee: Knut Anders Hatlen
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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
