[
https://issues.apache.org/jira/browse/DERBY-6017?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-6017:
--------------------------------------
Attachment: d6017-1a-duplicates.diff
Attaching a patch (d6017-1a-duplicates.diff) for the problem we see when
duplicates are eliminated from the IN list.
The original code would detect that an IN list consisting of the values
(9223372036854775805, 9223372036854775806, 9223372036854775807,
9.223372036854776E18) only contained one distinct value after conversion to the
dominant type. It would arbitrarily pick the first value and replace the IN
predicate with leftOperand =
9223372036854775805.
The patch changes this optimization so that it casts the right operand in the
comparison to the dominant type if neither of the two operands already is of
the dominant type.
It also adds a test case to verify that the expected results are produced.
All the regression tests ran cleanly with the patch.
The patch doesn't address any of the other problems discussed in this issue.
> 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
> 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, 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
