[jira] Commented: (DERBY-4416) Handle comparison of two constants as a boolean constant

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

    [ https://issues.apache.org/jira/browse/DERBY-4416?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12768213#action_12768213 ] 

Knut Anders Hatlen commented on DERBY-4416:
-------------------------------------------

The visitor and the methods in the patch could probably have more general names, since they could be used to all sorts of expression-to-constant transformations.

So instead of the ValueNode.getBooleanEquivalent() method in the current patch, we could have a ValueNode.getConstantEquivalent(). The override in BinaryRelationalOperatorNode would return a boolean constant like in the patch. In addition, possibly as part of separate issues later, overrides could be added to other nodes, like in BinaryArithmeticOperatorNode to return the result of arithmetic operations as a (non-boolean) constant.

Currently, since the accept() methods call visit() on the parent node before the children, the patch is not able to fully rewrite expressions like ((1=1) = (3=3)) to true (well, this expression was probably disallowed by the recent DERBY-887 commit, but it will probably be allowed again when the boolean data type is reintroduced). The rewriting stops at (true = true). Making BinaryRelationalOperatorNode.getBooleanEquivalent() call getBooleanEquivalent() on the left operand and the right operand before checking whether they are constant nodes, should fix this, I think.

Adding an override in IsNullNode to handle IS NULL/IS NOT NULL should be fairly trivial too, I think.

> Handle comparison of two constants as a boolean constant
> --------------------------------------------------------
>
>                 Key: DERBY-4416
>                 URL: https://issues.apache.org/jira/browse/DERBY-4416
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Priority: Minor
>         Attachments: replaceExpressions.diff
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.