postgres/spark/trino -> duckdb: ARRAY_REMOVE emits _u <> target and drops NULL elements

Open Beginner friendly
#9,301 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
74/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
postgresql, python, spark
Domain
databases

Research direction

Start at remove_from_array_using_filter in sqlglot/dialects/dialect.py around line 2754, where the lambda body is built as exp.NEQ and emitted as _u <> target. Run the issue's two transpile snippets to reproduce, then swap the comparison for exp.NullSafeNEQ and confirm both cases give [1, None] and [1, 2, 2] respectively, plus the ARRAY_FUNCS_PROPAGATES_NULLS wrapper still holds for spark/trino/snowflake. Done means the existing dialect tests (tests/dialects/test_duckdb.py, test_postgres.py, test_spark.py) pass and a regression case is added for NULL elements and NULL targets.

Written by the indexing model from the issue text.

Description

Before you file an issue

  • Read dialect: postgres (also affects spark, trino, snowflake)
  • Write dialect: duckdb (also affects clickhouse via remove_from_array_using_filter)
  • Verified on main @ 089891f2521780e2c5b243005017af9db62f25a6

Fully reproducible code snippet

remove_from_array_using_filter (sqlglot/dialects/dialect.py:2754) builds exp.NEQ(this=lambda_id, expression=expression.expression), emitting _u <> target. Under SQL three-valued logic, both NULL <> 2 and _u <> NULL evaluate to NULL, which DuckDB's LIST_FILTER treats as false:

import duckdb
import sqlglot

# 1. Non-NULL target: NULL elements inside the array are silently dropped
s1 = sqlglot.transpile(
    "SELECT ARRAY_REMOVE(ARRAY[1, 2, NULL, 2], 2)",
    read="postgres",
    write="duckdb",
)[0]
print(s1)
# SELECT LIST_FILTER([1, 2, NULL, 2], LAMBDA _u : _u <> 2)
print(duckdb.sql(s1).fetchall())
# [([1],)]  -- expected: [([1, None],)] (same for spark and trino -> duckdb)

# 2. NULL target in Postgres: all elements are dropped instead of removing NULLs
s2 = sqlglot.transpile(
    "SELECT ARRAY_REMOVE(ARRAY[1, 2, NULL, 2], NULL)",
    read="postgres",
    write="duckdb",
)[0]
print(s2)
# SELECT LIST_FILTER([1, 2, NULL, 2], LAMBDA _u : _u <> NULL)
print(duckdb.sql(s2).fetchall())
# [([],)]   -- expected: [([1, 2, 2],)]

Using exp.NullSafeNEQ (_u IS DISTINCT FROM target) preserves NULL elements when target is non-NULL ([1, None]), removes NULL elements for Postgres ARRAY_REMOVE(arr, NULL) ([1, 2, 2]), keeps NULL arrays as NULL, and stays compatible with the existing ARRAY_FUNCS_PROPAGATES_NULLS CASE WHEN target IS NULL THEN NULL ELSE ... END wrapper for dialects like Snowflake/Spark/Trino.

Official Documentation

Dominant language
Python
Stars
9.8k
Forks
1.4k
Avg merge
10h 52m
Merged PRs (30d)
303

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from tobymao/sqlglot

All issues in tobymao/sqlglot

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.