Bug in Outer Join Logic, Oracle 9204?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Bug in Outer Join Logic, Oracle 9204?

  1. #1
    Join Date
    Mar 2006
    Posts
    74

    Bug in Outer Join Logic, Oracle 9204?

    why do the following queries return different results:

    Code:
    tableL
    LEFT OUTER JOIN
    tableR
    ON
      tableL.primarykey = tableR.primarykey AND
      tableR.colour = 'orange'
    Code:
    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:

    Code:
    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
    Code:
    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:

    Code:
    PK	LEF	PK	RIG
    1	square		
    2	circle		
    3	rectangle	3	red
    4	trangle	4	blue
    huhh???


    and second

    ]
    Code:
    PK	LEF	PK	RIG
    1	square		
    2	circle		
    3	rectangle	3	red
    4	trangle
    thats correct...


    huh???

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Works fine on Windows32 9.2.0.7.
    Does Metalink say anything about a bug? (Unfortunately I no longer have access).
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width