ORA-00920 with Oracle 12, EfCore 2 and v9.5.483

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
seb1487
Posts: 23
Joined: Fri 24 Jun 2016 10:27

ORA-00920 with Oracle 12, EfCore 2 and v9.5.483

Post by seb1487 » Tue 03 Apr 2018 13:13

Hi,
dotConnect throws an error on an Query like this (Oracle 12):

Code: Select all

dbContext.Class1
                .Where(c1 =>
                    c1.ClassTwos
                        .Where(c2 => !c2.IsDeleted)
                        .SelectMany(c2 => c2.ClassThrees)
                        .Where(c3 =>
                            !c3.IsDeleted &&
                            !c3.ClassFours
                                .Any(c4 =>
                                    !c4.IsDeleted &&
                                    c4.Class5Id != null &&
                                    !c4.Class5.IsDeleted
                                )
                            )
                        .Select(c3 => c3.Class2.Class1Id)
                        .Contains(c1.Id)
                    )
                .ToList();
Error:
'ORA-00920: invalid relational operator'

Generated SQL:

Code: Select all

SELECT "c1"."Id", "c1"."IsDeleted", "c1"."Name"
FROM "Class1" "c1"
WHERE "c1"."Id" IN (
    SELECT "c2.ClassThrees.Class2"."Class1Id"
    FROM "Class2" "c2"
    INNER JOIN "Class3" "c2.ClassThrees" ON "c2"."Id" = "c2.ClassThrees"."Class2Id"
    LEFT JOIN "Class2" "c2.ClassThrees.Class2" ON "c2.ClassThrees"."Class2Id" = "c2.ClassThrees.Class2"."Id"
    WHERE (NOT ("c2"."IsDeleted") AND (NOT ("c2.ClassThrees"."IsDeleted") AND NOT EXISTS (
        SELECT 1
        FROM "Class4" "c4"
        LEFT JOIN "Class5" "c4.Class5" ON "c4"."Class5Id" = "c4.Class5"."Id"
        WHERE (((("c4"."IsDeleted" = 0) AND "c4"."Class5Id" IS NOT NULL) AND NOT ("c4.Class5"."IsDeleted")) = 1) AND "c2.ClassThrees"."Id" = "c4"."Class3Id"))) AND "c1"."Id" = "c2"."Class1Id"
)
Democode can be downloaded here.

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

Re: ORA-00920 with Oracle 12, EfCore 2 and v9.5.483

Post by Shalex » Wed 04 Apr 2018 17:42

Thank you for your report. We will investigate the issue and notify you about the result.

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

Re: ORA-00920 with Oracle 12, EfCore 2 and v9.5.483

Post by Shalex » Fri 25 May 2018 16:22

The bug with the "ORA-00920: invalid relational operator" error in the WHERE clause in EF Core 2 is fixed: viewtopic.php?f=1&t=37215.

Post Reply