P1-[4.0 bug hunting]-[SQL Plan Management]-Using the DEFAULT() function results in an ambiguous column error

[mrigger]

Consider the following statements:

CREATE TABLE t0(c0 INT DEFAULT 1);
SELECT t0.c0, t0.c0 FROM t0 ORDER BY DEFAULT(t0.c0); -- Column 'c0' in field list is ambiguous

Unexpectedly, executing the SELECT results in an error:

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

mysql> SELECT t0.c0, t0.c0 FROM t0 ORDER BY DEFAULT(t0.c0);
ERROR 1052 (23000): Column 'c0' in field list is ambiguous

This is unexpected, since there is no ambiguity in the column reference (i.e., the columns are all fully qualified). It seems that this bug is caused by the DEFAULT() function; when removing it, the query works as expected.

When using MySQL 8.0.19 to execute this, the behavior is as expected:

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

mysql> SELECT t0.c0, t0.c0 FROM t0 ORDER BY DEFAULT(t0.c0);
Empty set (0.00 sec)

I found this based on the latest master (commit 7e71069ffa6052b497eaaaa2c3863bff4fe0ef3f) and can also reproduce it on the 4.0 RC:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

I'm not very sure how to classify an unexpected error, since such a case it not really accounted for in the classification rules. I think it might be P1 (The function and performance do not meet expectations.) or P2 (Incompatible with MySQL).

[shuke987]

/bug P1