Substituting (NULL) for an empty In clause breaks NOT IN

[jberryman]

This just manifested as a bug. It wasn't difficult to track down, but would have been easy to miss (and may exist elsewhere in my code). https://stackoverflow.com/a/37419910/176841

Observe:

mysql> select count(*) from foo where y = 5623 and x = 2;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> DELETE FROM `foo` WHERE `x` = 2 AND `y` = 5623 AND `z` NOT IN (null);
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from foo where y = 5623 and x = 2;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

I'm not sure there's anything you can substitute for ? that would fix this. I guess you could rewrite IN ? and NOT IN ? to simply false or true respectively. Or maybe this has been solved in other libraries.

[jberryman]

subselects don't seem to have this issue, maybe there's a solution there

[paul-rouse]

I am not totally sure we should be fixing a mysql "feature", but, on the other hand, there is already special action for this case in Param.hs, which could be seen as incorrect.

If you feel like trying a solution, I'd be happy to look at a PR! Unfortunately, I don't think it can be local to the instance for In in Param.hs, since an empty sub-select still needs the correct type.

[jberryman]

I think if it can't be worked around properly, the next best thing would be to make In take/be a non-empty list type (maybe eventually https://hackage.haskell.org/package/base-4.9.1.0/docs/Data-List-NonEmpty.html), or to simply throw an exception on an empty list. I think the current (null) thing is a misfeature