Re: Ambiguous column name error after installing new server

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 28 Jan 2008 12:20:29 -0800 (PST)
Message-ID: <907925f4-5445-423d-a5f1-8e87b84f6ef4@b2g2000hsg.googlegroups.com>

On Jan 28, 12:11 pm, Nicolas Avila <nicolasav..._at_gmail.com> wrote:
> On Jan 28, 12:07 pm, Michael O <cleveridea...._at_gmail.com> wrote:
>
>
> > On Jan 28, 8:02 am, Nicolas Avila <nicolasav..._at_gmail.com> wrote:
>
> > > Hello
>
> > > I was using an Orcale 9i server on one of my applications and it
> > > worked fine.
>
> > > Now I installed a new server and got installed an Oracle 11g
>
> > > I am getting errors in the application which I didn't get before on my
> > > old server.
>
> > > The rarest one is a 'column ambiguosly defined' error in some (actualy
> > > many) stored procedures.
>
> > > Due to many stored procedures do dinamiclly generate the where filter
> > > i can not just fix the ones that didn't compile.
>
> > > In the 'old' server I DID get many times this error, so I guess (?)
> > > that this error was not happening IF the query was written using ANSI
> > > joins...
>
> > > Now the questions:
> > > - Was this really so at oracle 9? Is this change reported? I mean to
> > > do NOT support ambiguous column-names in ANSI joins at ORACLE 11.
> > > - Is this maybe configurable? Can I configure the new server to work
> > > as my old one did?
>
> > Are the column names in your dynamic SQL *actually* ambiguous?  If
> > yes, either improve your stored procedures or keep using what
> > previously worked.  If no, then open an SR with Oracle.  Seems simple
> > enough to me.  Of course without knowing more, I am suspecting your
> > application is what has really changed and is now rejecting what
> > Oracle 11g is sending it even though it is the same as what 9i was
> > sending it.
>
> > Michael Ohttp://blog.crisatunity.com
>
> mmmmmmmmm...
> I will try to make it easier by using an example
> I have some store procedures with something like
> (....)
> open pCursor for
>    select tableA_id,
>              tableA_desc,
>              tableB_desc
>        from tableA inner join tableB on (tableA.tableB_id =
> tableB.tableB_id)
>      where tableB_id=5;
> (...)
>
> This stored procedure compiled and worked fine on my Oracle 9i, but it
> does not even compile on my 11g
> So... I fixed all those proceures by just adding the table name on the
> filters.
>
> BUT... there are other stored procedures which, in order to use
> dynamicly generated filters (based on column names), do something like
> -
> pFilters is a VARCHAR2 parameter
> vSelectStr is a local string variable
> -
> (...)
> vSelectStr:='   select tableA_id, ' ||
>                   '             tableA_desc,' ||
>                   '             tableB_desc' ||
>                   '       from tableA inner join tableB on
> (tableA.tableB_id = tableB.tableB_id)' ||
>                   '     where ' || pFilters || ';';
> open pCursor for vSelectStr;
> (...)
>
> And I can not know in which of the thousands of grids will this
> automatic filtering actualy raise this ambiguous column issue in any
> other way than running a complete regression test. What would take
> really long.
>
> So... if- Hide quoted text -
>
> - Show quoted text -

There was a bug on some versions of 9i that allowed bad code to run as the optimizer just assumed that the ambiguous column came from a specific table for a specific type of ANSI syntax join.

I do not have the bug number but I do have sample code that reproduces the problem.

UT1 >
UT1 > drop table parent1 ;

Table dropped.

UT1 >
UT1 > create table parent1 ( key number(4) );

Table created.

UT1 >
UT1 > insert into parent1 values ( 1 );

1 row created.

UT1 > insert into parent1 values ( 2 );

1 row created.

UT1 > insert into parent1 values ( 3 );

1 row created.

UT1 > insert into parent1 values ( 4 );

1 row created.

UT1 >
UT1 > drop table child1 ;

Table dropped.

UT1 >
UT1 > create table child1 ( key number(4), ambiguous_column varchar(1) );

Table created.

UT1 >
UT1 > insert into child1 values ( 1, 'A' );

1 row created.

UT1 > insert into child1 values ( 2, 'B' );

1 row created.

UT1 > insert into child1 values ( 3, null );

1 row created.

UT1 >
UT1 > drop table child2 ;

Table dropped.

UT1 >
UT1 > create table child2 ( ambiguous_column varchar(1), value number(4) );

Table created.

UT1 >
UT1 > insert into child2 values ( 'A', 1 );

1 row created.

UT1 > insert into child2 values ( 'Z', 26 );

1 row created.

UT1 >
UT1 > select * from parent1 ;

       KEY

---

         1
         2
         3
         4

UT1 > select * from child1 ;

       KEY A
---------- -

         1 A
         2 B
         3

UT1 > select * from child2 ;

A VALUE
- ----------

A          1
Z         26

UT1 >
UT1 > select key, ambiguous_column from parent1 left outer join child1 using (key)
  2 order by key ;

       KEY A
---------- -

         1 A
         2 B
         3
         4

UT1 >

UT1 > -- This is the query of interest
UT1 > select key, ambiguous_column from parent1 P1 left outer join child1 C1
  2 using (key) left outer join child2 C2   3 on ( C1.ambiguous_column = C2.ambiguous_column ) order by key ;

       KEY A
---------- -

         1 A
         2
         3
         4

UT1 >
UT1 > select key, C1.ambiguous_column from parent1 P1 left outer join child1 C1
  2 using (key) left outer join child2 C2   3 on ( C1.ambiguous_column = C2.ambiguous_column ) order by key;

       KEY A
---------- -

         1 A
         2 B
         3
         4

UT1 >
UT1 > spool off

Failure to properly identify column source tables can lead to incorrect query results depending on the data. I posted this to metalink back in 2004 but I do not remember if support responded with a bug number. I failed to record it if they did.

HTH -- Mark D Powell -- Received on Mon Jan 28 2008 - 14:20:29 CST