`x IS NOT NULL` normalized to `NOT x IS NULL` changes results for row-valued expressions (postgres round-trip)

[tonywasson]

IS NOT NULL and NOT ... IS NULL are equivalent for scalars, but not for row-valued
(composite) expressions. sqlglot collapses both spellings into the same AST at parse time, so the
distinction cannot be recovered, and a postgres → postgres round-trip silently changes the
result set.

This is the assumption that #4209 was closed not planned on ("c IS NOT NULL and NOT c IS NULL
are the same thing"). That holds for scalars; this issue is the case where it does not.

Postgres documents the non-equivalence

From §9.2 Comparison Functions and Operators:

If the expression is row-valued, then IS NULL is true when the row expression itself is null
or when all the row's fields are null, while IS NOT NULL is true when the row expression itself
is non-null and all the row's fields are non-null. Because of this behavior, IS NULL and
IS NOT NULL do not always return inverse results for row-valued expressions; in particular, a
row-valued expression that contains both null and non-null fields will return false for both
tests.

Reproduction — the sqlglot half

import sqlglot
print(sqlglot.__version__)                     # 30.12.0

sql = "SELECT r IS NOT NULL FROM t"
print(sqlglot.parse_one(sql, read="postgres").sql(dialect="postgres"))
# SELECT NOT r IS NULL FROM t                  <- same dialect in and out

# the two spellings are indistinguishable after parsing:
a = sqlglot.parse_one("SELECT r IS NOT NULL FROM t", read="postgres")
b = sqlglot.parse_one("SELECT NOT r IS NULL FROM t", read="postgres")
print(a == b)                                  # True

Both parse to Not(this=Is(this=Column(r), expression=Null())).

Reproduction — why it matters

CREATE SCHEMA sg_repro;
CREATE TYPE sg_repro.ct AS (a int, b int);
CREATE TABLE sg_repro.t (id int, r sg_repro.ct);
INSERT INTO sg_repro.t VALUES (1, ROW(1,2)), (2, ROW(1,NULL)), (3, ROW(NULL,NULL));

SELECT id,
       (r IS NOT NULL) AS as_written,
       (NOT r IS NULL) AS as_sqlglot_emits
FROM sg_repro.t ORDER BY id;

PostgreSQL 16.13:

 id | as_written | as_sqlglot_emits
----+------------+------------------
  1 | t          | t
  2 | f          | t                 <-- differs
  3 | f          | f

So SELECT ... WHERE r IS NOT NULL and the SQL sqlglot emits for it return different rows. Row 2 is
the partially-null row; rows 1 and 3 agree, which is why the normalization survives casual testing.

Note on the fix

Always emitting IS NOT NULL for Not(Is(x, Null)) would not be correct either — it would break the
opposite direction, turning a user's NOT (r IS NULL) into r IS NOT NULL. Preserving the round
trip appears to require representing the two predicates distinctly (e.g. an IsNotNull node, or a
flag on Is) rather than a generator-side change.

Worth noting this needs no type inference to avoid: the parser currently discards information the
input had. Whether it's worth the AST change is your call — filing it because the closure rationale
on #4209 doesn't cover this case.

Environment

• sqlglot 30.12.0 (also reproduces on 30.8.0)
• PostgreSQL 16.13
• read/write dialect both postgres

[georgesittas]

Interesting one, thanks for reporting. We'll need to discuss about this one, because solving it properly, as you suggested, is a very breaking change.