Parentheses put around IS NULL only if operand is of type boolean (6.0)

[roji]

#25722 fixed #23990 by adding parentheses around IS NULL/IS NOT NULL, preventing operator precedence issues. However, it did so only when the operand of IS NULL is a bool. However, the same problem can happen if a non-bool column is being checked for null.

See npgsql/efcore.pg#2090 on PostgreSQL where the following LINQ query:

var boolParam = true;
_ = ctx.Blogs.Where(c => boolParam != c.NullableGuid.HasValue).ToList();

... generates the following SQL ...

SELECT b."Id", b."NullableGuid"
FROM "Blogs" AS b
WHERE @__boolParam_0 <> b."NullableGuid" IS NOT NULL

... which fails with operator does not exist: boolean <> uuid, because in PostgreSQL the precedence of <> is higher than the precedence of IS NOT NULL (docs). The issue also causes wrong data to be returned in Sqlite.

[ajcvickers]

@roji You put servicing-consider on this. Are you planning to get it into 6.0.1?

[roji]

@ajcvickers @smitpatel sorry, I confused this issue with something else in triage; the reason I put servicing-consider on this is that it makes SQLite return incorrect data (because of bad operator precedence). A patch specifically for this (without full-on general parentheses support) would be quite easy and risk-free.

[roji]

For the SQLite issue:

SELECT TRUE <> (NULL IS NULL); -- returns 0
SELECT TRUE <> NULL IS NULL; -- return 1

[smitpatel]

@roji - Can you file a separate issue for 7.0 fix? (You can assign to me if you want)

[roji]

@smitpatel is that basically #23895?

[smitpatel]

That issue has been fixed. You would need to file a new issue for this particular query if we plan to fix it differently.

[roji]

Well we'll definitely need to... I'd track it all under #23895 (correct parentheses around IS NULL, remove unneeded parentheses around AND/OR), but if you want to file something different go ahead.

[smitpatel]

#23895 is about adding mechanism which is already done.

[roji]

Filed #26767. You probably need another one for the AND/OR simplification?