pingcap / pingcap/tidb

`ANY_VALUE()` in a view raises planner error 1105 `Can't find column Column#N in schema`

Open
#70,361 2 comments 0 reactions 0 assignees View on GitHub
contribution severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

A view whose body applies `ANY_VALUE()` to a column cannot be used with a query that wraps that
column in **any** scalar expression and puts it in the left operand of `IN (subquery)`. The planner
fails with `1105 Can't find column Column#N in schema Column: [...]`. It fails at bare `EXPLAIN`, on an
**empty table**. This might be unexpected

### 1. Minimal reproduce step (Required)

```sql
CREATE TABLE k (c VARCHAR(9), g BIGINT);
CREATE VIEW t AS SELECT ANY_VALUE(c) AS c FROM k GROUP BY g;

SELECT 1 FROM t AS t1 WHERE UPPER(t1.c) IN (SELECT 'x');
-- Expected: 0 rows (the table is empty).
-- Actual: ERROR 1105 (HY000): Can't find column Column#N in schema Column: [...]
-- NOTE: the `Column#N` and the bracketed schema list both vary with the plan context -- e.g.
-- `Column#5 in schema Column: [Column#15]` here. Only the error class is stable; do not
-- pattern-match the bracket contents.
EXPLAIN SELECT 1 FROM t AS t1 WHERE UPPER(t1.c) IN (SELECT 'x');
-- Same error -- it never gets a plan, so nothing about execution is involved.

-- Even the GROUP BY is unnecessary:
CREATE VIEW t2 AS SELECT ANY_VALUE(c) AS c FROM k;
SELECT 1 FROM t2 AS t1 WHERE UPPER(t1.c) IN (SELECT 'x');
-- Expected 0 rows; actual 1105 (here: Column#5 in schema Column: [.k._tidb_rowid])

```

### 2. What did you expect to see? (Required)
A plan
### 3. What did you see instead (Required)
error 1105 `Can't find column Column#N in schema`
### 4. What is your TiDB version? (Required)

tidb `8.0.11-TiDB-v9.0.0-beta.2.pre-2051-g3bea8196a5` @ `3bea8196`

Contributor guide

Open the contributing guide

Research direction

Run the supplied CREATE TABLE/VIEW and EXPLAIN reproducer against the stated TiDB revision; begin in the planner path handling scalar expressions on view columns used as the left operand of IN (subquery). Done means EXPLAIN produces a plan and the SELECT returns zero rows, including the no-GROUP-BY t2 case, without error 1105.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.