8000 avoid copy by Antonov548 · Pull Request #1 · krlmlr/duckdb · GitHub
[go: up one dir, main page]

Skip to content

avoid copy - #1

Merged
krlmlr merged 2 commits into
krlmlr:f-2841-nestedfrom
Antonov548:f-2841-nested
Aug 17, 2023
Merged

krlmlr merged 2 commits into
krlmlr:f-2841-nestedfrom
Antonov548:f-2841-nested

Conversation

@Antonov548
Copy link
Copy Markdown

No description provided.

@krlmlr
krlmlr merged commit d0e3a37 into krlmlr:f-2841-nested Aug 17, 2023
krlmlr pushed a commit that referenced this pull request Aug 17, 2023
krlmlr pushed a commit that referenced this pull request Oct 18, 2023
make data chunk initialization optional during deserialization
krlmlr pushed a commit that referenced this pull request Jan 22, 2025
Fixes duckdblabs/duckdb-internal#3922

The failing query
```SQL
SET order_by_non_integer_literal=true;
SELECT DISTINCT ON ( 'string' ) 'string', GROUP BY CUBE ( 'string', ), 'string' IN ( SELECT 'string' ), HAVING 'string' IN ( SELECT 'string');
```

The Plan generated before optimization is below. During optimization
there is an attempt to convert the mark join into a semi. Before this
conversion takes place, we usually check to make sure the mark join is
not used in any operators above the mark join to prevent plan
verification errors. Up until this point, only logical projections were
checked for mark joins. Turns out this query is planned in such a way
that the mark join is in one of the expressions of the aggregate
operator. This was not checked, so the mark to semi conversion would
take place. The fix is to modify the filter pushdown optimization so
that it stores table indexes from logical aggregate operators.

```
┌───────────────────────────┐
│       PROJECTION #1       │
│    ────────────────────   │
│    Expressions: #[2.0]    │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    Expressions: #[2.1]    │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    AGGREGATE #2, #3, #4   │
│    ────────────────────   │
│          Groups:          │
│          'string'         │
│          #[14.0]          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      COMPARISON_JOIN      │
│    ────────────────────   │
│      Join Type: MARK      │
│                           ├──────────────┐
│        Conditions:        │              │
│    ('string' = #[8.0])    │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│       DUMMY_SCAN #0       ││       PROJECTION #8       │
│    ────────────────────   ││    ────────────────────   │
│                           ││   Expressions: 'string'   │
└───────────────────────────┘└─────────────┬─────────────┘
                             ┌─────────────┴─────────────┐
                             │       DUMMY_SCAN #7       │
                             │    ────────────────────   │
                             └───────────────────────────┘
```
krlmlr pushed a commit that referenced this pull request Mar 14, 2025
We had two users crash with the following backtrace:

```
    frame #0: 0x0000ffffab2571ec
    frame #1: 0x0000aaaaac00c5fc duckling`duckdb::InternalException::InternalException(this=<unavailable>, msg=<unavailable>) at exception.cpp:328:2
    frame #2: 0x0000aaaaac1ee418 duckling`duckdb::optional_ptr<duckdb::OptimisticDataWriter, true>::CheckValid(this=<unavailable>) const at optional_ptr.hpp:34:11
    frame #3: 0x0000aaaaac1eea8c duckling`duckdb::MergeCollectionTask::Execute(duckdb::PhysicalBatchInsert const&, duckdb::ClientContext&, duckdb::GlobalSinkState&, duckdb::LocalSinkState&) [inlined] duckdb::optional_ptr<duckdb::OptimisticDataWriter, true>::operator*(this=<unavailable>) at optional_ptr.hpp:43:3
    frame #4: 0x0000aaaaac1eea84 duckling`duckdb::MergeCollectionTask::Execute(this=0x0000aaaaf1b06150, op=<unavailable>, context=0x0000aaaba820d8d0, gstate_p=0x0000aaab06880f00, lstate_p=<unavailable>) at physical_batch_insert.cpp:219:90
    frame #5: 0x0000aaaaac1d2e10 duckling`duckdb::PhysicalBatchInsert::Sink(duckdb::ExecutionContext&, duckdb::DataChunk&, duckdb::OperatorSinkInput&) const [inlined] duckdb::PhysicalBatchInsert::ExecuteTask(this=0x0000aaaafa62ab40, context=<unavailable>, gstate_p=0x0000aaab06880f00, lstate_p=0x0000aab12d442960) const at physical_batch_insert.cpp:425:8
    frame #6: 0x0000aaaaac1d2dd8 duckling`duckdb::PhysicalBatchInsert::Sink(duckdb::ExecutionContext&, duckdb::DataChunk&, duckdb::OperatorSinkInput&) const [inlined] duckdb::PhysicalBatchInsert::ExecuteTasks(this=0x0000aaaafa62ab40, context=<unavailable>, gstate_p=0x0000aaab06880f00, lstate_p=0x0000aab12d442960) const at physical_batch_insert.cpp:431:9
    frame #7: 0x0000aaaaac1d2dd8 duckling`duckdb::PhysicalBatchInsert::Sink(this=0x0000aaaafa62ab40, context=0x0000aab2fffd7cb0, chunk=<unavailable>, input=<unavailable>) const at physical_batch_insert.cpp:494:4
    frame #8: 0x0000aaaaac353158 duckling`duckdb::PipelineExecutor::ExecutePushInternal(duckdb::DataChunk&, duckdb::ExecutionBudget&, unsigned long) [inlined] duckdb::PipelineExecutor::Sink(this=0x0000aab2fffd7c00, chunk=0x0000aab2fffd7d30, input=0x0000fffec0aba8d8) at pipeline_executor.cpp:521:24
    frame #9: 0x0000aaaaac353130 duckling`duckdb::PipelineExecutor::ExecutePushInternal(this=0x0000aab2fffd7c00, input=0x0000aab2fffd7d30, chunk_budget=0x0000fffec0aba980, initial_idx=0) at pipeline_executor.cpp:332:23
    frame #10: 0x0000aaaaac34f7b4 duckling`duckdb::PipelineExecutor::Execute(this=0x0000aab2fffd7c00, max_chunks=<unavailable>) at pipeline_executor.cpp:201:13
    frame #11: 0x0000aaaaac34f258 duckling`duckdb::PipelineTask::ExecuteTask(duckdb::TaskExecutionMode) [inlined] duckdb::PipelineExecutor::Execute(this=<unavailable>) at pipeline_executor.cpp:278:9
    frame #12: 0x0000aaaaac34f250 duckling`duckdb::PipelineTask::ExecuteTask(this=0x0000aab16dafd630, mode=<unavailable>) at pipeline.cpp:51:33
    frame #13: 0x0000aaaaac348298 duckling`duckdb::ExecutorTask::Execute(this=0x0000aab16dafd630, mode=<unavailable>) at executor_task.cpp:49:11
    frame #14: 0x0000aaaaac356600 duckling`duckdb::TaskScheduler::ExecuteForever(this=0x0000aaaaf0105560, marker=0x0000aaaaf00ee578) at task_scheduler.cpp:189:32
    frame #15: 0x0000ffffab0a31fc
    frame #16: 0x0000ffffab2ad5c8
```

Core dump analysis showed that the assertion `D_ASSERT(lstate.writer);`
in `MergeCollectionTask::Execute` (i.e. it is crashing because
`lstate.writer` is NULLPTR) was not satisfied when
`PhysicalBatchInsert::Sink` was processing merge tasks from (other)
pipeline executors.

My suspicion is that this is only likely to happen for heavily
concurrent workloads (applicable to the two users which crashed). The
patch submitted as part of this PR has addressed the issue for these
users.
krlmlr pushed a commit that referenced this pull request Jun 21, 2026
Each run, before rewriting, pushes the pre-de-merge tip to
<branch>-<NN> (NN = DEMERGED_SO_FAR, 0-padded) with a create-only push:
- preserves the original state that the run's force-push overwrites
  (numbered, immutable audit trail: -00 = original main, -01 after one
  de-merge, ...), and
- doubles as a concurrency gate: the push fails if the ref already
  exists, so a second concurrent run on the same step aborts.

bubble-cursor.sh emits GATE_BRANCH; SKILL.md step 0 does the gated push.
Pushed main-v1.5-variegata-00 (run #1 original input = main-dag, tree==main).

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01DtisRUvmSNJw5KnvrpbHrM
krlmlr pushed a commit that referenced this pull request Jun 21, 2026
…d-budget

Skill corrections and additions from runs #1–#2:
- Gate (step 0): detect creation positively ([new branch] in push output);
  a same-value push to an existing ref is a no-op success, so don't rely on
  'push fails if exists'. Abort if the snapshot branch already existed.
- Conflicts (step 3): never -X theirs/ours; every conflict and red herring is
  reconciled manually by the agent using the merge commit as the oracle.
- De-merge (step 2): tree-preserving graft (reattach.sh), not
  rebase --rebase-merges (which re-merges upper back-merges and conflicts).
  Base each run on origin/<branch> (the previous result), never main-dag;
  NEXT_MERGE is that branch's back-merge SHA.
- Build budget: drop session-length assumption; use a dedicated build/test
  worktree separate from the de-merge worktree; note tests run serially
  (unittest has no parallel option) — shard for speed.
- Reconciliation audit (reconcile-audit.sh + section): the reconcile commit's
  diff is the merge's cross-side resolutions; review non-generated deletions +
  semantic changes, record a factual note, do not gate (merge tree is oracle).
- RUN-NOTES: run #2 record + per-commit build times.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01DtisRUvmSNJw5KnvrpbHrM
krlmlr pushed a commit that referenced this pull request Jun 21, 2026
…h run

REPLAY-LOG.md lists, oldest-first (git log --reverse), every commit replayed
since the v1.5 bifurcation. Each entry: original commit (L1) + its -dag commit
and per-run derived commit (L2, with role: replayed / empty-absorbed /
reattached) + conflict resolution (L3); synthetic commits (reconcile/checkpoint)
inline. All links to github.com/krlmlr/duckdb. Seeded with runs #1-#2.

SKILL.md: new 'Replay log (provenance)' section + step 5 now updates the log
before publishing, so it grows with every run.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01DtisRUvmSNJw5KnvrpbHrM
krlmlr pushed a commit that referenced this pull request Jul 21, 2026
config.hpp (fan_in ~690, the #1 cost header) pulled the heavy logging/log_manager.hpp
only for a by-value LogConfig member - but LogConfig lives in the light
logging/logging.hpp. log_manager.hpp dragged log_storage + thread_context +
query_profiler onto every config includer. Include logging.hpp instead.

Consumers that genuinely use logging (LogManager, DUCKDB_LOG macros, LogTypes,
LogStorage) - reached transitively through config before - now include the logging
headers directly (9 src .cpp + shell.cpp + shell_renderer.hpp).

total_tu_include_cost
before: 383,785
after:  376,671
pull Bot pushed a commit that referenced this pull request Sep 10, 2026
<details>
<summary>WARNING: ThreadSanitizer: data race; Read of size 8</summary>

```c++
  Filters: test/sql/show_select/summarize_subquery.test

  [0/1] (0%): test/sql/show_select/summarize_subquery.test==================
  WARNING: ThreadSanitizer: data race (pid=80064)
    Read of size 8 at 0x00010708e758 by thread T10:
      #0 duckdb::ArenaAllocator::AlignNext() <null> (libduckdb.dylib:arm64+0x2a37af0)
      #1 std::__1::vector<double, duckdb::arena_stl_allocator<double>>::reserve(unsigned long) vector.h:1100 (libduckdb.dylib:arm64+0x3184418)
      #2 duckdb_tdigest::TDigest::updateCumulative() t_digest.hpp:538 (libduckdb.dylib:arm64+0x31819cc)
      #3 duckdb_tdigest::TDigest::process() t_digest.hpp:583 (libduckdb.dylib:arm64+0x31816d8)
      #4 void duckdb::(anonymous namespace)::ApproxQuantileScalarOperation::Finalize<long long, duckdb::(anonymous namespace)::ApproxQuantileState>(duckdb::(ano  nymous namespace)::ApproxQuantileState&, long long&, duckdb::AggregateFinalizeData&) approximate_quantile.cpp:162 (libduckdb.dylib:arm64+0x318d540)
      #5 void duckdb::AggregateFunction::StateFinalize<duckdb::(anonymous namespace)::ApproxQuantileState, long long, duckdb::(anonymous namespace)::ApproxQuant  ileScalarOperation>(duckdb::Vector&, duckdb::AggregateFinalizeInputData&, duckdb::Vector&, unsigned long long, unsigned long long) aggregate_function.hpp:822   (libduckdb.dylib:arm64+0x318ca64)
      #6 duckdb::RowOperations::FinalizeStates(duckdb::RowOperationsState&, duckdb::TupleDataLayout&, duckdb::Vector&, duckdb::DataChunk&, unsigned long long) r  ow_aggregate.cpp:182 (libduckdb.dylib:arm64+0x166f848)
      #7 duckdb::RadixHTLocalSourceState::Scan(duckdb::RadixHTGlobalSinkState&, duckdb::RadixHTGlobalSourceState&, duckdb::DataChunk&) <null> (libduckdb.dylib:a  rm64+0x2458dac)
      #8 duckdb::RadixHTLocalSourceState::ExecuteTask(duckdb::RadixHTGlobalSinkState&, duckdb::RadixHTGlobalSourceState&, duckdb::DataChunk&) <null> (libduckdb.  dylib:arm64+0x2457ea0)
      #9 duckdb::RadixPartitionedHashTable::GetData(duckdb::ExecutionContext&, duckdb::DataChunk&, duckdb::GlobalSinkState&, duckdb::OperatorSourceInput&) const   <null> (libduckdb.dylib:arm64+0x2459624)
      #10 duckdb::PhysicalHashAggregate::GetDataInternal(duckdb::ExecutionContext&, duckdb::DataChunk&, duckdb::OperatorSourceInput&) const physical_hash_aggreg  ate.cpp:978 (libduckdb.dylib:arm64+0x21305cc)
      #11 duckdb::PhysicalOperator::GetData(duckdb::ExecutionContext&, duckdb::DataChunk&, duckdb::OperatorSourceInput&) const <null> (libduckdb.dylib:arm64+0x2  448dac)
