ORA-00918: column ambiguously defined while joining tables with duplicate column names

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Butzei
Posts: 12
Joined: Fri 26 May 2017 07:41

ORA-00918: column ambiguously defined while joining tables with duplicate column names

Post by Butzei » Tue 30 May 2017 08:53

Hi,

I have another problem while moving from NHibernate to DotConnect with EfCore. We have DotConnect 9.4.280 running against an existing Oracle 11g Database.

Now I have trouble with joining some entities. I have a ReportBox with one to one mapping to a ReportVersion, which belongs to one Report. So one ReportBox has one ReportVersion with one Report.

If I try to get all of these together I get an ORA-00918: column ambiguously defined. As you can see in the following SQL query some columns on different tables have the same name, but aren't renamed in the query.

Code: Select all

SELECT "x".ReportBoxId,
       ...
       "x".ReportVersionId,
       "x.reportversion".ReportVersionId,
       "x.reportversion".ReportBoxId,
       "x.reportversion".ReportId,
       ...
       "x.reportversion.report".ReportId,
       ...
FROM   TblReportBox "x"
       LEFT JOIN TblReportVersion "x.reportversion"
              ON "x".ReportBoxId = "x.reportversion".ReportBoxId 
       LEFT JOIN TblReport "x.reportversion.report"
              ON "x.reportversion".ReportId = "x.reportversion.report".ReportId
WHERE  EXISTS
       ( SELECT 1 FROM   TblReportBoxSequence "s"
         WHERE  ( "s".berichtboxsequenzid = :p__boxSequenceId_0 ) AND  ( "x".ReportBoxId = "s".ReportBoxId) )
ORDER  BY "x".ReportBoxId,"x.reportversion".ReportBoxId
FETCH first 2 ROWS only 
Did I miss any configuration?

Sincerely,
Butzei

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Post by Shalex » Wed 31 May 2017 16:38

Please upload a small test project with the corresponding DDL/DML script for reproducing to our ftp server (ftp://ftp.devart.com/, credentials: anonymous/anonymous ) or to any file exchange server so that we could download it from there.

Butzei
Posts: 12
Joined: Fri 26 May 2017 07:41

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Post by Butzei » Thu 01 Jun 2017 13:29

@Shalex: I uploaded a "Butzei_Devart.Demo.zip" containing a example solution to your server. Thank you for your investigations.
It's in the same example as viewtopic.php?f=1&t=35470

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Post by Shalex » Fri 02 Jun 2017 16:11

Thank you for the test project. Here is SQL generated by your code (option 1):

Code: Select all

SELECT "x".NAME, "x".PARENTID, "x".POSITION, "x.Parent".NAME
FROM CHILD "x"
INNER JOIN PARENT "x.Parent" ON "x".PARENTID = "x.Parent".PARENTID
WHERE ROWNUM <= 2
It works without any errors.

1. Please specify SQL generated in your environment with the test application.
2. Tell us the version and edition of your Oracle Server.

Butzei
Posts: 12
Joined: Fri 26 May 2017 07:41

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Post by Butzei » Tue 06 Jun 2017 07:17

Thank you shalex the query is exactly the same. My oracle version is 12.1.0.2.0 Standard Edition.
I just noticed that the query works if I try it using SQL Developer. In the output table "x.Parent".NAME is named "NAME_1".
Do you know any way to configure this automatic renaming? I can't find anything like that. All working solutions to this problem i found contain manual renaming using the AS keyword.

rambam
Posts: 1
Joined: Thu 08 Jun 2017 11:38

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Post by rambam » Thu 08 Jun 2017 11:50

You can simply use aliases for each column, or simply for the one ambiguously defined column. Like this:

Code: Select all

select cld.name  child_name
,      pnt.name  parent_name
from          t_child cld
   inner join t_parent pnt
           on cld.parent_id = pnt.parent.id
which is basically the same as using AS, as you already mentioned.. And now I am slowly waking up... you're talking about a query that DevArt generated for you, right? Then, I cannot help you. Know a lot about Oracle queries, but almost nothing about DevArt.

Butzei
Posts: 12
Joined: Fri 26 May 2017 07:41

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Post by Butzei » Fri 09 Jun 2017 14:25

Hi rambam, nice to see you waking up. You definitely made my day :-D Thanks for your effort

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Post by Shalex » Fri 09 Jun 2017 20:15

Butzei wrote:My oracle version is 12.1.0.2.0 Standard Edition.
Thank you for the additional information. We have reproduced the "ORA-00918: column ambiguously defined" error. We will notify you when it is fixed.


Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Post by Shalex » Wed 28 Jun 2017 12:05

The bug with throwing "ORA-00918: column ambiguously defined" while joining tables with duplicate column names in Oracle 12c when using EF Core is fixed: viewtopic.php?f=1&t=35592.

Post Reply