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?
asked 21 Nov '14, 08:28
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.
answered 24 Nov '14, 13:36
Ivan T. Bowman
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.
answered 24 Nov '14, 04:47