db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6017) IN lists with constants may return wrong results
Date Wed, 19 Dec 2012 15:31:13 GMT

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

Bryan Pendleton commented on DERBY-6017:
----------------------------------------

Thank you Knut Anders for digging deep into the standard to explore these topics.

I see no holes in your logic; it seems unavoidable that (a) these queries are intended to
be
clearly-defined by the standard, and (b) Derby is doing it wrong. I can *wish* that the
standard were written differently, but it ain't so... :)

I think your proposed approach is excellent.

Hopefully the fact that we currently seem to behave correctly in the ANY queries and
in the table value constructor (VALUES ... ) give some clues about what's needed to
be included in the other queries to give them the right form.

Am I right in conceptualizing this as "we need to be implicitly casting the values to
the correct type in certain situations, and we're currently not doing so."?

                
> 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