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 mixed types may return wrong results
Date Thu, 20 Dec 2012 09:29:13 GMT

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

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

I changed the title of the bug since the problem is not restricted to constants.

Here's another example that we may add to our tests once the bug is fixed:

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
ij> select * from t3 where b1 in (values b2, d);
B1                  |B2                  |D                     
----------------------------------------------------------------
9223372036854775805 |9223372036854775806 |1.0                   

1 row selected

The two select queries return different results, although the standard says they should be
equivalent.
                
> IN lists with mixed types 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