[
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
