wrong result in "left join" statement

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Drop table if exists t1, t2;
CREATE TABLE t1 (a int PRIMARY KEY, b int);
CREATE TABLE t2 (a int PRIMARY KEY, b int);
INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
INSERT INTO t2 VALUES (1,2), (2,2);
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not((t1.a=30 and t2.b=1));
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));

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

mysql 8.0.21

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
+---+------+------+------+
| a | b    | a    | b    |
+---+------+------+------+
| 1 |    1 |    1 |    2 |
| 2 |    1 |    2 |    2 |
| 3 |    1 | NULL | NULL |
| 4 |    2 | NULL | NULL |
+---+------+------+------+
4 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
+---+------+------+------+
| a | b    | a    | b    |
+---+------+------+------+
| 1 |    1 |    1 |    2 |
| 2 |    1 |    2 |    2 |
+---+------+------+------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

master 937949f and release-4.0 2f62191

[ChenPeng2013]

#19620 is supposed to fix this issue.

[lzmhhh123]

Closed by #19620

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

[morgo]

This appears to be a regression or not fixed correctly:

tidb> SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not((t1.a=30 and t2.b=1));
+---+------+------+------+
| a | b    | a    | b    |
+---+------+------+------+
| 1 |    1 |    1 |    2 |
| 2 |    1 |    2 |    2 |
| 3 |    1 | NULL | NULL |
| 4 |    2 | NULL | NULL |
+---+------+------+------+
4 rows in set (0.00 sec)

tidb> SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
+---+------+------+------+
| a | b    | a    | b    |
+---+------+------+------+
| 1 |    1 |    1 |    2 |
| 2 |    1 |    2 |    2 |
+---+------+------+------+
2 rows in set (0.00 sec)

tidb> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.4.0-alpha-305-gcec4acbf5
Edition: Community
Git Commit Hash: cec4acbf5ea952a8f92c8258fce3142528f9fb1e
Git Branch: master
UTC Build Time: 2021-12-02 18:57:09
GoVersion: go1.16.9
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[lzmhhh123]

/assign @rebelice

[lzmhhh123]

I have tested it. The selection is wrongly pushed to join.

[Reminiscent]

mysql> explain SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not((t1.a=30 and t2.b=1));
+--------------------------------+----------+-----------+---------------+----------------------------------------------------------+
| id                             | estRows  | task      | access object | operator info                                            |
+--------------------------------+----------+-----------+---------------+----------------------------------------------------------+
| Selection_7                    | 8000.00  | root      |               | not(and(eq(test.t1.a, 30), eq(test.t2.b, 1)))            |
| └─MergeJoin_8                  | 10000.00 | root      |               | left outer join, left key:test.t1.a, right key:test.t2.a |
|   ├─TableReader_26(Build)      | 7781.14  | root      |               | data:Selection_25                                        |
|   │ └─Selection_25             | 7781.14  | cop[tikv] |               | or(ne(test.t2.a, 30), ne(test.t2.b, 1))                  |
|   │   └─TableFullScan_24       | 10000.00 | cop[tikv] | table:t2      | keep order:true, stats:pseudo                            |
|   └─TableReader_23(Probe)      | 10000.00 | root      |               | data:TableFullScan_22                                    |
|     └─TableFullScan_22         | 10000.00 | cop[tikv] | table:t1      | keep order:true, stats:pseudo                            |
+--------------------------------+----------+-----------+---------------+----------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> explain SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
+-------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
| id                            | estRows | task      | access object | operator info                                                                                                          |
+-------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
| Projection_9                  | 2.00    | root      |               | test.t1.a, test.t1.b, test.t2.a, test.t2.b                                                                             |
| └─MergeJoin_10                | 2.00    | root      |               | inner join, left key:test.t2.a, right key:test.t1.a, other cond:not(plus(0, and(eq(test.t1.a, 30), eq(test.t2.b, 1)))) |
|   ├─TableReader_37(Build)     | 4.00    | root      |               | data:TableFullScan_36                                                                                                  |
|   │ └─TableFullScan_36        | 4.00    | cop[tikv] | table:t1      | keep order:true, stats:pseudo                                                                                          |
|   └─TableReader_35(Probe)     | 1.60    | root      |               | data:Selection_34                                                                                                      |
|     └─Selection_34            | 1.60    | cop[tikv] |               | not(istrue_with_null(plus(0, and(eq(test.t2.a, 30), eq(test.t2.b, 1)))))                                               |
|       └─TableFullScan_33      | 2.00    | cop[tikv] | table:t2      | keep order:true, stats:pseudo                                                                                          |
+-------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.