Wrong result when update multiple columns with CTE

[harry1129]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE t1,t2;
CREATE TABLE t1(id int,col1 varchar(10),col2 varchar(10),col3 varchar(10));
CREATE TABLE t2(id int,col1 varchar(10),col2 varchar(10),col3 varchar(10));

INSERT INTO t1 values(1,NULL,NULL,null),(2,NULL,NULL,null),(3,NULL,NULL,null);
INSERT INTO t2 values(1,'a','aa','aaa'),(2,'b','bb','bbb'),(3,'c','cc','ccc');


WITH tmp AS (SELECT t2.* FROM t2)
UPDATE t1 SET
t1.col1=(SELECT tmp.col1 FROM tmp WHERE tmp.id=t1.id ),
t1.col2=(SELECT tmp.col2 FROM tmp WHERE tmp.id=t1.id ),
t1.col3=(SELECT tmp.col3 FROM tmp WHERE tmp.id=t1.id )
;

2. What did you expect to see? (Required)

MySQL [test]> select * from t1;
+------+------+------+------+
| id   | col1 | col2 | col3 |
+------+------+------+------+
|    1 | a    | aa   | aaa  |
|    2 | b    | bb   | bbb  |
|    3 | c    | cc   | ccc  |
+------+------+------+------+
3 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> select * from t1;
+------+------+------+------+
| id   | col1 | col2 | col3 |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    | NULL | NULL |
|    3 | c    | cc   | ccc  |
+------+------+------+------+
3 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

This problem can be reproduced in v6.1.6 and v6.5.2.

MySQL [test]> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.5.2
Edition: Community
Git Commit Hash: 29116c0256c52b224da2b34d712c1063d171c0ad
Git Branch: heads/refs/tags/v6.5.2
UTC Build Time: 2023-04-19 10:52:06
GoVersion: go1.19.8
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.1.6
Edition: Community
Git Commit Hash: 705abbd2e2f00c3386bf284654c746521ab26444
Git Branch: heads/refs/tags/v6.1.6
UTC Build Time: 2023-04-06 10:20:29
GoVersion: go1.19.7
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[windtalker]

Seems related to CTE

mysql>  select (SELECT tmp.col1 FROM t2 tmp WHERE tmp.id=t1.id ) col1, (SELECT tmp.col2 FROM t2 tmp WHERE tmp.id=t1.id ) col2, (SELECT tmp.col3 FROM t2 tmp WHERE tmp.id=t1.id ) col3 from t1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| a    | aa   | aaa  |
| b    | bb   | bbb  |
| c    | cc   | ccc  |
+------+------+------+
3 rows in set (0.01 sec)

mysql>  WITH tmp AS (SELECT t2.* FROM t2) select (SELECT tmp.col1 FROM tmp WHERE tmp.id=t1.id ) col1, (SELECT tmp.col2 FROM tmp WHERE tmp.id=t1.id ) col2, (SELECT tmp.col3 FROM tmp WHERE tmp.id=t1.id ) col3 from t1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| a    | aa   | aaa  |
| NULL | NULL | NULL |
| NULL | NULL | NULL |
+------+------+------+
3 rows in set (0.01 sec)

[pcqz]

May introduced by #33158

[guo-shaoge]

Although Selection_17 in CTE_0 is correlated, but CTE_0 will only execute once. Because there are correlated cols in Selection_17, but we miss updating cte.IsInApply when push down Selection to CTE. /cc @wjhuang2016

MySQL [test]> explain WITH tmp AS (SELECT t2.* FROM t2)
    -> UPDATE t1 SET
    -> t1.col1=(SELECT tmp.col1 FROM tmp WHERE tmp.id=t1.id ),
    -> t1.col2=(SELECT tmp.col2 FROM tmp WHERE tmp.id=t1.id );
+-----------------------------------+-------------+-----------+---------------+------------------------------------------------------------+
| id                                | estRows     | task      | access object | operator info                                              |
+-----------------------------------+-------------+-----------+---------------+------------------------------------------------------------+
| Update_15                         | N/A         | root      |               | N/A                                                        |
| └─Apply_22                        | 10000.00    | root      |               | CARTESIAN left outer join                                  |
|   ├─Apply_24(Build)               | 10000.00    | root      |               | CARTESIAN left outer join                                  |
|   │ ├─TableReader_27(Build)       | 10000.00    | root      |               | data:TableFullScan_26                                      |
|   │ │ └─TableFullScan_26          | 10000.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                             |
|   │ └─MaxOneRow_28(Probe)         | 10000.00    | root      |               |                                                            |
|   │   └─Projection_29             | 20000.00    | root      |               | test.t2.col1                                               |
|   │     └─Selection_30            | 20000.00    | root      |               | eq(test.t2.id, test.t1.id)                                 |
|   │       └─CTEFullScan_31        | 80000000.00 | root      | CTE:tmp       | data:CTE_0                                                 |
|   └─MaxOneRow_32(Probe)           | 10000.00    | root      |               |                                                            |
|     └─Projection_33               | 20000.00    | root      |               | test.t2.col2                                               |
|       └─Selection_34              | 20000.00    | root      |               | eq(test.t2.id, test.t1.id)                                 |
|         └─CTEFullScan_35          | 80000000.00 | root      | CTE:tmp       | data:CTE_0                                                 |
| CTE_0                             | 8000.00     | root      |               | Non-Recursive CTE                                          |
| └─Selection_17(Seed Part)         | 8000.00     | root      |               | or(eq(test.t2.id, test.t1.id), eq(test.t2.id, test.t1.id)) |
|   └─TableReader_20                | 10000.00    | root      |               | data:TableFullScan_19                                      |
|     └─TableFullScan_19            | 10000.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                             |
+-----------------------------------+-------------+-----------+---------------+------------------------------------------------------------+
17 rows in set (0.003 sec)

[guo-shaoge]

Also the correlated column in Selection_17 is wrong, it should point to the result row of Apply_24 instead of the result of TableReader_27