-- NULL semantics of the IN family (ScalarArrayOpExpr pushdown). -- -- ClickHouse evaluates IN under two-valued logic: a probe that finds no -- match yields 0 even when the searched set contains a NULL, where Postgres -- computes NULL, which changes negation semantics (NOT(NULL) -> NULL). -- The has()/countEqual() functions also match a NULL probe against a NULL -- element as if it were an ordinary value. Every shape below ships regardless: -- deparse.c uses the cheap native form when it can prove neither side can be -- NULL, and a guarded CASE otherwise that checks at runtime instead, computing -- Postgres's exact three-valued answer (NULL, not FALSE, or worse, TRUE) -- in every context. These tests ensure both forms deparse correctly and that -- the rows returned match local (Postgres) semantics either way. CREATE SERVER in_null_svr FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'in_null_test', driver 'binary'); CREATE USER MAPPING FOR CURRENT_USER SERVER in_null_svr; CREATE SERVER in_null_admin FOREIGN DATA WRAPPER clickhouse_fdw; CREATE USER MAPPING FOR CURRENT_USER SERVER in_null_admin; CALL clickhouse_perform('in_null_admin', 'DROP DATABASE IF EXISTS in_null_test'); CALL clickhouse_perform('in_null_admin', 'CREATE DATABASE in_null_test'); -- xn imports as a plain (nullable) int column, xp as int NOT NULL, and arr -- as int[]; its elements' nullability is never provable at plan time CALL clickhouse_perform('in_null_admin', 'CREATE TABLE in_null_test.tnull (id Int32, xn Nullable(Int32), xp Int32, arr Array(Int32)) ENGINE = MergeTree ORDER BY id'); CALL clickhouse_perform('in_null_admin', $$ INSERT INTO in_null_test.tnull VALUES (1, 1, 1, [1, 500]), (2, 2, 2, [500]), (3, NULL, 3, []) $$); CREATE SCHEMA in_null_test; IMPORT FOREIGN SCHEMA in_null_test FROM SERVER in_null_svr INTO in_null_test; SET SESSION search_path = in_null_test,public; -- The import must carry ClickHouse nullability into the declarations the -- never-null proofs rely on \d tnull Foreign table "in_null_test.tnull" Column | Type | Collation | Nullable | Default | FDW options --------+-----------+-----------+----------+---------+------------- id | integer | | not null | | xn | integer | | | | xp | integer | | not null | | arr | integer[] | | not null | | Server: in_null_svr FDW options: (database 'in_null_test', table_name 'tnull', engine 'MergeTree') -- ============================================================ -- 1. IN over a constant list: always pushable in a WHERE condition -- ============================================================ -- NULL-free list is exactly equivalent to the native IN EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xn IN (1, 500) ORDER BY id; QUERY PLAN ---------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((xn IN (1,500))) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xn IN (1, 500) ORDER BY id; id ---- 1 (1 row) -- A NULL in the list can't be proven absent, so this ships via a guarded -- CASE instead of the native IN, computing the real answer at runtime EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xn IN (1, NULL) ORDER BY id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((CASE WHEN xn IS NULL AND notEmpty([1,NULL]) THEN NULL WHEN countEqual([1,NULL], xn) > 0 THEN true WHEN countEqual([1,NULL], NULL) > 0 THEN NULL ELSE false END)) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xn IN (1, NULL) ORDER BY id; id ---- 1 (1 row) -- ============================================================ -- 2. NOT IN over a constant list: always pushable, native when NULL-free -- ============================================================ -- ClickHouse's native NOT IN yields NULL for a NULL probe, so a NULL-free -- list is exact: id 3 (xn IS NULL) is dropped on both systems EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xn NOT IN (1, 500) ORDER BY id; QUERY PLAN -------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((xn NOT IN (1,500))) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xn NOT IN (1, 500) ORDER BY id; id ---- 2 (1 row) -- With a NULL in the list, ClickHouse's native NOT IN would return id 2 -- where Postgres returns no rows (every result is NULL or FALSE): the -- guarded CASE computes Postgres's answer instead of using the native form EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xn NOT IN (1, NULL) ORDER BY id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((CASE WHEN xn IS NULL AND notEmpty([1,NULL]) THEN NULL WHEN countEqual([1,NULL], xn) > 0 THEN false WHEN countEqual([1,NULL], NULL) > 0 THEN NULL ELSE true END)) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xn NOT IN (1, NULL) ORDER BY id; id ---- (0 rows) -- NOT over IN is the same thing arriving unrewritten EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE NOT (xn IN (1, NULL)) ORDER BY id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((CASE WHEN xn IS NULL AND notEmpty([1,NULL]) THEN NULL WHEN countEqual([1,NULL], xn) > 0 THEN false WHEN countEqual([1,NULL], NULL) > 0 THEN NULL ELSE true END)) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE NOT (xn IN (1, NULL)) ORDER BY id; id ---- (0 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE NOT (xn IN (1, 500)) ORDER BY id; QUERY PLAN -------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((xn NOT IN (1,500))) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE NOT (xn IN (1, 500)) ORDER BY id; id ---- 2 (1 row) -- ============================================================ -- 3. Non-constant arrays: the has()/countEqual() deparse forms -- ============================================================ -- has() matches a NULL probe against a NULL element where Postgres computes -- NULL, so = ANY uses the cheap has() form only with a provably non-NULL -- probe; xp is declared NOT NULL EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xp = ANY(ARRAY[xn, 5]) ORDER BY id; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((CASE WHEN xp IS NULL AND notEmpty([xn, 5]) THEN NULL WHEN countEqual([xn, 5], xp) > 0 THEN true WHEN countEqual([xn, 5], NULL) > 0 THEN NULL ELSE false END)) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xp = ANY(ARRAY[xn, 5]) ORDER BY id; id ---- 1 2 (2 rows) -- A nullable probe against an array that may hold a NULL would turn -- Postgres's NULL (id 3: NULL = ANY({NULL,5})) into TRUE via has(): the -- guarded CASE ships instead, computing the real NULL at runtime EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xn = ANY(ARRAY[xn, 5]) ORDER BY id; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((CASE WHEN xn IS NULL AND notEmpty([xn, 5]) THEN NULL WHEN countEqual([xn, 5], xn) > 0 THEN true WHEN countEqual([xn, 5], NULL) > 0 THEN NULL ELSE false END)) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xn = ANY(ARRAY[xn, 5]) ORDER BY id; id ---- 1 2 (2 rows) -- Basic arithmetic over provably non-NULL inputs is itself provably -- non-NULL (it returns a value or raises; see never_null_opfuncs), so an -- expression element doesn't cost the cheap form... EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xp = ANY(ARRAY[xp + 0, 500]) ORDER BY id; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((has([(xp + 0), 500], xp))) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xp = ANY(ARRAY[xp + 0, 500]) ORDER BY id; id ---- 1 2 3 (3 rows) -- ...while the same arithmetic over a nullable input proves nothing: -- guarded (id 3: 3 = ANY({NULL,500}) is NULL, dropped either way) EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xp = ANY(ARRAY[xn + 0, 500]) ORDER BY id; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((CASE WHEN xp IS NULL AND notEmpty([(xn + 0), 500]) THEN NULL WHEN countEqual([(xn + 0), 500], xp) > 0 THEN true WHEN countEqual([(xn + 0), 500], NULL) > 0 THEN NULL ELSE false END)) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xp = ANY(ARRAY[xn + 0, 500]) ORDER BY id; id ---- 1 2 (2 rows) -- countEqual() = 0 turns any NULL involved into TRUE (id 3: 3 <> ALL of -- {NULL,5} is NULL on Postgres): <> ALL uses the cheap countEqual() form -- only with both sides provably non-NULL EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xp <> ALL(ARRAY[xn, 5]) ORDER BY id; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((CASE WHEN xp IS NULL AND notEmpty([xn, 5]) THEN NULL WHEN countEqual([xn, 5], xp) > 0 THEN false WHEN countEqual([xn, 5], NULL) > 0 THEN NULL ELSE true END)) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xp <> ALL(ARRAY[xn, 5]) ORDER BY id; id ---- (0 rows) -- ...and with both sides proven, the cheap form ships EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xp <> ALL(ARRAY[xp, 500]) ORDER BY id; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((countEqual([xp, 500], xp) = 0)) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xp <> ALL(ARRAY[xp, 500]) ORDER BY id; id ---- (0 rows) -- `!=` is just `<>` by the time the parser hands it to us (transformed at -- parse analysis, before any FDW code sees the tree); confirm it deparses -- identically rather than falling through chfdw_is_equal_op's string match EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xp != ALL(ARRAY[xp, 500]) ORDER BY id; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((countEqual([xp, 500], xp) = 0)) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xp != ALL(ARRAY[xp, 500]) ORDER BY id; id ---- (0 rows) -- <> ANY deparses by counting elements rather than has() (which would -- compute <> ALL instead): the cheap form ships with both sides proven -- non-NULL EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xp <> ANY(ARRAY[xp, 500]) ORDER BY id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((countEqual([xp, 500], xp) < length([xp, 500]))) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xp <> ANY(ARRAY[xp, 500]) ORDER BY id; id ---- 1 2 3 (3 rows) -- Neither side is proven non-NULL here, but this still ships: a guarded -- CASE checks at runtime whether the probe or an array element is NULL and -- returns a genuine NULL there instead of requiring a plan-time proof EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xn <> ANY(ARRAY[xn, 500]) ORDER BY id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((CASE WHEN xn IS NULL AND notEmpty([xn, 500]) THEN NULL WHEN (length([xn, 500]) - countEqual([xn, 500], xn) - countEqual([xn, 500], NULL)) > 0 THEN true WHEN countEqual([xn, 500], NULL) > 0 THEN NULL ELSE false END)) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xn <> ANY(ARRAY[xn, 500]) ORDER BY id; id ---- 1 2 (2 rows) -- A provably empty array is exact via the cheap form regardless of the -- probe's nullability (has()/countEqual() degenerate correctly on an -- empty array by arithmetic): id 3's NULL probe joins ids 1/2 under the -- same FALSE flag, since <> ANY(empty) is FALSE for every probe on both -- systems, NULL probe included EXPLAIN (VERBOSE, COSTS OFF) SELECT xn <> ANY(ARRAY[]::int[]) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: ((xn <> ANY ('{}'::integer[]))), (count(*)) Relations: Aggregate on (tnull) Remote SQL: SELECT (countEqual([], xn) < length([])), count(*) FROM in_null_test.tnull GROUP BY ((countEqual([], xn) < length([]))) ORDER BY (countEqual([], xn) < length([])) ASC NULLS LAST (4 rows) SELECT xn <> ANY(ARRAY[]::int[]) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; flag | count ------+------- f | 3 (1 row) -- A non-constant array can be empty at *runtime*, and Postgres resolves an -- empty array before even looking at the probe: id 3's NULL = ANY('{}') is -- FALSE, not NULL, so it must group with id 2. The guarded CASE answers -- NULL for a NULL probe only against a non-empty array and falls through -- to the empty-array answer otherwise EXPLAIN (VERBOSE, COSTS OFF) SELECT xn = ANY(arr) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: ((xn = ANY (arr))), (count(*)) Relations: Aggregate on (tnull) Remote SQL: SELECT (CASE WHEN xn IS NULL AND notEmpty(arr) THEN NULL WHEN countEqual(arr, xn) > 0 THEN true WHEN countEqual(arr, NULL) > 0 THEN NULL ELSE false END), count(*) FROM in_null_test.tnull GROUP BY ((CASE WHEN xn IS NULL AND notEmpty(arr) THEN NULL WHEN countEqual(arr, xn) > 0 THEN true WHEN countEqual(arr, NULL) > 0 THEN NULL ELSE false END)) ORDER BY (CASE WHEN xn IS NULL AND notEmpty(arr) THEN NULL WHEN countEqual(arr, xn) > 0 THEN true WHEN countEqual(arr, NULL) > 0 THEN NULL ELSE false END) ASC NULLS LAST (4 rows) SELECT xn = ANY(arr) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; flag | count ------+------- f | 2 t | 1 (2 rows) -- ...and the ALL side of the same coin: id 3's NULL <> ALL('{}') is TRUE EXPLAIN (VERBOSE, COSTS OFF) SELECT xn <> ALL(arr) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: ((xn <> ALL (arr))), (count(*)) Relations: Aggregate on (tnull) Remote SQL: SELECT (CASE WHEN xn IS NULL AND notEmpty(arr) THEN NULL WHEN countEqual(arr, xn) > 0 THEN false WHEN countEqual(arr, NULL) > 0 THEN NULL ELSE true END), count(*) FROM in_null_test.tnull GROUP BY ((CASE WHEN xn IS NULL AND notEmpty(arr) THEN NULL WHEN countEqual(arr, xn) > 0 THEN false WHEN countEqual(arr, NULL) > 0 THEN NULL ELSE true END)) ORDER BY (CASE WHEN xn IS NULL AND notEmpty(arr) THEN NULL WHEN countEqual(arr, xn) > 0 THEN false WHEN countEqual(arr, NULL) > 0 THEN NULL ELSE true END) ASC NULLS LAST (4 rows) SELECT xn <> ALL(arr) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; flag | count ------+------- f | 1 t | 2 (2 rows) -- = ALL ships via the cheap form with both sides proven non-NULL... EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xp = ALL(ARRAY[xp]) ORDER BY id; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((countEqual([xp], xp) = length([xp]))) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xp = ALL(ARRAY[xp]) ORDER BY id; id ---- 1 2 3 (3 rows) -- ...but countEqual() counts NULL as equal to NULL (id 3 would come back -- TRUE where Postgres computes NULL = ALL({NULL}) as NULL): the guarded -- CASE ships instead EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xn = ALL(ARRAY[xn]) ORDER BY id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Remote SQL: SELECT id FROM in_null_test.tnull WHERE ((CASE WHEN xn IS NULL AND notEmpty([xn]) THEN NULL WHEN (length([xn]) - countEqual([xn], xn) - countEqual([xn], NULL)) > 0 THEN false WHEN countEqual([xn], NULL) > 0 THEN NULL ELSE true END)) ORDER BY id ASC NULLS LAST (3 rows) SELECT id FROM tnull WHERE xn = ALL(ARRAY[xn]) ORDER BY id; id ---- 1 2 (2 rows) -- ============================================================ -- 4. Value contexts no longer observe any divergence -- ============================================================ -- A NULL-free list needs no guard: the native IN is already exact EXPLAIN (VERBOSE, COSTS OFF) SELECT xn IN (1, 500) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: ((xn = ANY ('{1,500}'::integer[]))), (count(*)) Relations: Aggregate on (tnull) Remote SQL: SELECT (xn IN (1,500)), count(*) FROM in_null_test.tnull GROUP BY ((xn IN (1,500))) ORDER BY (xn IN (1,500)) ASC NULLS LAST (4 rows) SELECT xn IN (1, 500) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; flag | count ------+------- f | 1 t | 1 | 1 (3 rows) -- A NULL in the list previously forced this local, since the value -- position demands exactness the native IN can't give; the guarded CASE -- now ships instead, returning a genuine NULL so id 3 lands in the same -- group Postgres would put it in EXPLAIN (VERBOSE, COSTS OFF) SELECT xn IN (1, NULL) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: ((xn = ANY ('{1,NULL}'::integer[]))), (count(*)) Relations: Aggregate on (tnull) Remote SQL: SELECT (CASE WHEN xn IS NULL AND notEmpty([1,NULL]) THEN NULL WHEN countEqual([1,NULL], xn) > 0 THEN true WHEN countEqual([1,NULL], NULL) > 0 THEN NULL ELSE false END), count(*) FROM in_null_test.tnull GROUP BY ((CASE WHEN xn IS NULL AND notEmpty([1,NULL]) THEN NULL WHEN countEqual([1,NULL], xn) > 0 THEN true WHEN countEqual([1,NULL], NULL) > 0 THEN NULL ELSE false END)) ORDER BY (CASE WHEN xn IS NULL AND notEmpty([1,NULL]) THEN NULL WHEN countEqual([1,NULL], xn) > 0 THEN true WHEN countEqual([1,NULL], NULL) > 0 THEN NULL ELSE false END) ASC NULLS LAST (4 rows) SELECT xn IN (1, NULL) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; flag | count ------+------- t | 1 | 2 (2 rows) -- <> ANY ships the same way, aggregation included, despite neither side -- being proven non-NULL EXPLAIN (VERBOSE, COSTS OFF) SELECT xn <> ANY(ARRAY[xn, 500]) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: ((xn <> ANY (ARRAY[xn, 500]))), (count(*)) Relations: Aggregate on (tnull) Remote SQL: SELECT (CASE WHEN xn IS NULL AND notEmpty([xn, 500]) THEN NULL WHEN (length([xn, 500]) - countEqual([xn, 500], xn) - countEqual([xn, 500], NULL)) > 0 THEN true WHEN countEqual([xn, 500], NULL) > 0 THEN NULL ELSE false END), count(*) FROM in_null_test.tnull GROUP BY ((CASE WHEN xn IS NULL AND notEmpty([xn, 500]) THEN NULL WHEN (length([xn, 500]) - countEqual([xn, 500], xn) - countEqual([xn, 500], NULL)) > 0 THEN true WHEN countEqual([xn, 500], NULL) > 0 THEN NULL ELSE false END)) ORDER BY (CASE WHEN xn IS NULL AND notEmpty([xn, 500]) THEN NULL WHEN (length([xn, 500]) - countEqual([xn, 500], xn) - countEqual([xn, 500], NULL)) > 0 THEN true WHEN countEqual([xn, 500], NULL) > 0 THEN NULL ELSE false END) ASC NULLS LAST (4 rows) SELECT xn <> ANY(ARRAY[xn, 500]) AS flag, count(*) FROM tnull GROUP BY flag ORDER BY flag; flag | count ------+------- t | 2 | 1 (2 rows) -- An aggregate FILTER only qualifies rows, so it keeps truth-context rules EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FILTER (WHERE xn IN (1, NULL)) FROM tnull; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (count(*) FILTER (WHERE (xn = ANY ('{1,NULL}'::integer[])))) Relations: Aggregate on (tnull) Remote SQL: SELECT countIf((((CASE WHEN xn IS NULL AND notEmpty([1,NULL]) THEN NULL WHEN countEqual([1,NULL], xn) > 0 THEN true WHEN countEqual([1,NULL], NULL) > 0 THEN NULL ELSE false END)) > 0)) FROM in_null_test.tnull (4 rows) SELECT count(*) FILTER (WHERE xn IN (1, NULL)) FROM tnull; count ------- 1 (1 row) -- ============================================================ -- 5. Shippability-walker regressions fixed alongside the rules -- ============================================================ -- A NULL array constant survives const-folding when the probe is a column; -- inspecting it for the IN-list deparse would detoast a null datum. Local. EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE xn = ANY(NULL::int[]) ORDER BY id; QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on in_null_test.tnull Output: id Filter: (tnull.xn = ANY (NULL::integer[])) Remote SQL: SELECT id, xn FROM in_null_test.tnull ORDER BY id ASC NULLS LAST (4 rows) SELECT id FROM tnull WHERE xn = ANY(NULL::int[]) ORDER BY id; id ---- (0 rows) -- A CASE arg WHEN .. with an unshippable tested expression shipped without -- its branches ever being checked. Local, with correct rows. EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM tnull WHERE CASE pg_backend_pid() WHEN 0 THEN xp = 1 ELSE xp > 0 END ORDER BY id; QUERY PLAN ------------------------------------------------------------------------------------ Foreign Scan on in_null_test.tnull Output: id Filter: CASE pg_backend_pid() WHEN 0 THEN (tnull.xp = 1) ELSE (tnull.xp > 0) END Remote SQL: SELECT id, xp FROM in_null_test.tnull ORDER BY id ASC NULLS LAST (4 rows) SELECT id FROM tnull WHERE CASE pg_backend_pid() WHEN 0 THEN xp = 1 ELSE xp > 0 END ORDER BY id; id ---- 1 2 3 (3 rows) -- Cleanup SET SESSION search_path = public; DROP SCHEMA in_null_test CASCADE; NOTICE: drop cascades to foreign table in_null_test.tnull CALL clickhouse_perform('in_null_admin', 'DROP DATABASE in_null_test'); DROP USER MAPPING FOR CURRENT_USER SERVER in_null_svr; DROP SERVER in_null_svr;