SQL with CTE return wrong results

[Yui-Song]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t1 (id int, bench_type varchar(10),version varchar(10),tps int(20));   
insert into t1 (id,bench_type,version,tps) values (1,'sysbench','5.4.0',1111111);
insert into t1 (id,bench_type,version,tps) values (2,'sysbench','6.0.0',222222);


with all_data as
(select * from t1
),version1 as (select * from all_data where version ='5.4.0'
),version2 as(select * from all_data where version ='6.0.0')
select v1.tps v1_tps,v2.tps v2_tps
from version1 v1, version2 v2
where v1.bench_type =v2.bench_type; 

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

>with all_data as
    -> (select * from t1
    -> )select v1.tps v1_tps,v2.tps v2_tps
    -> from (select * from all_data where version ='5.4.0') v1,
    -> (select * from all_data where version ='6.0.0') v2
    -> where v1.bench_type =v2.bench_type;
+---------+--------+
| v1_tps  | v2_tps |
+---------+--------+
| 1111111 | 222222 |
+---------+--------+
1 row in set (0.05 sec)

3. What did you see instead (Required)

with all_data as
    -> (select * from t1
    -> ),version1 as (select * from all_data where version ='5.4.0'
    -> ),version2 as(select * from all_data where version ='6.0.0')
    -> select v1.tps v1_tps,v2.tps v2_tps
    -> from version1 v1, version2 v2
    -> where v1.bench_type =v2.bench_type;
Empty set (0.05 sec)

4. What is your TiDB version? (Required)

| Release Version: v6.0.0
Edition: Community
Git Commit Hash: 6f28ac4
Git Branch: heads/refs/tags/v6.0.0
UTC Build Time: 2022-03-29 07:07:30
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[Yui-Song]

It seems that the predicate version = '6.0.0' is not pushed down?

>explain analyze with all_data as
    -> (select * from t1
    -> ),version1 as (select * from all_data where version ='5.4.0'
    -> ),version2 as(select * from all_data where version ='6.0.0')
    -> select v1.tps v1_tps,v2.tps v2_tps
    -> from version1 v1, version2 v2
    -> where v1.bench_type =v2.bench_type;
+-------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------+-----------+---------+
| id                            | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                            | operator info                                                              | memory    | disk    |
+-------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------+-----------+---------+
| HashJoin_31                   | 1.05    | 0       | root      |               | time:1.41ms, loops:1, build_hash_table:{total:1.33ms, fetch:1.33ms, build:0s}                                                                                                                                                             | inner join, equal:[eq(perfdata.t1.bench_type, perfdata.t1.bench_type)]     | 0 Bytes   | 0 Bytes |
| ├─Selection_35(Build)         | 1.02    | 0       | root      |               | time:1.3ms, loops:1                                                                                                                                                                                                                       | not(isnull(perfdata.t1.bench_type))                                        | 1.98 KB   | N/A     |
| │ └─CTEFullScan_36            | 1.28    | 0       | root      | CTE:v2        | time:1.3ms, loops:1                                                                                                                                                                                                                       | data:CTE_2                                                                 | 0 Bytes   | 0 Bytes |
| └─Selection_33(Probe)         | 1.02    | 1       | root      |               | time:1.28ms, loops:1                                                                                                                                                                                                                      | not(isnull(perfdata.t1.bench_type))                                        | 1.98 KB   | N/A     |
|   └─CTEFullScan_34            | 1.28    | 1       | root      | CTE:v1        | time:1.27ms, loops:2                                                                                                                                                                                                                      | data:CTE_1                                                                 | 3.97 KB   | 0 Bytes |
| CTE_2                         | 1.28    | 0       | root      |               | time:1.3ms, loops:1                                                                                                                                                                                                                       | Non-Recursive CTE                                                          | 0 Bytes   | 0 Bytes |
| └─Selection_27(Seed Part)     | 1.28    | 0       | root      |               | time:1.29ms, loops:1                                                                                                                                                                                                                      | eq(perfdata.t1.version, "6.0.0"), not(isnull(perfdata.t1.bench_type))      | 1.98 KB   | N/A     |
|   └─CTEFullScan_28            | 1.60    | 1       | root      | CTE:all_data  | time:1.27ms, loops:2                                                                                                                                                                                                                      | data:CTE_0                                                                 | 0 Bytes   | 0 Bytes |
| CTE_1                         | 1.28    | 1       | root      |               | time:1.27ms, loops:2                                                                                                                                                                                                                      | Non-Recursive CTE                                                          | 3.97 KB   | 0 Bytes |
| └─Selection_23(Seed Part)     | 1.28    | 1       | root      |               | time:1.26ms, loops:2                                                                                                                                                                                                                      | eq(perfdata.t1.version, "5.4.0"), not(isnull(perfdata.t1.bench_type))      | 1.98 KB   | N/A     |
|   └─CTEFullScan_24            | 1.60    | 1       | root      | CTE:all_data  | time:1.25ms, loops:3                                                                                                                                                                                                                      | data:CTE_0                                                                 | 3.97 KB   | 0 Bytes |
| CTE_0                         | 1.60    | 1       | root      |               | time:1.27ms, loops:2                                                                                                                                                                                                                      | Non-Recursive CTE                                                          | 0 Bytes   | 0 Bytes |
| └─Selection_18(Seed Part)     | 1.60    | 1       | root      |               | time:1.22ms, loops:2                                                                                                                                                                                                                      | and(eq(perfdata.t1.version, "5.4.0"), not(isnull(perfdata.t1.bench_type))) | 1.98 KB   | N/A     |
|   └─TableReader_21            | 2.00    | 2       | root      |               | time:1.2ms, loops:3, cop_task: {num: 1, max: 1.32ms, proc_keys: 2, rpc_num: 1, rpc_time: 1.27ms, copr_cache_hit_ratio: 0.00}                                                                                                              | data:TableFullScan_20                                                      | 332 Bytes | N/A     |
|     └─TableFullScan_20        | 2.00    | 2       | cop[tikv] | table:t1      | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 2, total_process_keys_size: 126, total_keys: 7, rocksdb: {delete_skipped_count: 0, key_skipped_count: 6, block: {cache_hit_count: 4, read_count: 0, read_byte: 0 Bytes}}} | keep order:false, stats:pseudo                                             | N/A       | N/A     |
+-------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------+-----------+---------+
15 rows in set (0.07 sec)

