ORA-00918: column ambiguously defined

[Martin-Andersen]

Using Oracle 12C 12.1.0 with latest EF Core 3.1 provider

I am mapping to an existing user/schema/database (: and are not in charge of it.

I have a contact person that have a contact type.
I only have navigation to the ContactType

When I run this code

var contact = context.Contacts.Include(x=>x.Type).FirstOrDefault();

That produces this sql:

 fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (67ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
      SELECT "c"."ID", "c"."TYPE", "c"."CUSTOMER", "c"."EMAIL", "c"."NAME", "c"."PHONE", "c0"."ID", "c0"."NAME"
      FROM "SAMSON"."CUSTOMER_CONTACT" "c"
      INNER JOIN "SAMSON"."CUSTOMER_CONTACT_TYPE" "c0" ON "c"."TYPE" = "c0"."ID"
      FETCH FIRST 1 ROWS ONLY

I have no problems running the sql:
Is it an error, or are my relation mapping wrong?

SELECT "c"."ID", "c"."TYPE", "c"."CUSTOMER", "c"."EMAIL", "c"."NAME", "c"."PHONE", "c0"."ID", "c0"."NAME"
FROM "SAMSON"."CUSTOMER_CONTACT" "c"
INNER JOIN "SAMSON"."CUSTOMER_CONTACT_TYPE" "c0" ON "c"."TYPE" = "c0"."ID";

[bitdozer]

Is your Oracle platform older than 12c and perhaps not support FETCH FIRST. See:

https://stackoverflow.com/questions/56341445/entity-framework-core-take1-single-first-not-working-with-oracle-pr

The answer there suggests:

.UseOracle(connection_string, options => options
.UseOracleSQLCompatibility("11"))

[Martin-Andersen]

I am using Oracle 12C 12.1.0 with latest EF Core 3.1 provider
But funny enough I can fetch a list.
Any kind of first/firstordefault will fail. So I can't select a single item only lists

using var context = new SamDBContext(Builder.Options);
// this will fail
var contact1 = context.Contacts
    .Include(x => x.Type)
    .FirstOrDefault(x=>x.Id == 1);
// works
var contact2 = context.Contacts.Include(x=>x.Type).Take(1).ToList();
// works
var contacts = context.Contacts
    .Include(x=>x.Type)
    .ToList();
            
contacts.Should().HaveCountGreaterThan(1);
var contact = contacts.First();
contact.ContactTypeId.Should().NotBe(0);
contact.Type.Should().NotBeNull();
contact.Type.Name.Should().NotBeNull();

[bitdozer]

If you manually issue the query with the FETCH FIRST clause included does that work? Specifically:

SELECT "c"."ID", "c"."TYPE", "c"."CUSTOMER", "c"."EMAIL", "c"."NAME", "c"."PHONE", "c0"."ID", "c0"."NAME"
FROM "SAMSON"."CUSTOMER_CONTACT" "c"
INNER JOIN "SAMSON"."CUSTOMER_CONTACT_TYPE" "c0" ON "c"."TYPE" = "c0"."ID"
FETCH FIRST 1 ROWS ONLY

[Martin-Andersen]

@bitdozer Nope getting [42000][918] ORA-00918
But take(1) works.
There must be a bug somewhere

[bitdozer]

Possibly this Oracle issue with ambiguous columns names in the select:

https://stackoverflow.com/questions/57787579/oracle-sql-column-ambiguously-defined-with-fetch-first-n-rows-only

[Martin-Andersen]

@bitdozer so I can hardly blame the EF teams (-:
And it's also explain why everything was working when I was creating the schema with code first. I was using Oracle naming conventions provider that was creating PK names as: table + "_" + key = CONTACT_ID, CONTACT_TYPE_ID
I must admit that I see the db as an implementation detail. It should work and perform but I rarely write sql by hand so I don't care about "funny" names.

[bitdozer]

EF could work around this by aliasing the duplicate column names in SELECT, but I'm not familiar enough with the implementation to tell you if there is a way to force that. For example, I think this version of the query would run because the columns are aliased to unique names.

SELECT "c"."ID" as cID, "c"."TYPE", "c"."CUSTOMER", "c"."EMAIL", "c"."NAME" as cNAME, "c"."PHONE", "c0"."ID" as c0ID, "c0"."NAME" as c0NAME
FROM "SAMSON"."CUSTOMER_CONTACT" "c"
INNER JOIN "SAMSON"."CUSTOMER_CONTACT_TYPE" "c0" ON "c"."TYPE" = "c0"."ID"
FETCH FIRST 1 ROWS ONLY

[Martin-Andersen]

@bitdozer yes that will work. You need to only aliasing one name and one id.
Thanks for your help, really appreciate that

[alexkeh]

This is a known issue with 12.1 DB. From the Oracle EF Core README:

Oracle Database 12.1 has the following limitation: if the select list contains columns with identical names and you specify the 
row limiting clause, then an ORA-00918 error occurs. This error occurs whether the identically named columns are in the same table or in different tables.

Let us suppose that database contains following two table definitions:
SQL> desc X;
 Name    Null?    Type
 ------- -------- ----------------------------
 COL1    NOT NULL NUMBER(10)
 COL2             NVARCHAR2(2000)

SQL> desc Y;
 Name    Null?    Type
 ------- -------- ----------------------------
 COL0    NOT NULL NUMBER(10)
 COL1             NUMBER(10)
 COL3             NVARCHAR2(2000)

Executing the following LINQ, for example, would generate a select query which would contain "COL1" column from both the tables.  
Hence, it would result in error ORA-00918:
dbContext.Y.Include(a => a.X).Skip(2).Take(3).ToList();
This error does not occur when using Oracle Database 11.2, 12.2, and higher versions.

A code workaround is to use an alias name for the duplicate column name, which has been pointed out.

[Martin-Andersen]

@alexkeh Thanks for the detailed explanation.
I understand that you can use an alias, but try telling that to EF Core (-:

[alexkeh]

@Martin-Andersen Yes, you would have to issue your own SQL to do that rather than use LINQ. Sorry, I don't have a better solution.

[Martin-Andersen]

@alexkeh Will you work on a solution for this problem? You should be able to alias the columns with duplicated names

[alexkeh]

@Martin-Andersen There aren't really any good solutions.

EF Core doesn't have a way for providers to retrieve the DB server version. Thus, if ODP.NET EF Core generates aliases, it would have to make this change for all DB versions, not just 12.1. Now, I'm not sure if aliases can be used in this way in LINQ. I'll find out.

The SQL generated is actually correct. DB 12.1 has a known, documented issue.
If the select list contains columns with identical names and you specify the row_limiting_clause, then an ORA-00918 error occurs. This error occurs whether the identically named columns are in the same table or in different tables. You can work around this issue by specifying unique column aliases for the identically named columns.

This issue was fixed in 12.2, but was not backported to 12.1. Essentially, the "patch" is to move to DB 12.2 or higher.

[Martin-Andersen]

@alexkeh are you sure that it fixed?
https://www.oraexcel.com/database-oracle-18cR1-ORA-00918

Database: 18c Release 1
Error code: ORA-00918
Description: column ambiguously defined
Cause: An index name of the form [ identifier . ] identifier is expected but not present. If OIDINDEX clause, index name must be identifier
Action: Enter an appropriate index name.

Database: 12c Release 2
Error code: ORA-00918
Description: column ambiguously defined
Cause: An index name of the form [ identifier . ] identifier is expected but not present. If OIDINDEX clause, index name must be identifier
Action: Enter an appropriate index name.

Database: 19c Release 1
Error code: ORA-00918
Description: column ambiguously defined
Cause: An index name of the form [ identifier . ] identifier is expected but not present. If OIDINDEX clause, index name must be identifier
Action: Enter an appropriate index name.

[alexkeh]

@Martin-Andersen
Yes, the bug does not occur in DB 12.2 and higher as it does in DB 12.1. I was assured by the SQL dev team the recommended solution is to upgrade to 12.2 or higher if aliases can't be used.