Derby
  1. Derby
  2. DERBY-4416

Handle comparison of two constants as a boolean constant

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.5.3.0
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Bug behavior facts:
      Performance

      Description

      In the lack of the boolean data type, Derby forces you to use expressions like 1=1 and 1<>1 to express true and false. Generated SQL statements also tend to use such expressions, and so does Derby in its own meta-data queries.

      Derby has many useful optimizations for boolean true/false. For instance, ProjectRestrictNode and PredicateList are able to eliminate predicates, and in some cases the entire ProjectRestrictNode, if the predicate contains constant true or false values. However, during parsing and compilation, expressions like 1=1 are not rewritten to TRUE, and we don't get any benefit from the boolean optimization code. This leads to more complex, and possibly less efficient, byte code being generated for the statements.

      Also, boolean constants are assigned a selectivity of 0.0 (false) or 1.0 (true), since they will always match no rows when false and all rows when true. The expression 1=1 does however get it's selectivity from the = operator, which means that it'll be 0.1. The same selectivity is assigned to 1=0. Other operators have different selectivity, so 2<3 has the selectivity 0.33, even though the actual selectivity of the expression is the same as 1=1 and TRUE, namely 1.0.

      This leads to oddities like the optimizer choosing a different plan when you change 2<3 to 1=1 in a WHERE clause. See http://mail-archives.apache.org/mod_mbox/db-derby-user/200909.mbox/%3c25531166.post@talk.nabble.com%3e for an example of that.

      If we could go through the query tree and replace occurrences of comparisons between constant values with a boolean constant at bind time, such queries would end up with simpler byte code, and the selectivity passed to the optimizer would be more accurate, possibly resulting in a better plan being chosen.

      1. replaceExpressions.diff
        6 kB
        Knut Anders Hatlen
      2. d4416-1a.diff
        17 kB
        Knut Anders Hatlen
      3. d4416-1a.stat
        0.6 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Knut Anders Hatlen created issue -
          Knut Anders Hatlen made changes -
          Field Original Value New Value
          Attachment replaceExpressions.diff [ 12422800 ]
          Knut Anders Hatlen made changes -
          Link This issue is blocked by DERBY-4421 [ DERBY-4421 ]
          Knut Anders Hatlen made changes -
          Assignee Knut Anders Hatlen [ knutanders ]
          Knut Anders Hatlen made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Knut Anders Hatlen made changes -
          Attachment d4416-1a.diff [ 12424863 ]
          Attachment d4416-1a.stat [ 12424864 ]
          Knut Anders Hatlen made changes -
          Issue & fix info [Patch Available]
          Knut Anders Hatlen made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Issue & fix info [Patch Available]
          Fix Version/s 10.6.0.0 [ 12313727 ]
          Resolution Fixed [ 1 ]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Workflow jira [ 12480042 ] Default workflow, editable Closed status [ 12800823 ]

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development