-
Notifications
You must be signed in to change notification settings - Fork 9
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Empty list as param generates incorrect sql query at runtime. #5
Comments
I can make a patch, but I dont see easy way to fix it - if we substitute an empty array here we have to specify types explicitly because we generate prepared statement instead of real query and Postgres can't infer it, but we don't know params types at this point. |
I can't think of any good solution. Other than what you mention, the alternative is to replace the whole expression |
Thinking a bit more about it, the @NightBlues thoughts? |
According to postgres documentation NULL is like a blackhole - everything touched by it becomes NULL too:) |
@NightBlues I have an untested alternative that may solve every case in a clean way, while at the same time avoiding the need of having to create a separate prepared statement for each list length (something that is needed with the current implementation). I'm not sure how well it will work in practice because PGOCaml doesn't define arrays for every type IIRC. The basic idea is: SELECT COUNT(*) FROM users
WHERE id IN $@ids::int[] gets translated into SELECT COUNT(*) FROM users
WHERE id IN (SELECT unnest($1::int[])) This requires the user to be explicit and cast the value to the required type. Anyway, I have to think about this more, just wanted to share the idea I had. |
@NightBlues by now you probably have this solved already, but I found another solution that seems to work fine: -- Equivalent to IN (...)
SELECT COUNT(*) FROM users
WHERE id = ANY($user_ids_array::int[])
-- Equivalent to NOT IN (...)
SELECT COUNT(*) FROM users
WHERE id <> ALL($user_ids_array::int[]) This will work with empty arrays too, and has the advantage of requiring a single prepared statement for every list length, while the I will update the documentation with a comment mentioning this (along with other gotchas like nullable columns in views, and you |
Modern postgres has very powerfull features, for example sometimes I use conversions to json and back with json operators. :) |
If we pass empty list, this code
https://github.com/tizoc/ppx_pgsql/blob/master/lib/runtime/ppx_pgsql_runtime.ml#L49
generates
()
that postgres can't understand. For example:is correct, but
gives syntax error.
The text was updated successfully, but these errors were encountered: