Db Function json path parameter bug

[Xor-el]

executing a query that utilizes Oracle Db JSON functions like JSON_VALUE

var filterKey = "$.description";
return await (
    from book in _db.Books
    let metadata = book.Metadata
    where !string.IsNullOrEmpty(metadata)
    let description = OracleDbFunctions.JsonValue(metadata, filterKey)
    where !string.IsNullOrEmpty(description) && description.Contains("Harry")
    select book
).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_VALUE("b"."Metadata", N'$.description') IS NOT NULL) AND (JSON_VALUE("b"."Metadata", N'$.description') LIKE N'%Harry%')))

which throws ORA-40442: JSON path expression syntax error ('') when run against the database.

I think this affects all Oracle Db JSON functions that take a path. been able to reproduce it on JSON_VALUE, JSON_EXISTS, and JSON_QUERY.

manually rewriting the generated SQL by removing the N before the JSON path '$.description' 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_VALUE("b"."Metadata", '$.description') IS NOT NULL) AND (JSON_VALUE("b"."Metadata", '$.description') LIKE N'%Harry%')))

Workaround

I can work around this by rewriting the second parameter of the Db function in my OnModelCreating of DbContext when configuring the HasDbFunction for the specific Db Function as a constant string.

Original code

var jsonValueMethodInfo = typeof(OracleDbFunctions).GetMethod(    
    nameof(OracleDbFunctions.JsonValue),
    new[] { typeof(string), typeof(string) }
)!;

builder
    .HasDbFunction(jsonValueMethodInfo)
    .HasTranslation(
        args =>
        {
            return new SqlFunctionExpression(
                functionName: "JSON_VALUE",
                arguments: args,
                nullable: true,
                argumentsPropagateNullability: new[] { false, false },
                type: jsonValueMethodInfo.ReturnType,
                typeMapping: null
            );
        }
    );

Workaround code

var jsonValueMethodInfo = typeof(OracleDbFunctions).GetMethod(
    nameof(OracleDbFunctions.JsonValue),
    new[] { typeof(string), typeof(string) }
)!;

builder
    .HasDbFunction(jsonValueMethodInfo)
    .HasTranslation(
        args =>
        {
            var newArgs = args.ToList();
            newArgs[1] = new SqlFragmentExpression(
                $"'{(newArgs[1] as SqlConstantExpression)?.Value as string}'"
            );

            return new SqlFunctionExpression(
                functionName: "JSON_VALUE",
                arguments: newArgs,
                nullable: true,
                argumentsPropagateNullability: new[] { false, false },
                type: jsonValueMethodInfo.ReturnType,
                typeMapping: null
            );
        }
    );

Repro Project

https://github.com/Xor-el/EFCore-Oracle-Buggy-Demo/tree/db-function-json-path-parameter-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

[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 34989726 has been filed to track this issue. The parent enhancement request is https://github.com/oracle/dotnet-db-samples/issues/259.

[alexkeh]

ODP.NET 21c for EF Core 8 will support JSON columns. We've been investigating the reported JSON bugs in parallel. This bug appears to be a DB issue. We've created a separate DB bug (36000673) to track that issue, which we think will resolve the client side issue.

[alexkeh]

The ODP.NET issue (Bug 34989726) has been investigated and resolved. As such, I'm closing this issue.

The DB team will continue to work on resolving the DB server issue that remains: Bug 34989726.

[alexkeh]

The Oracle JSON dev team has concluded this bug cannot be fixed as the specific JSON syntax is not supported.

The workaround is to provide a string with an escaped Unicode character.