Inconsistent Results and Runtime Error When Querying a View with Different Projections in TiDB
1. Minimal reproduce step (Required)
The following SQL statements create a table, insert sample data, and define a view:
CREATE TABLE `t1` (
`c1` int
);
INSERT INTO `t1` VALUES (1),(2);
CREATE VIEW `v1` ( `c_1`, `c_2`, `c_3`, `c_4`, `c_5`, `c_6`) AS
SELECT
`ref_0`.`c1` AS `c_1`,
2 AS `c_2`,
3 AS `c_3`,
(NULL)!=((LAST_VALUE(22) OVER (PARTITION BY `ref_0`.`c1` ORDER BY `ref_0`.`c1`))) AS `c_4`,
5 AS `c_5`,
`ref_0`.`c1` AS `c_6`
FROM `t1` AS `ref_0`
ORDER BY `c_1`, `c_2`,`c_3`,`c_4`,`c_5`,`c_6` LIMIT 164 ;Then, executing the following queries produces inconsistent and unexpected behavior: Query 1: Selecting all columns from the view
select
*
from
v1 as ref_0Output:
+------+-----+-----+------+-----+------+
| c_1 | c_2 | c_3 | c_4 | c_5 | c_6 |
+------+-----+-----+------+-----+------+
| 1 | 2 | 3 | NULL | 5 | 1 |
| 2 | 2 | 3 | NULL | 5 | 2 |
+------+-----+-----+------+-----+------+
2 rows in set (0.00 sec)Query 2: Selecting specific columns (c_1, c_2, c_3, c_4) from the view
select
ref_0.c_1 as c_1,
ref_0.c_2 as c_2,
ref_0.c_3 as c_3,
ref_0.c_4 as c_4
from
v1 as ref_0Unexpected Output:
+------+-----+-----+------+
| c_1 | c_2 | c_3 | c_4 |
+------+-----+-----+------+
| 22 | 2 | 3 | NULL |
| 22 | 2 | 3 | NULL |
+------+-----+-----+------+
2 rows in set (0.00 sec)Query 3: Selecting only c_1 from the view
select
ref_0.c_1 as c_1
from
v1 as ref_0Unexpected Error:
ERROR 1105 (HY000): runtime error: index out of range [4] with length 2
2. What did you expect to see? (Required)
The results should be consistent regardless of the selected columns.
The second query should return the same c_1 values as the first query (1 and 2), not 22.
The third query should not cause a runtime error.
3. What did you see instead (Required)
The second query produced an incorrect c_1 value (22 instead of 1 and 2).
The third query resulted in a runtime error: index out of range [4] with length 2.
4. What is your TiDB version? (Required)
8.0.11-TiDB master https://github.com/pingcap/tidb/commit/b6141ec589ed8e73176692dc982a210ad7cf070b
GoVersion: go1.23.4 Race Enabled: false Check Table Before Drop: false Store: unistore |
Source: pingcap/tidb