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 Fri, 21 Dec 2012 12:53:15 GMT

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

Knut Anders Hatlen commented on DERBY-6017:

For the problem fixed by the 1a patch, I didn't find any problematic combinations except BIGINT/DOUBLE.
As far as I can see REAL/DOUBLE doesn't have the problem because the conversion from REAL
to DOUBLE never loses precision. And mixing INT and REAL is not a problem either, as the problem
only affects IN lists that consists entirely of literals, and Derby's grammar doesn't have
any way to express REAL literals (all approximate number literals get the type DOUBLE). You
can of course cast a DOUBLE literal to a REAL, but then it's no longer a ConstantNode, and
the problematic optimization is not applied.

However, mixing REAL and INT seems to have similar problems as DOUBLE and BIGINT when using
non-constant expressions in the IN list. Here's one example:

ij> select * from tt where r1 in (i1, r2);
I1         |R1           |R2           
2147483645 |2.14748365E9 |2.14748365E9 
2147483645 |2.14748365E9 |0.0          

2 rows selected
ij> select * from tt where r1 in (values i1, r2);
I1         |R1           |R2           
2147483645 |2.14748365E9 |2.14748365E9 

1 row selected

The two queries are supposed to be equivalent, but they return different results.

I think the former query performs both of the comparisons (r1=i1, r1=r2) using REAL semantics,
whereas the latter query uses DOUBLE semantics because the (values i1,r2) sub-query has DOUBLE
as its row type. 
> 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:
>            Reporter: Knut Anders Hatlen
>            Assignee: Knut Anders Hatlen
>         Attachments: d6017-1a-duplicates.diff
> 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,
> 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

View raw message