FULL JOIN oracle bug!

From: Stoyan <stoyanov007_at_hotmail.com>
Date: 8 Nov 2005 03:58:39 -0800
Message-ID: <1131451119.303021.6310@f14g2000cwb.googlegroups.com>

Hello all,
  Here is an example of a SQL query that doesn't work under ORACLE and it should work. The ORACLE version may be is 10g (I should ask our DB administrator)

First create that view:
--it is OK, it works fine
CREATE OR REPLACE VIEW simple_view AS
(
  SELECT
    'frist_column' as first_column,
    'second_column' as second_column
  FROM
(

    SELECT
     dual.*
    FROM
     dual
  )
)

Here is the query that should work but ORACLE reports "ORA-00918:column ambiguously" defined!

SELECT
  *
FROM
(

    SELECT
      'column_1' AS column_1
    FROM
      dual
  ) t1
  FULL JOIN
(

    SELECT

      'column_1' AS column_1
      --,first_column
      ,second_column --"ORA-00918:column ambiguously defined" reported!
    FROM
      simple_view

  ) t2
  ON
    t1.column_1=t2.column_1

If one makes one of the following modifications (or both) it works:

1. uncomment the commented line "--,first_column" in the "bad" query
2. replace the line "dual.*" in the view with "*"

Note that the problem disapears if FULL JOIN is replaced with INNER JOIN or JOIN. But it changes the query meaning while the modifications 1) and 2) doesn't the change the way query works.

1.Has someone had the problem described above? 2.Does someone know a way to report that bug to ORACLE and have a reasonable "official" explanation?

Thanks in advance

Stoyan Stoyanov Received on Tue Nov 08 2005 - 05:58:39 CST