Re: FULL JOIN oracle bug!

From: Stoyan <stoyanov007_at_hotmail.com>
Date: 8 Nov 2005 05:57:45 -0800
Message-ID: <1131458265.429139.91600@g44g2000cwa.googlegroups.com>

Please see that:
X:\>sqlplus hidb_sstoyanov/hidb_sstoyanov_at_test

SQL*Plus: Release 9.2.0.1.0 - Production on -= -es 8 15:51:43 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning and Data Mining options

SQL> CREATE OR REPLACE VIEW SIMPLE_VIEW AS   2 (
  3 SELECT

  4      'frist_column' as first_column,
  5      'second_column' as second_column

  6 FROM
  7 (

  8      SELECT
  9       dual.*
 10      FROM
 11       dual

 12 )
 13 );

View created.

--here the line is commented

SQL> SELECT
  2 *
  3 FROM
  4 (

  5      SELECT
  6        'column_1' AS column_1
  7      FROM
  8        dual

  9 ) t1
 10 FULL JOIN
 11 (

 12      SELECT
 13        'column_1' AS column_1
 14        --,first_column
 15        ,second_column
 16      FROM
 17        simple_view

 18 ) t2
 19 ON

 20     t1.column_1=t2.column_1;
      ,second_column
       *

ERROR at line 15:
ORA-00918: column ambiguously defined

--here the line is uncommented and the query works

SQL> SELECT
  2 *
  3 FROM
  4 (

  5      SELECT
  6        'column_1' AS column_1
  7      FROM
  8        dual

  9 ) t1
 10 FULL JOIN
 11 (

 12      SELECT
 13        'column_1' AS column_1
 14        ,first_column
 15        ,second_column
 16      FROM
 17        simple_view

 18 ) t2
 19 ON
 20 t1.column_1=t2.column_1;

COLUMN_1 COLUMN_1 FIRST_COLUMN SECOND_COLUMN
-------- -------- ------------ -------------
column_1 column_1 frist_column second_column

--Here we replace dual.* with * in the view
SQL> CREATE OR REPLACE VIEW SIMPLE_VIEW AS   2 (
  3 SELECT

  4      'frist_column' as first_column,
  5      'second_column' as second_column

  6 FROM
  7 (

  8      SELECT
  9       *
 10      FROM
 11       dual

 12 )
 13 );

View created.

--and the query that DIDN'T work now works
SQL> SELECT
  2 *
  3 FROM
  4 (

  5      SELECT
  6        'column_1' AS column_1
  7      FROM
  8        dual

  9 ) t1
 10 FULL JOIN
 11 (

 12      SELECT
 13        'column_1' AS column_1
 14        --,first_column
 15        ,second_column
 16      FROM
 17        simple_view

 18 ) t2
 19 ON
 20 t1.column_1=t2.column_1;

COLUMN_1 COLUMN_1 SECOND_COLUMN
-------- -------- -------------

column_1 column_1 second_column

That is! Received on Tue Nov 08 2005 - 07:57:45 CST