What is the best expression so that no row matches?

[MCMartin]

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:

Select * from Table where 1=2


Any better expression?

[André_Schild]

We usually use the form primarykey=null, but no idea if that is better than yours

[Breck_Carter]

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 🙂

[VolkerBarth]

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:)

[Former Member · Accepted solution]

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:

select rewrite( 'select * from rowgenerator R where 1=2 and row_num < 10 ')
-->
select R.row_num from rowgenerator as R where 1 = 0


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.

[VolkerBarth · Accepted solution]

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...

[VolkerBarth]

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

OR 1=0 is eliminated

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.