tidb
tidb copied to clipboard
pingcap
Incorrect results of SELECT caused by subquery and logical operations
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
Setup the environment:
CREATE TABLE t0 (pkey int, c6 varchar(100));
INSERT INTO t0 VALUES (1,NULL);
Testcase 1
select *
from
t0 as ref_0
where
(((ref_0.c6 is null) and false) and null) xor (true or ('111' >= (
select
null as c0
from
t0 as ref_15
where exists (
select *
from
t0 as ref_16
where false xor ((ref_16.pkey <= (
select
ref_0.pkey as c0
from
t0 as ref_30
order by c0 asc limit 1))
and false))
order by c0 desc limit 1)));
Testcase 1 outputs empty (0 rows).
Then, I replace (ref_0.c6 is null) and false with false. The results should not be changed because anything and false is false. The test case becomes:
Testcase 2
select *
from
t0 as ref_0
where
((false) and null) xor (true or ('111' >= (
select
null as c0
from
t0 as ref_15
where exists (
select *
from
t0 as ref_16
where false xor ((ref_16.pkey <= (
select
ref_0.pkey as c0
from
t0 as ref_30
order by c0 asc limit 1))
and false))
order by c0 desc limit 1)));
2. What did you expect to see? (Required)
Testcase 1 and Testcase 2 should output the same results.
3. What did you see instead (Required)
Testcase 2 outputs
+------+------+
| pkey | c6 |
+------+------+
| 1 | NULL |
+------+------+
1 row in set (0.00 sec)
While Testcase 1 outputs empty set:
Empty set (0.00 sec)
4. What is your TiDB version? (Required)
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Release Version: v7.1.0-alpha-27-gf5ca27ef3 Edition: Community Git Commit Hash: f5ca27ef326e94fb29b4ba35a5d59aa575f66880 Git Branch: master UTC Build Time: 2023-03-23 13:57:53 GoVersion: go1.20.2 Race Enabled: false TiKV Min Version: 6.2.0-alpha Check Table Before Drop: false Store: unistore | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Have some debugs, the correct result should be
+------+------+
| pkey | c6 |
+------+------+
| 1 | NULL |
+------+------+
However, for the first sql, the plan generated by TiDB is
mysql> explain select * from t0 as ref_0 where (((ref_0.c6 is null) and false) and null) xor (true or ('111' >= ( select null as c0 from t0 as ref_15
where exists ( select * from t0 as ref_16 where false xor ((ref_16.pkey <= ( select
ref_0.pkey as c0 from t0 as ref_30 order by c0 asc limit 1)) and false)) order by c0 desc limit 1)));
+------------------------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Projection_25 | 1.00 | root | | test.t0.pkey, test.t0.c6 |
| └─Apply_27 | 1.00 | root | | CARTESIAN inner join, other cond:xor(and(and(isnull(test.t0.c6), 0), NULL), or(1, ge("111", from_binary(Column#14)))) |
| ├─TableReader_29(Build) | 1.00 | root | | data:TableFullScan_28 |
| │ └─TableFullScan_28 | 1.00 | cop[tikv] | table:ref_0 | keep order:false, stats:pseudo |
| └─Projection_30(Probe) | 0.80 | root | | <nil>->Column#14 |
| └─Limit_33 | 0.80 | root | | offset:0, count:1 |
| └─HashJoin_34 | 0.80 | root | | CARTESIAN semi join |
| ├─HashJoin_37(Build) | 1.00 | root | | CARTESIAN inner join, other cond:xor(0, and(le(test.t0.pkey, Column#13), 0)) |
| │ ├─MaxOneRow_41(Build) | 1.00 | root | | |
| │ │ └─Projection_42 | 1.00 | root | | test.t0.pkey |
| │ │ └─Limit_43 | 1.00 | root | | offset:0, count:1 |
| │ │ └─TableReader_47 | 1.00 | root | | data:Limit_46 |
| │ │ └─Limit_46 | 1.00 | cop[tikv] | | offset:0, count:1 |
| │ │ └─TableFullScan_45 | 1.00 | cop[tikv] | table:ref_30 | keep order:false, stats:pseudo |
| │ └─TableReader_40(Probe) | 1.00 | root | | data:TableFullScan_39 |
| │ └─TableFullScan_39 | 1.00 | cop[tikv] | table:ref_16 | keep order:false, stats:pseudo |
| └─TableReader_36(Probe) | 1.00 | root | | data:TableFullScan_35 |
| └─TableFullScan_35 | 1.00 | cop[tikv] | table:ref_15 | keep order:false, stats:pseudo |
+------------------------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------+
18 rows in set (0.00 sec)
Note that Apply_27 is a inner join, which is not expected, the join type should be left outer join, so I think we should let the optimizer team to take a look.
It's about the filter's null-rejective testing.
We get a false-positive result for the (((ref_0.c6 is null) and false) and null) xor (true or ('111' >= the subquery col)))
@winoros we don't get false positive result for (((ref_0.c6 is null) and false) and null) xor (true or ('111' >= the subquery col))). First of all, we have a bug in the explain result for Apply: https://github.com/pingcap/tidb/blob/dc32451b9135f5366bc5ae69bb3c43743d785834/planner/core/flat_plan.go#L249-L252
When explain, the InnerChildIdx side of PhysicalApply is marked as the probeside, which is not right, since for PhysicalApply, useOuterToBuild is always false, which means we always use innerChildIdx as the build side:
https://github.com/pingcap/tidb/blob/dc32451b9135f5366bc5ae69bb3c43743d785834/planner/core/exhaust_physical_plans.go#L2641-L2643
So the InnerChildIdx should always be the probe side.
After fix this bug, then the explain analyze of the query of wrong result is
mysql> explain analyze select * from t0 as ref_0 where (((ref_0.c6 is null) and false) and null) xor (true or ('111' >= ( select null as c0 from t0 as ref_15 where exists ( select * from t0 as ref_16 where false xor ((ref_16.pkey <= ( select ref_0.pkey as c0 from t0 as ref_30 order by c0 asc limit 1)) and false)) order by c0 desc limit 1)));
+--------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+---------+
| Projection_25 | 1.00 | 0 | root | | time:19.6ms, loops:1, RRU:1.078212, WRU:0.000000, Concurrency:OFF | test.t0.pkey, test.t0.c6 | 1.61 KB | N/A |
| └─Apply_27 | 1.00 | 0 | root | | time:19.6ms, loops:1, Concurrency:OFF, cache:OFF | CARTESIAN inner join, other cond:xor(and(and(isnull(test.t0.c6), 0), NULL), or(1, ge("111", from_binary(Column#14)))) | 0 Bytes | N/A |
| ├─Projection_30(Build) | 0.80 | 0 | root | | time:674.7µs, loops:1, Concurrency:OFF | <nil>->Column#14 | 129 Bytes | N/A |
| │ └─Limit_33 | 0.80 | 0 | root | | time:671.7µs, loops:1 | offset:0, count:1 | N/A | N/A |
| │ └─HashJoin_34 | 0.80 | 0 | root | | time:670.4µs, loops:1, build_hash_table:{total:578.4µs, fetch:578.4µs, build:0s} | CARTESIAN semi join | 0 Bytes | 0 Bytes |
| │ ├─HashJoin_37(Build) | 1.00 | 0 | root | | time:492.6µs, loops:1, build_hash_table:{total:317.5µs, fetch:310.5µs, build:6.99µs}, probe:{concurrency:5, total:1.67ms, max:398.7µs, probe:80.3µs, fetch:1.59ms} | CARTESIAN inner join, other cond:xor(0, and(le(test.t0.pkey, Column#13), 0)) | 9.46 KB | 0 Bytes |
| │ │ ├─MaxOneRow_41(Build) | 1.00 | 1 | root | | time:272.4µs, loops:2 | | N/A | N/A |
| │ │ │ └─Projection_42 | 1.00 | 1 | root | | time:266.8µs, loops:2, Concurrency:OFF | test.t0.pkey | 136 Bytes | N/A |
| │ │ │ └─Limit_43 | 1.00 | 1 | root | | time:259µs, loops:2 | offset:0, count:1 | N/A | N/A |
| │ │ │ └─TableReader_47 | 1.00 | 1 | root | | time:257.6µs, loops:1, cop_task: {num: 1, max: 445.2µs, proc_keys: 1, tot_proc: 47.7µs, tot_wait: 55.7µs, rpc_num: 1, rpc_time: 429.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 1.04µs, max_distsql_concurrency: 1} | data:Limit_46 | 248 Bytes | N/A |
| │ │ │ └─Limit_46 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 38.5µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | offset:0, count:1 | N/A | N/A |
| │ │ │ └─TableFullScan_45 | 1.00 | 1 | cop[tikv] | table:ref_30 | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A |
| │ │ └─TableReader_40(Probe) | 1.00 | 1 | root | | time:311.8µs, loops:2, cop_task: {num: 1, max: 516.8µs, proc_keys: 1, tot_proc: 44.5µs, tot_wait: 45.6µs, rpc_num: 1, rpc_time: 493.7µs, copr_cache_hit_ratio: 0.00, build_task_duration: 3.86µs, max_distsql_concurrency: 1} | data:TableFullScan_39 | 240 Bytes | N/A |
| │ │ └─TableFullScan_39 | 1.00 | 1 | cop[tikv] | table:ref_16 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 26.6µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A |
| │ └─TableReader_36(Probe) | 1.00 | 1 | root | | time:378.6µs, loops:1, cop_task: {num: 1, max: 454.1µs, proc_keys: 1, tot_proc: 45.7µs, tot_wait: 50.9µs, rpc_num: 1, rpc_time: 433.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 6.23µs, max_distsql_concurrency: 1} | data:TableFullScan_35 | 240 Bytes | N/A |
| │ └─TableFullScan_35 | 1.00 | 1 | cop[tikv] | table:ref_15 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 24.1µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_29(Probe) | 1.00 | 1 | root | | time:18.8ms, loops:2, cop_task: {num: 1, max: 18.8ms, proc_keys: 1, tot_proc: 90.8µs, tot_wait: 68.7µs, rpc_num: 1, rpc_time: 18.7ms, copr_cache_hit_ratio: 0.00, build_task_duration: 265.6µs, max_distsql_concurrency: 1} | data:TableFullScan_28 | 265 Bytes | N/A |
| └─TableFullScan_28 | 1.00 | 1 | cop[tikv] | table:ref_0 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 38.8µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A |
+--------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+---------+
18 rows in set (0.02 sec)
Note that Apply_27 is inner join, and the build side of Apply_27 is empty, so there is not chance to evaluate other_conditions in Apply_27, since the buld side is empty, the output of Apply_27 is always empty.
For the sql with right result
mysql> explain analyze select * from t0 as ref_0 where (false and null) xor (true or ('111' >= ( select null as c0 from t0 as ref_15 where exists ( select * from t0 as ref_16 where false xor ((ref_16.pkey <= ( select ref_0.pkey as c0 from t0 as ref_30 order by c0 asc limit 1)) and false)) order by c0 desc limit 1)));
+----------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+-----------+---------+
| Projection_25 | 0.80 | 1 | root | | time:1.71ms, loops:2, RRU:1.098353, WRU:0.000000, Concurrency:OFF | test.t0.pkey, test.t0.c6 | 12.2 KB | N/A |
| └─Selection_26 | 0.80 | 1 | root | | time:1.71ms, loops:2 | xor(0, or(1, ge("111", from_binary(Column#14)))) | 1.86 KB | N/A |
| └─Apply_28 | 1.00 | 1 | root | | time:1.69ms, loops:3, Concurrency:OFF, cache:OFF | CARTESIAN left outer join | 0 Bytes | N/A |
| ├─Projection_31(Build) | 0.80 | 0 | root | | time:878µs, loops:1, Concurrency:OFF | <nil>->Column#14 | 129 Bytes | N/A |
| │ └─Limit_34 | 0.80 | 0 | root | | time:875.5µs, loops:1 | offset:0, count:1 | N/A | N/A |
| │ └─HashJoin_35 | 0.80 | 0 | root | | time:873.6µs, loops:1, build_hash_table:{total:806.8µs, fetch:806.8µs, build:0s} | CARTESIAN semi join | 0 Bytes | 0 Bytes |
| │ ├─HashJoin_38(Build) | 1.00 | 0 | root | | time:780.6µs, loops:1, build_hash_table:{total:668.4µs, fetch:664.7µs, build:3.66µs}, probe:{concurrency:5, total:3.33ms, max:685.2µs, probe:37.1µs, fetch:3.29ms} | CARTESIAN inner join, other cond:xor(0, and(le(test.t0.pkey, Column#13), 0)) | 9.46 KB | 0 Bytes |
| │ │ ├─MaxOneRow_42(Build) | 1.00 | 1 | root | | time:634.6µs, loops:2 | | N/A | N/A |
| │ │ │ └─Projection_43 | 1.00 | 1 | root | | time:631.4µs, loops:2, Concurrency:OFF | test.t0.pkey | 136 Bytes | N/A |
| │ │ │ └─Limit_44 | 1.00 | 1 | root | | time:624.3µs, loops:2 | offset:0, count:1 | N/A | N/A |
| │ │ │ └─TableReader_48 | 1.00 | 1 | root | | time:622.5µs, loops:1, cop_task: {num: 1, max: 671.3µs, proc_keys: 1, tot_proc: 74.3µs, tot_wait: 54.7µs, rpc_num: 1, rpc_time: 646.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 944ns, max_distsql_concurrency: 1} | data:Limit_47 | 248 Bytes | N/A |
| │ │ │ └─Limit_47 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 31µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | offset:0, count:1 | N/A | N/A |
| │ │ │ └─TableFullScan_46 | 1.00 | 1 | cop[tikv] | table:ref_30 | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A |
| │ │ └─TableReader_41(Probe) | 1.00 | 1 | root | | time:560.6µs, loops:2, cop_task: {num: 1, max: 678.7µs, proc_keys: 1, tot_proc: 70.1µs, tot_wait: 63.5µs, rpc_num: 1, rpc_time: 641.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 1.27µs, max_distsql_concurrency: 1} | data:TableFullScan_40 | 240 Bytes | N/A |
| │ │ └─TableFullScan_40 | 1.00 | 1 | cop[tikv] | table:ref_16 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 34.4µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A |
| │ └─TableReader_37(Probe) | 1.00 | 1 | root | | time:497.7µs, loops:1, cop_task: {num: 1, max: 553.3µs, proc_keys: 1, tot_proc: 50.9µs, tot_wait: 64.9µs, rpc_num: 1, rpc_time: 532.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 3.85µs, max_distsql_concurrency: 1} | data:TableFullScan_36 | 240 Bytes | N/A |
| │ └─TableFullScan_36 | 1.00 | 1 | cop[tikv] | table:ref_15 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 26µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_30(Probe) | 1.00 | 1 | root | | time:714.5µs, loops:4, cop_task: {num: 1, max: 701.9µs, proc_keys: 1, tot_proc: 93.9µs, tot_wait: 68.2µs, rpc_num: 1, rpc_time: 671.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 7.76µs, max_distsql_concurrency: 1} | data:TableFullScan_29 | 265 Bytes | N/A |
| └─TableFullScan_29 | 1.00 | 1 | cop[tikv] | table:ref_0 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 41.9µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A |
+----------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+-----------+---------+
19 rows in set (0.00 sec)
The bulid side of Apply_28 is also empty, but Apply_28 is left outer join, so the probe side will be kept after Apply_28, and filter in Selection_26 is true, this row is output after Selection_26
FYI, I found the first bad commit using git bisect:
3e23cdd0e82a3fb04be9940390647e68b387bc76 is the first bad commit
commit 3e23cdd0e82a3fb04be9940390647e68b387bc76
Author: pingcap-github-bot <[email protected]>
Date: Tue Mar 31 23:19:57 2020 +0800
chunk: make `mysql.TypeNull` be judged as fixedLen (#15512) (#15740)
executor/executor_test.go | 11 +++++++++++
util/chunk/codec.go | 2 +-
2 files changed, 12 insertions(+), 1 deletion(-)
Before this commit, Testcase 1 will make TiDB panic (i.e., ERROR 1105 (HY000): runtime error: index out of range [1] with length 1) instead of returning incorrect results.
@winoros we don't get false positive result for
(((ref_0.c6 is null) and false) and null) xor (true or ('111' >= the subquery col))). First of all, we have a bug in the explain result forApply:https://github.com/pingcap/tidb/blob/dc32451b9135f5366bc5ae69bb3c43743d785834/planner/core/flat_plan.go#L249-L252
When explain, the
InnerChildIdxside ofPhysicalApplyis marked as theprobeside, which is not right, since forPhysicalApply,useOuterToBuildis alwaysfalse, which means we always useinnerChildIdxas the build side: https://github.com/pingcap/tidb/blob/dc32451b9135f5366bc5ae69bb3c43743d785834/planner/core/exhaust_physical_plans.go#L2641-L2643So the
InnerChildIdxshould always be the probe side. After fix this bug, then the explain analyze of the query of wrong result ismysql> explain analyze select * from t0 as ref_0 where (((ref_0.c6 is null) and false) and null) xor (true or ('111' >= ( select null as c0 from t0 as ref_15 where exists ( select * from t0 as ref_16 where false xor ((ref_16.pkey <= ( select ref_0.pkey as c0 from t0 as ref_30 order by c0 asc limit 1)) and false)) order by c0 desc limit 1))); +--------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+---------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +--------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+---------+ | Projection_25 | 1.00 | 0 | root | | time:19.6ms, loops:1, RRU:1.078212, WRU:0.000000, Concurrency:OFF | test.t0.pkey, test.t0.c6 | 1.61 KB | N/A | | └─Apply_27 | 1.00 | 0 | root | | time:19.6ms, loops:1, Concurrency:OFF, cache:OFF | CARTESIAN inner join, other cond:xor(and(and(isnull(test.t0.c6), 0), NULL), or(1, ge("111", from_binary(Column#14)))) | 0 Bytes | N/A | | ├─Projection_30(Build) | 0.80 | 0 | root | | time:674.7µs, loops:1, Concurrency:OFF | <nil>->Column#14 | 129 Bytes | N/A | | │ └─Limit_33 | 0.80 | 0 | root | | time:671.7µs, loops:1 | offset:0, count:1 | N/A | N/A | | │ └─HashJoin_34 | 0.80 | 0 | root | | time:670.4µs, loops:1, build_hash_table:{total:578.4µs, fetch:578.4µs, build:0s} | CARTESIAN semi join | 0 Bytes | 0 Bytes | | │ ├─HashJoin_37(Build) | 1.00 | 0 | root | | time:492.6µs, loops:1, build_hash_table:{total:317.5µs, fetch:310.5µs, build:6.99µs}, probe:{concurrency:5, total:1.67ms, max:398.7µs, probe:80.3µs, fetch:1.59ms} | CARTESIAN inner join, other cond:xor(0, and(le(test.t0.pkey, Column#13), 0)) | 9.46 KB | 0 Bytes | | │ │ ├─MaxOneRow_41(Build) | 1.00 | 1 | root | | time:272.4µs, loops:2 | | N/A | N/A | | │ │ │ └─Projection_42 | 1.00 | 1 | root | | time:266.8µs, loops:2, Concurrency:OFF | test.t0.pkey | 136 Bytes | N/A | | │ │ │ └─Limit_43 | 1.00 | 1 | root | | time:259µs, loops:2 | offset:0, count:1 | N/A | N/A | | │ │ │ └─TableReader_47 | 1.00 | 1 | root | | time:257.6µs, loops:1, cop_task: {num: 1, max: 445.2µs, proc_keys: 1, tot_proc: 47.7µs, tot_wait: 55.7µs, rpc_num: 1, rpc_time: 429.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 1.04µs, max_distsql_concurrency: 1} | data:Limit_46 | 248 Bytes | N/A | | │ │ │ └─Limit_46 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 38.5µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | offset:0, count:1 | N/A | N/A | | │ │ │ └─TableFullScan_45 | 1.00 | 1 | cop[tikv] | table:ref_30 | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A | | │ │ └─TableReader_40(Probe) | 1.00 | 1 | root | | time:311.8µs, loops:2, cop_task: {num: 1, max: 516.8µs, proc_keys: 1, tot_proc: 44.5µs, tot_wait: 45.6µs, rpc_num: 1, rpc_time: 493.7µs, copr_cache_hit_ratio: 0.00, build_task_duration: 3.86µs, max_distsql_concurrency: 1} | data:TableFullScan_39 | 240 Bytes | N/A | | │ │ └─TableFullScan_39 | 1.00 | 1 | cop[tikv] | table:ref_16 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 26.6µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | | │ └─TableReader_36(Probe) | 1.00 | 1 | root | | time:378.6µs, loops:1, cop_task: {num: 1, max: 454.1µs, proc_keys: 1, tot_proc: 45.7µs, tot_wait: 50.9µs, rpc_num: 1, rpc_time: 433.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 6.23µs, max_distsql_concurrency: 1} | data:TableFullScan_35 | 240 Bytes | N/A | | │ └─TableFullScan_35 | 1.00 | 1 | cop[tikv] | table:ref_15 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 24.1µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | | └─TableReader_29(Probe) | 1.00 | 1 | root | | time:18.8ms, loops:2, cop_task: {num: 1, max: 18.8ms, proc_keys: 1, tot_proc: 90.8µs, tot_wait: 68.7µs, rpc_num: 1, rpc_time: 18.7ms, copr_cache_hit_ratio: 0.00, build_task_duration: 265.6µs, max_distsql_concurrency: 1} | data:TableFullScan_28 | 265 Bytes | N/A | | └─TableFullScan_28 | 1.00 | 1 | cop[tikv] | table:ref_0 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 38.8µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | +--------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+---------+ 18 rows in set (0.02 sec)Note that
Apply_27is inner join, and thebuildside ofApply_27is empty, so there is not chance to evaluateother_conditionsinApply_27, since the buld side is empty, the output ofApply_27is always empty.For the sql with right result
mysql> explain analyze select * from t0 as ref_0 where (false and null) xor (true or ('111' >= ( select null as c0 from t0 as ref_15 where exists ( select * from t0 as ref_16 where false xor ((ref_16.pkey <= ( select ref_0.pkey as c0 from t0 as ref_30 order by c0 asc limit 1)) and false)) order by c0 desc limit 1))); +----------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+-----------+---------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +----------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+-----------+---------+ | Projection_25 | 0.80 | 1 | root | | time:1.71ms, loops:2, RRU:1.098353, WRU:0.000000, Concurrency:OFF | test.t0.pkey, test.t0.c6 | 12.2 KB | N/A | | └─Selection_26 | 0.80 | 1 | root | | time:1.71ms, loops:2 | xor(0, or(1, ge("111", from_binary(Column#14)))) | 1.86 KB | N/A | | └─Apply_28 | 1.00 | 1 | root | | time:1.69ms, loops:3, Concurrency:OFF, cache:OFF | CARTESIAN left outer join | 0 Bytes | N/A | | ├─Projection_31(Build) | 0.80 | 0 | root | | time:878µs, loops:1, Concurrency:OFF | <nil>->Column#14 | 129 Bytes | N/A | | │ └─Limit_34 | 0.80 | 0 | root | | time:875.5µs, loops:1 | offset:0, count:1 | N/A | N/A | | │ └─HashJoin_35 | 0.80 | 0 | root | | time:873.6µs, loops:1, build_hash_table:{total:806.8µs, fetch:806.8µs, build:0s} | CARTESIAN semi join | 0 Bytes | 0 Bytes | | │ ├─HashJoin_38(Build) | 1.00 | 0 | root | | time:780.6µs, loops:1, build_hash_table:{total:668.4µs, fetch:664.7µs, build:3.66µs}, probe:{concurrency:5, total:3.33ms, max:685.2µs, probe:37.1µs, fetch:3.29ms} | CARTESIAN inner join, other cond:xor(0, and(le(test.t0.pkey, Column#13), 0)) | 9.46 KB | 0 Bytes | | │ │ ├─MaxOneRow_42(Build) | 1.00 | 1 | root | | time:634.6µs, loops:2 | | N/A | N/A | | │ │ │ └─Projection_43 | 1.00 | 1 | root | | time:631.4µs, loops:2, Concurrency:OFF | test.t0.pkey | 136 Bytes | N/A | | │ │ │ └─Limit_44 | 1.00 | 1 | root | | time:624.3µs, loops:2 | offset:0, count:1 | N/A | N/A | | │ │ │ └─TableReader_48 | 1.00 | 1 | root | | time:622.5µs, loops:1, cop_task: {num: 1, max: 671.3µs, proc_keys: 1, tot_proc: 74.3µs, tot_wait: 54.7µs, rpc_num: 1, rpc_time: 646.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 944ns, max_distsql_concurrency: 1} | data:Limit_47 | 248 Bytes | N/A | | │ │ │ └─Limit_47 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 31µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | offset:0, count:1 | N/A | N/A | | │ │ │ └─TableFullScan_46 | 1.00 | 1 | cop[tikv] | table:ref_30 | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A | | │ │ └─TableReader_41(Probe) | 1.00 | 1 | root | | time:560.6µs, loops:2, cop_task: {num: 1, max: 678.7µs, proc_keys: 1, tot_proc: 70.1µs, tot_wait: 63.5µs, rpc_num: 1, rpc_time: 641.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 1.27µs, max_distsql_concurrency: 1} | data:TableFullScan_40 | 240 Bytes | N/A | | │ │ └─TableFullScan_40 | 1.00 | 1 | cop[tikv] | table:ref_16 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 34.4µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | | │ └─TableReader_37(Probe) | 1.00 | 1 | root | | time:497.7µs, loops:1, cop_task: {num: 1, max: 553.3µs, proc_keys: 1, tot_proc: 50.9µs, tot_wait: 64.9µs, rpc_num: 1, rpc_time: 532.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 3.85µs, max_distsql_concurrency: 1} | data:TableFullScan_36 | 240 Bytes | N/A | | │ └─TableFullScan_36 | 1.00 | 1 | cop[tikv] | table:ref_15 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 26µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | | └─TableReader_30(Probe) | 1.00 | 1 | root | | time:714.5µs, loops:4, cop_task: {num: 1, max: 701.9µs, proc_keys: 1, tot_proc: 93.9µs, tot_wait: 68.2µs, rpc_num: 1, rpc_time: 671.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 7.76µs, max_distsql_concurrency: 1} | data:TableFullScan_29 | 265 Bytes | N/A | | └─TableFullScan_29 | 1.00 | 1 | cop[tikv] | table:ref_0 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 41.9µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | +----------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+-----------+---------+ 19 rows in set (0.00 sec)The bulid side of
Apply_28is also empty, butApply_28is left outer join, so the probe side will be kept afterApply_28, and filter inSelection_26is true, this row is output afterSelection_26
It's about the filter's null-rejective testing.
@windtalker Apply_27 is initialized as an outer join. Then we will test the filter (((ref_0.c6 is null) and false) and null) xor (true or ('111' >= the subquery col))) to see whether the filter can filter out all null values from the join's inner side.
What I said that it's false-positive is that TiDB thinks the filter (((ref_0.c6 is null) and false) and null) xor (true or ('111' >= the subquery col))) can filter out all null values that comes from the join's inner side. So we convert the outer join to inner join. But actually, it cannot.
@winoros we don't get false positive result for
(((ref_0.c6 is null) and false) and null) xor (true or ('111' >= the subquery col))). First of all, we have a bug in the explain result forApply: https://github.com/pingcap/tidb/blob/dc32451b9135f5366bc5ae69bb3c43743d785834/planner/core/flat_plan.go#L249-L252When explain, the
InnerChildIdxside ofPhysicalApplyis marked as theprobeside, which is not right, since forPhysicalApply,useOuterToBuildis alwaysfalse, which means we always useinnerChildIdxas the build side: https://github.com/pingcap/tidb/blob/dc32451b9135f5366bc5ae69bb3c43743d785834/planner/core/exhaust_physical_plans.go#L2641-L2643So the
InnerChildIdxshould always be the probe side. After fix this bug, then the explain analyze of the query of wrong result ismysql> explain analyze select * from t0 as ref_0 where (((ref_0.c6 is null) and false) and null) xor (true or ('111' >= ( select null as c0 from t0 as ref_15 where exists ( select * from t0 as ref_16 where false xor ((ref_16.pkey <= ( select ref_0.pkey as c0 from t0 as ref_30 order by c0 asc limit 1)) and false)) order by c0 desc limit 1))); +--------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+---------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +--------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+---------+ | Projection_25 | 1.00 | 0 | root | | time:19.6ms, loops:1, RRU:1.078212, WRU:0.000000, Concurrency:OFF | test.t0.pkey, test.t0.c6 | 1.61 KB | N/A | | └─Apply_27 | 1.00 | 0 | root | | time:19.6ms, loops:1, Concurrency:OFF, cache:OFF | CARTESIAN inner join, other cond:xor(and(and(isnull(test.t0.c6), 0), NULL), or(1, ge("111", from_binary(Column#14)))) | 0 Bytes | N/A | | ├─Projection_30(Build) | 0.80 | 0 | root | | time:674.7µs, loops:1, Concurrency:OFF | <nil>->Column#14 | 129 Bytes | N/A | | │ └─Limit_33 | 0.80 | 0 | root | | time:671.7µs, loops:1 | offset:0, count:1 | N/A | N/A | | │ └─HashJoin_34 | 0.80 | 0 | root | | time:670.4µs, loops:1, build_hash_table:{total:578.4µs, fetch:578.4µs, build:0s} | CARTESIAN semi join | 0 Bytes | 0 Bytes | | │ ├─HashJoin_37(Build) | 1.00 | 0 | root | | time:492.6µs, loops:1, build_hash_table:{total:317.5µs, fetch:310.5µs, build:6.99µs}, probe:{concurrency:5, total:1.67ms, max:398.7µs, probe:80.3µs, fetch:1.59ms} | CARTESIAN inner join, other cond:xor(0, and(le(test.t0.pkey, Column#13), 0)) | 9.46 KB | 0 Bytes | | │ │ ├─MaxOneRow_41(Build) | 1.00 | 1 | root | | time:272.4µs, loops:2 | | N/A | N/A | | │ │ │ └─Projection_42 | 1.00 | 1 | root | | time:266.8µs, loops:2, Concurrency:OFF | test.t0.pkey | 136 Bytes | N/A | | │ │ │ └─Limit_43 | 1.00 | 1 | root | | time:259µs, loops:2 | offset:0, count:1 | N/A | N/A | | │ │ │ └─TableReader_47 | 1.00 | 1 | root | | time:257.6µs, loops:1, cop_task: {num: 1, max: 445.2µs, proc_keys: 1, tot_proc: 47.7µs, tot_wait: 55.7µs, rpc_num: 1, rpc_time: 429.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 1.04µs, max_distsql_concurrency: 1} | data:Limit_46 | 248 Bytes | N/A | | │ │ │ └─Limit_46 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 38.5µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | offset:0, count:1 | N/A | N/A | | │ │ │ └─TableFullScan_45 | 1.00 | 1 | cop[tikv] | table:ref_30 | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A | | │ │ └─TableReader_40(Probe) | 1.00 | 1 | root | | time:311.8µs, loops:2, cop_task: {num: 1, max: 516.8µs, proc_keys: 1, tot_proc: 44.5µs, tot_wait: 45.6µs, rpc_num: 1, rpc_time: 493.7µs, copr_cache_hit_ratio: 0.00, build_task_duration: 3.86µs, max_distsql_concurrency: 1} | data:TableFullScan_39 | 240 Bytes | N/A | | │ │ └─TableFullScan_39 | 1.00 | 1 | cop[tikv] | table:ref_16 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 26.6µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | | │ └─TableReader_36(Probe) | 1.00 | 1 | root | | time:378.6µs, loops:1, cop_task: {num: 1, max: 454.1µs, proc_keys: 1, tot_proc: 45.7µs, tot_wait: 50.9µs, rpc_num: 1, rpc_time: 433.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 6.23µs, max_distsql_concurrency: 1} | data:TableFullScan_35 | 240 Bytes | N/A | | │ └─TableFullScan_35 | 1.00 | 1 | cop[tikv] | table:ref_15 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 24.1µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | | └─TableReader_29(Probe) | 1.00 | 1 | root | | time:18.8ms, loops:2, cop_task: {num: 1, max: 18.8ms, proc_keys: 1, tot_proc: 90.8µs, tot_wait: 68.7µs, rpc_num: 1, rpc_time: 18.7ms, copr_cache_hit_ratio: 0.00, build_task_duration: 265.6µs, max_distsql_concurrency: 1} | data:TableFullScan_28 | 265 Bytes | N/A | | └─TableFullScan_28 | 1.00 | 1 | cop[tikv] | table:ref_0 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 38.8µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | +--------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+---------+ 18 rows in set (0.02 sec)Note that
Apply_27is inner join, and thebuildside ofApply_27is empty, so there is not chance to evaluateother_conditionsinApply_27, since the buld side is empty, the output ofApply_27is always empty. For the sql with right resultmysql> explain analyze select * from t0 as ref_0 where (false and null) xor (true or ('111' >= ( select null as c0 from t0 as ref_15 where exists ( select * from t0 as ref_16 where false xor ((ref_16.pkey <= ( select ref_0.pkey as c0 from t0 as ref_30 order by c0 asc limit 1)) and false)) order by c0 desc limit 1))); +----------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+-----------+---------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +----------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+-----------+---------+ | Projection_25 | 0.80 | 1 | root | | time:1.71ms, loops:2, RRU:1.098353, WRU:0.000000, Concurrency:OFF | test.t0.pkey, test.t0.c6 | 12.2 KB | N/A | | └─Selection_26 | 0.80 | 1 | root | | time:1.71ms, loops:2 | xor(0, or(1, ge("111", from_binary(Column#14)))) | 1.86 KB | N/A | | └─Apply_28 | 1.00 | 1 | root | | time:1.69ms, loops:3, Concurrency:OFF, cache:OFF | CARTESIAN left outer join | 0 Bytes | N/A | | ├─Projection_31(Build) | 0.80 | 0 | root | | time:878µs, loops:1, Concurrency:OFF | <nil>->Column#14 | 129 Bytes | N/A | | │ └─Limit_34 | 0.80 | 0 | root | | time:875.5µs, loops:1 | offset:0, count:1 | N/A | N/A | | │ └─HashJoin_35 | 0.80 | 0 | root | | time:873.6µs, loops:1, build_hash_table:{total:806.8µs, fetch:806.8µs, build:0s} | CARTESIAN semi join | 0 Bytes | 0 Bytes | | │ ├─HashJoin_38(Build) | 1.00 | 0 | root | | time:780.6µs, loops:1, build_hash_table:{total:668.4µs, fetch:664.7µs, build:3.66µs}, probe:{concurrency:5, total:3.33ms, max:685.2µs, probe:37.1µs, fetch:3.29ms} | CARTESIAN inner join, other cond:xor(0, and(le(test.t0.pkey, Column#13), 0)) | 9.46 KB | 0 Bytes | | │ │ ├─MaxOneRow_42(Build) | 1.00 | 1 | root | | time:634.6µs, loops:2 | | N/A | N/A | | │ │ │ └─Projection_43 | 1.00 | 1 | root | | time:631.4µs, loops:2, Concurrency:OFF | test.t0.pkey | 136 Bytes | N/A | | │ │ │ └─Limit_44 | 1.00 | 1 | root | | time:624.3µs, loops:2 | offset:0, count:1 | N/A | N/A | | │ │ │ └─TableReader_48 | 1.00 | 1 | root | | time:622.5µs, loops:1, cop_task: {num: 1, max: 671.3µs, proc_keys: 1, tot_proc: 74.3µs, tot_wait: 54.7µs, rpc_num: 1, rpc_time: 646.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 944ns, max_distsql_concurrency: 1} | data:Limit_47 | 248 Bytes | N/A | | │ │ │ └─Limit_47 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 31µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | offset:0, count:1 | N/A | N/A | | │ │ │ └─TableFullScan_46 | 1.00 | 1 | cop[tikv] | table:ref_30 | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A | | │ │ └─TableReader_41(Probe) | 1.00 | 1 | root | | time:560.6µs, loops:2, cop_task: {num: 1, max: 678.7µs, proc_keys: 1, tot_proc: 70.1µs, tot_wait: 63.5µs, rpc_num: 1, rpc_time: 641.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 1.27µs, max_distsql_concurrency: 1} | data:TableFullScan_40 | 240 Bytes | N/A | | │ │ └─TableFullScan_40 | 1.00 | 1 | cop[tikv] | table:ref_16 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 34.4µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | | │ └─TableReader_37(Probe) | 1.00 | 1 | root | | time:497.7µs, loops:1, cop_task: {num: 1, max: 553.3µs, proc_keys: 1, tot_proc: 50.9µs, tot_wait: 64.9µs, rpc_num: 1, rpc_time: 532.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 3.85µs, max_distsql_concurrency: 1} | data:TableFullScan_36 | 240 Bytes | N/A | | │ └─TableFullScan_36 | 1.00 | 1 | cop[tikv] | table:ref_15 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 27, total_keys: 2, get_snapshot_time: 26µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | | └─TableReader_30(Probe) | 1.00 | 1 | root | | time:714.5µs, loops:4, cop_task: {num: 1, max: 701.9µs, proc_keys: 1, tot_proc: 93.9µs, tot_wait: 68.2µs, rpc_num: 1, rpc_time: 671.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 7.76µs, max_distsql_concurrency: 1} | data:TableFullScan_29 | 265 Bytes | N/A | | └─TableFullScan_29 | 1.00 | 1 | cop[tikv] | table:ref_0 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 41.9µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 4}}} | keep order:false, stats:pseudo | N/A | N/A | +----------------------------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+-----------+---------+ 19 rows in set (0.00 sec)The bulid side of
Apply_28is also empty, butApply_28is left outer join, so the probe side will be kept afterApply_28, and filter inSelection_26is true, this row is output afterSelection_26It's about the filter's null-rejective testing.
@windtalker Apply_27 is initialized as an outer join. Then we will test the filter
(((ref_0.c6 is null) and false) and null) xor (true or ('111' >= the subquery col)))to see whether the filter can filter out all null values from the join's inner side.What I said that it's false-positive is that TiDB thinks the filter
(((ref_0.c6 is null) and false) and null) xor (true or ('111' >= the subquery col)))can filter out all null values that comes from the join's inner side. So we convert the outer join to inner join. But actually, it cannot.
Got it.
