Closed
Description
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)
Activity