WHERE (1 = 0)

Came across an interesting issue today on the iSeries ... if you check the detailed jas logs on a web dev client you'll find SQL statements like SELECT * FROM PY900DTA.F0911 WHERE (1 = 0). These are not, obviously, from application code, but the tool as it's collecting metadata about tables.

I noticed these queries taking quite a long time on large tables. The VisualExplain on this query shows it doing a table scan!

According to Oracle, these queries should return instantly, and the issue is with IBM. I was wondering if someone else could run a similar query on a different DB and let me know the query plan.

thanks,
Craig

[ QUOTE ]
Came across an interesting issue today on the iSeries ... if you check the detailed jas logs on a web dev client you'll find SQL statements like SELECT * FROM PY900DTA.F0911 WHERE (1 = 0). These are not, obviously, from application code, but the tool as it's collecting metadata about tables.

I noticed these queries taking quite a long time on large tables. The VisualExplain on this query shows it doing a table scan!

According to Oracle, these queries should return instantly, and the issue is with IBM. I was wondering if someone else could run a similar query on a different DB and let me know the query plan.

thanks,
Craig

[/ QUOTE ]

I ran the statement on a couple of SQL systems and got "Invalid object name 'PY900DTA.F0911'."

Just kidding. The appropriate SQL Server code is

<font class="small">Code:</font><hr /><pre>USE JDE_CRP
GO
SELECT * FROM CRPDTA.F0911 WHERE (1 = 0)
</pre><hr />

It ran quickly.


BTW, DB2 is apparently smart enough to not do a full scan on a known false query:

http://bytes.com/topic/db2/answers/183541-does-select-1-mytable-where-1-0-do-table-scan


If there are DB2 tools to confirm this (similar to SQL Server Profiler) you could determine whether it is doing a table scan or not. Given that we are talking about a table that typically has a lot of records my guess is that it is indeed doing a full scan for some reason.

Thanks Brother,

Yes, it does do a table scan. But, there appears to be a way to make it smarter ...

Adding the setting IGNORE_DERIVED_INDEX *YES to the QAQQINI in QUSRSYS.

This allows the query to use the SQE optimizer which knows that 1 will never = 0. The strange thing is the Explain (like SQL profiler) still shows a table scan, but the resulting query returns quickly.

[ QUOTE ]
Thanks Brother,

Yes, it does do a table scan. But, there appears to be a way to make it smarter ...

Adding the setting IGNORE_DERIVED_INDEX *YES to the QAQQINI in QUSRSYS.

This allows the query to use the SQE optimizer which knows that 1 will never = 0. The strange thing is the Explain (like SQL profiler) still shows a table scan, but the resulting query returns quickly.

[/ QUOTE ]

SQL's execution plan says that it's doing a Constant Scan.