morningman opened a new pull request, #70306:
URL: https://github.com/apache/doris/pull/70306
### Background
`nereids_syntax_p1/mv/aggregate/agg_sync_mv` creates one synchronous
materialized view per aggregate function on the same base table and checks that
queries are rewritten to it. An unaliased aggregate in a sync MV gets a
generated column name such as `__stddev_1`, and a new MV's column names must
not repeat any column of the table's full schema, which includes the columns of
its existing sync MVs.
`tpch_unique_sql_zstd_bucket1_p1/sql/test_left_anti_join_batch_size.sql` runs a
`NOT EXISTS` query over TPC-H `lineitem` with the hint `SET_VAR(batch_size=3)`,
so that the left anti join sees many small blocks; the suite's `load.groovy`
creates the tables in the suite database
`regression_test_tpch_unique_sql_zstd_bucket1_p1`.
### What problem does this PR solve?
Issue Number: None
Related PR: #65336 and #65338 (the branch-4.1 fixes by @shuke987, ported
here), #69446 (the branch-4.2 follow-up by @mrhhsg, ported here), #56986 (moved
the TPC-H unique suite from p0 to p1), #65538 (fixed the suite's second load on
master)
PR Type: Test
Problem Summary:
Neither suite can pass on master. `agg_sync_mv` stops at `mv_sync41` with
`Duplicate column name '__stddev_1'`, because `stddev(kint)` and
`stddev_pop(kint)` are one function and get one generated name; `variance` /
`var_pop` and `variance_samp` / `var_samp` collide the same way. Past that
point its last check compares `map_agg` output with a stale golden.
`test_left_anti_join_batch_size` reads
`regression_test_tpch_unique_sql_zstd_bucket1_p0.lineitem`, a database that no
suite creates since #56986, and its hint is misspelled `SEV_VAR`, which Doris
ignores. This PR ports the branch-4.1 fixes (aliases, the suite's own
`lineitem`, `SET_VAR`) and branch-4.2's order-independent rewrite of the last
check.
### Details
- **Failure:** `agg_sync_mv` fails on every run at `mv_sync41` with `errCode
= 2, detailMessage = Duplicate column name '__stddev_1'`. With the aliases
alone it fails at its last check, `qt_test`: the golden expects `{}` for the
`kbint = NULL` group, master returns `{null:"null"}`, and the `kbint = 1` group
now holds two entries (`{null:"string1", 0:"string1"}`).
`test_left_anti_join_batch_size` fails with `Database
[regression_test_tpch_unique_sql_zstd_bucket1_p0] does not exist` on any
cluster that has not kept a database from the old p0 suite.
- **Cause:** In `agg_sync_mv`, `stddev_pop`, `stddev`, and `std` bind to the
`Stddev` function, `var_pop` and `variance` to `Variance`, and `var_samp` and
`variance_samp` to `VarianceSamp`, so the unaliased outputs of each pair get
the same name, and `MaterializedViewHandler` rejects a name already in the
table's full schema. The `qt_test` golden predates `map_agg` keeping NULL keys,
and a map with two entries prints them in no guaranteed order. In the anti-join
case, the database name is left over from when the suite lived in p0, and
`SEV_VAR` is not a hint name, so `batch_size` keeps its default (`SELECT
/*+SEV_VAR(batch_size=3)*/ @@batch_size` returns 8160; with `SET_VAR` it
returns 3).
- **Stability evidence:** The failures were deterministic, and the fixes
remove their causes: the aliases make the generated names irrelevant, the
rewritten `qt_test` checks the map size, NULL-key presence, and the value for
each key instead of the map's text, and the query reads the database its own
suite loads. Results of the local runs are under Testing.
### Testing
All runs used `./run-regression-test.sh --run -d <dir> -s <suite>` against a
local master-based cluster.
| Suite | Version | Result |
|---|---|---|
| `agg_sync_mv` | master | fails at `mv_sync41`: `Duplicate column name
'__stddev_1'` |
| `agg_sync_mv` | with the aliases only | fails at `qt_test`: expected `{}`,
got `{null:"null"}` |
| `agg_sync_mv` | this PR | passes |
| `test_left_anti_join_batch_size` | master | fails: database
`regression_test_tpch_unique_sql_zstd_bucket1_p0` does not exist |
| `test_left_anti_join_batch_size` | this PR | passes against the existing
`.out` |
The new `qt_test` block in `agg_sync_mv.out` was generated by the framework
(`-forceGenOut`) on master and equals branch-4.2's block. The rest of the
regenerated file differs from the committed one only in how doubles print (`0`
instead of `0.0`), which the framework compares by value, so those blocks are
left unchanged, as #69446 did. For the anti-join suite, the full TPC-H fixture
download was too slow locally, so `lineitem` held only the rows with
`l_orderkey < 10000`, which are all the query reads, taken from the start of
the sf0.1 fixture file (sorted by `l_orderkey`).
### Notes for Reviewers
- The second commit carries branch-4.2's #69446; without it `agg_sync_mv`
still fails on master at its last check. Apart from that block, the `.out`
stays as on master, including `select_group_array_intersect` for the NULL group
(`\N [1]`, from #66284), where branch-4.2 has `\N []`.
- `mv_sync42` (`stddev_samp`) has no synonym MV in the suite; #65336 aliased
it too, and this PR keeps that for consistency.
### Release note
None
### Check List (For Author)
- Behavior changed: No
- Does this need documentation: No
- Pre-submission status: Ready
### AI Disclosure
- Tool: Claude Code (Claude Opus 5.5)
- Generated: the port of #65336, #65338, and #69446, the regenerated `.out`
block, the local verification runs, and this description
- Human review: not stated
- Prompts: The author asked to continue moving branch-4.2's changes back to
master, where branch-4.1 #65336 and #65338 were recorded as one task, and chose
to verify both suites on a local cluster, loading only the `lineitem` rows the
anti-join query reads.
--
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]
