The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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?

asked 21 Nov '14, 08:28

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

1

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

(22 Nov '14, 03:14) André Schild
Replies hidden

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

(24 Nov '14, 09:45) Breck Carter

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

(25 Nov '14, 06:51) Volker Barth

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.

permanent link

answered 24 Nov '14, 13:36

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

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

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.

permanent link

answered 24 Nov '14, 04:47

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×46
×23

question asked: 21 Nov '14, 08:28

question was seen: 405 times

last updated: 25 Nov '14, 07:49