...

    Previous write of size 8 at 0x00010708e758 by thread T7:
      #0 std::__1::vector<double, duckdb::arena_stl_allocator<double>>::reserve(unsigned long) vector.h:1100 (libduckdb.dylib:arm64+0x31844a0)
      #1 duckdb_tdigest::TDigest::updateCumulative() t_digest.hpp:538 (libduckdb.dylib:arm64+0x31819cc)
      #2 duckdb_tdigest::TDigest::process() t_digest.hpp:583 (libduckdb.dylib:arm64+0x31816d8)
      #3 void duckdb::(anonymous namespace)::ApproxQuantileScalarOperation::Finalize<long long, duckdb::(anonymous namespace)::ApproxQuantileState>(duckdb::(ano  nymous namespace)::ApproxQuantileState&, long long&, duckdb::AggregateFinalizeData&) approximate_quantile.cpp:162 (libduckdb.dylib:arm64+0x318d540)
      #4 void duckdb::AggregateFunction::StateFinalize<duckdb::(anonymous namespace)::ApproxQuantileState, long long, duckdb::(anonymous namespace)::ApproxQuant  ileScalarOperation>(duckdb::Vector&, duckdb::AggregateFinalizeInputData&, duckdb::Vector&, unsigned long long, unsigned long long) aggregate_function.hpp:822   (libduckdb.dylib:arm64+0x318ca64)
      #5 duckdb::RowOperations::FinalizeStates(duckdb::RowOperationsState&, duckdb::TupleDataLayout&, duckdb::Vector&, duckdb::DataChunk&, unsigned long long) r  ow_aggregate.cpp:182 (libduckdb.dylib:arm64+0x166f848)
      #6 duckdb::RadixHTLocalSourceState::Scan(duckdb::RadixHTGlobalSinkState&, duckdb::RadixHTGlobalSourceState&, duckdb::DataChunk&) <null> (libduckdb.dylib:a  rm64+0x2458dac)
      #7 duckdb::RadixHTLocalSourceState::ExecuteTask(duckdb::RadixHTGlobalSinkState&, duckdb::RadixHTGlobalSourceState&, duckdb::DataChunk&) <null> (libduckdb.  dylib:arm64+0x2457ea0)
      #8 duckdb::RadixPartitionedHashTable::GetData(duckdb::ExecutionContext&, duckdb::DataChunk&, duckdb::GlobalSinkState&, duckdb::OperatorSourceInput&) const   <null> (libduckdb.dylib:arm64+0x2459624)
      #9 duckdb::PhysicalHashAggregate::GetDataInternal(duckdb::ExecutionContext&, duckdb::DataChunk&, duckdb::OperatorSourceInput&) const physical_hash_aggrega  te.cpp:978 (libduckdb.dylib:arm64+0x21305cc)
      #10 duckdb::PhysicalOperator::GetData(duckdb::ExecutionContext&, duckdb::DataChunk&, duckdb::OperatorSourceInput&) const <null> (libduckdb.dylib:arm64+0x2  448dac)
...

    Location is heap block of size 56 at 0x00010708e740 allocated by main thread:
      #0 operator new(unsigned long) <null> (libclang_rt.tsan_osx_dynamic.dylib:arm64e+0x91650)
      #1 duckdb::ArenaAllocator::AllocateNewBlock(unsigned long long) <null> (libduckdb.dylib:arm64+0x2a37830)
      #2 std::__1::vector<duckdb_tdigest::Centroid, duckdb::arena_stl_allocator<duckdb_tdigest::Centroid>>::reserve(unsigned long) vector.h:1100 (libduckdb.dyli  b:arm64+0x3180da0)
      #3 void duckdb::(anonymous namespace)::ApproxQuantileOperation::Operation<long long, duckdb::(anonymous namespace)::ApproxQuantileState, duckdb::(anonymou  s namespace)::ApproxQuantileScalarOperation>(duckdb::(anonymous namespace)::ApproxQuantileState&, long long const&, duckdb::AggregateUnaryInput&) approximate_  quantile.cpp:122 (libduckdb.dylib:arm64+0x318ccd4)
      #4 void duckdb::AggregateFunction::UnaryScatterUpdate<duckdb::(anonymous namespace)::ApproxQuantileState, long long, duckdb::(anonymous namespace)::Approx  QuantileScalarOperation>(duckdb::Vector*, duckdb::AggregateInputData&, unsigned long long, duckdb::Vector&, unsigned long long) aggregate_function.hpp:782 (li  bduckdb.dylib:arm64+0x318c454)
      #5 duckdb::RowOperations::UpdateStates(duckdb::RowOperationsState&, duckdb::AggregateObject&, duckdb::Vector&, duckdb::DataChunk&, unsigned long long, duc  kdb::optional_ptr<duckdb::ClusteredAggr const, true>) aggregate_function.hpp (libduckdb.dylib:arm64+0x166ed24)
      #6 duckdb::GroupedAggregateHashTable::UpdateAggregates(duckdb::DataChunk&, duckdb::vector<unsigned long long, false, std::__1::allocator<unsigned long lon  g>> const&, unsigned long long, bool) <null> (libduckdb.dylib:arm64+0x240d234)
      #7 duckdb::GroupedAggregateHashTable::AddChunk(duckdb::DataChunk&, duckdb::Vector&, duckdb::DataChunk&, duckdb::vector<unsigned long long, false, std::__1  ::allocator<unsigned long long>> const&) <null> (libduckdb.dylib:arm64+0x240f000)
      #8 duckdb::GroupedAggregateHashTable::AddChunk(duckdb::DataChunk&, duckdb::DataChunk&, duckdb::vector<unsigned long long, false, std::__1::allocator<unsig  ned long long>> const&) <null> (libduckdb.dylib:arm64+0x240cb30)
      #9 duckdb::RadixPartitionedHashTable::Sink(duckdb::ExecutionContext&, duckdb::DataChunk&, duckdb::OperatorSinkInput&, duckdb::DataChunk&, duckdb::vector<u  nsigned long long, false, std::__1::allocator<unsigned long long>> const&) const <null> (libduckdb.dylib:arm64+0x2455190)
      #10 duckdb::PhysicalHashAggregate::Sink(duckdb::ExecutionContext&, duckdb::DataChunk&, duckdb::OperatorSinkInput&) const physical_hash_aggregate.cpp:466 (  libduckdb.dylib:arm64+0x212bafc)

  SUMMARY: ThreadSanitizer: data race (libduckdb.dylib:arm64+0x2a37af0) in duckdb::ArenaAllocator::AlignNext()+0x2c
  ==================
```
</details>

`SUMMARIZE` calculates three `approx_quantile` aggregates (`q25`, `q50`,
`q75`). Each aggregate owns a TDigest whose vectors use the hash table’s
`ArenaAllocator`.

Two hash-aggregate worker threads concurrently finalized separate
TDigest states:

- Both called `TDigest::process()` → `updateCumulative()` →
`vector::reserve()`.
- The states shared the same non-thread-safe `ArenaAllocator`.
- One thread read allocator position metadata in
`ArenaAllocator::AlignNext()` while another wrote it.
- The shared arena had originally been allocated during
`approx_quantile` aggregation on the main thread.

Result: a race in arena allocation during parallel `approx_quantile`
finalization, reported at `ArenaAllocator::AlignNext()`.

It was intermittent because `reserve()` only occurs when vector capacity
must grow, and the worker calls had to overlap closely enough.

The fix reserves the cumulative buffer and sufficient centroid merge
capacity when the TDigest is constructed and its allocator is still used
by only one thread. Parallel finalization then operates entirely within
already allocated buffers and no longer mutates the shared arena.
pull Bot pushed a commit that referenced this pull request Sep 14, 2026
…kdb#25378)

`PrefixRangeBitmap::LookupKeys` (no-selection overload) ignored its
`count`
parameter and iterated `keys.ValidValues<T>()`, which spans the vector's
buffer capacity rather than the rows being filtered. The caller sizes
`result_sel` to `approved_tuple_count` via `PrepareCapacity`, so a
vector
holding more valid values than that count overflows the selection
buffer.

The write is unconditional per iteration (the cursor only advances on a
match), so it overflows even when nothing matches.

Fixed by bounding the loop by `count`, matching the selection overload
directly below it.

Found via AddressSanitizer debugging a flaky ducklake test:

```
WRITE of size 4 at 0x7767409efc78 thread T73
  #0 SelectionVector::set_index                          selection_vector.hpp:126
  #1 PrefixRangeBitmap<uint64_t>::LookupKeys<int64_t,..> table_filter_prefix_range_function.cpp:148
  #2 NumericPrefixRangeFilter<int64_t>::LookupKeys       :322
  #3 PrefixRangeFilterExecutor::FilterSelection          table_filter_state.cpp:386
