zhengxiang378928908-code opened a new issue, #18605:
URL: https://github.com/apache/iceberg/issues/18605
### Apache Iceberg version
main (`e70a2efcd`)
### Query engine
Spark 4.2. The same `NOT_EQ` and `NOT` conversion paths exist in the Spark
3.5, 4.0 and 4.1 source directories, but I haven't run the reproduction on
those versions.
### Please describe the bug 🐞
`DELETE FROM t WHERE data <> 'a1'` can also delete rows where `data` is
NULL, although `SELECT ... WHERE data <> 'a1'` doesn't return them. In SQL
`NULL <> 'a1'` is NULL, so those rows should stay.
Iceberg's `notEqual` against a non-null literal and `notStartsWith` match
null values, unlike the corresponding SQL predicates. `SparkV2Filters`
explicitly adds `notNull` for supported `NOT IN` predicates ("col NOT IN (1, 2)
in Spark is equal to notNull(col) && notIn(col, 1, 2) in Iceberg"), but its
`NOT_EQ` path calls `handleNotEqual` without that guard, and its generic `NOT`
path negates the converted child.
If `SparkTable.canDeleteUsingMetadata` accepts the resulting expression,
`deleteWhere` passes it to `deleteFromRowFilter`, and files containing NULL
rows can be deleted. Single-value `NOT IN ('a1')` shows the same result as
`<>`; an optimizer rewrite to an equality may explain this, but I haven't
captured the pushed predicate to confirm it.
### Reproduction
A `TestDeleteFrom`-style test in `spark/v4.2`, run with the Hadoop catalog.
`spark_catalog`, Hive and REST gave the same results for `NOT LIKE` and
single-value `NOT IN`; the other rows were run with the Hadoop catalog only.
Each predicate runs against a freshly created table, once partitioned and
once unpartitioned (`[PARTITIONED BY (data)]` is an optional clause, not
literal SQL):
```sql
CREATE TABLE t (id bigint, data string) USING iceberg [PARTITIONED BY
(data)] TBLPROPERTIES ('format-version'='2');
INSERT INTO t VALUES (1, 'a1'), (2, 'b'), (3, CAST(NULL AS STRING));
SELECT id FROM t WHERE <predicate>; -- returns 2 in every case below
DELETE FROM t WHERE <predicate>;
SELECT id FROM t;
```
In this setup the insert wrote three data files for the partitioned table
and two for the unpartitioned one; the unpartitioned result may depend on the
file layout and on whether a metadata-only delete is possible.
Rows left after the `DELETE` (expected: `1, 3`):
| predicate | unpartitioned | `PARTITIONED BY (data)` |
|---|---|---|
| `data <> 'a1'` | `1` | `1` |
| `data NOT IN ('a1')` | `1` | `1` |
| `data NOT LIKE 'a%'` | `1` | fails with the `ResidualEvaluator` NPE from
#18412 |
| `data NOT IN ('a1', 'x')` | `1, 3` | `1, 3` |
The deletes that removed id 3 committed `delete` snapshots. The two-value
`NOT IN` control kept id 3 (a `delete` snapshot for the partitioned table,
`overwrite` for the unpartitioned one).
With the `ResidualEvaluator` change from #18414 applied, the partitioned
`NOT LIKE` case no longer throws and also leaves only id 1 in a `delete`
snapshot. #18590 contains the same production change.
### Possible fix
The expression used for a metadata-only DELETE has to match exactly the rows
SQL matches, including for NULL. For the cases above that means excluding NULL
for `<>` and for negated `STARTS_WITH`. This can't be a blanket `notNull` for
every single-column `NOT`, since predicates such as `NOT (data IS NOT NULL)` or
a negated null-safe equality do match NULL, and nested or compound predicates
need to keep their SQL semantics too. Would it be better to make the conversion
exact for these cases, or to skip metadata-only deletion when an exact
conversion isn't guaranteed and fall back to the row-level path?
### Willingness to contribute
- [x] I would be willing to contribute a fix for this bug with guidance from
the Iceberg community
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
