db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DERBY-6017) IN lists with constants may return wrong results
Date Tue, 18 Dec 2012 13:28:14 GMT
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

Mime
View raw message