windtalker
commented
1 year ago 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.
winoros
JZuming
aytrack
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
Setup the environment:
Testcase 1
Testcase 1 outputs empty (0 rows).
Then, I replace
(ref_0.c6 is null) and falsewithfalse. The results should not be changed becauseanything and falseisfalse. The test case becomes:Testcase 2
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
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 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+