[
https://issues.apache.org/jira/browse/DERBY-4416?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-4416:
--------------------------------------
Issue & fix info: [Patch Available]
All the regression tests ran cleanly.
> 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
> Assignee: Knut Anders Hatlen
> Priority: Minor
> Attachments: d4416-1a.diff, d4416-1a.stat, 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/%[email protected]%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.
