[jira] [Updated] (DERBY-6017) IN lists with mixed types may return wrong results

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

     [ 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-2a-cast-if-needed.diff

Attaching d6017-2a-cast-if-needed.diff which solves the remaining IN
list problems discussed in this issue (except the second anomaly
mentioned in my 18/Dec/12 21:58 comment, as that's an IN subquery, not
an IN list).

The 2a patch replaces the fix in the 1a patch with a more general one
that also addresses the problems with non-constants.

The problems are caused by the comparison operations being performed
using the semantics of the dominant type of the two values being
compared. It should be performed using the dominant type of *all* the
values in the IN predicate.

To fix this, the patch makes InListOperatorNode.preprocess() cast the
left operand to the dominant type if some of the comparisons otherwise
would have been performed using a non-dominant type.

The fix works because the left operand is part of every single
comparison operation, and as long as one of the values being compared
is of the dominant type, the comparison will be performed using the
semantics of that type.

The cast is not added if the left operand is already of the dominant
type, in which case the cast would be redundant. Also, it is not added
if all the values in the right operand (the IN list) are of the
dominant type. In that case, too, every single comparison operation
would include one value of the dominant type, and the correct
semantics would be used.

In all other cases, a cast is added to ensure that the comparisons are
performed using the right type.

All the regression tests ran cleanly with the patch.

Description of the changes:

* engine/org/apache/derby/impl/sql/compile/InListOperatorNode.java

- Made preprocess() cast the left operand to the dominant type if
  necessary.

- Removed the cast added by the 1a patch for the case where all values
  in the IN list represented the same constant value, as that code was
  made redundant by the new fix.

* engine/org/apache/derby/iapi/types/DataType.java

- Improved comment about how types are handled when evaluating an IN
  predicate.

* engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
* engine/org/apache/derby/impl/sql/compile/ValueNodeList.java

- Removed unnecessary throws clauses. Did this so that I didn't have
  to add another unnecessary throws clause to a helper method I added
  to InListOperatorNode.

* testing/org/apache/derbyTesting/functionTests/tests/lang/InPredicateTest.java

- Added test cases from previous comments on this issue. They work as
  expected with the patch.
                
> 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, d6017-2a-cast-if-needed.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