Multiple references of same CTE may got wrong result

[guo-shaoge]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table t1, t2;
create table t1(c1 int, c2 varchar(100));
insert into t1 values(1, '2020-10-10');
create table t2(c1 int, c2 date);
insert into t2 values(1, '2020-10-10');
with cte1 as (select t1.c1, (select t2.c2 from t2 where t2.c2 = str_to_date(t1.c2, '%Y-%m-%d')) from t1 inner join t2 on t1.c1 = t2.c1) select /*+ hash_join_build(alias1) */ * from cte1 alias1 inner join cte1 alias2 on alias1.c1 =      alias2.c1;

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

+------+---------------------------------------------------------------------+------+---------------------------------------------------------------------+
| c1   | (select t2.c2 from t2 where t2.c2 = str_to_date(t1.c2, '%Y-%m-%d')) | c1   | (select t2.c2 from t2 where t2.c2 = str_to_date(t1.c2, '%Y-%m-%d')) |
+------+---------------------------------------------------------------------+------+---------------------------------------------------------------------+
|    1 | 2020-10-10                                                          |    1 | 2020-10-10                                                          |
+------+---------------------------------------------------------------------+------+---------------------------------------------------------------------+

3. What did you see instead (Required)

Sometimes the result may be wrong( try multiple times, it depends whether build side of HashJoin_38 runs first or probe side runs first.

MySQL [test]> with cte1 as (select t1.c1, (select t2.c2 from t2 where t2.c2 = str_to_date(t1.c2, '%Y-%m-%d')) from t1 inner join t2 on t1.c1 = t2.c1) select * from cte1 alias1 inner join cte1 alias2 on alias1.c1 = alias2.c1;
+------+---------------------------------------------------------------------+------+---------------------------------------------------------------------+
| c1   | (select t2.c2 from t2 where t2.c2 = str_to_date(t1.c2, '%Y-%m-%d')) | c1   | (select t2.c2 from t2 where t2.c2 = str_to_date(t1.c2, '%Y-%m-%d')) |
+------+---------------------------------------------------------------------+------+---------------------------------------------------------------------+
|    1 | NULL                                                                |    1 | NULL                                                                |
+------+---------------------------------------------------------------------+------+---------------------------------------------------------------------+
1 row in set (0.008 sec)

Plan

MySQL [test]> explain with cte1 as (select t1.c1, (select t2.c2 from t2 where t2.c2 = str_to_date(t1.c2, '%Y-%m-%d')) from t1 inner join t2 on t1.c1 = t2.c1) select * from cte1 alias1 inner join cte1 alias2 on alias1.c1 = alias2.c1;
+--------------------------------------+---------+-----------+--------------------+-----------------------------------------------------+
| id                                   | estRows | task      | access object      | operator info                                       |
+--------------------------------------+---------+-----------+--------------------+-----------------------------------------------------+
| HashJoin_38                          | 0.64    | root      |                    | inner join, equal:[eq(test.t1.c1, test.t1.c1)]      |
| ├─Selection_42(Build)                | 0.80    | root      |                    | not(isnull(test.t1.c1))                             |
| │ └─CTEFullScan_43                   | 1.00    | root      | CTE:cte1 AS alias2 | data:CTE_0                                          |
| └─Selection_40(Probe)                | 0.80    | root      |                    | not(isnull(test.t1.c1))                             |
|   └─CTEFullScan_41                   | 1.00    | root      | CTE:cte1 AS alias1 | data:CTE_0                                          |
| CTE_0                                | 1.00    | root      |                    | Non-Recursive CTE                                   |
| └─Projection_21(Seed Part)           | 1.00    | root      |                    | test.t1.c1, test.t2.c2                              |
|   └─Apply_23                         | 1.00    | root      |                    | CARTESIAN left outer join                           |
|     ├─HashJoin_24(Build)             | 1.00    | root      |                    | inner join, equal:[eq(test.t1.c1, test.t2.c1)]      |
|     │ ├─TableReader_31(Build)        | 1.00    | root      |                    | data:Selection_30                                   |
|     │ │ └─Selection_30               | 1.00    | cop[tikv] |                    | not(isnull(test.t2.c1))                             |
|     │ │   └─TableFullScan_29         | 1.00    | cop[tikv] | table:t2           | keep order:false, stats:pseudo                      |
|     │ └─TableReader_28(Probe)        | 1.00    | root      |                    | data:Selection_27                                   |
|     │   └─Selection_27               | 1.00    | cop[tikv] |                    | not(isnull(test.t1.c1))                             |
|     │     └─TableFullScan_26         | 1.00    | cop[tikv] | table:t1           | keep order:false, stats:pseudo                      |
|     └─MaxOneRow_32(Probe)            | 1.00    | root      |                    |                                                     |
|       └─Selection_35                 | 0.80    | root      |                    | eq(test.t2.c2, str_to_date(test.t1.c2, "%Y-%m-%d")) |
|         └─TableReader_34             | 1.00    | root      |                    | data:TableFullScan_33                               |
|           └─TableFullScan_33         | 1.00    | cop[tikv] | table:t2           | keep order:false, stats:pseudo                      |
+--------------------------------------+---------+-----------+--------------------+-----------------------------------------------------+

4. What is your TiDB version? (Required)

MySQL [test]> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.2.0-alpha-395-g080d4885cd
Edition: Community
Git Commit Hash: 080d4885cd658de2e7e1d18144ab54b9f6ddae49
Git Branch: master
UTC Build Time: 2023-06-13 11:48:30
GoVersion: go1.20.1
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

[guo-shaoge]

1. CTEFullScan_43 and CTEFullScan_41 share the same CTE definition, so they have same PhysicalPlan of CTE definition.
2. When building executor tree of CTE definition, we build it twice, one for CTEFullScan_43 and another for CTEFullScan_44
3. When constructing the executor tree for the second time, because the correlated column of Selection_35 is shared with the one constructed for the first time, the correlated column value of the executor tree constructed for the first time is changed.
4. The corCol.Data in ApplyExec of the first executor tree and the corCol.Data of filter of Selection will be different, which makes wrong result.

[guo-shaoge]

You can turn on tidb_enable_parallel_apply as workaround.

[winoros]

since the DMR version will not have its minor bug-fix patch version.
I'll remove their label.

[seiya-annie]

/found community