ClickHouse / ClickHouse/ClickHouse

`tupleElement` default argument referencing an `ARRAY JOIN`-ed `Nested` subcolumn: `PruneArrayJoinColumnsPass` prunes it, causing silent wrong results or `BAD_ARGUMENTS`

Open
#119,622 0 comments 0 reactions 0 assignees View on GitHub
comp-query-analyzer minor potential bug
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Company or project name

_No response_

### Describe what's wrong

TL;DR: when the default (third) argument of `tupleElement` references a subcolumn of an `ARRAY JOIN`-ed `Nested` column, `PruneArrayJoinColumnsPass` never sees that reference, prunes the subcolumn anyway, and the query either silently returns values of a different subcolumn or throws an exception on a valid query.

1. Silent wrong result. In `SELECT n.a, n.c, tupleElement(n, 'zz', tupleElement(n, 2)) FROM t ARRAY JOIN n` over `n Nested(a, b, c)`, the hidden reference `tupleElement(n, 2)` (subcolumn `b`) inside the default argument is not marked as used, so the pass prunes `n` down to `Tuple(a, c)`. The stale index `2` is not in the index remap (only marked indices are remapped), so it silently reads subcolumn `c` instead of `b`: the query returns `100, 200` where the correct values are `10, 20`.

2. Exception on a valid query. `SELECT tupleElement(n, 'zz', n.b) FROM t ARRAY JOIN n` throws `Code: 36. DB::Exception: Tuple doesn't have element with name 'b'. (BAD_ARGUMENTS)` because `b` was pruned away while still referenced by the default argument.

Cause: `MarkUsedArrayJoinColumnsVisitor::needChildVisit` in `src/Analyzer/Passes/PruneArrayJoinColumnsPass.cpp` (line 159 on current master) skips all children of a `tupleElement` whose first argument is a tracked `ARRAY JOIN` column, so `enterImpl` only records the subcolumn named by the second argument. The third (default) argument of the three-argument form is never visited, and any `ARRAY JOIN` subcolumn references inside it are never counted.

### Does it reproduce on the most recent release?

Yes: reproduced on current master (26.9.1), 26.8.2 and 26.6.4.

### How to reproduce

```sql
CREATE TABLE t (id UInt64, n Nested(a UInt64, b UInt64, c UInt64)) ENGINE = MergeTree ORDER BY id;
INSERT INTO t VALUES (1, [1,2], [10,20], [100,200]);

-- wrong result: returns 100, 200 (subcolumn c) instead of 10, 20 (subcolumn b)
SELECT n.a, n.c, tupleElement(n, 'zz', tupleElement(n, 2)) FROM t ARRAY JOIN n;

-- control: a direct reference to n disables pruning and gives the correct values 10, 20
SELECT n.a, n.c, tupleElement(n, 'zz', tupleElement(n, 2)), n FROM t ARRAY JOIN n;

-- exception on a valid query
SELECT tupleElement(n, 'zz', n.b) FROM t ARRAY JOIN n;
```

Fiddle (master head, all three queries): https://fiddle.clickhouse.com/c0a349c7-924d-4703-a6fd-178ddaed6d18
Fiddle 26.8: https://fiddle.clickhouse.com/be84f364-22a3-4f38-a2c8-a004391d2de5
Fiddle 26.6: https://fiddle.clickhouse.com/5b3344d4-2640-4bac-885e-650a845e8c8f

### Expected behavior

The first query returns `10, 20` for the third column (the default argument evaluates `tupleElement(n, 2)`, i.e. subcolumn `b`), and the last query returns `10, 20` without an exception.

### Error message and/or stacktrace

```
Code: 36. DB::Exception: Tuple doesn't have element with name 'b'. (BAD_ARGUMENTS)
```

### Additional context

The plain numeric form `tupleElement(n, 2)` without a default argument works correctly on current master (the index remap added after #100026 handles it); the remaining gap is only the unvisited default argument of the three-argument form. On 25.8 the same queries also fail, with `Code: 10 (NOT_FOUND_COLUMN_IN_BLOCK)`, where even the plain `tupleElement(n, 2)` form is broken, so that release line is affected through an older mechanism.

Contributor guide

Open the contributing guide

Research direction

Start in src/Analyzer/Passes/PruneArrayJoinColumnsPass.cpp at MarkUsedArrayJoinColumnsVisitor::needChildVisit and compare handling of tupleElement's three arguments. Reproduce the three SQL queries from the issue against the current master. Done means references in the default argument preserve the needed ARRAY JOIN subcolumn, the first query returns 10, 20, and the last query completes without BAD_ARGUMENTS.

Written by the indexing model from the issue text.

Assessment

Tech stack
cpp, sql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
76/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.