We're updating the issue view to help you get more done. 

POSTGRESQL: error thrown on NULL filters

Description

Caused by PRESIDECMS-395.

Original report from Seb Duggan

Run into an issue in the admin running Preside against a PostgreSQL datasource. If I try to edit the Homepage, I get the error `syntax error at or near "$1"`

Did a bit of digging, and the issue is when a null value is being passed to the database (here, the page.id is null). There's a method `_transformNullClauses()` in `SqlRunner.cfc` which swaps out `=` or `!=` for `is` or `is not` if the value is null.

PostgreSQL has a problem with this (not sure if other DBs do). But it works perfectly with the original `=` or `!=`.

I'm not sure what the purpose of this transformation is though. If you're passing named parameters (which we are here - `where page.id = :id`) and the parameter is set as null, then the null value is automatically handled. We shouldn't need to manually alter the SQL query...

Do you have any recollection of the problems that were caused by the "incorrect SQL" reported in PRESIDECMS-395?

Environment

Status

Assignee

Unassigned

Reporter

Dominic Watson

Accepted

Yes

Fix versions

Affects versions

10.6.0

Priority

Medium