Hi,
Please bear with me as this is my first post in the community - feel free to chastise me for any protocol violations:-)
As an avid fan of the "WITH" construct, I use it for many purposes from simple to complex queries as it provides much more structure and clarity than your garden variety "spaghetti SQL" statement. Much to my surprise when developing a set of new queries recently, I ran into an inexplicable error message using the "WITH" construct - I would venture to say perhaps a bug, but I will let you be the judge of that (if you wish to "cut to the chase" please scroll down to example 7).
Let me explain:
In a simple WITH query, the main query which references one or more of the subs, may refer to all, some or even none of the sub-queries defined in the WITH clause. (although writing sub-queries which aren't at all referred to admittedly makes little sense). Consider for example:
Example 1:
WITH
sub1 AS (SELECT 'A' COL FROM dual),
sub2 AS (SELECT 'B' COL FROM dual)
SELECT 'C' COL FROM dual;
This query returns as one would expect "C". The 2 sub-queries are valid but of course quite meaningless as they are never referenced. In example 2 we refer to the second of the 2 sub-queries which also works fine and as expected returns "B" :
Example 2:
WITH
sub1 AS (SELECT 'A' COL FROM dual),
sub2 AS (SELECT 'B' COL FROM dual)
SELECT * FROM sub2;
...and of course, both could be referenced which would be more meaningful:
Example 3:
WITH
sub1 AS (SELECT 'A' COL FROM dual),
sub2 AS (SELECT 'B' COL FROM dual)
(SELECT * FROM sub1) UNION ALL (SELECT * FROM sub2);
...As expected this returns the UNION of the 2 results:
COL
A
B
So far so good - now let's get fancy and introduce a sub3 which INNER JOIN's sub1 and sub2 :
Example 4:
WITH
sub1 AS (SELECT 'A' COL FROM dual),
sub2 AS (SELECT 'B' COL FROM dual),
sub3 AS (SELECT sub1.COL col1, sub2.COL col2 FROM sub1 INNER JOIN sub2 ON sub1.COL = sub2.COL)
SELECT * FROM sub3;
The main query calls sub3 which as expected returns no rows as "A"<>"B"
I can also call one of the first 2 subqueries which as expected works fine - in this case calling sub1 returns "A":
Example 5:
WITH
sub1 AS (SELECT 'A' COL FROM dual),
sub2 AS (SELECT 'B' COL FROM dual),
sub3 AS (SELECT sub1.COL col1, sub2.COL col2 FROM sub1 INNER JOIN sub2 ON sub1.COL = sub2.COL)
SELECT * FROM sub1;
Now let's do the same with an LEFT OUTER JOIN - referencing the sub with the OUTER JOIN (sub3) works as expected and returns "A" in COL1 as this is the "left" side of the join and nothing in COL2 also as expected:
Example 6:
WITH
sub1 AS (SELECT 'A' COL FROM dual),
sub2 AS (SELECT 'B' COL FROM dual),
sub3 AS (SELECT sub1.COL col1, sub2.COL col2 FROM sub1 LEFT OUTER JOIN sub2 ON sub1.COL = sub2.COL)
SELECT * FROM sub3;
HOWEVER if I refer to sub1 or sub2:
Example 7:
WITH
sub1 AS (SELECT 'A' COL FROM dual),
sub2 AS (SELECT 'B' COL FROM dual),
sub3 AS (SELECT sub1.COL col1, sub2.COL col2 FROM sub1 LEFT OUTER JOIN sub2 ON sub1.COL = sub2.COL)
SELECT * FROM sub2;
I get ORA-01762: vopdrv: view query block not in FROM
You could argue that example 7 does not make sense since I am not really using sub3 for anything, however the reason I bring this up is that I am working with an application that auto-generates WITH query syntax, and these are used by other functionality which need to have the option of "using" any or all of the WITH sub-queries, in which case this becomes an issue.
Experimenting with various other combinations of sub-queries with and without OUTER JOIN's, it seems that the issue always revolves around situations in which an OUTER JOIN is present in a sub-query that is not referenced directly or indirectly. As a final example, in this query having 4 subs, where sub3 JOINS sub1 and sub2, is referenced by sub 4 and where the main query references sub4, all works as expected - the OUTER JOIN query was happy to be useful and therefore did not "throw a fit" :
Example 8:
WITH
sub1 AS (SELECT 'A' COL FROM dual),
sub2 AS (SELECT 'B' COL FROM dual),
sub3 AS (SELECT sub1.COL col1, sub2.COL col2 FROM sub1 LEFT OUTER JOIN sub2 ON sub1.COL = sub2.COL),
sub4 AS (SELECT * FROM sub3)
SELECT * FROM sub4;
The above examples were reproduced in 11g Enterprise Edition Release 11.2.0.1.0 - 64bit.
My question: is Example 7 expected behavior or is it a bug?
Any comments would be welcome.
Thanks,
Dennis