Missing Parantheses around JSON patterns in generated SQL

[fritz3n]

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(")");

to:

Sql.Append("' || (");
Visit(expression.Pattern);
Sql.Append("))");

A more correct approach may utilize RequiresParentheses.

Can supply minimal example and tests if needed.