Bug: ambiguous column reference with reused CTE #10429
Description
In the current implementation of CTE, we will first bind all the tables in the WITH clause and cache the result SExpr. Then we can look up the CTE table to get the corresponding SExpr when encountering a CTE reference in the query body.
This brought an issue that if a CTE is used in a query body multiple times, they will produce the same column set. If there are joins between the reused CTE references, then the column reference will become ambiguous(i.e. different columns have the same column index).
For example:
-- CTE
MySQL [(none)]> with v as (select * from numbers(2)) select * from v t1, (select * from v where number = 0) t2 where t1.number = 1 and t2.number = 1;
+--------+--------+
| number | number |
+--------+--------+
| 0 | 0 |
+--------+--------+
1 row in set (0.046 sec)
-- Equivalent query
MySQL [(none)]> select t1.number, t2.number from (select * from numbers(2)) t1, (select * from numbers(2) where number = 0) t2 where t1.number = 1 and t2.number = 1;
Empty set (0.045 sec)The result is apparently incorrect. And the root cause is that the number columns are ambiguous, even if it's qualified with table name, see following explain result:
MySQL [(none)]> explain with v as (select * from numbers(2)) select t1.number, t2.number from v t1, (select * from v where number = 0) t2 where t1.number = 1 and t2.number = 1;
+-----------------------------------------------------------------------------+
| explain |
+-----------------------------------------------------------------------------+
| HashJoin |
| ├── join type: CROSS |
| ├── build keys: [] |
| ├── probe keys: [] |
| ├── filters: [] |
| ├── estimated rows: 0.44 |
| ├── Filter(Build) |
| │ ├── filters: [v.number (#0) = 0] |
| │ ├── estimated rows: 0.67 |
| │ └── TableScan |
| │ ├── table: default.system.numbers |
| │ ├── read rows: 2 |
| │ ├── read bytes: 16 |
| │ ├── partitions total: 1 |
| │ ├── partitions scanned: 1 |
| │ ├── push downs: [filters: [v.number (#0) = 0], limit: NONE] |
| │ └── estimated rows: 2.00 |
| └── Filter(Probe) |
| ├── filters: [t1.number (#0) = 1] |
| ├── estimated rows: 0.67 |
| └── TableScan |
| ├── table: default.system.numbers |
| ├── read rows: 2 |
| ├── read bytes: 16 |
| ├── partitions total: 1 |
| ├── partitions scanned: 1 |
| ├── push downs: [filters: [t1.number (#0) = 1], limit: NONE] |
| └── estimated rows: 2.00 |
+-----------------------------------------------------------------------------+
28 rows in set (0.024 sec)
MySQL [(none)]> explain select t1.number, t2.number from (select * from numbers(2)) t1, (select * from numbers(2) where number = 0) t2 where t1.number = 1 and t2.number = 1;
+-----------------------------------------------------------------------------------+
| explain |
+-----------------------------------------------------------------------------------+
| HashJoin |
| ├── join type: CROSS |
| ├── build keys: [] |
| ├── probe keys: [] |
| ├── filters: [] |
| ├── estimated rows: 0.15 |
| ├── Filter(Build) |
| │ ├── filters: [t2.number (#1) = 1, numbers.number (#1) = 0] |
| │ ├── estimated rows: 0.22 |
| │ └── TableScan |
| │ ├── table: default.system.numbers |
| │ ├── read rows: 2 |
| │ ├── read bytes: 16 |
| │ ├── partitions total: 1 |
| │ ├── partitions scanned: 1 |
| │ ├── push downs: [filters: [numbers.number (#1) = 0], limit: NONE] |
| │ └── estimated rows: 2.00 |
| └── Filter(Probe) |
| ├── filters: [t1.number (#0) = 1] |
| ├── estimated rows: 0.67 |
| └── TableScan |
| ├── table: default.system.numbers |
| ├── read rows: 2 |
| ├── read bytes: 16 |
| ├── partitions total: 1 |
| ├── partitions scanned: 1 |
| ├── push downs: [filters: [t1.number (#0) = 1], limit: NONE] |
| └── estimated rows: 2.00 |
+-----------------------------------------------------------------------------------+
28 rows in set (0.049 sec)