The exact-decimal _cast makes a Decimal/float pair Python won't divide #123

Narrowed to item 3 (2026-09-14).

Items 1 and 2 are fixed — not by carrying the left operand's nullability as suggested below, but by _rewrite_not_in being deleted outright in b53be1a, when the floor moved to v30.18.0 (#109). NOT IN (subquery) is sqlglot's own now, via 8190 + 8236. Re-verified against TableSession on both sqlglot 30.18.0 and main (30.18.1.dev43):

	Postgres 18.4	TableSession now
item 1 — NULL left operand	[(1,)]	[(1,)]
item 2 — NOT IN in a select list	true	[(True,)]

Worth recording that the fix was a deletion: item 1 was described here as "a regression relative to not having the workaround at all", and removing the workaround is exactly what resolved it.

Item 3 is still live, on both the floor and main, and is what this issue now tracks. SELECT CAST(1 AS DECIMAL) / 0.5 FROM u still raises 0A000 — unsupported operand type(s) for /: 'decimal.Decimal' and 'float'. _cast is still in tables.py, and still reaches the executor's storage-type arithmetic gap more often than unpatched sqlglot does.
#58's fix would close it; nothing in the 43 commits between v30.18.0 and main
touches it.

The correction this issue asked #49 for is done: with _rewrite_not_in gone,
#49's opening "none of these affect pg_mimic users today" is true again.

Found by tools/fuzz (#122). Unlike everything in #49 and #58 these are ours: sqlglot's executor answers them and pg_mimic/tables.py is what breaks them. The fuzzer reports whether a finding also reproduces in raw sqlglot, and these are the four that did not.

Verified against PostgreSQL 18.4 and sqlglot 30.17.0.

1. NOT IN (subquery) keeps a row Postgres drops, when the left operand is NULL

tables = {"t": [{"a": 1}, {"a": None}], "u": [{"b": 2}]}
TableSession(tables, columns={"t": {"a": int}, "u": {"b": int}})
# SELECT a FROM t WHERE a NOT IN (SELECT b FROM u)

NULL NOT IN (2) is NULL, not TRUE, so Postgres drops the row. _rewrite_not_in rewrites to NOT EXISTS, and NOT IN and NOT EXISTS are not equivalent when the left operand is nullable: no matching row makes NOT EXISTS TRUE where NOT IN is unknown.

The docstring shows the asymmetry was half-anticipated — it handles the case where the subquery yields a NULL, and that half is right:

# t.a = [1, 2], u.b = [2, NULL]  -- NULL inside the subquery
# Postgres: no rows.  raw sqlglot: [(1,), (2,)] (wrong).  TableSession: [] (correct)

So the two halves of SQL's NULL rule for NOT IN fail in opposite directions, and only the subquery half is covered. The fix is to carry the left operand's nullability too — the rewritten predicate is only equivalent when the left side is known non-NULL.

This is the worst of the four: a wrong answer, no error, and a regression relative to not having the workaround at all.

2. NOT IN (subquery) in a select list raises

tables = {"u": [{"b": 10}]}
# SELECT NOT 0 IN (SELECT b FROM u) FROM u

_rewrite_not_in finds every exp.Not wrapping an exp.In, wherever it sits, but the NOT EXISTS + COUNT shape it produces is only valid in a boolean predicate position. In a select list it becomes generated Python that will not compile — so a query the executor answered correctly now fails.

Probably wants the rewrite scoped to predicate positions (WHERE, HAVING, ON, QUALIFY) and left alone elsewhere, since elsewhere the executor is already right.

3. The exact-decimal CAST makes a Decimal/float pair Python won't divide

# SELECT CAST(1 AS DECIMAL) / 0.5 FROM u

The _cast override exists so CAST('9.99' AS NUMERIC) is an exact Decimal rather than int('9.99') raising (#33), and that is right. But it makes CAST(x AS DECIMAL) produce a Decimal where the unpatched executor produced a float, and Python refuses Decimal / float. Raw sqlglot only avoids this because its CAST is broken in the other direction.

This is the same underlying executor gap as the mixed numeric/double arithmetic in #58 — the executor does Python arithmetic on storage types — so the honest fix is upstream coercion, and our _cast merely reaches it more often. Worth deciding whether to widen the workaround (coerce in a patched arithmetic env) or accept it and note it, since #58's fix would close it.

Why the tripwires didn't catch these

tests/test_sqlglot_workarounds.py runs against sqlglot directly, by design: it asks "has upstream fixed this yet". Nothing there exercises the workarounds themselves, so a workaround that is wrong, or right only for the shapes it was written against, has no test that fails. test_not_in_a_subquery_filters covers the case _rewrite_not_in was written for and passes-as-xfail regardless.

That is the gap tools/fuzz --target mimic closes, and why it defaults to that target rather than raw.

Also: #49 needs a correction

#49 opens with "none of these affect pg_mimic users today". True for six of its seven rows; false for _rewrite_not_in, per item 1 above. Worth striking once this is fixed, or qualifying now.

Suggested order

1. Item 1 — a silent wrong answer, and the only one here that is worse than no workaround.
2. Item 2 — scope the rewrite to predicate positions. Small and self-contained.
3. Item 3 — decide rather than fix; may be subsumed by the Executor gaps with no workaround: rounding, NULL logic, duplicate column names, EXISTS, collation #58 upstream work.

Each wants a regression test against TableSession, not against sqlglot — a new file, since test_sqlglot_workarounds.py is deliberately about upstream.