0x7767409efc78 is located 0 bytes after 88-byte region
```

88 bytes = 22 `sel_t` entries; the write lands at index 22.

No test: existing prefix-range tests do not trip ASAN, and I could not
construct a deterministic DuckDB-level reproducer. Observed via a
DuckLake
concurrency test on a musl build, where the corrupted chunk aborts in
`free()`; glibc tolerates it silently.

Made with AI help
pull Bot pushed a commit that referenced this pull request Sep 22, 2026
Passing positional column references (#1, #2) to a macro can bind them
by the input column names instead of their argument positions. If those
names match the macro parameters in a different order, the query
silently returns the wrong result. If the names do not match, the call
can fail with a Binder Error.
Skip inferred aliases for positional references inside function
expressions, matching the existing handling of ordinary column
references from duckdb#9990. Explicit argument names and output aliases
outside function calls are preserved.

Fixes duckdb#25758 duckdb#19516
krlmlr pushed a commit that referenced this pull request Nov 8, 2026
…uckdb#28376)

`tools/shell/tests/test_address_space_limit.py` intermittently fails in
the merge queue with a SIGSEGV and empty stderr where DuckDB should
report an out-of-memory error. Three queue ejections in two days hit it,
across two unrelated PRs and two RLIMIT values (runs 41615035663,
41674312724, 41809347880).

`madvise_MADV_DONTNEED_zeroes_pages` in
`third_party/jemalloc/src/pages.c` runs in `pages_boot`, during the lazy
`malloc_init` triggered by the first jemalloc allocation
(`DuckCatalog::Initialize` in the shell). It maps a one-page probe, and
when that `mmap` fails it only warns and falls through to `memset(addr,
'A', PAGE)` with `addr == MAP_FAILED`:

```
#0  __memset_generic
#1  duckdb_je_pages_boot             (madvise_MADV_DONTNEED_zeroes_pages inlined, pages.c:84)
#2  malloc_init_hard_a0_locked
#3  malloc_init_hard
#4  duckdb_je_malloc_default
#5  duckdb::Allocator::AllocateData(unsigned long)
...
#16 duckdb::DuckCatalog::Initialize(bool)
```

stderr stays empty because the vendored `malloc_write` is compiled out
in non-DEBUG builds. The failure is stochastic because jemalloc boot
races against the worker-thread stack reservations (16 threads x 8 MiB
on the CI runners), so the probe only lands in an exhausted moment for
some layouts and timings. The allocation-failure fallbacks from duckdb#27767
(arena 0 sharing, tcache-less threads) cover the arena and tcache
allocations later in the same startup path. This probe was the remaining
unchecked dereference of a fresh mapping in boot.

The fix returns 0 (faulty) when the probe page cannot be mapped.
`pages_purge_forced` then reports failure, freed extents are recorded as
not zeroed, and reuse that needs zeroed pages falls back to `memset`, so
zeroing never relies on `MADV_DONTNEED`. The flag is sticky for the
process lifetime: a transient failure disables forced purging for good,
which can keep more memory resident afterwards. The alternative of
failing `pages_boot` (what the lazy-purge probe next to it does) would
turn a transient spike into a spurious out-of-memory error instead.
Under the shipped configuration the degradation is limited: decommit and
`MADV_FREE` lazy purging are unaffected. The change is documented in
`third_party/jemalloc/README.md` next to the other DuckDB modifications.

Plain `RLIMIT_AS` stress did not reproduce the crash locally (960 runs
across 12 limits), so the reproduction uses an `LD_PRELOAD` shim that
fails the Nth and following anonymous `mmap`s with ENOMEM. Before the
fix, every injection point that falls inside jemalloc boot segfaults in
the probe. After it, the same points produce the regular out-of-memory
error, a single transient failure at any index is survived completely,
and sticky and single-failure sweeps up to N=3000 plus the original
RLIMIT matrix show no remaining crash. `test_address_space_limit.py`
passes unchanged. No new RLIMIT values were added to it: the crash
window is a race against thread startup that specific limits cannot pin,
which is also why the same limit both passed and failed on CI.
krlmlr pushed a commit that referenced this pull request Nov 11, 2026
…n throw below the join (duckdb#28573)

Fixes duckdb#28568

### Problem

`PartialAggregatePushdown` moves a grouped aggregate below the join it
sits on and takes the aggregate's argument expressions with it. The
lower aggregate runs over the whole aggregate-side input, so an argument
that can raise an error is evaluated on rows the join would have
eliminated:

```sql
CREATE TABLE dim AS SELECT i AS id, i % 5 AS a, i % 3 AS b FROM range(100) t(i) WHERE i <> 50;
CREATE TABLE fact AS SELECT i AS id, CASE WHEN i = 50 THEN 'oops' ELSE i::VARCHAR END AS v FROM range(100) t(i);
SELECT a, b, sum(CAST(v AS INTEGER)) FROM fact JOIN dim ON fact.id = dim.id GROUP BY a, b ORDER BY a, b;
```

Row `id = 50` exists only in `fact`, so the join drops it and the
`'oops'` value is never cast by the query as written. On main the query
fails with

```
Conversion Error: Could not convert string 'oops' to INT32 when casting from source column v
```

while `SET disabled_optimizers = 'partial_aggregate_pushdown'` returns
the 15 groups. The same happens with arithmetic that can overflow:
`sum(v * 2)` with the BIGINT maximum in the unmatched row raises `Out of
Range Error`. The plan shows the cast under the `sum(...) EXPORT_STATE`
aggregate over the full `fact` scan:

```
Perfect Hash Group By: combine_aggr(#2)
└─ Hash Join (INNER) id = #0
   ├─ Seq Scan dim
   └─ Perfect Hash Group By: sum(#1) EXPORT_STATE
      └─ Projection: #0, CAST(v AS INTEGER)
         └─ Seq Scan fact
```

### Fix (first commit)

Before pushing an aggregate below an inner join, refuse when any
aggregate argument `CanThrow()` - the predicate the other optimizer
passes use when an expression would be evaluated on more rows than
before (`ScalarAggregateMerger`, `ProjectionPlacement`, filter pushdown,
...). The check sits in `AnalyzePushdown` for the one-sided path and
next to `DEClassifyAggregates` for the double-eager path, so it also
sees the expressions `FuseInterveningProjections` inlined into the
arguments from a projection below the aggregate. A LEFT/RIGHT join that
preserves the aggregate side keeps every row of that side, so the
argument is evaluated on all of them either way; the check is therefore
limited to inner joins and those pushdowns still fire. Aggregate
`FILTER` clauses are already rejected by the pass and the
lower-aggregate groups are plain column references, so the arguments are
the only expressions the pass moves.

### Keeping the common shapes pushed (second commit)

With the first commit alone the pushdown stops firing for TPC-DS q04,
q11 and q66 and for one of the nine aggregates in q14, because
`sum(ss_ext_list_price - ss_ext_discount_amt)`, `sum(cs_quantity *
cs_list_price)` and `sum(... / 2)` were all reported as able to throw:

- the decimal `+`, `-` and `*` operators are registered as fallible, but
`DecimalAddSubtractResolveTypes` / `DecimalMultiplyResolveTypes` widen
the result type so that the operation cannot overflow, and only install
an overflow-checking operator when the width is clamped at the DECIMAL
maximum;
- the DOUBLE `/` operator is registered as fallible, but under
`ieee_floating_point_ops` (the default) a division by zero yields inf or
nan, and with `error_on_division_by_zero = false` it yields NULL.

The second commit clears the error mode at bind time in exactly these
cases (the same thing `regexp_replace` and `substring` do for safe
constant arguments), so `CanThrow()` is precise for them. The
overflow-checked decimal operators, `//`, `%` and the DOUBLE `/` without
IEEE semantics stay fallible. With `CALL dsdgen(sf = 1)` the pushdown
then fires for the same 15 queries as on main (q04, q05, q11, q14, q18,
q22, q36, q43, q66, q67, q70, q74, q77, q80, q86) with the same number
of combined aggregates in each; `test/sql/tpcds/tpcds_sf1.test_slow`
passes.

### Tests

`test/optimizer/partial_aggregate_pushdown_throwing_argument.test` runs
the two repros with the optimizer on, pins that the cast and the
overflowing multiplication keep the aggregate above the inner join, and
that `TRY_CAST`, a plain column and the LEFT JOIN variant are still
pushed down. The second commit adds pins for the decimal arithmetic and
the floating-point division (pushed), and for `//`, the division without
IEEE semantics and a DECIMAL(38,2) addition (not pushed).

Note: this PR was generated by an AI coding agent and reviewed by the
author.
krlmlr pushed a commit that referenced this pull request Nov 12, 2026
…uckdb#28376)

`tools/shell/tests/test_address_space_limit.py` intermittently fails in
the merge queue with a SIGSEGV and empty stderr where DuckDB should
report an out-of-memory error. Three queue ejections in two days hit it,
across two unrelated PRs and two RLIMIT values (runs 41615035663,
41674312724, 41809347880).

`madvise_MADV_DONTNEED_zeroes_pages` in
`third_party/jemalloc/src/pages.c` runs in `pages_boot`, during the lazy
`malloc_init` triggered by the first jemalloc allocation
(`DuckCatalog::Initialize` in the shell). It maps a one-page probe, and
when that `mmap` fails it only warns and falls through to `memset(addr,
'A', PAGE)` with `addr == MAP_FAILED`:

```
#0  __memset_generic
#1  duckdb_je_pages_boot             (madvise_MADV_DONTNEED_zeroes_pages inlined, pages.c:84)
#2  malloc_init_hard_a0_locked
#3  malloc_init_hard
#4  duckdb_je_malloc_default
#5  duckdb::Allocator::AllocateData(unsigned long)
...
#16 duckdb::DuckCatalog::Initialize(bool)
```

stderr stays empty because the vendored `malloc_write` is compiled out
in non-DEBUG builds. The failure is stochastic because jemalloc boot
races against the worker-thread stack reservations (16 threads x 8 MiB
on the CI runners), so the probe only lands in an exhausted moment for
some layouts and timings. The allocation-failure fallbacks from duckdb#27767
(arena 0 sharing, tcache-less threads) cover the arena and tcache
allocations later in the same startup path. This probe was the remaining
unchecked dereference of a fresh mapping in boot.

The fix returns 0 (faulty) when the probe page cannot be mapped.
`pages_purge_forced` then reports failure, freed extents are recorded as
not zeroed, and reuse that needs zeroed pages falls back to `memset`, so
zeroing never relies on `MADV_DONTNEED`. The flag is sticky for the
process lifetime: a transient failure disables forced purging for good,
which can keep more memory resident afterwards. The alternative of
failing `pages_boot` (what the lazy-purge probe next to it does) would
turn a transient spike into a spurious out-of-memory error instead.
Under the shipped configuration the degradation is limited: decommit and
`MADV_FREE` lazy purging are unaffected. The change is documented in
`third_party/jemalloc/README.md` next to the other DuckDB modifications.

Plain `RLIMIT_AS` stress did not reproduce the crash locally (960 runs
across 12 limits), so the reproduction uses an `LD_PRELOAD` shim that
fails the Nth and following anonymous `mmap`s with ENOMEM. Before the
fix, every injection point that falls inside jemalloc boot segfaults in
the probe. After it, the same points produce the regular out-of-memory
error, a single transient failure at any index is survived completely,
and sticky and single-failure sweeps up to N=3000 plus the original
RLIMIT matrix show no remaining crash. `test_address_space_limit.py`
passes unchanged. No new RLIMIT values were added to it: the crash
window is a race against thread startup that specific limits cannot pin,
which is also why the same limit both passed and failed on CI.
krlmlr pushed a commit that referenced this pull request Nov 13, 2026
…n throw below the join (duckdb#28573)

Fixes duckdb#28568

`PartialAggregatePushdown` moves a grouped aggregate below the join it
sits on and takes the aggregate's argument expressions with it. The
lower aggregate runs over the whole aggregate-side input, so an argument
that can raise an error is evaluated on rows the join would have
eliminated:

```sql
CREATE TABLE dim AS SELECT i AS id, i % 5 AS a, i % 3 AS b FROM range(100) t(i) WHERE i <> 50;
CREATE TABLE fact AS SELECT i AS id, CASE WHEN i = 50 THEN 'oops' ELSE i::VARCHAR END AS v FROM range(100) t(i);
SELECT a, b, sum(CAST(v AS INTEGER)) FROM fact JOIN dim ON fact.id = dim.id GROUP BY a, b ORDER BY a, b;
```

Row `id = 50` exists only in `fact`, so the join drops it and the
`'oops'` value is never cast by the query as written. On main the query
fails with

```
Conversion Error: Could not convert string 'oops' to INT32 when casting from source column v
```

while `SET disabled_optimizers = 'partial_aggregate_pushdown'` returns
the 15 groups. The same happens with arithmetic that can overflow:
`sum(v * 2)` with the BIGINT maximum in the unmatched row raises `Out of
Range Error`. The plan shows the cast under the `sum(...) EXPORT_STATE`
aggregate over the full `fact` scan:

```
Perfect Hash Group By: combine_aggr(#2)
└─ Hash Join (INNER) id = #0
   ├─ Seq Scan dim
   └─ Perfect Hash Group By: sum(#1) EXPORT_STATE
      └─ Projection: #0, CAST(v AS INTEGER)
         └─ Seq Scan fact
```

Before pushing an aggregate below an inner join, refuse when any
aggregate argument `CanThrow()` - the predicate the other optimizer
passes use when an expression would be evaluated on more rows than
before (`ScalarAggregateMerger`, `ProjectionPlacement`, filter pushdown,
...). The check sits in `AnalyzePushdown` for the one-sided path and
next to `DEClassifyAggregates` for the double-eager path, so it also
sees the expressions `FuseInterveningProjections` inlined into the
arguments from a projection below the aggregate. A LEFT/RIGHT join that
preserves the aggregate side keeps every row of that side, so the
argument is evaluated on all of them either way; the check is therefore
limited to inner joins and those pushdowns still fire. Aggregate
`FILTER` clauses are already rejected by the pass and the
lower-aggregate groups are plain column references, so the arguments are
the only expressions the pass moves.

With the first commit alone the pushdown stops firing for TPC-DS q04,
q11 and q66 and for one of the nine aggregates in q14, because
`sum(ss_ext_list_price - ss_ext_discount_amt)`, `sum(cs_quantity *
cs_list_price)` and `sum(... / 2)` were all reported as able to throw:

- the decimal `+`, `-` and `*` operators are registered as fallible, but
`DecimalAddSubtractResolveTypes` / `DecimalMultiplyResolveTypes` widen
the result type so that the operation cannot overflow, and only install
an overflow-checking operator when the width is clamped at the DECIMAL
maximum;
- the DOUBLE `/` operator is registered as fallible, but under
`ieee_floating_point_ops` (the default) a division by zero yields inf or
nan, and with `error_on_division_by_zero = false` it yields NULL.

The second commit clears the error mode at bind time in exactly these
cases (the same thing `regexp_replace` and `substring` do for safe
constant arguments), so `CanThrow()` is precise for them. The
overflow-checked decimal operators, `//`, `%` and the DOUBLE `/` without
IEEE semantics stay fallible. With `CALL dsdgen(sf = 1)` the pushdown
then fires for the
BD0
 same 15 queries as on main (q04, q05, q11, q14, q18,
q22, q36, q43, q66, q67, q70, q74, q77, q80, q86) with the same number
of combined aggregates in each; `test/sql/tpcds/tpcds_sf1.test_slow`
passes.

`test/optimizer/partial_aggregate_pushdown_throwing_argument.test` runs
the two repros with the optimizer on, pins that the cast and the
overflowing multiplication keep the aggregate above the inner join, and
that `TRY_CAST`, a plain column and the LEFT JOIN variant are still
pushed down. The second commit adds pins for the decimal arithmetic and
the floating-point division (pushed), and for `//`, the division without
IEEE semantics and a DECIMAL(38,2) addition (not pushed).

Note: this PR was generated by an AI coding agent and reviewed by the
author.

(cherry picked from commit 1b40002)
krlmlr pushed a commit that referenced this pull request Nov 17, 2026
…regate already exports its state (ROLLUP / CUBE / GROUPING SETS over a join) (duckdb#28790)

## What this changes

`PartialAggregatePushdown` pre-aggregates the aggregate side of a join
by the join key when the query groups by columns of the other side (the
one-sided eager group-by). It does this for a plain `GROUP BY`, but
never for `GROUP BY ROLLUP (...)`, `CUBE (...)` or cascadable `GROUPING
SETS (...)` over the same join.

The reason is an interaction of two passes. The grouping sets optimizer
turns a ROLLUP into a cascade: the finest level aggregates the join and
exports its aggregate states (`EXPORT_STATE`), and every coarser level
combines the states of the level below it (`combine_aggr`).
`IsSupportedAggregate` rejects an aggregate that already exports its
state (2096a3d), because the pushdown would export it a second time
and finalize the result, which does not give back the type the cascade
expects. So the finest level, the only one that reads the join, is never
pushed.

This PR handles that case instead of rejecting it. For an aggregate that
already exports its state:

- the lower aggregate (below the join, grouped by the join key) is the
exported aggregate as it is. It is not exported a second time.
- the upper aggregate (above the join, the original groups) is
`combine_aggr(state)`, as for every other pushed aggregate.
- the final projection passes the combined state on instead of calling
`finalize`, because the consumers of the aggregate (the coarser levels
of the cascade) expect the state.

The finest level then looks like every other level of the cascade: it
combines states. The trigger is the existing one
(`PassesCardinalityHeuristic`, `PassesLowerGroupHeuristic`), and the
double-eager and owner-outer rewrites still refuse exported aggregates.

Two things come with it:

- An exported aggregate whose update or combine can throw is not pushed
below an inner join (see "When it fires").
- A hash aggregate of `combine_aggr` can now spill arena-backed states.
`AggregateStateSpilling::TryCreateSpillPlan` needs the state layout of
every aggregate, and `combine_aggr` has no state type of its own, so a
level that combines states (every coarser level of a cascade, and with
this PR the finest one) could not move its states to spillable storage
the way the aggregate that exported them can. The spill plan now takes
the layout of the aggregate whose states `combine_aggr` accumulates
(`ExportAggregateFunction::TryGetCombinedStateLayout`). `combine_aggr`
itself does not get a state type callback: that would make it
exportable, and the optimizer passes that look at
`HasGetStateTypeCallback` would start exporting it.

## Query shape

A grouped aggregate over a fact table joined to one or more dimension
tables, grouped by dimension columns with subtotals:

```sql
SELECT d.a, d.b, sum(f.x), avg(f.x), count(*)
FROM fact f JOIN dim d ON f.k = d.k
GROUP BY ROLLUP (d.a, d.b);
```

This is the usual form of a reporting query: totals per product, with
subtotals per brand and per category, and a grand total. The fact table
has many rows per join key, the groups are wide dimension attributes
(names, categories), and the aggregates are sums, counts, averages,
minima and maxima of fact columns. Without the pushdown every fact row
is joined to its dimension row and then hashed on the wide group
columns. With it, the fact rows are aggregated once on the integer join
key, and the join and the cascade above it only see one row per key.

It also applies to an aggregate that a query exports itself (`sum(x)
EXPORT_STATE` grouped by dimension columns over a join): the states are
combined above the join and returned without a `finalize`.

## When it fires

All preconditions of the one-sided pushdown apply unchanged. The
aggregate must

- sit directly on an INNER comparison join, or on a LEFT / RIGHT join
that preserves the aggregate side, with equality conditions whose
aggregate-side keys are column references;
- have no DISTINCT, FILTER or ORDER BY, at most one argument, and a
state type (`HasGetStateTypeCallback`);
- take its arguments from one side of the join only, and group by at
least one column of the other side, with more group columns than the
lower aggregate would have;
- have no argument that can throw below an inner join (duckdb#28573);
- new, for an aggregate that already exports its state: not be able to
throw in its update or combine below an inner join. The lower aggregate
also aggregates the rows the join removes, so `sum` / `avg` over HUGEINT
or DECIMAL(38), which add with an overflow check, could fail on rows the
query never aggregates. The check is the function's own error mode
(`FunctionErrors::CAN_THROW_RUNTIME_ERROR`, what
`BoundAggregateExpression::CanThrow` reads), not a list of types. As
with throwing arguments, a LEFT / RIGHT join that preserves the
aggregate side keeps every row anyway and is still pushed;
- pass the cardinality heuristic (the join keeps at least 70 % of the
aggregate side and that side is at least 4x the other side, or the join
expands).

The estimate of the new lower aggregate is capped as in duckdb#28716.

For a cascade, the grouping sets optimizer only exports aggregates that
are combinable and have no destructor, so the aggregates that reach the
pushdown this way are `sum`, `count`, `count(*)`, `avg`, `min`, `max`,
`any_value` / `first` / `last`, `bool_and` / `bool_or`, `bit_and` /
`bit_or` / `bit_xor`, `product` and `approx_count_distinct`. Below an
inner join the variants marked as able to throw stay above the join:
`sum` and `avg` over HUGEINT and DECIMAL(38), `skewness`, `kurtosis`.
DISTINCT and ordered aggregates are not part of a cascade and stay where
they are.

The plain `GROUP BY` path of the pass is not changed by this PR. It does
not have the new check, so `sum(HUGEINT)` grouped by dimension columns
over an inner join can still overflow on rows the join removes. That is
the behaviour on main and is tracked separately.

## Example

```sql
CREATE TABLE products AS
    SELECT i AS product_id, 'category ' || (i % 20) AS category, 'brand ' || (i % 5000) AS brand, 'product ' || i AS name
    FROM range(100000) t(i);
CREATE TABLE sales AS
    SELECT (i * 7919) % 100000 AS product_id, i % 100 AS quantity FROM range(20000000) t(i);

SELECT category, brand, name, sum(quantity), avg(quantity), count(*)
FROM sales JOIN products USING (product_id)
GROUP BY ROLLUP (category, brand, name);
```

| | 1 thread | 8 threads |
|---|---|---|
| main | 2.01 s | 0.27 s |
| this PR | 0.31 s | 0.08 s |

(Release build, arm64, best of 5 warm runs, on a shared machine. The
rows are identical.)

`EXPLAIN ANALYZE` at 8 threads, finest level of the cascade only (the
coarser levels are the same in both plans).

main, 0.249 s:

```
HASH_GROUP_BY (rows=100000, time=0.65s) Groups: #0, #1, #2; Aggregates: sum(#3) EXPORT_STATE, count_star() EXPORT_STATE, count(#3) EXPORT_STATE
  PROJECTION (rows=20000000, time=0.00s) Projections: category, brand, name, quantity
    PROJECTION (rows=20000000, time=1.05s) Projections: #0, __internal_compress_string_uhugeint(#1), __internal_compress_string_uhugeint(#2), __internal_compress_string_uhugeint(#3)
      HASH_JOIN (rows=20000000, time=0.08s) Join Type: INNER; Conditions: product_id = product_id
        TABLE_SCAN (rows=20000000, time=0.04s) Table: sales
        TABLE_SCAN (rows=100000, time=0.00s) Table: products
```

this PR, 0.071 s:

```
HASH_GROUP_BY (rows=100000, time=0.01s) Groups: #0, #1, #2; Aggregates: combine_aggr(#3), combine_aggr(#4), combine_aggr(#5), combine_aggr(#6)
  PROJECTION (rows=100000, time=0.00s) Projections: category, brand, name, #3, #4, #5, #6
    PROJECTION (rows=100000, time=0.00s) Projections: __internal_compress_string_uhugeint(#0), __internal_compress_string_uhugeint(#1), __internal_compress_string_uhugeint(#2), #3, #4, #5, #6
      HASH_JOIN (rows=100000, time=0.01s) Join Type: INNER; Conditions: product_id = #0
        TABLE_SCAN (rows=100000, time=0.00s) Table: products
        PROJECTION (rows=100000, time=0.00s)
          HASH_GROUP_BY (rows=100000, time=0.34s) Groups: #0; Aggregates: sum(#1) EXPORT_STATE, count_star() EXPORT_STATE, count(#1) EXPORT_STATE
            TABLE_SCAN (rows=20000000, time=0.04s) Table: sales
```

The 20M-row join, the string compression of the group columns and the
20M-row aggregate on three string columns are replaced by one 20M-row
aggregate on the integer join key. Everything above it handles 100k
rows.

## Relation to duckdb#28573 and duckdb#28716

Both apply to the new case without changes, because it goes through the
same code path:

- duckdb#28573: an exported aggregate whose argument can throw stays above an
inner join. `sum(CAST(s AS INTEGER))` under a ROLLUP is not pushed,
`sum(TRY_CAST(s AS INTEGER))` is (tested, with rows that only fail on
the fact rows the join drops).
- duckdb#28716: the lower aggregate created for an exported aggregate gets the
capped estimate (its input and the product of its group domains).

Float sums: as with the existing pushdown of a plain `GROUP BY`, the
states are combined in a different order than without the pushdown, so a
floating-point `sum` / `avg` can differ in the last digits. This is the
same reassociation the pass already does, and that parallel aggregation
does anyway.

## Tests

- `test/optimizer/partial_aggregate_pushdown_exported_state.test`
- plans: the pre-aggregate is below the join for ROLLUP, CUBE, GROUPING
SETS (with a fact column among the groups), with GROUPING() and HAVING
on top, for LEFT / RIGHT joins that preserve the aggregate side, for an
aggregate the query exports itself, and for every one-argument aggregate
function the cascade exports;
- plans that must not change: the pass disabled; no aggregate with an
argument (`count(*)` only); arguments from both sides; FILTER; two
arguments; DISTINCT; ordered aggregate; an argument that can throw below
an inner join (and its `TRY_CAST` twin that is pushed); an aggregate
that can throw below an inner join (and the LEFT JOIN twin that is
pushed); the aggregate side not preserved (`dim LEFT JOIN fact`, FULL
join); a non-equality join condition; an aggregate below the aggregate
side; no group from the other side; a single group column; a
many-to-many join, where a plain GROUP BY takes the double-eager rewrite
and the ROLLUP gets exactly one pre-aggregate;
- results with the pushdown and with the pass disabled, at 1 and 8
threads: NULL join keys, NULL arguments, NULL group values next to the
NULLs of the ROLLUP, unmatched fact rows, a duplicated dimension key, an
empty dimension side, a fact side that is empty only at run time (so the
levels combine over an empty pre-aggregate), outer joins, INTEGER /
HUGEINT / DECIMAL / DOUBLE / VARCHAR arguments, all supported aggregate
functions.
-
`test/optimizer/partial_aggregate_pushdown_exported_state_star_join.test`:
a ROLLUP over a fact table joined to a filtered dimension and to the
dimension that provides the groups (tpcds extension as data source, sf
0.01). The pre-aggregate sits between the two joins; rows equal to the
plan without the pushdown at 1 and 8 threads.
-
`test/optimizer/partial_aggregate_pushdown_exported_state_overflow.test`:
a fact table with the HUGEINT and the DECIMAL(38) maximum on the rows of
one key and a dimension without that key, `threads = 1` and
`join_filter_pushdown` disabled. `sum` and `avg` under ROLLUP, CUBE and
GROUPING SETS, and an aggregate the query exports itself, return the
rows main returns (all sums 0) instead of an overflow error; the plans
show no aggregate below the join, while the BIGINT sum and the LEFT JOIN
variant are pushed.
-
`test/optimizer/partial_aggregate_pushdown_exported_state_external.test_slow`:
10M fact rows over 2M keys under `memory_limit='300MB'`, so the
pre-aggregate and the levels that combine its states run as external
aggregates; same rows with and without the pushdown. A second part runs
`min` over 800 byte strings (62500 states of about 50 MB per level)
under `memory_limit='64MB'`: it only completes when the levels that
combine states spill them, with and without the pushdown.
- `test/configs/verify_aggregate_state_export.json`: the first test is
added to the list of tests whose EXPLAIN output differs under
`debug_verify_aggregate_state_export`, next to the other partial
aggregate pushdown plan tests. The skip "null pointer dereference when
the partial aggregate pushdown optimizer encounters STATE_EXPORT
aggregates" is removed: `test/sql/tpcds/tpcds_sf0.test` passes without
it and `test/optimizer/partial_aggregate_pushdown.test` only differs in
EXPLAIN text, so it moves to that list.

No expected plan or result of an existing test changes.

## Regression check

Same binary, the new case switched off and on (a local switch that is
not part of this PR), release build, arm64:

- Plans: TPC-H SF1 / SF10 (22 queries each), TPC-DS SF1 / SF10 (99
each), JOB (113), ClickBench (43), at 1, 8 and 64 threads. Two TPC-DS
plans change at each scale factor and thread count; no other plan
changes.
- Results: TPC-H SF1, TPC-DS SF1 and SF10 at 1, 8 and 64 threads:
identical with the case off and on, except for one query whose plan does
not change and whose result also differs between two runs with the case
off.
- User-mode instructions at 1 thread (`perf stat -e instructions:u`): no
query above 1.05x, and the two queries whose plan changes use fewer
instructions. Geomean per suite: TPC-H 1.00 / 1.00, TPC-DS 0.99 / 0.98,
JOB 1.00, ClickBench 1.00.
- Peak buffer memory of the two changed queries is not higher with the
pushdown.

On the current head, against the commit it branches from:

- `scripts/plan_cost_runner.py` on `benchmark/tpch_plan_cost`: no
differences.
- TPC-DS SF1, all 99 queries at 1 and 64 threads: results identical.
- All 398 plans of the suites above are byte-identical to the ones
measured above, so the instruction counts carry over.
- The whole fast test suite passes on a release build, as do
`test/optimizer`, `test/sql/aggregate`, `test/sql/join`, `test/sql/tpch`
and `test/sql/tpcds` including their slow tests.
- `test/optimizer`, `test/sql/aggregate` and `test/sql/join` (825 tests
including the slow ones and the new files) under the 66 configs in
`test/configs`, on a clang assertion build compiled with `-Werror`: no
test fails that does not fail the same way on the commit the branch
starts from. 52 configs ran all 825 tests; the other 14 ran the 725
tests that are not slow plus the new slow test. The new memory-limited
test was also run on its own under every config (passes, or is skipped
by the config).
- The new tests on a `STANDARD_VECTOR_SIZE=2` build and on an
`ALTERNATIVE_VERIFY=1 DISABLE_STRING_INLINE=1 DESTROY_UNPINNED_BLOCKS=1
DISABLE_POINTER_SALT=1` build: pass (the memory-limited test requires
full vectors and is skipped on the first).

Limits of these measurements:

- The primary signal is instructions at 1 thread. Queries whose plan
does not change were measured once; the two whose plan changes and every
query that read above 1.03x in the first pass were repeated (median of 3
to 5). The instruction counts were taken before the last commit and not
repeated, because no plan changed.
- The config sweep ran on a loaded shared machine. Slow tests that
exceeded the 30 minute limit there (six under `disable_optimizer`, nine
under `verify_compression`, none of them new) were not counted as
failures and were not all repeated. `encryption` (needs httpfs for tests
that open a database file) and `io_metrics` (not a test config) could
not run here; `storage_compatibility` and `one_schema_per_test` share
one database file between tests and were judged from a single-worker
run.
- Wall clock was not measured on a quiet machine. The timings of the
example above were taken on a shared, loaded machine and are indicative
only.
- The state spilling of `combine_aggr` is covered by one memory-limited
test (`min` over long strings); it was not benchmarked.

Note: this PR was generated by an AI coding agent; it is a draft pending
review by the author.
krlmlr pushed a commit that referenced this pull request Nov 18, 2026
…duckdb#28799)

Conditional aggregation is very often written as an aggregate over a
`CASE`: pivot-style reports that compute one column per status / weekday
/ channel (`sum(CASE WHEN status = 'shipped' THEN amount ELSE 0 END)`),
counts per category (`count(CASE WHEN .. THEN 1 END)`, `sum(CASE WHEN ..
THEN 1 ELSE 0 END)`), and it is what SQL generators emit for dialects
without `FILTER`. For such an aggregate we currently compute the `CASE`
into a vector for every row and update the aggregate state for every
row, including the rows that only contribute a NULL or a 0.

This PR adds an optimizer rule, `case_to_filter_aggregate`, that turns
these aggregates into filtered aggregates. It builds on duckdb#28264: the
state is only updated for the rows that match, and aggregates that share
a condition evaluate it once per chunk.

### The rewrite

| aggregate | becomes | when |
|---|---|---|
| `f(CASE WHEN c THEN x END)` | `f(x) FILTER (WHERE c)` | `f` is `sum`,
`count`, `avg`, `min`, `max`, `bool_and` or `bool_or`: both forms are
NULL (0 for `count`) if no row matches |
| `f(CASE WHEN c THEN x END) FILTER (WHERE p)` | `f(x) FILTER (WHERE p
AND c)` | `p` is not volatile and cannot throw: a row is aggregated if
`p` and `c` are both true, in either form |
| `f(DISTINCT CASE WHEN c THEN x END)` | `f(DISTINCT x) FILTER (WHERE
c)` | the distinct values of the `CASE` are the distinct `x` of the
matching rows and a NULL, which these aggregates ignore (see "DISTINCT"
below for when the rule meets such an aggregate) |
| `count(CASE WHEN c THEN k END)` | `count(*) FILTER (WHERE c)` | `k` is
a non-NULL constant and the count is not `DISTINCT`: `count(DISTINCT
CASE WHEN c THEN k END)` is 0 or 1 and becomes `count(DISTINCT k) FILTER
(WHERE c)` |
| `sum(CASE WHEN c THEN x ELSE 0 END)` | `coalesce(sum(x) FILTER (WHERE
c), 0)` | `x` is provably not NULL |
| `sum(CASE WHEN c THEN x ELSE 0 END)` | `CASE WHEN count(*) > count(*)
FILTER (WHERE c) THEN coalesce(sum(x) FILTER (WHERE c), 0) ELSE sum(x)
FILTER (WHERE c) END` | `x` is nullable: a group in which every row
matches and every `x` is NULL is NULL (a sum over NULLs); any other
group without a non-NULL matching `x` has a row that contributed the
`ELSE 0`. The two counts are shared by all aggregates with the same
condition |
| `sum(CASE WHEN c THEN k ELSE 0 END)` | `count(*) FILTER (WHERE c) * k`
| `k` is an integer constant |

Where the aggregate returns a row for an empty input (no `GROUP BY`, or
a grouping set without columns), the `ELSE 0` forms are wrapped in `CASE
WHEN count(*) > 0 THEN .. END`, because the sum over no rows is NULL and
not 0.

### Preconditions

The aggregate is left alone if

* it has an `ORDER BY` or exports its state;
* it has a `FILTER` that is volatile or can throw (the rewritten filter
`p AND c` evaluates `p` or `c` for a part of the rows only);
* it is an `ELSE 0` form that is `DISTINCT` or has a `FILTER`: the
helper counts of these forms count all rows of the group, not the rows
that pass the filter, and the 0 is one of the distinct values;
* it is a `DISTINCT` `sum` / `avg` over `FLOAT` / `DOUBLE` values: the
distinct values are added in the order of their hash table, which
changes with the values in it;
* the `CASE` has more than one `WHEN`, or an `ELSE` other than NULL (or
other than the constant 0 for `sum`);
* the condition is volatile or contains a subquery;
* the `THEN` expression can throw or is volatile: the `CASE` evaluates
it for the matching rows only, the filtered aggregate evaluates its
argument for every row (so `sum(CASE WHEN is_number THEN s::INTEGER
END)` keeps its `CASE`);
* the `THEN` expression is a `FLOAT` / `DOUBLE` constant under `sum` /
`avg`: for a constant input vector `sum` adds `value * count` instead of
adding the value `count` times, and that is another floating-point
number (100 times `0.1` is `9.99999999999998`, `100 * 0.1` is `10.0`). A
constant `THEN` is such a vector in every chunk, so the filtered sum
would never have the digits of the `CASE` form;
* it is a `min` / `max` over a string or a nested type: these read the
vector that the `CASE` produces faster than a slice of the argument (two
such aggregates over the same `CASE` measured 1.12x - 1.16x slower as
filtered aggregates when 90% or more of the rows match).

The NOT NULL proof for the bare `coalesce` form only follows constants,
columns that are proven to be free of NULLs, `+`, `-`, `*` and casts
between numeric types. Anything else (a struct field, a list element, a
`TRY_CAST`) takes the form with the two counts.

A column is proven with the existing `NotNullExpressionAnalyzer`: a
filter or join condition that rejects NULL, or the statistics of the
scanned table (which is also how a NOT NULL constraint is seen). The
proof has to hold for the rows that the aggregate reads, so the
statistics are only consulted where the optimizer itself relies on them:

* not in a plan with a data-modifying CTE (`WITH u AS (UPDATE t SET x =
NULL RETURNING x) SELECT sum(CASE WHEN c THEN x ELSE 0 END) FROM t`):
the scan reads rows that the statistics did not describe when the plan
was made. Statistics propagation is skipped for these plans for the same
reason, and they always take the form with the two counts, which is
correct for any input;
* not when statistics propagation is disabled.

The table statistics include the uncommitted changes of the running
transaction, they still cover NULLs that another connection has
overwritten (and checkpointed) while a transaction can read them, and a
prepared statement that reads a table is planned again when it is
executed. Rows that do not come from a table (a table function, a
recursive CTE, the NULL-extended side of an outer join) are never proven
NOT NULL.

The rule runs after aggregate reuse and partial aggregate pushdown.
These only match aggregates without a filter, so they still see the
`CASE`: a pre-aggregation that is pushed below a join stays as it is
today.

### DISTINCT and existing filters

An existing filter combines with the condition for every `ELSE NULL`
form, with or without `DISTINCT`. `p AND c` has the three-valued logic
that the `CASE` form has: a row with a false or NULL `p`, or a false or
NULL `c`, contributes nothing. Measured like the example below
(instructions, one thread, 10M rows, 1000 groups), `sum(CASE WHEN c THEN
x END) FILTER (WHERE p)` next to a `count` with the same condition and
filter runs at 0.57x - 0.71x of the `CASE` form for selectivities of `c`
and `p` between 1% and 99%; three aggregates over the same `CASE` and
filter with 99% / 99% are at 0.81x.

`DISTINCT` aggregates are turned into grouped aggregates over their
argument by `distinct_aggregate_rewrite`, which runs before this rule
(and before aggregate reuse and partial aggregate pushdown, which this
rule has to follow). The rule therefore only meets a `DISTINCT`
aggregate when that optimizer is disabled; with the default settings the
plan of `count(DISTINCT CASE WHEN c THEN x END)` is unchanged, with and
without `ROLLUP` / grouping sets. I did not move the rewrite in front of
that optimizer: it plans the `CASE` form as a `GROUP BY` over the groups
and the `CASE`, and `count(DISTINCT x) FILTER (WHERE c)` as a `GROUP BY`
over the groups, `x` and `c`, which are more groups - the hand-written
`FILTER` form measures 1.03x - 1.61x of the `CASE` form there. With
`distinct_aggregate_rewrite` disabled the filtered aggregate only adds
the matching rows to its distinct hash table: one to four `DISTINCT`
aggregates run at 0.35x - 0.97x of the `CASE` form (grouped and
ungrouped, 1% - 99% of the rows matching), with one exception, a single
grouped `count(DISTINCT ..)` with 99% of the rows matching at 1.10x.

### Floating-point sums

`sum` / `avg` over a `DOUBLE` argument that is not a constant are
rewritten (a `FLOAT` argument is not: its cast to `DOUBLE` sits between
the aggregate and the `CASE`). The filtered aggregate adds the same
values in the same order as the `CASE` form (the rows of the `ELSE 0`
add `0.0`, which changes nothing), so that the digits are the same when
the query runs on one thread. With more threads the digits of a `DOUBLE`
sum already vary from run to run, in either form.

There is one exception, and it is behaviour of `sum` and not of the
rewrite: the constant input vector mentioned above. The `CASE` computes
a flat vector and thereby hides a constant vector from the sum; the
filtered aggregate receives it, as a hand-written `sum(x) FILTER (WHERE
c)` and a plain `sum(x)` do. A column arrives as constant vectors from
the one-row side of a cross join and from constant or RLE compressed
segments of a persistent table, which is not known when the plan is
made. In these cases the result is the one of the hand-written `FILTER`
and can differ from the `CASE` form in the low digits: `10.0` instead of
`9.99999999999998` for 100 times `0.1` from a one-row table,
`9.951039336363154` instead of `9.95103933636457` for the `avg` over
half of 1M rows of an RLE compressed column (ungrouped, one thread). For
comparison, a plain `sum(x)` over 1M rows of `0.1` is
`100000.00000000087`, `100000.00000006249` or `100000.00000133288`
today, depending on whether the table is persistent and compressed,
persistent and uncompressed, or in memory. Leaving `sum` / `avg` over
`DOUBLE` alone instead would give up the rewrite for these columns: the
example below with a `DOUBLE` amount runs at 0.71x the instructions of
the `CASE` form on one thread, the same as with the integer amount.

The rule has its own optimizer type, so `SET disabled_optimizers =
'case_to_filter_aggregate'` switches it off.

### Structure

The rule analyzes an aggregate once. The analysis names the condition,
the value, the `ELSE` and an existing filter of the aggregate, and
returns one result: the kind of the rewrite (an enum with one value per
row of the table above), the bound filtered aggregate, the helper counts
by name, the constant and the result type. The projection above the
aggregate is built from that kind. For this the aggregate function
rewriter accepts an analyzer next to a rule; the rules that are driven
by a matcher and callbacks (`avg`, `sum`, `list`) and their code path
are untouched.

### Two supporting changes

A filter that selects every row should cost as little as no filter,
otherwise a condition that happens to be always true makes the rewritten
form slower than the `CASE`:

* the statistics propagator drops an aggregate filter that is proven to
be true for every row (this also applies to a hand-written `FILTER`),
and reports it like the other expressions it removes, so that a column
which only the filter read is pruned from the scan;
* the aggregate filter data passes the payload on without slicing it
when a chunk passes the filter completely.

With these two, the worst shape I could construct is three numeric
aggregates over the *same* `CASE` expression with 99% of the rows
matching, at 1.04x the instructions of the `CASE` form; the same shape
is at 1.00x with 90% and 0.89x with 50% of the rows matching.

One fix belongs to the `DISTINCT` form.
`GroupedAggregateHashTable::TryAddConstantGroups` adds the single group
of an all-constant chunk; release builds take that path for chunks with
more than one row, DEBUG builds for every chunk, including one without
rows, for which it created a group that no row has. With
`distinct_aggregate_rewrite` disabled, a user-written `SELECT
count(DISTINCT 1) FILTER (WHERE x > 100) FROM t` without a matching row
returned 1 instead of 0 in a DEBUG build, and `count(DISTINCT CASE WHEN
x > 100 THEN 1 END)` is rewritten to that aggregate. The function now
returns before the fast path for a chunk without rows.

### Example

```sql
CREATE TABLE orders AS
SELECT i % 1000 AS customer, i % 4 AS status, (i * 7) % 100 AS amount
FROM range(50000000) t(i);

SELECT customer,
       sum(CASE WHEN status = 0 THEN amount ELSE 0 END) AS open_amount,
       sum(CASE WHEN status = 1 THEN amount ELSE 0 END) AS shipped_amount,
       count(CASE WHEN status = 2 THEN 1 END) AS returned,
       avg(CASE WHEN status = 3 THEN amount END) AS avg_cancelled
FROM orders
GROUP BY customer;
```

Before:

```
PROJECTION            CAST(#0 AS BIGINT), #1, #2, #3, #4
  PERFECT_HASH_GROUP_BY   Groups: #0
                          Aggregates: sum_no_overflow(#1), sum_no_overflow(#2), count(#3), avg(#4)
    PROJECTION            customer,
                          CASE WHEN ((status = 0)) THEN (amount) ELSE 0 END,
                          CASE WHEN ((status = 1)) THEN (amount) ELSE 0 END,
                          CASE WHEN ((status = 2)) THEN (1) ELSE NULL END,
                          CASE WHEN ((status = 3)) THEN (amount) ELSE NULL END
      SEQ_SCAN            orders
```

After:

```
PROJECTION            #0, COALESCE(#1, 0), COALESCE(#2, 0), #3, #4
  PERFECT_HASH_GROUP_BY   Groups: #0
                          Aggregates: sum_no_overflow(#1) FILTER (WHERE #3),
                                      sum_no_overflow(#1) FILTER (WHERE #4),
                                      count() FILTER (WHERE #5),
                                      avg(#1) FILTER (WHERE #6)
    PROJECTION            customer, amount, status = 0, status = 1, status = 2, status = 3
      SEQ_SCAN            orders
```

On this example (release build, in-memory table, best of 3 on a shared
machine): 88 ms -> 65 ms with 8 threads, 0.64 s -> 0.45 s with 1 thread.
A single conditional aggregate gains as well: `sum(CASE WHEN c THEN
amount ELSE 0 END)` grouped by `customer` runs at 0.92x / 0.85x / 0.77x
the instructions of the `CASE` form when 99% / 50% / 1% of the rows
match.

### Tests

* `test/optimizer/case_to_filter_aggregate.test`: every rewritten form
with the rule disabled and enabled (same rows), grouped and ungrouped,
empty inputs, groups without a matching row, NULL arguments and NULL
conditions, conditions that are always true or always false, DECIMAL /
HUGEINT / UBIGINT / FLOAT / DOUBLE arguments, `IF()`, a `THEN` that can
throw, the plans (including the scan after a filter was dropped), and
the interaction with partial aggregate pushdown.
* `test/optimizer/case_to_filter_aggregate_exactness.test`: the shapes
in which a naive rewrite is not exact: the grand total of ROLLUP / CUBE
/ GROUPING SETS over an empty input, `inf` / `nan` / `0.1` / `-0.0`
constants, `DOUBLE` columns (also as a constant vector), struct fields
and list elements of NOT NULL columns.
* `test/optimizer/case_to_filter_aggregate_guards.test`: one negative
test per precondition and results on edge inputs.
* `test/optimizer/case_to_filter_aggregate_distinct_filter.test`:
`DISTINCT` aggregates, aggregates with a `FILTER` and their combination,
with the rule and `distinct_aggregate_rewrite` disabled and enabled:
expected rows for duplicates, NULL values, NULL / false conditions and
filters, constant `THEN` branches, empty inputs, inputs without a
matching row, `ROLLUP` and grouping sets, and the plans (the combined
filter, the `DISTINCT` count of a constant, the forms that are left
alone).
* `test/optimizer/case_to_filter_aggregate_statistics.test`: the NOT
NULL proof against rows that change after the statistics were read:
data-modifying CTEs (`UPDATE`, `INSERT`, upsert), uncommitted inserts /
updates / `ALTER TABLE` of the running transaction, a prepared statement
executed again after an insert, a transaction that still reads NULLs
which another connection has overwritten and checkpointed, table
functions, recursive CTEs, outer joins, views and materialized CTEs.

The five files pin plans and are added to the skip list of
`test/configs/verify_aggregate_state_export.json`, next to the other
aggregate rewrite tests.

### Regression check

Rule disabled vs enabled in the same binary (`SET disabled_optimizers`),
TPC-H and TPC-DS at SF1 and SF10 and ClickBench: 1 of 22, 9 of 99 and 0
of 43 plans change. Results are identical on TPC-H and TPC-DS SF1 with 1
and 32 threads. Measured in user-mode instructions at 1 thread (`perf
stat -e instructions:u`, median of 3), no query with a changed plan is
above 1.01x; the geometric mean over the changed plans is 0.96 at SF1
and 0.93 at SF10.

No expected plan of an existing test changes.

This PR is independent of the other optimizer PRs I am opening.

Note: this PR was generated by an AI coding agent.
krlmlr pushed a commit that referenced this pull request Nov 19, 2026
…e left rows (duckdb#28796)

## The query shape

Unnesting a list that is built from columns of the same row, or a
function of a list column, in the `FROM` clause:

```sql
SELECT id, u.v FROM readings CROSS JOIN UNNEST([a, b, c]) AS u(v);          -- columns to rows
SELECT id, u.v FROM readings, UNNEST(list_reverse(l)) AS u(v);              -- a function of a list column
SELECT id, v FROM readings, LATERAL (SELECT unnest([a, b, c]) AS v);        -- the same as a lateral subquery
```

This is the standard way to turn columns into rows (an unpivot written
by hand, key / value pairs built with `[{'k': ..., 'v': ...}, ...]`), to
explode a transformed list, and what dialect translation produces for
`CROSS JOIN UNNEST(ARRAY[...])` and `LATERAL VIEW explode(array(...))`.
The right side has no table of its own: what it produces is a function
of the left row alone.

## What happens today

The lateral join is decorrelated the general way: the left rows are
materialized in a CTE, their distinct correlated columns are computed
with a GROUP BY, the right side runs once per distinct key, and the
result is joined back to the left rows on the correlated columns:

```
CTE __duckdb_delim
  SEQ_SCAN readings  [a, b, c, id]                                  ~3,000,000 rows
  HASH_JOIN  INNER; a = a, b = b, c = c                             ~3,000,000 rows
    CTE_SCAN
    INOUT_FUNCTION unnest
      PROJECTION  list_value(a, b, c), a, b, c
        HASH_GROUP_BY  Groups: #0, #1, #2                           ~2,997,201 rows
          CTE_SCAN
```

The correlated columns of such a query are typically close to unique, so
the domain is as large as its input and the plan groups, hashes and
probes every row once more than needed. The unnest rewriter removes this
for one case, `UNNEST(col)` of a single correlated list column that is
unnested as it is; an expression over the correlated columns, several
correlated columns, and projections or filters around the unnest keep
the plan above.

## The change

`LogicalUnnest` already is the requested operation, so the join is
recognised while it is planned, before it is decorrelated, instead of
being recovered from the decorrelated plan by an optimizer pass (the
first version of this PR, see the review):

```
DEPENDENT_JOIN  INNER                              UNNEST(list)
  left                                     ->        left
  GET unnest
    PROJECTION  list
      DUMMY_SCAN
```

The list is computed from the left row alone, so its correlated column
references become plain references to the left side. The `UNNEST` passes
the columns of the left side through and takes over the table index of
the table function, so every reference above stays valid. An ARRAY
argument keeps its cast to a list. An `ON` condition of the join that is
deterministic and cannot raise an error becomes a filter above the
`UNNEST`.

A lateral subquery around the unnest is handled the same way when its
operators map every row on its own: a chain of `PROJECTION`, `FILTER`
and `UNNEST` down to the `DUMMY_SCAN` runs over the left rows instead of
the single row of the `DUMMY_SCAN`. Its projections pass the columns of
the left side through; the new bindings of these columns are handed up
through the replacement graph of the dependent join planner.

```
DEPENDENT_JOIN  INNER                              PROJECTION  v, [left columns]
  left                                     ->        FILTER  v > 500
  PROJECTION  v                                        UNNEST(list_value(a, b, c))
    FILTER  v > 500                                      left
      UNNEST(list_value(a, b, c))
        DUMMY_SCAN
```

The plans are those of the select-list form `SELECT id, unnest([a, b,
c]) FROM readings`.

Where it happens: in `RecursiveDependentJoinPlanner::PlanOperator`, on
the `LogicalDependentJoin` that `Binder::PlanLateralJoin` creates, after
the children of the join are planned and before `FlattenDependentJoins`
runs. It cannot be done inside `Binder::PlanLateralJoin` itself: at that
point the select list is not bound yet, and the binding of the table
function in the bind context holds a reference to the column ids of its
`LogicalGet`, so the `LogicalGet` has to stay alive until the statement
is bound. The dependent join planner is the first place after that, and
it has the replacement graph that the lateral subquery case needs.

The fast path is optional and respects optimizer disablement: it is
skipped with `SET disabled_optimizers = 'unnest_rewriter'`, with `SET
enable_optimizer = false` and with `debug_disable_optimizer` (the
`disable_optimizer` test configuration), and the join is then
decorrelated as before. It is also skipped when the filter pushdown is
disabled, because a filter on the left columns above the join has to
reach the rows below the `UNNEST` (see the guard under the
preconditions). No new optimizer type is added.

## Preconditions

Anything else is left to the general decorrelation:

- an INNER dependent join (comma, `CROSS JOIN`, `JOIN ... ON`);
- on its right side, down to the `DUMMY_SCAN`, only the `unnest` table
function with one LIST / ARRAY argument, one output column and no
ordinality, or `PROJECTION` / `FILTER` / `UNNEST` operators with at
least one unnest among them;
- no volatile expression: the join evaluates the right side for every
left row, also for rows that a `LIMIT` above does not need, and that
count is kept;
- no subquery on the right side, and every correlated column is a column
of the left side of this join (not of an enclosing query);
- an `ON` condition, if there is one, is not volatile, cannot raise an
error and has no subquery. It becomes a filter that is evaluated once
for every joined row, while the join does not evaluate its condition
once per row (with a sequence in the condition it advances less often);
only a condition whose result does not depend on how often and on which
of these rows it is evaluated can move. Comparisons, `IS NULL`, `AND` /
`OR` / `NOT` over columns of both sides and constants qualify (`ON u.v >
b`); arithmetic, casts that can fail and volatile functions do not;
- with a projection on the right side (a lateral subquery), no
expression that can raise an error. A filter on the left columns above
the join guards the expressions of the right side - `FROM t,
UNNEST(CAST(s AS INTEGER[])) AS u(v) WHERE s LIKE '[%'` does not cast
the rows that the filter removes - and the filter pushdown moves such a
filter below an `UNNEST`, but not always below a projection. The plain
table function form has no projection and has no such restriction.

Left alone: `WITH ORDINALITY`, `LEFT JOIN LATERAL` (rows without a list
element must be kept), a lateral aggregate, limit, DISTINCT or join
against another table, a list computed with a subquery, a list that
reads a column of an enclosing query, volatile expressions, an `ON`
condition that is volatile or can raise an error (`ON nextval('s') > v`,
`ON v + 1 > b`), a lateral subquery with an expression that can raise an
error (`v + a`, a cast of a string).

## What is visible besides speed

**Values that compare equal but are not identical.** The decorrelated
plan computes the right side once per distinct value of the correlated
columns and joins it back, so rows whose correlated columns compare
equal all receive the unnested values of one of them. With DOUBLE /
FLOAT (`0.0` and `-0.0`, NaNs with different signs) and INTERVAL (`1
month`, `30 days`, `720 hours`) those are not the values of the row:

```sql
CREATE TABLE iz AS SELECT * FROM (VALUES (1, INTERVAL 1 MONTH), (2, INTERVAL 30 DAY), (3, INTERVAL 720 HOUR)) t(id, i);
SELECT id, v FROM iz, UNNEST([i]) AS u(v) ORDER BY id;
-- before:  1 month, 1 month, 1 month        now:  1 month, 30 days, 720:00:00
SELECT id, v FROM (SELECT id, unnest([i]) AS v FROM iz) ORDER BY id;
-- before and now:  1 month, 30 days, 720:00:00
```

With the `UNNEST` over the left rows every row unnests its own values,
which is what the select-list form returns. So for these types the
results change, to those of the select-list form; for all other types
they are the same. No restriction on the types of the correlated columns
is needed, and there is no CTE whose definition could move or be
computed more than once.

**Row order.** Queries without an `ORDER BY` can return their rows in a
different order than before (the order of the left rows instead of the
order of the hash join).

**Evaluation count.** The list expression is evaluated once per left row
instead of once per distinct value of the correlated columns. This is
the reason for the gain when the correlated columns are close to unique,
and a cost when they have few distinct values and the list expression is
expensive (last row of the table below).

## Example

```sql
CREATE TABLE readings AS
    SELECT i AS id, i % 1000 AS sensor, (i * 31) % 997 AS a, (i * 17) % 991 AS b, (i * 13) % 983 AS c,
           [i % 5, i % 7, i % 11] AS l
    FROM range(3000000) t(i);
```

Upstream `main` against this branch, user-mode instructions of a
single-threaded run (`perf stat -e instructions:u`, median of 3,
including the 0.24 G of process start-up):

| query | before | after |
|---|---|---|
| `SELECT count(*), sum(id), sum(u.v) FROM readings CROSS JOIN
UNNEST([a, b, c]) AS u(v)` | 96.9 G | 1.27 G |
| `SELECT sensor, max(u.v), count(*) FROM readings CROSS JOIN UNNEST([a,
b, c]) AS u(v) GROUP BY sensor` | 97.2 G | 1.59 G |
| `SELECT count(*), sum(id), sum(u.v) FROM readings CROSS JOIN
UNNEST(list_reverse(l)) AS u(v)` | 78.5 G | 1.94 G |
| `... FROM readings CROSS JOIN UNNEST([{'k': 1, 'x': a}, {'k': 2, 'x':
b}, {'k': 3, 'x': c}]) AS u(v) WHERE u.v.x > 500` | 119.3 G | 1.86 G |
| `... FROM readings JOIN UNNEST([a, b, c]) AS u(v) ON u.v > b` | 95.4 G
| 1.27 G |
| `... FROM readings, UNNEST([a, b]) AS u(v), UNNEST([u.v, c]) AS w(x)`
| 55.1 G | 2.75 G |
| `... FROM readings, LATERAL (SELECT unnest([a, b, c]) AS v)` | 5.11 G
| 1.28 G |
| `... FROM readings, LATERAL (SELECT v FROM (SELECT unnest([a, b, c])
AS v) WHERE v > 500)` | 3.66 G | 1.37 G |
| `... FROM readings, LATERAL (SELECT unnest([[a, b], [c]], recursive :=
true) AS v)` | 5.44 G | 2.63 G |
| `... FROM readings JOIN LATERAL (SELECT unnest([a, b, c]) AS v) ON v >
b` | 3.84 G | 1.27 G |
| control: `UNNEST(l)` (the case of the unnest rewriter) | 1.05 G | 1.04
G |
| control: `SELECT id, unnest([a, b, c]) FROM readings` written by hand
| 1.28 G | 1.28 G |
| control: `... FROM readings, LATERAL (SELECT v + a AS k FROM (SELECT
unnest([b, c]) AS v) ...)` (can raise an error: left alone) | 3.24 G |
3.24 G |
| few distinct keys, cheap list: `UNNEST([sensor, sensor + 1, sensor +
2])` | 1.11 G | 1.09 G |
| few distinct keys, expensive list:
`UNNEST(string_split(repeat(sensor::VARCHAR \|\| ',', 20), ','))` | 8.53
G | 19.6 G |

The last row is the case the general plan is built for: 1,000 distinct
values of the correlated column in 3,000,000 rows and a list expression
that is expensive to compute, which is then evaluated per row. The
hand-written select-list form and the unnest rewriter's `UNNEST(col)`
behave the same way.

## Regression check

None of the TPC-H and TPC-DS queries has this shape, so the two suites
are regression evidence only and the example above carries the case. At
SF1, upstream `main` against this branch:

- plans: 0 of 121 queries change;
- results: identical (TPC-DS q39 compared single-threaded, its parallel
floating point aggregation can differ in the last digit between two runs
of the same binary);
- `scripts/plan_cost_runner.py` over `benchmark/tpch_plan_cost`: no
differences.

## Tests

`test/sql/subquery/lateral/lateral_unnest_over_left_rows.test` (1,429
assertions): 76 result cases over a table with duplicate rows, NULL
columns, empty and NULL lists and NULL list elements - expression lists,
a function of a list column, a list that is NULL for some rows, lateral
subqueries with filters on the outer row and on the unnested value, an
`ON` condition, an aggregate and a join above, two chained laterals, an
empty left table, struct / VARCHAR / ARRAY lists, a lambda, a prepared
statement parameter, `SELECT *`, the bare list column, a lateral unnest
inside a correlated subquery and in the recursive member of a recursive
CTE - including the shapes that keep the general decorrelation (`WITH
ORDINALITY`, `LEFT JOIN LATERAL`, a lateral aggregate / limit /
DISTINCT, a lateral over another table, a list computed with a subquery
or from a column of an enclosing query, a volatile list with its
evaluation count below a `LIMIT`), values that compare equal but are not
identical (DOUBLE / FLOAT signed zeros and NaNs, INTERVAL, a
case-insensitive VARCHAR, nested in lists and structs) next to their
select-list form, filters on the left columns that guard a list
expression that fails for other rows, and a volatile and a failing `ON`
condition; and 36 plan pins for both groups, for the unnest rewriter and
the filter pushdown disabled by name, for the optimizer disabled and for
the optimizer run skipped.

Existing tests that change:

- `test/optimizer/pushdown/issue_18653.test_slow` pins that the filter
`id = 87100` ends up below the unnest (`UNNEST.*FILTER`). It now reaches
the table scan as a pushed-down table filter, so the pin accepts
`UNNEST.*Filters: id = 87100` as well.
- `test/sql/subquery/lateral/lateral_filter_into_delim_cte.test` (added
by duckdb#28760) pins that a filter that can raise an error sits directly on
the `unnest` table function of the decorrelated plan. Its two chained
unnests are now planned over the rows of the table, so that pin checks
the filter directly above the `UNNEST` with no join or CTE; the original
pin is kept for the same query with `WITH ORDINALITY` (still
decorrelated) and with `unnest_rewriter` disabled.

`test/sql/table_function/range_lateral_threads.test` and
`test/sql/json/table/json_table_in_out_threads.test`, which disable
`unnest_rewriter` to keep `LATERAL unnest([i])` planned as a join, are
unchanged: that switch covers the fast path too.

On a clang relassert build with warnings as errors the whole fast test
suite passes, and `test/optimizer/*`, `test/sql/subquery/*`,
`test/sql/cte/*`, `test/sql/join/*`, `test/sql/types/list/*`,
`test/sql/types/nested/*`, `test/sql/types/struct/*`,
`test/sql/unnest/*`, `test/sql/table_function/*`, `test/sql/json/*`,
`test/fuzzer/*`, `test/sql/tpch/*` and `test/sql/tpcds/*` including the
slow tests pass in the default configuration and under the
`verify_column_bindings`, `force_external`, `force_storage` and
`verify_fetch_row` configurations. Under the `disable_optimizer`
configuration the fast path is off; the new test switches the optimizer
back on for itself and checks the disabled paths at its end.

## What is no longer part of this PR

The optimizer pass of the first version (`lateral_row_function`) and its
optimizer type are removed. The pass also rewrote a query-written CTE
that is joined with its own `SELECT DISTINCT` keys; that is not a
lateral join, keeps its plan, and can be proposed separately.

Relation to duckdb#28102 / duckdb#28760: duckdb#28102 relaxes the unnest rewriter from a
column reference to any non-volatile expression as the `UNNEST`
argument. Its test file as of 2026-11-13
(`test/optimizer/unnest_rewriter_expression.test`, 299 assertions
including its plan pins) passes on this branch without its source
change, because these joins no longer reach the unnest rewriter as a
duplicate-eliminated join; the two changes do not conflict and do not
apply twice to the same join. duckdb#28760 / duckdb#28639 let filters reach a
lateral `UNNEST` or table function below the CTEs of the decorrelated
plan. For the `unnest` table function form there is no such CTE any more
and the filter moves below the `UNNEST`, also inside a correlated
subquery; `range`, `json_each` and lateral subqueries whose list
expression can raise an error keep the decorrelated plan and are what
duckdb#28760 remains necessary for.

This PR is independent of other open PRs.

Note: this PR was generated by an AI coding agent.
krlmlr pushed a commit that referenced this pull request Nov 21, 2026
…uckdb#28889)

Hi team, I found in the current implementation, `SumRewriteRule` only
applies to addition operation but not subtraction operation, for example
```sql
memory D CREATE TABLE t AS SELECT (i % 1000)::INTEGER AS a, (i % 100)::INTEGER AS g FROM range(10000000) r(i);

-- sum calculated once
memory D EXPLAIN ANALYZE SELECT sum(a + 1), sum(a + 2), sum(a + 3) FROM t;
╭─ Summary ───────────╮
│ Total Time: 0.0051s │
╰─────────────────────╯
╭─ Projection ───────────────────────╮
│ 1 row                          0µs │
╰──────────────────┬─────────────────╯
╭─ Ungrouped Aggregate ──────────────╮
│ Aggregates: sum_no_overflow(#0),   │
│             count_star()           │
│ 1 row                       10.0ms │
╰──────────────────┒┎────────────────╯
╭─ Projection ─────┚┖────────────────╮
│ 10,000,000 rows                0µs │
╰──────────────────┒┎────────────────╯
╭─ Table Scan ─────┚┖────────────────╮
│ Table: memory.main.t               │
│ Type: Sequential Scan              │
│ Projections: a                     │
│ Row Groups Scanned: 82 / 82        │
│ 10,000,000 rows             10.0ms │
╰────────────────────────────────────╯

-- sum calculated three times
memory D EXPLAIN ANALYZE SELECT sum(a - 1), sum(a - 2), sum(a - 3) FROM t;
╭─ Summary ───────────╮
│ Total Time: 0.0025s │
╰─────────────────────╯
╭─ Ungrouped Aggregate ────────────╮
│ Aggregates: sum_no_overflow(#0), │
│             sum_no_overflow(#1), │
│             sum_no_overflow(#2)  │
│ 1 row                     10.0ms │
╰─────────────────┒┎───────────────╯
╭─ Projection ────┚┖───────────────╮
│ Projections: a - 1, a - 2, a - 3 │
│ 10,000,000 rows           10.0ms │
╰─────────────────┒┎───────────────╯
╭─ Table Scan ────┚┖───────────────╮
│ Table: memory.main.t             │
│ 10,000,000 rows              0µs │
╰──────────────────────────────────╯
```
As you can tell from the plan, sum operation is now calculated for
multiple times for the second query.
SumRewriteRule` rewrote `SUM(x + C)` to `SUM(x) + C * COUNT(x)`, but
only for ungrouped aggregates and only for addition.

This PR extends it in two ways:
- **Subtraction**
  - `SUM(x - C)` -> `SUM(x) - C * COUNT(x)`
  - `SUM(C - x)` -> `C * COUNT(x) - SUM(x)`
- Unsigned subtraction is not rewritten: it commonly underflows and
throws, and the rewrite would hide that error.
- **Grouped aggregates**: the rule now runs whenever the aggregate has
no `GROUPING()` functions, matching `AvgRewriteRule`. In a grouped
aggregate the added `COUNT(x)` is extra per-group state, so the rewrite
only fires when another aggregate over the same `x` lets common
aggregate elimination share the work:
  - `SUM(x)`, `COUNT(x)`, or another `SUM(x +/- C)`
  - only when `COMMON_AGGREGATE` is enabled
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants

0