P0-[4.0 bug hunting]-[SQL Plan Management]-Incorrect result when using SPACE() in a predicate

[mrigger]

Consider the following statements:

CREATE TABLE t0(c0 INT);
INSERT INTO t0 VALUES (100000);
SELECT * FROM t0 WHERE NOT SPACE(t0.c0); -- expected: {100000}, actual: {}

Unexpectedly, the SELECT does not fetch any rows:

Your MySQL connection id is 1880
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> INSERT INTO t0 VALUES (100000);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t0 WHERE NOT SPACE(t0.c0);
Empty set, 1 warning (0.00 sec)

Note that the query prints the following warning:

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1105 | Result of space() was larger than max_allowed_packet (65536) - truncated |
+---------+------+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

Nevertheless, I would expect the row to be fetched. This is the case when replacing the column by a constant:

CREATE TABLE t0(c0 INT);
INSERT INTO t0 VALUES (100000);
SELECT * FROM t0 WHERE NOT SPACE(100000); -- {100000}

I checked with MySQL 8.0.19, which produces the result I would expect:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
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.04 sec)

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

mysql> SELECT * FROM t0 WHERE NOT SPACE(t0.c0);
+--------+
| c0     |
+--------+
| 100000 |
+--------+
1 row in set (0.00 sec)

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

[shuke987]

already fixed in 4.0.0-rc

[mrigger]

@shuke987 Are you sure this is already fixed? I can reproduce this both based on the RC version and the latest master:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
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;
Query OK, 0 rows affected (0.00 sec)

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.00 sec)

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

mysql> SELECT * FROM t0 WHERE NOT SPACE(t0.c0); -- expected: {100000}, actual: {}
Empty set, 1 warning (0.00 sec)

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]

here is my test: mysql> create database test0; Query OK, 0 rows affected (0.09 sec)

mysql> use test0; Database changed mysql> CREATE TABLE t0(c0 INT); Query OK, 0 rows affected (0.09 sec)

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

mysql> SELECT * FROM t0 WHERE NOT SPACE(t0.c0); +--------+ | c0 | +--------+ | 100000 | +--------+ 1 row in set, 1 warning (0.00 sec)

mysql> select tidb_version(); +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tidb_version() | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Release Version: v4.0.0-rc Git Commit Hash: 79db9e30ab8f98ac07c8ae55c66dfecc24b43d56 Git Branch: HEAD UTC Build Time: 2020-04-08 03:31:58 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)

[mrigger]

That's strange, I double checked again and can still reproduce the bug on my system with the RC built from https://github.com/pingcap/tidb/releases/tag/v4.0.0-rc. I'm using Ubuntu 19.04. Do you have any suggestions on what to try reproducing the bug?

[shuke987]

I will try it again, and do some trouble shooting. thanks

[shuke987]

/bug P1