QUOTE(null) should return 'NULL' string instead of NULL value

[DQinYuan]

Bug Report

In doc:

quote(null) should return 'NULL' string, but tidb return NULL value instead.

1. What did you do?

select quote(null) is NULL;

2. What did you expect to see?

in mysql:

mysql> select quote(null) is NULL;
+---------------------+
| quote(null) is NULL |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

3. What did you see instead?

In TiDB:

mysql> select quote(null) is NULL;
+---------------------+
| quote(null) is NULL |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

mysql> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0
Git Commit Hash: 60965b006877ca7234adaced7890d7b029ed1306
Git Branch: HEAD
UTC Build Time: 2019-06-28 12:14:07
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Mysql version is 5.7

[gaoxingliang]

I will check this issue

[gaoxingliang]

I looked into the definition here. but it's weird for mysql impl (or I can say it's wrong).
See below cases:

mysql> SELECT QUOTE(NULL);
+-------------+
| QUOTE(NULL) |
+-------------+
| NULL        |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT NULL is NULL;
+--------------+
| NULL is NULL |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT "" is NULL;
+------------+
| "" is NULL |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT QUOTE(NULL) IS null;
+---------------------+
| QUOTE(NULL) IS null |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.13    |
+-----------+
1 row in set (0.00 sec)

as you can see, the NULL IS NULL = true = 1 and according to the doc, the quote(NULL) should be NULL. so the value should be 1 instead of 0.
I think this is wrong for mysql's implementation.

I will compare with latest mysql and check in the community

[gaoxingliang]

Aha, I got more information about this issue.
the cause is the doc is wrong. the quote(null) will return a string "NULL" instead of NULL is mysql.
see my below test:

mysql> select length(quote(null));
+---------------------+
| length(quote(null)) |
+---------------------+
|                   4 |
+---------------------+
1 row in set (0.00 sec)

mysql> select quote(null) REGEXP 'NULL'
    -> ;
+---------------------------+
| quote(null) REGEXP 'NULL' |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select quote(null) REGEXP 'null'
    -> ;
+---------------------------+
| quote(null) REGEXP 'null' |
+---------------------------+
|                         1 |
+---------------------------+

it's the upper case "NULL" instead of null.
See the case sensitive version:

mysql> select quote(null) REGEXP binary 'null'
    -> ;
+----------------------------------+
| quote(null) REGEXP binary 'null' |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select quote(null) REGEXP binary 'NULL';
+----------------------------------+
| quote(null) REGEXP binary 'NULL' |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

[DQinYuan]

@gaoxingliang I think doc say very clearly quote(null) will return string 'NULL', and mysql impl is right

[gaoxingliang]

Yes. u r right. I confirmed. I will try to fix this issue.