>explain analyze with all_data as
    -> (select * from t1
    -> )select v1.tps v1_tps,v2.tps v2_tps
    -> from (select * from all_data where version ='5.4.0') v1,
    -> (select * from all_data where version ='6.0.0') v2
    -> where v1.bench_type =v2.bench_type;
+-------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id                            | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                            | operator info                                                                                                                                              | memory    | disk    |
+-------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| HashJoin_19                   | 1.60    | 1       | root      |               | time:1.99ms, loops:2, build_hash_table:{total:1.77ms, fetch:1.77ms, build:6.79µs}, probe:{concurrency:5, total:9.15ms, max:1.85ms, probe:14µs, fetch:9.14ms}                                                                              | inner join, equal:[eq(perfdata.t1.bench_type, perfdata.t1.bench_type)]                                                                                     | 49.0 KB   | 0 Bytes |
| ├─Selection_23(Build)         | 1.28    | 1       | root      |               | time:1.68ms, loops:2                                                                                                                                                                                                                      | eq(perfdata.t1.version, "6.0.0"), not(isnull(perfdata.t1.bench_type))                                                                                      | 1.98 KB   | N/A     |
| │ └─CTEFullScan_24            | 1.60    | 2       | root      | CTE:all_data  | time:1.66ms, loops:3                                                                                                                                                                                                                      | data:CTE_0                                                                                                                                                 | 3.97 KB   | 0 Bytes |
| └─Selection_21(Probe)         | 1.28    | 1       | root      |               | time:1.82ms, loops:2                                                                                                                                                                                                                      | eq(perfdata.t1.version, "5.4.0"), not(isnull(perfdata.t1.bench_type))                                                                                      | 1.98 KB   | N/A     |
|   └─CTEFullScan_22            | 1.60    | 2       | root      | CTE:all_data  | time:1.75ms, loops:3                                                                                                                                                                                                                      | data:CTE_0                                                                                                                                                 | 0 Bytes   | 0 Bytes |
| CTE_0                         | 1.60    | 2       | root      |               | time:1.66ms, loops:3                                                                                                                                                                                                                      | Non-Recursive CTE                                                                                                                                          | 3.97 KB   | 0 Bytes |
| └─Selection_13(Seed Part)     | 1.60    | 2       | root      |               | time:1.64ms, loops:2                                                                                                                                                                                                                      | or(and(eq(perfdata.t1.version, "5.4.0"), not(isnull(perfdata.t1.bench_type))), and(eq(perfdata.t1.version, "6.0.0"), not(isnull(perfdata.t1.bench_type)))) | 1.98 KB   | N/A     |
|   └─TableReader_16            | 2.00    | 2       | root      |               | time:1.58ms, loops:3, cop_task: {num: 1, max: 1.61ms, proc_keys: 2, rpc_num: 1, rpc_time: 1.57ms, copr_cache_hit_ratio: 0.00}                                                                                                             | data:TableFullScan_15                                                                                                                                      | 332 Bytes | N/A     |
|     └─TableFullScan_15        | 2.00    | 2       | cop[tikv] | table:t1      | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 2, total_process_keys_size: 126, total_keys: 7, rocksdb: {delete_skipped_count: 0, key_skipped_count: 6, block: {cache_hit_count: 4, read_count: 0, read_byte: 0 Bytes}}} | keep order:false, stats:pseudo                                                                                                                             | N/A       | N/A     |
+-------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
9 rows in set (0.05 sec)