Derby
  1. Derby
  2. DERBY-2282

Incorrect "transitive closure" logic leads to inconsistent behavior for binary comparison predicates.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 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.3.3, 10.2.1.6, 10.2.2.0, 10.2.2.1, 10.3.1.4
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      Known fix, Repro attached

      Description

      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....

      1. d2282-1a.diff
        12 kB
        Knut Anders Hatlen
      2. d2282-1a.stat
        0.5 kB
        Knut Anders Hatlen
      3. d2282-2a.diff
        4 kB
        Knut Anders Hatlen
      4. d2282-2a.stat
        0.2 kB
        Knut Anders Hatlen
      5. test.diff
        5 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Gavin made changes -
          Workflow jira [ 12395570 ] Default workflow, editable Closed status [ 12799863 ]
          Kathey Marsden made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Dag H. Wanvik made changes -
          Affects Version/s 10.1.3.3 [ 12313478 ]
          Affects Version/s 10.1.4.0 [ 12311950 ]
          Dag H. Wanvik made changes -
          Affects Version/s 10.2.3.0 [ 12312215 ]
          Knut Anders Hatlen made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Issue & fix info [Repro attached, Patch Available, Known fix] [Known fix, Repro attached]
          Fix Version/s 10.6.0.0 [ 12313727 ]
          Resolution Fixed [ 1 ]
          Knut Anders Hatlen made changes -
          Issue & fix info [Repro attached, Known fix] [Known fix, Patch Available, Repro attached]
          Knut Anders Hatlen made changes -
          Attachment d2282-2a.stat [ 12427694 ]
          Attachment d2282-2a.diff [ 12427695 ]
          Knut Anders Hatlen made changes -
          Issue & fix info [Repro attached, Patch Available, Known fix] [Known fix, Repro attached]
          Knut Anders Hatlen made changes -
          Link This issue is duplicated by DERBY-813 [ DERBY-813 ]
          Knut Anders Hatlen made changes -
          Issue & fix info [Repro attached, Known fix] [Known fix, Patch Available, Repro attached]
          Knut Anders Hatlen made changes -
          Attachment d2282-1a.stat [ 12426663 ]
          Attachment d2282-1a.diff [ 12426664 ]
          Knut Anders Hatlen made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Knut Anders Hatlen made changes -
          Assignee Knut Anders Hatlen [ knutanders ]
          Knut Anders Hatlen made changes -
          Attachment test.diff [ 12417731 ]
          Knut Anders Hatlen made changes -
          Bug behavior facts [Wrong query result]
          Issue & fix info [Repro attached] [Known fix, Repro attached]
          Rick Hillegas made changes -
          Field Original Value New Value
          Bug behavior facts [Wrong query result]
          Urgency Normal
          Issue & fix info [Repro attached]
          A B created issue -

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              A B
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development