"ORA-00936: missing expression" on 9.6.570

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
erolg
Posts: 2
Joined: Mon 27 Aug 2018 05:34

"ORA-00936: missing expression" on 9.6.570

Post by erolg » Mon 27 Aug 2018 06:46

Hi,

the ef core linq expression

Code: Select all

dbContext.Class2.Where(two => 
                    two.Test && 
                    two.Threes.Any(three => three.Test && !three.IsDeleted ))
                .Select(o =>
                    new { 
                        o.OneId, 
                        NoFours = o.Threes.Any(three => 
                            three.Test &&
                            !three.IsDeleted &&
                            !three.Fours.Any(four =>
                                    !four.IsDeleted &&
                                    four.Test &&
                                    four.FiveId != null &&
                                    !four.Five.IsDeleted))})
                .ToList()
results to the generated sql

Code: Select all

SELECT "two"."OneId", CASE
    WHEN (
        SELECT CASE
            WHEN EXISTS (
                SELECT 1
                FROM "Class3" "three0"
                WHERE ((("three0"."Test" = 1) AND ("three0"."IsDeleted" = 0)) AND NOT EXISTS (
                    SELECT 1
                    FROM "Class4" "four"
                    LEFT JOIN "Class5" "four.Five" ON "four"."FiveId" = "four.Five"."Id"
                    WHERE (((("four"."IsDeleted" = 0) AND "four"."Test") AND "four"."FiveId" IS NOT NULL) AND ("four.Five"."IsDeleted" = 0)) AND "three0"."Id" = "four"."Class3Id")) AND "two"."Id" = "three0"."TwoId")
            THEN 1 ELSE 0
        END
        FROM DUAL
    ) = True
    THEN 1 ELSE 0
END AS "NoFours"
FROM "Class2" "two"
WHERE ("two"."Test" = 1) AND EXISTS (
    SELECT 1
    FROM "Class3" "three"
    WHERE (("three"."Test" = 1) AND ("three"."IsDeleted" = 0)) AND "two"."Id" = "three"."TwoId")
which throws the error "ORA-00936: missing expression". There are two errors in the statement.

First error in line 11:

WHERE (((("four"."IsDeleted" = 0) AND "four"."Test") AND "four"."FiveId" IS NOT NULL) AND ("four.Five"."IsDeleted" = 0)) AND "three0"."Id" = "four"."Class3Id")) AND "two"."Id" = "three0"."TwoId")

should be

WHERE (((("four"."IsDeleted" = 0) AND ("four"."Test" = 1)) AND "four"."FiveId" IS NOT NULL) AND ("four.Five"."IsDeleted" = 0)) AND "three0"."Id" = "four"."Class3Id")) AND "two"."Id" = "three0"."TwoId")


Second error in line 15:

FROM DUAL
) = True
THEN 1 ELSE 0

should be

FROM DUAL
) = 1
THEN 1 ELSE 0

Democode can be downloaded here

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

Re: "ORA-00936: missing expression" on 9.6.570

Post by Shalex » Tue 28 Aug 2018 17:03

Thank you for your report. We have reproduced the issue and will notify you when it is fixed.

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

Re: "ORA-00936: missing expression" on 9.6.570

Post by Shalex » Wed 07 Nov 2018 18:38

* The bug with throwing "ORA-00936: missing expression" in EF Core is fixed.
* The bug with using parentheses in binary expressions in EF Core is fixed.

We will notify you when the new public build of dotConnect for Oracle is available for download.

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

Re: "ORA-00936: missing expression" on 9.6.570

Post by Shalex » Wed 07 Nov 2018 18:42

Nuget package with the fix can be downloaded from https://www.devart.com/pub/nuget_oracle_9_6_630.zip.

Post Reply