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] [Commented] (DERBY-6017) IN lists with constants may return wrong results
Date Tue, 18 Dec 2012 21:00:13 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6017?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13535311#comment-13535311
] 

Knut Anders Hatlen commented on DERBY-6017:
-------------------------------------------

I think the query is allowed by the SQL standard. The first paragraph in SQL:2003, part 2,
section 4.4.1 (Introduction to numbers) says: "A number is either an exact numeric value or
an approximate numeric value. Any two numbers are comparable." So no such luck.

We probably need to study the standard more closely to find out what the exact semantics are,
though. In particular this: Is it the sorting or the binary search that uses the right kind
of comparison. If it's the sorting (which uses the same kind of comparison for all the values,
based on the dominant type), I suspect the problem also affects IN lists that don't have constants.
For example:

ij> create table t3(b1 bigint, b2 bigint, d double);
0 rows inserted/updated/deleted
ij> insert into t3 values (9223372036854775805, 9223372036854775806, 1);
1 row inserted/updated/deleted
ij> select * from t3 where b1 in (b2, d);
B1                  |B2                  |D                     
----------------------------------------------------------------

0 rows selected

If it is correct that the dominant type should be used, I would have expected the above query
to return one row, as there is a DOUBLE value in the IN list, and b1=b2 when they are converted
to DOUBLE.

Another puzzling result that doesn't involve constants, is this:

ij> create table t4 (b bigint);
0 rows inserted/updated/deleted
ij> insert into t4 values 9223372036854775806, 9223372036854775807;
2 rows inserted/updated/deleted
ij> create table t5 (d double);
0 rows inserted/updated/deleted
ij> insert into t5 values 9.223372036854776E18;
1 row inserted/updated/deleted
ij> select * from t4 where b in (select d from t5);
B                   
--------------------
9223372036854775807 

1 row selected
ij> select * from t4 where b in (select cast(d as double) from t5);
B                   
--------------------
9223372036854775806 
9223372036854775807 

2 rows selected

Is it correct that the two queries should return different results? The only difference is
that the first query accesses the D column with no cast, and the second one casts D to DOUBLE.
But since D already is a DOUBLE column, I wouldn't expect the cast to make any difference.
                
> 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