As far as i can see, JSON patterns are never surrounded by parenthesis, even if needed.
In our particular use case, we load the pattern from a json columns property. This currently results in a generated query like the following:
SELECT 'a' ~ ('(?p)' || '{"a":1}'::json ->> 'a')Because of operator precedence, this is parsed as ('(?p)' || '{"a":1}'::json) ->> 'a', e.g. ('(?p){"a":1}') ->> 'a' which results in a 22P02: invalid input syntax for type json.
This can be fixed by always including parenthesis around sql patterns. Particularly changing the code block at
|
Sql.Append("' || "); |
|
Visit(expression.Pattern); |
|
Sql.Append(")"); |
to:
Sql.Append("' || (");
Visit(expression.Pattern);
Sql.Append("))");A more correct approach may utilize RequiresParentheses.
Can supply minimal example and tests if needed.
As far as i can see, JSON patterns are never surrounded by parenthesis, even if needed.
In our particular use case, we load the pattern from a json columns property. This currently results in a generated query like the following:
Because of operator precedence, this is parsed as
('(?p)' || '{"a":1}'::json) ->> 'a', e.g.('(?p){"a":1}') ->> 'a'which results in a22P02: invalid input syntax for type json.This can be fixed by always including parenthesis around sql patterns. Particularly changing the code block at
efcore.pg/src/EFCore.PG/Query/Internal/NpgsqlQuerySqlGenerator.cs
Lines 1014 to 1016 in 930bd29
A more correct approach may utilize
RequiresParentheses.Can supply minimal example and tests if needed.