bug(mssql): translates null booleans incorrectly

[NickCrews]

What happened?

import ibis

e = ibis.null().cast(bool)
ibis.to_sql(e, "mssql")

Generated SQL:

SELECT
  IIF([t0].[Cast(None, boolean)] <> 0, 1, 0) AS [Cast(None, boolean)]
FROM (
  SELECT
    NULL AS [Cast(None, boolean)]
) AS [t0]

which evaluates to 0, not None.

This is caused by https://github.com/ibis-project/ibis/blob/4e7a00cb4a383a01466c39b0929f582e66f7ba07/ibis/backends/mssql/init.py#L575-L588

I discovered this in https://github.com/ibis-project/ibis/pull/9097, but I don't want to have to fix it there.

What version of ibis are you using?

main

What backend(s) are you using, if any?

mssql

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[cpcloud]

I don't think this is possible to fix. MS SQL doesn't have a boolean type.

[NickCrews]

Would changing ibis.ifelse(table_expr[name], 1, 0).cast("boolean") to something like ibis.case().when(x.isnull(), ibis.null()).when(x, 1).else_(0).end() be the right direction?

[cpcloud]

Perhaps. Might be worth trying that out in a PR and seeing if it works!

[NickCrews]

Ok, to be honest I don't use mssql so I'm not motivated to fix this myself. Feel free to close or move to backlog or whatever you think is best. Thanks!