[Bug]: SqlalchemyRender silently drops NOT on IS [NOT] NULL predicates — wrong query results across all SqlalchemyRender-based integrations

[toddyLee]

Short description of current behavior

When a query pushed down to a data integration contains a negated null predicate — NOT (col IS NULL) or NOT col IS NULL — the renderer silently drops the NOT. The integration executes col IS NULL instead and returns the exact opposite rows. No error is raised.

Observed on a live deployment (ClickHouse integration, mindsdb/mindsdb:latest docker image):

SELECT COUNT(*) FROM db.t WHERE code = 'UAV' AND TypeName IS NOT NULL;       -- 1 row (correct)
SELECT COUNT(*) FROM db.t WHERE code = 'UAV' AND NOT (TypeName IS NULL);     -- 0 rows (wrong, should be 1)
SELECT COUNT(*) FROM db.t WHERE code = 'UAV' AND NOT TypeName IS NULL;       -- 0 rows (wrong, should be 1)
SELECT COUNT(*) FROM db.t WHERE code = 'UAV' AND NOT (TypeName IS NOT NULL); -- 1 row (wrong, should be 0)

The bug lives in the shared renderer (backend/data-vault/mindsdb/utilities/render/sqlalchemy_render.py), so it is dialect-independent: the same dropped NOT reproduces when rendering with MySQL, PostgreSQL and SQLite dialects. It affects every handler that pushes SQL through SqlalchemyRender — official ones (mysql, postgres, mssql, oracle, snowflake, duckdb, bigquery, databricks, ...) and 30+ community handlers (clickhouse, sqlite, hive, ...).

Video or screenshots

No response

Expected behavior

NOT (col IS NULL) and col IS NOT NULL are semantically equivalent in standard SQL (NOT binds looser than IS). Both forms must return identical results; the negated form must not lose its NOT during pushdown rendering.

How to reproduce the error

A. Live server (any SQL integration; verified with ClickHouse 23.3):

1. Connect an integration containing a table with a mostly non-NULL column.
2. Run the four queries from the description — the negated forms return inverted counts.

B. Minimal renderer-level repro (no server needed):

from mindsdb_sql_parser import parse_sql
from mindsdb.utilities.render.sqlalchemy_render import SqlalchemyRender
from clickhouse_sqlalchemy.drivers.base import ClickHouseDialect

r = SqlalchemyRender(ClickHouseDialect)  # same result with mysql.dialect / postgresql.dialect
print(r.get_string(parse_sql("SELECT * FROM t WHERE NOT (x IS NULL)"), with_failback=False))
# actual:   SELECT * FROM t WHERE x IS NULL      <-- NOT is gone
# expected: SELECT * FROM t WHERE x IS NOT NULL

Anything else?

Root cause — backend/data-vault/mindsdb/utilities/render/sqlalchemy_render.py, to_expression(), Constant branch:

elif isinstance(t, ast.Constant):
    col = sa.literal(t.value)
    ...
    col = col.label(alias)   # every Constant, including NULL, is wrapped in a Label

The Label is intended for SELECT-list aliases but is also applied to WHERE operands. x IS <Label(null)> becomes a bind-param comparison, which defeats SQLAlchemy's negate optimization, so expr.__invert__() compiles to the identical expression:

expr = col.operate(operators.is_, null_label)  # x IS :param_1
str(expr.__invert__().compile())               # x IS :param_1  <-- NOT lost
str(col.is_(None).__invert__().compile())      # x IS NOT NULL  <-- correct without the Label

Suggested fix: return sa.null() without a label when t.value is None and not t.alias (keep the labeled behavior for aliased SELECT-list constants). I verified this one-line fix on a live deployment: all four probe queries returned correct results afterwards, with no regressions on the positive forms.

Related secondary bug in the same area: isNotNull(col) is lowercased to isnotnull(col) by the parser (Operation.__init__ lowercases op), which ClickHouse rejects with "Unknown function isnotnull" (CH function names are case-sensitive).