Issue with Postgresql filtered queries on V7

Help me!
1

Expected behavior

When performing a query with a filter that is not using the IN operator (e.g. name IS NOT NULL) the query is expected to succeed.

Actual behavior

The query is failing because the SQL produced or the query is invalid (name IS NOT (NULL)), it surrounds the value with parentheses. Postgresql doesn’t consider this syntax valid.

Failure Logs

"name":"ForestLiana","message":"Records Index Count error: PG::SyntaxError: ERROR: syntax error at or near \"(\"
LINE 1: ...\".\"order_id\" WHERE ((\"order\".\"id\" IS NOT (NULL)))

Context

Please provide any relevant information about your setup.

  • Package Version: 7.0.0
  • Rails Version: 6.1.4
  • Database Dialect: Postgresql
  • Database Version: 13.3
  • Project Name: Back Office

I’ve created a PR with a suggested solution for the problem.

1 Like
2

Thank you very much for the PR!

IIRC those parenthesis were introduced to deal with a problem when escaping lists for the ‘IN’ operator, which in turn were needed when evaluating content restriction for scopes and smart actions.

(like in table.id IN (1, 2, 3, 4))

I’ll ask one of the ruby devs to review your PR (and hopefully unit test it, so that we stop having regressions on those features).

3

I’m closing this, we can continue the conversation in the PR comments