[
https://issues.apache.org/jira/browse/DERBY-2282?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-2282:
--------------------------------------
Attachment: d2282-1a.diff
d2282-1a.stat
Here's a patch that addresses the issue by changing the else-if condition as
suggested, and by swapping the operands and changing the operator type if
necessary.
The method BinaryOperatorNode.swapOperands(), which only works if the operator
is symmetric, is no longer used, and it is therefore removed. Instead, a new
method called getSwappedEquivalent() (in the lack of a better name, other
suggestions are welcome) was added to BinaryComparisonOperatorNode. This method
is modelled after getNegation() and creates a new node with the left and right
operands swapped, and it changes the operator type to preserve the meaning of
the expression (< is changed to >, = is kept as it is, >= is changed to <=,
etc).
All the regression tests ran cleanly.
> Incorrect "transitive closure" logic leads to inconsistent behavior for
> binary comparison predicates.
> -----------------------------------------------------------------------------------------------------
>
> Key: DERBY-2282
> URL: https://issues.apache.org/jira/browse/DERBY-2282
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.0.2.2, 10.1.1.0, 10.1.2.1,
> 10.1.3.1, 10.1.3.2, 10.1.4.0, 10.2.1.6, 10.2.2.0, 10.2.2.1, 10.2.3.0, 10.3.1.4
> Reporter: A B
> Assignee: Knut Anders Hatlen
> Priority: Minor
> Attachments: d2282-1a.diff, d2282-1a.stat, test.diff
>
>
> The logic that handles "transive closure" for search predicates is in the
> "searchClauseTransitiveClosure()" method of
> impl/sql/compile/PredicateList.java. That method contains the following
> logic:
> else if (operator instanceof BinaryComparisonOperatorNode)
> {
> BinaryComparisonOperatorNode bcon =
> (BinaryComparisonOperatorNode) operator;
> ValueNode left = bcon.getLeftOperand();
> ValueNode right = bcon.getRightOperand();
> // RESOLVE: Consider using variant type of the expression,
> instead of
> // ConstantNode or ParameterNode in the future.
> if (left instanceof ColumnReference &&
> (right instanceof ConstantNode || right instanceof
> ParameterNode))
> {
> searchClauses.addElement(predicate);
> }
> else if (right instanceof ConstantNode && left instanceof
> ColumnReference)
> {
> // put the ColumnReference on the left to simplify things
> bcon.swapOperands();
> searchClauses.addElement(predicate);
> }
> continue;
> }
> Notice that the inner "else-if" condition is wrong. It's supposed to be
> checking to see if the right node is a ColumnReference and the left node is a
> Constant, but that's not what it does--instead, it does a check that is
> really a sub-condition of the "if" condition--i.e. whenever the "else if"
> condition is true the "if" condition will be true and thus we won't ever
> execute the "else if" branch.
> I confirmed this by looking at the code coverage results for 10.2:
> http://people.apache.org/~fuzzylogic/codecoverage/428586/_files/2f4.html#2d
> The lines in question are never executed.
> What this means is that a query which specifies constants on the *left* side
> of a comparison predicate will behave differently than a query which
> specifies constants on the *right* side of the same comparison. As an
> example:
> create table t1 (i int);
> create table t2 (j int);
> insert into t1 values 1, 5, 7, 11, 13, 17, 19;
> insert into t2 values 23, 29, 31, 37, 43, 47, 53;
> insert into t1 select 23 * i from t1 where i < 19;
> insert into t2 select 23 * j from t2 where j < 55;
> -- Following will show two qualifiers for T2 and three for T1
> -- because transitive closure adds two new qualifiers, "t2.j >= 23"
> -- and "t1.i <= 30" to the list.
> select * from t1, t2 where t1.i = t2.j and t1.i >= 23 and t2.j <= 30;
> -- But if we put the constants on the left-hand side, we don't
> -- detect the transitive closure and thus we have a single qualifier
> -- for T2 and only two qualifiers for T1.
> select * from t1, t2 where t1.i = t2.j and 23 <= t1.i and 30 >= t2.j;
> The above two queries should in theory show the same query plan--but if we
> execute the above statements while logging query plans, we'll see a
> difference (as explained in the sql comments above).
> I did a quick scan of the various branches and found that this incorrect
> logic appears in every branch back to 10.0 (hence the massive "Affects
> Versions" list). That said, the result of this bug isn't an error nor is it
> wrong results, so I'm just marking it "Minor".
> The fix looks to be pretty straightforward....
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
