pingcap / tidb

TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
https://pingcap.com
Apache License 2.0
36.8k stars 5.8k forks source link

question: why the condition `t1.a>1` in this sql is left condition? #26090

Open LittleFall opened 3 years ago

LittleFall commented 3 years ago

General Question

create table t1(a int not null);
create table t2(a int not null);
explain select * from t1 where t1.a not in (
    select * from t2 where t1.a>1
);
+-----------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------+
| id                          | estRows  | task      | access object | operator info                                                                  |
+-----------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------+
| HashJoin_9                  | 8000.00  | root      |               | anti semi join, equal:[eq(test.t1.a, test.t2.a)], left cond:[gt(test.t1.a, 1)] |
| ├─TableReader_13(Build)     | 10000.00 | root      |               | data:TableFullScan_12                                                          |
| │ └─TableFullScan_12        | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                 |
| └─TableReader_11(Probe)     | 10000.00 | root      |               | data:TableFullScan_10                                                          |
|   └─TableFullScan_10        | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                 |
+-----------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------+

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.1.0
Edition: Community
Git Commit Hash: 8acd5c88471cb7b4d4c4a8ed73b4d53d6833f13e
Git Branch: heads/refs/tags/v5.1.0
UTC Build Time: 2021-06-24 07:08:50
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
LittleFall commented 3 years ago

question: in this anti semi join, which is the left table?

LittleFall commented 3 years ago

question: in this anti semi join, which is the left table?

the left table should be select * from t2

LittleFall commented 3 years ago

in this situation, are all the left conditions useless? If they are, is there any plan to eliminate them?