CASE should not use bind parameter for THEN clause

[Mike Bayer]

seems like firebird has this problem as well as a dialect jek is working on

[12:01pm](12:01pm) mummra: SELECT CASE WHEN (infos.pk < 3) THEN 'lessthan3' WHEN (infos.pk >= 3 AND infos.pk < 7) THEN 'gt3' END AS x, infos.pk, infos.info from infos
[12:01pm](12:01pm) mummra: it works if I run it directly in IBConsole
[12:02pm](12:02pm) mummra: but since SA run it with parameters..
[12:05pm](12:05pm) mummra: SELECT CASE WHEN (infos.pk < ?) THEN ? WHEN (infos.pk >= ? AND infos.pk < ?) THEN 'dd' END AS x, infos.pk, infos.info from infos
[12:05pm](12:05pm) mummra: it gives me error...

i would propose for now we change CASE to not convert the param after THEN to a bind parameter.

ultimately, we should have an explicit CaseClause construct in expression.py so that dialects can make better decisions about CASE (oracle has some issues too).

[jek]

sapdb needs this too, and a good example is in the SQL generated when the IN operator is fed an empty sequence: CASE WHEN (somecol IS NULL) THEN NULL ELSE 0 END = 1

For somecol.in_([]) to work, the IN operator's generation must be changed to cast NULL to a numeric, i.e. NUM(NULL) in this dialect. The DB-API doesn't accept a bind parameter there, so that particular generation must inline the NULL value, which depends on #807.

[Mike Bayer]

im proposing changing CASE across the board to not produce a bind parameter for the "THEN" clause..that way we don't depend on #807 for now (I probably need a half/full day's refactoring time to get 807 off the ground and i think it should be post-0.4.0).

[Mike Bayer]

in no circumstances should SQLA ever produce a parameterized value as a non-bind parameter, as we do not apply quoting and this would essentially be a security hole, since it is expected that SQLA quotes all literal values unless you're using text() or literal_column().

The docs for case() specify that if you don't want a bind param, use text(), and that works fine (as does literal_column()):

>>> c = case([== 5, literal_column("'bar'", String))]((literal('foo')))
>>> print c
CASE WHEN (:param_1 = :param_2) THEN 'bar' END
>>>

updated the docs regarding literal_column() in b01d6341611d4457b70aa1f631abe8223bd16a57.

[Mike Bayer]

Removing milestone: 0.5.xx (automated comment)