Repository navigation
Missing Parantheses around JSON patterns in generated SQL #3923
Description
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
efcore.pg/src/EFCore.PG/Query/Internal/NpgsqlQuerySqlGenerator.cs
Lines 1014 to 1016 in 930bd29
Sql.Append("' || (");
Visit(expression.Pattern);
Sql.Append("))");A more correct approach may utilize RequiresParentheses.
Can supply minimal example and tests if needed.
Activity
roji commentedon Oct 4, 2026
Thanks, makes sense. I opened #3927 (replacing #3924) which fixes this via RequiresParentheses as you suggested. Will backport the fix.
[10.0] Backport regex operand parentheses fix (#3928)
[9.0] Backport regex operand parentheses fix (#3929)
Fix regex operand parentheses based on SQL precedence (#3927) (#3930)
roji commentedon Oct 4, 2026
Backported to 10.0.4 via 26aadb3
Backported to 9.0.5 via 9a84250
Backported to 8.0.12 via 1d873bd