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

ibis/ibis/backends/mssql/__init__.py

Lines 575 to 588 in 4e7a00c

	def _to_sqlglot(
	self, expr: ir.Expr, *, limit: str | None = None, params=None, **_: Any
	):
	"""Compile an Ibis expression to a sqlglot object."""
	table_expr = expr.as_table()
	conversions = {
	name: ibis.ifelse(table_expr[name], 1, 0).cast("boolean")
	for name, typ in table_expr.schema().items()
	if typ.is_boolean()
	}
	if conversions:
	table_expr = table_expr.mutate(**conversions)
	return super()._to_sqlglot(table_expr, limit=limit, params=params)

I discovered this in #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

• 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!