I have a select statement which is constructed program internally. Under certain circumstances I need this statement to return no rows at all. Which condition expression will be the best for that purpose? Currently I use:
Any better expression? |
Volker is correct, 1=0 is recognized as a contradiction early on during semantic transformations. Recognizing the contradiction early reduces further optimization effort and the optimizer will select a plan with a PreFilter node. There can be several good reasons for using this type of construct. In addition to getting a cursor with the right "shape", this trick can also be used with a SELECT INTO to generate a temporary table with a desired column structure but without including any rows. When simplifying a more complex query, the 1=0 can be used in branches of a UNION to eliminate entire branches quickly. You can see how semantic transformations eliminate other predicates once a contradiction is detected:
Notice that the row_num < 10 has been removed and the 1=2 was replaced by 1=0. The rewrite() function applies semantic transforms that are done on the parse tree only, before data types are fully established. For example, the following is not simplified "... where 1.0 = 2". The data types do not match and semantic transforms in the parse tree do not simplify this expression. It is simplified in later stages of query optimization. So, I would suggest that many simple contradictions could be used but Volker's answer (1=0) is the one the semantic transforms generate as a canonical contradiction. 1
A great explanation, and thanks for the pointer to the REWRITE function that helps us to find out about these optimizations ourselves - I tend to forget that nice helper function...
(25 Nov '14, 06:54)
Volker Barth
|
According to the "Query Processing Based on SQL Anywhere 12.0.1 Architecture whitepaper by Ani Nica, as referenced in this doc page, the condition
so I would think a condition 1=0 would certainly be recognized by the optimizer as a contradiction. I can't tell whether 1=2 are recognized as well. |
We usually use the form primarykey=null, but no idea if that is better than yours
Here's a WAG: Find out what predicate is used by Crystal Reports for the same purpose, and use that one... over the years Crystal Reports has been the cause of many [cough] optimization irritations for SQL Anywhere and this may be one of the solved cases :)
FWIW, a condition like "primary_key_column is null" seems to be rewritten to the "canonical contradiction 1 = 0", as well.
"primary_key_column = null" will not, for obvious reasons:)