[jira] [Created] (IMPALA-15536) UPDATE row count no longer distinguishes "no match" from "no-op" after IMPALA-12588

["Joaquín Antonio De Vicente López (Jira)" <ji...@apache.org>]

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)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org