postgres/spark/trino -> duckdb: ARRAY_REMOVE emits _u <> target and drops NULL elements
Maintainers usually reply within 1 day
Nobody has claimed this yet.
Assessment
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Newbie friendliness
- 74/100
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 affectsspark,trino,snowflake) - Write dialect:
duckdb(also affectsclickhouseviaremove_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
- PostgreSQL
array_remove("Comparisons are done usingIS NOT DISTINCT FROMsemantics, so it is possible to removeNULLs"): https://www.postgresql.org/docs/current/functions-array.html - Apache Spark
array_remove: https://spark.apache.org/docs/latest/api/sql/index.html#array_remove - Trino
array_remove: https://trino.io/docs/current/functions/array.html#array_remove - DuckDB
list_filter: https://duckdb.org/docs/stable/sql/functions/list.html#list_filterlist-lambda
- Dominant language
- Python
- Stars
- 9.8k
- Forks
- 1.4k
- Avg merge
- 10h 52m
- Merged PRs (30d)
- 303
Getting set up
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from tobymao/sqlglot
-
Difficulty 1/5 Under an hour Newbie friendliness 85/100
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
Maintainers usually reply within 1 day
-
Difficulty 3/5 1-2 days Newbie friendliness 66/100
Maintainers usually reply within 1 day
-
Difficulty 3/5 1-2 days Newbie friendliness 65/100
Maintainers usually reply within 1 day
Similar issues
-
Difficulty 1/5 Under an hour Newbie friendliness 86/100
py-econometrics/pyfixest#2072 ·
Maintainers usually reply within 1 day
-
~planning docs
Difficulty 2/5 1-3 hours Newbie friendliness 64/100
Kromatic-Innovation/athenaeum#3084 ·
Maintainers usually reply within 1 day
-
Bosch Room Thermostat II number entities offer -500 to 3000 °C setpoint limits and a -32768 to 32767 °C outdoor temperaturePossibly taken @zigpy-review-bot claimed this today. Openbot: code audit
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
zigpy/zha-device-handlers#5769 ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 Half a day Newbie friendliness 80/100
R0Y00/Xbox2Windows#254 ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 85/100
Kludex/python-multipart#375 ·
Maintainers usually reply within 1 day