[
https://issues.apache.org/jira/browse/DERBY-4416?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-4416:
--------------------------------------
Attachment: replaceExpressions.diff
Attached is a patch (not for commit) that I've experimented with. It adds a
visitor that replaces (some) expressions that are guaranteed to evaluate to
true or false with a boolean constant node. This visitor is used on the WHERE
clauses and HAVING clauses from SelectNode.normExpressions() and on the ON
clauses in JoinNode.normExpressions().
I've seen that for instance this statement
SELECT * FROM T WHERE 1=1
is translated into a ProjectRestrictResultSet on top of a table scan without
the patch, and simply to a table scan with the patch.
I tested it with suites.All and derbyall, and saw three failures:
1) lang/outerjoin.sql: Changed query plans. Looks like the expected changes, in
that some ProjectRestrictResultSets are eliminated, but will have to check to
be sure.
2) lang/specjPlans.sql: Changed query plans. These also look benign, but will
have to check.
3) lang/logop.sql: Query that previously failed now succeeds and returns no
rows. Expected output is below, and judging by the comment in the test, it
looks as if the empty result that was actually supposed to be the expected one.
The reason why it stops failing, is that (1=2) is rewritten to false and
PredicateList.restorePredicates() is therefore able eliminate the other half of
the AND node (2147483647 + 10 = 2) that causes integer overflow.
ij> -- ... and false and ... should get resolved to false
select x from s where 2147483647 + 10 = 2 and (1=2);
ERROR 22003: The resulting value is outside the range for the data type INTEGER.
Now:
ij> -- ... and false and ... should get resolved to false
select x from s where 2147483647 + 10 = 2 and (1=2);
X
-----------
> 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/%[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.
