Joaquín Antonio De Vicente López created IMPALA-15536:
---------------------------------------------------------
Summary: UPDATE row count no longer distinguishes "no match" from
"no-op" after IMPALA-12588
Key: IMPALA-15536
URL: https://issues.apache.org/jira/browse/IMPALA-15536
Project: IMPALA
Issue Type: Improvement
Components: Backend, Frontend
Affects Versions: Impala 5.0.0
Reporter: Joaquín Antonio De Vicente López
IMPALA-12588 ("Don't UPDATE rows that already have the desired value") makes
Impala add implicit predicates to UPDATE statements on Kudu and Iceberg tables,
so rows that already hold the target values are skipped. This avoids useless
writes, but it also changes what the reported affected-row count means:
Modified 0 row(s) (and NumModifiedRows: 0 in the profile) is now returned both
when no row matches the WHERE clause and when the matching rows already hold
the target values.
Clients that use the affected-row count to detect whether a record exists
(reconciliation, idempotency checks, upsert-like logic built on UPDATE) will
break silently when they upgrade to 5.0.0.
The meaning of the counter is also inconsistent within the same release. The
optimization is skipped when the SET list has more than
SKIP_UNNEEDED_UPDATES_COL_LIMIT assignments (default 10) or when any assignment
contains a UDF. So depending on how the UPDATE is written, the same client gets
either "rows matched" or "rows actually changed".
h1. Steps to reproduce
CREATE TABLE test_update (id INT PRIMARY KEY, val STRING)
PARTITION BY HASH(id) PARTITIONS 2 STORED AS KUDU;
INSERT INTO test_update VALUES (1, 'INITIAL');
UPDATE test_update SET val = 'VALUE_1' WHERE id = 1; – case 1
UPDATE test_update SET val = 'VALUE_2' WHERE id = 1; – case 2
UPDATE test_update SET val = 'VALUE_2' WHERE id = 1; – case 3 (no-op)
UPDATE test_update SET val = 'VALUE_2' WHERE id = 999; – case 4 (no match)
h1. Results
|Case|Row matched|Value changed|Impala 4.5.3 (verified)|Impala 5.0.0 / master
(with IMPALA-12588)|
|—|—|—|—|—|
|INITIAL → VALUE_1|Yes|Yes|Modified 1 row(s)|Modified 1 row(s)|
|VALUE_1 → VALUE_2|Yes|Yes|Modified 1 row(s)|Modified 1 row(s)|
|VALUE_2 → VALUE_2|Yes|No|Modified 1 row(s)|*Modified 0 row(s)*|
|id = 999|No|No|Modified 0 row(s)|Modified 0 row(s)|
The 4.5.3 column was obtained by running the script above against Impala
4.5.3-RELEASE (build b3e78704d824) with Kudu 1.18.1.
The master column follows from the tests added by IMPALA-12588 in
testdata/workloads/functional-query/queries/QueryTest/kudu_update.test:
- update tdata set vali=43 where id = 40 on a row that already has vali = 43
now expects NumModifiedRows: 0.
- update tdata set valb=false where name LIKE '%he' matches two rows, but the
expected value changed from NumModifiedRows: 2 to NumModifiedRows: 1 because
one row already had valb = false.
EXPLAIN on master shows the extra predicate on the scan, for example:
UPDATE KUDU [default.test_update]
00:SCAN KUDU [default.test_update]
predicates: val IS DISTINCT FROM 'VALUE_2'
kudu predicates: id = 1
In 4.5.3 the same EXPLAIN shows only kudu predicates: id = 1.
The same rewrite applies to Iceberg UPDATE statements, so Iceberg tables are
affected in the same way.
h1. Existing workaround
IMPALA-12588 already added the query option SKIP_UNNEEDED_UPDATES_COL_LIMIT
(advanced, default `10`). Setting it to a non-positive value turns the
optimization off and restores the previous semantics:
SET SKIP_UNNEEDED_UPDATES_COL_LIMIT=0;
However, this option is not documented: there is no mention of it under `docs/`
on master. Users upgrading have no way to find out that the row-count semantics
changed, or that this option exists.
h1. Proposal
1. *Document the behavior change and the query option.* Call out the new
meaning of the affected-row count as a behavior change in the Impala 5.0.0
release notes, and add `SKIP_UNNEEDED_UPDATES_COL_LIMIT` to the query options
documentation, including that `0` disables the optimization.
2. *Report matched rows separately* from modified rows, in both the client
output and the query profile, for example:
Modified 0 row(s), Found 1 row(s)
This keeps the performance benefit of IMPALA-12588 and gives clients back the
information they lost. Note that today the extra predicate is evaluated in the
scan (or in the join), so skipped rows never reach the table sink. Counting
matched rows would require counting them before that predicate is applied, for
example with a separate counter at the point where the predicate is evaluated.
3. *Optional:* consider making the optimization opt-in (default
`SKIP_UNNEEDED_UPDATES_COL_LIMIT=0`), or at least making the reported count
consistent regardless of the number of `SET` assignments or the presence of
UDFs.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
