P0-[4.0 bug hunting]-[SQL Plan Management]-RIGHT JOIN with ELT() predicate returns an incorrect result

[mrigger]

Consider the following statements:

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
INSERT INTO t0(c0) VALUES (NULL);
SELECT * FROM t1 RIGHT JOIN t0 ON true WHERE (ELT(1^t0.c0, 0, NULL, 0)) IS NULL; -- expected: {NULL, NULL}, actual: {}

I would expect the query to fetch a row; however, none is returned:

Server version: 5.7.25-TiDB-v4.0.0-beta.2-231-gc66320c46 TiDB Server (Apache License 2.0), MySQL 5.7 compatible

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1(c0 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t0(c0) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 RIGHT JOIN t0 ON true WHERE ((ELT(1^t0.c0, 0, NULL, 0)) IS NULL); -- expected: {NULL, NULL}, actual: {}
Empty set (0.00 sec) 

When using MySQL 8.0.19, the result is as expected:

Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t1(c0 INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t0(c0) VALUES (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1 RIGHT JOIN t0 ON true WHERE ((ELT(1^t0.c0, 0, NULL, 0)) IS NULL);
+------+------+
| c0   | c0   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

TiDB version:

| Release Version: v4.0.0-beta.2-231-gc66320c46
Git Commit Hash: c66320c46456c0d5b23b3b0403be6b9f8227d6d8
Git Branch: master
UTC Build Time: 2020-04-14 11:12:29
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

I propose P0 as a bug level, since it matches the following description: wrong results returned by the query, and inconsistent results returned by the SQL output.

[winkyao]

Could you please try 4.0.0 rc version? https://github.com/pingcap/tidb/releases/tag/v4.0.0-rc

[mrigger]

I can also reproduce this on the RC:

Server version: 5.7.25-TiDB- TiDB Server (Apache License 2.0), MySQL 5.7 compatible

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP DATABASE db0;
ERROR 1008 (HY000): Can't drop database 'db0'; database doesn't exist
mysql> CREATE DATABASE db0;
Query OK, 0 rows affected (0.01 sec)

mysql> USE db0;
Database changed
mysql> 
mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1(c0 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t0(c0) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 RIGHT JOIN t0 ON true WHERE (ELT(1^t0.c0, 0, NULL, 0)) IS NULL; -- expected: {NULL, NULL}, actual: {}
Empty set (0.01 sec)

Version:

mysql> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: 
Git Commit Hash: 
Git Branch: 
UTC Build Time: 2020-04-15 06:06:10
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) 

[shuke987]

Thank you for submitting this bug. It returns error but it is a corner case and appears less frequently. Can you accept rating it as a P1 bug？

[mrigger]

Yes, P1 sounds fair. I would suggest to update the classification at https://github.com/pingcap/community/blob/master/bug-hunting-programs/bug-hunting-program.md accordingly, so that the classification stays objective. What about the following (additions are in bold):

Maybe for P0: [...] wrong results returned by the query in a common case [...] And for P1: [...] 5. Wrong results returned by a query in a corner case [...]

[shuke987]

Yes, P1 sounds fair. I would suggest to update the classification at https://github.com/pingcap/community/blob/master/bug-hunting-programs/bug-hunting-program.md accordingly, so that the classification stays objective. What about the following (additions are in bold):

Maybe for P0: [...] wrong results returned by the query in a common case [...] And for P1: [...] 5. Wrong results returned by a query in a corner case [...]

Yes, we will change it.

[shuke987]

/bug P1

[mrigger]

Thanks for awarding (or nominating me to get) the points! The bug-hunting program site mentions that there will be 100 bonus points under the following condition:

When the issue is verified to be reproducible with clear steps a, there will be an additional 100 points granted.

Would you consider the steps to be clear for this and my other issues?

[mrigger]

I found that this bug report might be a duplicate of one of my earlier bug reports (https://github.com/pingcap/tidb/issues/16023), which was fixed earlier today by @fzhedu. In each case, this seems to now work on master. Feel free to deduct the points if this is indeed a duplicate.