SQL Fascination

I came across this problem and I still can’t find any documented reason as to why the Oracle query processor can not handle mixing the join types in a statement. The reason I was mixing join types at the time was due to an ORA-600 bug involving nested table types; the cross join to the nested table field was not working properly. This resulted in me having to use a combination of ANSI joins (which is my default approach to joins for many years) and the old style join using a comma and the where clause.

So a quick look at the docs : http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm – the query_block::= section is what interests me here.

Now for me, the train tracks here show a syntax fault. The loop back to add another join shows a comma on it which is not required depending on the route taken after the FROM part of the statement. technically, that would end up with a FROM clause like this:

FROM tbl_a, INNER JOIN tbl_b on tbl_a.field = tbl_d.field

We know that is just not legal syntax – so we can already consider this train track diagram a bit off. Am I reading the diagram incorrectly? http://docs.oracle.com/cd/B28359_01/server.111/b28286/ap_syntx.htm#SQLRF018 is the guide to reading them, and the syntax loops section hows an example where fields are seperated by commas in a loop. Optional keywords and parameters show that if the comma was optional, it would appear differently in the diagram.

Using the diagram we should also be able to use this syntax:
select *
FROM tbl_a a, tbl_b b
INNER JOIN tbl_c c on c.field1 = a.field1
WHERE c.field2 = b.field2

This again fails – although the error thrown is correct, but not as helpful as you might like.
ORA-00904: "A"."FIELD1": invalid identifier
00904. 00000 - "%s: invalid identifier"

So the error is basically saying that the query processor can not find a reference to A.FIELD1, even though it is clearly there in the FROM clause. This leads me to assume that it processes ANSI joins first, and that the old style joins using a where clause are processed second. At the time that the ANSI join is processed it has no way of knowing A.FIELD1 does indeed exist.

Could the error message be better? most definitely, if you tried the same but using an old style left join, like this:
select *
FROM tbl_a a, tbl_b b
INNER JOIN tbl_c c on c.field1 = a.field1
WHERE c.field2 (+)= b.field2

Oracle will kindly inform you that ORA-25156: old style outer join (+) cannot be used with ANSI joins – so the query processor is quite aware of the distinction between the join styles. Just to add to the frustration, re-ordering the joins allows it to work:
select *
FROM tbl_b b, tbl_a a
INNER JOIN tbl_c c on c.field1 = a.field1
WHERE c.field2 = b.field2

This is because the ANSI join processed sees this as tbl_a inner join tbl_c, so tbl_a is ‘visible’ to the query processor again.

This is just an annoying bit of behaviour by the query processor with bad error messaging and it seems flawed documentation – to be fair, if I was not forced to mix join styles due to the ORA-600, I wouldn’t have come across it either.