`a > 1 or a in (subquery)` will lead the CARTESIAN join

[hawkingrei]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t1(a int default null);
create table t(a int not null);
explain select * from t1 where t1.a>1 or t1.a in (select a from t);

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

we can rewrite it as

explain select * from t1 where t1.a>1 or exists (select a from t where t1.a = t.a);

+-----------------------------+----------+-----------+---------------+---------------------------------------------------------------------------------+
| id                          | estRows  | task      | access object | operator info                                                                   |
+-----------------------------+----------+-----------+---------------+---------------------------------------------------------------------------------+
| Projection_8                | 8000.00  | root      |               | test.t1.a                                                                       |
| └─Selection_11              | 8000.00  | root      |               | or(gt(test.t1.a, 1), Column#5)                                                  |
|   └─HashJoin_20             | 10000.00 | root      |               | left outer semi join, left side:TableReader_22, equal:[eq(test.t1.a, test.t.a)] |
|     ├─TableReader_24(Build) | 10000.00 | root      |               | data:TableFullScan_23                                                           |
|     │ └─TableFullScan_23    | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                  |
|     └─TableReader_22(Probe) | 10000.00 | root      |               | data:TableFullScan_21                                                           |
|       └─TableFullScan_21    | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                  |
+-----------------------------+----------+-----------+---------------+---------------------------------------------------------------------------------+

3. What did you see instead (Required)

+-----------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------+
| id                          | estRows  | task      | access object | operator info                                                                                |
+-----------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------+
| Projection_7                | 8000.00  | root      |               | test.t1.a                                                                                    |
| └─Selection_10              | 8000.00  | root      |               | or(gt(test.t1.a, 1), Column#5)                                                               |
|   └─HashJoin_19             | 10000.00 | root      |               | CARTESIAN left outer semi join, left side:TableReader_21, other cond:eq(test.t1.a, test.t.a) |
|     ├─TableReader_23(Build) | 10000.00 | root      |               | data:TableFullScan_22                                                                        |
|     │ └─TableFullScan_22    | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                               |
|     └─TableReader_21(Probe) | 10000.00 | root      |               | data:TableFullScan_20                                                                        |
|       └─TableFullScan_20    | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                               |
+-----------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------+

4. What is your TiDB version? (Required)

[AilinKid]

explain select * from t1 where t1.a>1 or exists (select a from t where t1.a = t.a);

[hawkingrei]

explain select * from t1 where t1.a>1 or exists (select a from t where t1.a = t.a);

Great, I update the issue.