Db Function bool return value bug

[Xor-el]

executing a query that utilizes Oracle Db functions which returns bool like JSON_EXISTS

var filterKey = "$.genre";
return await _db.Books.Where(x => !string.IsNullOrEmpty(x.Metadata) && OracleDbFunctions.JsonExists(x.Metadata, filterKey)).ToListAsync(cancellationToken);

generates the following SQL

SELECT "b"."Id", "b"."AuthorId", "b"."Metadata", "b"."Title"
FROM "Books" "b"
WHERE (("b"."Metadata" IS NOT NULL) AND (JSON_EXISTS("b"."Metadata", '$.genre') <> 0))

which throws ORA-00907: missing right parenthesis when run against the database.

manually rewriting the generated SQL by removing the <> 0 after JSON_EXISTS allows the query to execute successfully.

SELECT "b"."Id", "b"."AuthorId", "b"."Metadata", "b"."Title"
FROM "Books" "b"
WHERE (("b"."Metadata" IS NOT NULL) AND (JSON_EXISTS("b"."Metadata", '$.genre') ))

Negating the JSON_EXISTS Linq query

var filterKey = "$.genre";
return await _db.Books.Where(x => !string.IsNullOrEmpty(x.Metadata) && !OracleDbFunctions.JsonExists(x.Metadata, filterKey)).ToListAsync(cancellationToken);

generates the following SQL

SELECT "b"."Id", "b"."AuthorId", "b"."Metadata", "b"."Title"
FROM "Books" "b"
WHERE (("b"."Metadata" IS NOT NULL) AND (JSON_EXISTS("b"."Metadata", '$.genre') = 0))

which also throws ORA-00907: missing right parenthesis when run against the database.

manually rewriting the generated SQL by putting a NOT before JSON_EXISTS and removing the = 0 after JSON_EXISTS allows the query to execute successfully.

SELECT "b"."Id", "b"."AuthorId", "b"."Metadata", "b"."Title"
FROM "Books" "b"
WHERE (("b"."Metadata" IS NOT NULL) AND NOT (JSON_EXISTS("b"."Metadata", '$.genre') ))

I think this affects all Oracle Db functions that return a bool. been able to reproduce it on JSON_EXISTS.

Repro Project

https://github.com/Xor-el/EFCore-Oracle-Buggy-Demo/tree/json-exists-db-function-bug

Provider and version information

EF Core version: Oracle.EntityFrameworkCore 7.21.8 Database provider: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Target framework: .NET 6.0 Operating system: Windows 10 IDE: Visual Studio 2022 17.4.3

[Xor-el]

@alexkeh can you please acknowledge being able to reproduce this and my other reported bugs?

[alexkeh]

@Xor-el You filed your issues on a Saturday over a long holiday weekend in the United States. I have not had a chance to try out your test cases yet. If you need expedited service, please open a service request with My Oracle Support. My Oracle Support provides 24/7 support and can expedite filing bugs.

[Xor-el]

@alexkeh no rush, please take your time.

[alexkeh]

JSON is not currently supported in the Oracle EF Core 21.8 version. Oracle is planning to add this support soon.

This issue exists as a bug because the overarching enhancement has not been added. Bug 34989631 has been filed to track this issue. The parent enhancement request is #259.

[alexkeh]

This appears to be a DB bug that has been fixed in DB 23c, but not backported to DB 19c. The ODP.NET team has created bug 35998066 to request the DB server team backport the fix.

[alexkeh]

The ODP.NET team has completed its investigation and closed bug 34989631. The underlying root cause for this issue is the DB bug 35998066. As noted previously, this DB bug is fixed in DB 23c, but still requires a backport to DB 19c.