Repository navigation
Commit d75fd7b
fix: do not evaluate a volatile coalesce or nvl argument twice (apache#25561)
## Which issue does this PR close?
- Closes apache#25477.
## Rationale for this change
`coalesce` is rewritten by `simplify` into `CASE WHEN a IS NOT NULL THEN
a ELSE b END`, which names every argument but the last one twice. For a
volatile argument those two mentions are two independent draws, so the
null test and the returned value disagree:
```sql
-- both draws are independent, so COALESCE can return NULL,
-- which a single evaluation can never produce
SELECT count(*), count(c) FROM (SELECT coalesce(nullif(floor(random() * 2), 0), neg) AS c FROM w);
+--------+----------+
| 100000 | 75117 |
+--------+----------+
```
24,883 NULLs, which is exactly `P(draw1 != 0) * P(draw2 == 0) = 0.5 *
0.5`. With a non-null literal as the last argument the planner marks the
output non-nullable and it fails outright:
```
Arrow error: Invalid argument error: Column 'c' is declared as non-nullable but contains null values
```
`nvl` delegates to `CoalesceFunc`, so it has the same bug.
## What changes are included in this PR?
`simplify` still rewrites `coalesce` into the fast `CASE`, but it stops
at the first volatile argument that is not the last one and leaves the
remaining arguments as a `coalesce` call, so each argument is named
once:
```sql
coalesce(a, v, c) --> CASE WHEN a IS NOT NULL THEN a ELSE coalesce(v, c) END
coalesce(v, b) --> unchanged
```
That leftover call is evaluated by `invoke_with_args`, which gets back
the runtime kernel removed in e5dcc8c (apache#17357) and evaluates each
argument exactly once. Calls with no volatile argument before the last
plan exactly as before and never reach the kernel. A volatile *last*
argument is named once by the `ELSE`, so it needs no guard.
`nvl` delegates to `CoalesceFunc` and is fixed by the same change.
`nvl2` names each argument once and is unaffected.
As discussed in review, a scalar binding function (`apply(v, x -> CASE
WHEN x IS NOT NULL THEN x ELSE coalesce(b, c) END)`) built on the new
lambda support would be a cleaner long-term replacement for the kernel.
It needs a new always-registered higher-order function and has Substrait
implications, so I have kept this PR to the narrow fix.
## What is the testing strategy for this PR?
`sqllogictest` cases in `coalesce.slt`. To get exact expected rows from
a volatile argument they use `case when random() >= 0 then x end`, which
is volatile but always equals `x`. They cover array arguments, a scalar
fallback, all-null input, strings, and a volatile middle argument. The
100k-row `random()` reproducer above is kept as one query covering both
`coalesce` and `nvl`, asserting `count(c) = count(*)`.
## Are there any user-facing changes?
The bug fix itself (no more NULLs or non-nullable errors from a volatile
`coalesce`/`nvl`). One behaviour change: in a call with a volatile
non-final argument, that argument and the ones after it are evaluated
eagerly by the kernel rather than lazily by `CASE`. For example,
`coalesce(random(), y / x)` now evaluates `y / x`. Arguments before the
first volatile one, and all non-volatile calls, keep the lazy `CASE`
behaviour.
## AI disclosure
Following the [DataFusion AI
policy](https://datafusion.apache.org/contributor-guide/ai-policy.html)
and the [ASF Generative Tooling
Guidance](https://www.apache.org/legal/generative-tooling.html): I used
Claude Code to help write the code, tests and this description, and I
reviewed all of it. The restored kernel is DataFusion's own code from
before apache#17357; no third-party code is included.
Things I would like a reviewer to check:
- Whether the eager evaluation of the arguments after a volatile one is
acceptable, or whether it should be documented on `coalesce`/`nvl`.
- The `case when random() >= 0 then x end` trick in the tests relies on
`CASE` with a volatile condition staying volatile through optimization.
---------
Co-authored-by: Claude Opus 5 (1M context) <[email protected]>1 parent 676c003 commit d75fd7b
2 files changed
Lines changed: 168 additions & 11 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
2 | 2 | | |
3 | 3 | | |
4 | 4 | | |
| 5 | + | |
| 6 | + | |
| 7 | + | |
5 | 8 | | |
6 | 9 | | |
7 | 10 | | |
8 | 11 | | |
| 12 | + | |
9 | 13 | | |
10 | 14 | | |
11 | 15 | | |
| |||
90 | 94 | | |
91 | 95 | | |
92 | 96 | | |
93 | | - | |
94 | | - | |
95 | | - | |
96 | | - | |
97 | | - | |
98 | | - | |
99 | | - | |
| 97 | + | |
| 98 | + | |
| 99 | + | |
| 100 | + | |
| 101 | + | |
| 102 | + | |
| 103 | + | |
| 104 | + | |
| 105 | + | |
| 106 | + | |
| 107 | + | |
| 108 | + | |
| 109 | + | |
| 110 | + | |
| 111 | + | |
| 112 | + | |
| 113 | + | |
| 114 | + | |
| 115 | + | |
| 116 | + | |
100 | 117 | | |
101 | | - | |
102 | | - | |
| 118 | + | |
103 | 119 | | |
104 | 120 | | |
105 | 121 | | |
106 | 122 | | |
107 | | - | |
108 | | - | |
| 123 | + | |
| 124 | + | |
| 125 | + | |
| 126 | + | |
| 127 | + | |
| 128 | + | |
| 129 | + | |
| 130 | + | |
| 131 | + | |
| 132 | + | |
| 133 | + | |
| 134 | + | |
| 135 | + | |
| 136 | + | |
| 137 | + | |
| 138 | + | |
| 139 | + | |
| 140 | + | |
| 141 | + | |
| 142 | + | |
| 143 | + | |
| 144 | + | |
| 145 | + | |
| 146 | + | |
| 147 | + | |
| 148 | + | |
| 149 | + | |
| 150 | + | |
| 151 | + | |
| 152 | + | |
| 153 | + | |
| 154 | + | |
| 155 | + | |
| 156 | + | |
| 157 | + | |
| 158 | + | |
| 159 | + | |
| 160 | + | |
| 161 | + | |
| 162 | + | |
| 163 | + | |
| 164 | + | |
| 165 | + | |
| 166 | + | |
| 167 | + | |
| 168 | + | |
| 169 | + | |
| 170 | + | |
| 171 | + | |
| 172 | + | |
| 173 | + | |
| 174 | + | |
| 175 | + | |
| 176 | + | |
| 177 | + | |
| 178 | + | |
109 | 179 | | |
110 | 180 | | |
111 | 181 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
445 | 445 | | |
446 | 446 | | |
447 | 447 | | |
| 448 | + | |
| 449 | + | |
| 450 | + | |
| 451 | + | |
| 452 | + | |
| 453 | + | |
| 454 | + | |
| 455 | + | |
| 456 | + | |
| 457 | + | |
| 458 | + | |
| 459 | + | |
| 460 | + | |
| 461 | + | |
| 462 | + | |
| 463 | + | |
| 464 | + | |
| 465 | + | |
| 466 | + | |
| 467 | + | |
| 468 | + | |
| 469 | + | |
| 470 | + | |
| 471 | + | |
| 472 | + | |
| 473 | + | |
| 474 | + | |
| 475 | + | |
| 476 | + | |
| 477 | + | |
| 478 | + | |
| 479 | + | |
| 480 | + | |
| 481 | + | |
| 482 | + | |
| 483 | + | |
| 484 | + | |
| 485 | + | |
| 486 | + | |
| 487 | + | |
| 488 | + | |
| 489 | + | |
| 490 | + | |
| 491 | + | |
| 492 | + | |
| 493 | + | |
| 494 | + | |
| 495 | + | |
| 496 | + | |
| 497 | + | |
| 498 | + | |
| 499 | + | |
| 500 | + | |
| 501 | + | |
| 502 | + | |
| 503 | + | |
| 504 | + | |
| 505 | + | |
| 506 | + | |
| 507 | + | |
| 508 | + | |
| 509 | + | |
| 510 | + | |
| 511 | + | |
| 512 | + | |
| 513 | + | |
| 514 | + | |
| 515 | + | |
| 516 | + | |
| 517 | + | |
| 518 | + | |
| 519 | + | |
| 520 | + | |
| 521 | + | |
| 522 | + | |
| 523 | + | |
| 524 | + | |
| 525 | + | |
| 526 | + | |
| 527 | + | |
| 528 | + | |
| 529 | + | |
| 530 | + | |
| 531 | + | |
| 532 | + | |
| 533 | + | |
| 534 | + | |
0 commit comments