`selectList [FooColumn /<-. [Nothing]]` generates SQL that chokes PostgreSQL

[chreekat]

Tested with Postgres 9.3. The generated sql looks like

("column" IS NOT NULL AND "column" NOT IN ())

That works on sqlite v3, but Postgres 9.3 says

ERROR:  syntax error at or near ")"
LINE 1: select * from foo where column not in ();
                                               ^

...

Note that the following work fine:

Maybe null

selectList [PatronPaymentToken /<-. [Just 1]] []

WHERE (("payment_token" IS NULL OR "payment_token" NOT IN (?)));

Not null and not x

selectList [PatronPaymentToken /<-. [Nothing, Just 1] []

WHERE (("payment_token" IS NOT NULL AND "payment_token" NOT IN (?)));

[andrewthad]

I think you can just use:

selectList [MyFieldName !=. Nothing ] []

[chreekat]

Heh that's a good point. I just assumed that that would create a naughty comparison against null, but I didn't actually confirm such a thing.

It's still broken either way, though, and I wonder what /<-. [Just 1, Nothing] looks like. (Busy now, but I'll look at these cases next week.)

[chreekat]

I'm removing my comment about is not null. It is handled by !=. Nothing. Also updating to show that there are cases that are handled correctly.

[gregwebs]

I am not familiar with using NOT IN with a list that contains null. It sounds like a recipe for confusion. Are you suggesting to generate SQL that does that?

[chreekat]

I think what x /<-. (Nothing : xs) represents is x IS NOT NULL AND x NOT IN (...). That's actually what Persistent already seems to do.

While nobody needs to write x /<-. [Nothing], one could conceivably write x /<-. (Nothing : xs) in a situation where xs == []. That would result in the same runtime failure. So, while my original report was flawed, I still think there's a bug worth noting here.

[gregwebs]

hmm, if I were starting from scratch I would only allow passing in [a] and not [Maybe a].

[gregwebs]

here is a fix: #631

[chreekat]

Cool, that may do it. I'd like to try it out locally, but it's a busy week and might not get to soon (and a spotty internet connection seems to have just caused stack to corrupt ~/.stack...). lgtm, thanks!

[gregwebs]

Ok, this is merge to master now. Let me know how it goes.