Bug in Outer Join Logic, Oracle 9204?

[cjard]

why do the following queries return different results:


tableL
LEFT OUTER JOIN
tableR
ON
tableL.primarykey = tableR.primarykey AND
tableR.colour = 'orange'



tableL
LEFT OUTER JOIN
tableR
ON
tableL.primarykey = tableR.primarykey AND
'orange' = tableR.colour


the order of the truths in the ON clause matters! can anyone explain why?


try for yourself:



select *
from
(
select '1' as pk, 'square' as lef from dual union all
select '2' as pk, 'circle' as lef from dual union all
select '3' as pk, 'rectangle' as lef from dual union all
select '4' as pk, 'trangle' as lef from dual
) tableL
LEFT OUTER JOIN
(
select '3' as pk, 'red' as rig from dual union all
select '4' as pk, 'blue' as rig from dual union all
select '5' as pk, 'red' as rig from dual union all
select '6' as pk, 'blue' as rig from dual
) tableR
ON
tableL.pk = tableR.pk AND
'red' = tableR.rig



select *
from
(
select '1' as pk, 'square' as lef from dual union all
select '2' as pk, 'circle' as lef from dual union all
select '3' as pk, 'rectangle' as lef from dual union all
select '4' as pk, 'trangle' as lef from dual
) tableL
LEFT OUTER JOIN
(
select '3' as pk, 'red' as rig from dual union all
select '4' as pk, 'blue' as rig from dual union all
select '5' as pk, 'red' as rig from dual union all
select '6' as pk, 'blue' as rig from dual
) tableR
ON
tableL.pk = tableR.pk AND
tableR.rig = 'red'


results for me with first query:


PK LEF PK RIG
1 square
2 circle
3 rectangle 3 red
4 trangle 4 blue


huhh???


and second

]
PK LEF PK RIG
1 square
2 circle
3 rectangle 3 red
4 trangle


thats correct...


huh???

[DaPi]

Works fine on Windows32 9.2.0.7.
Does Metalink say anything about a bug? (Unfortunately I no longer have access).