Mittwoch, 10. Februar 2016

if you write SQL, be specific!

Today  I got a ticket from a developer where he claimed Oracle has a bug somewhere. The reason for this claim was a statement similar to
SELECT *
FROM   table1
WHERE  tab1col1 IN
       (      SELECT 
tab1col1 
              FROM   table2)


This query returned rows.

But when he run the inner query on it's own, he received
ORA-00904: "TAB1COL1": invalid identifier
00904. 00000 -  "%s: invalid identifier"
as there is no column TAB1COL1 in TABLE2.

So why should there be any result for the full query when part of it fails already? Is oracle somehow ignoring the query on table2 totally due to some errors in optimization?

After some research and a 10053 trace (yes, it was not obvious for me) I saw Oracle doing a Cost‑based predicate pushdown (JPPD) and translates it to something like

AND  EXISTS (SELECT 0 FROM "TABLE2" WHERE "TABLE1"."TAB1COL1"="TABLE1"."TAB1COL1")

With this hint (for me, not  a /*+ syntax thing) it was obvious.

So my proposal was to be more specific and use alias like this:

SELECT g1.*
FROM   table1 t1
WHERE  t1.tab1col1 IN
       (      SELECT t2.
tab1col1 
              FROM   table2 t2)


to generate the expected ORA-904
Kommentar veröffentlichen