Bug #100305 ORDER BY on subquery column does not return literal value of subquery
Submitted: 23 Jul 2020 12:37 Modified: 23 Jul 2020 17:24
Reporter: Hironobu Komiya Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7/8.0 OS:Debian (mysql docker official image(5.7 latest))
Assigned to: CPU Architecture:Any

[23 Jul 2020 12:37] Hironobu Komiya
Description:
Hello.
Sorry my poor English.

I ran the following SELECT query:

SELECT t1.id AS `t1.id`, t2.id AS `t2.id`, t2.str AS `t2.str`
FROM t1
LEFT JOIN (
  SELECT id, 'foo' AS str
  FROM t2
) AS t2
ON t1.id = t2.id
ORDER BY t2.id;

Then I got the following results:

+-------+-------+--------+
| t1.id | t2.id | t2.str |
+-------+-------+--------+
|     2 |  NULL | NULL   |
|     1 |     1 | NULL   |
+-------+-------+--------+

I expected the value of t2.str in the row with t1.id = 1 to be 'foo'.
However, it actually returned NULL.

It works if I change the ORDER BY column to t1.id.

SELECT t1.id AS `t1.id`, t2.id AS `t2.id`, t2.str AS `t2.str`
FROM t1
LEFT JOIN (
  SELECT id, 'foo' AS str
  FROM t2
) AS t2
ON t1.id = t2.id
ORDER BY t1.id;

+-------+-------+--------+
| t1.id | t2.id | t2.str |
+-------+-------+--------+
|     1 |     1 | foo    |
|     2 |  NULL | NULL   |
+-------+-------+--------+

Is this a specification, or a bug?

Thanks.

Hironobu Komiya

How to repeat:
mysql> STATUS;
--------------
mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

Connection id:          2
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.30 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 22 min 32 sec

Threads: 1  Questions: 26  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 100  Queries per second avg: 0.019
--------------

mysql> CREATE TABLE IF NOT EXISTS `t1` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.29 sec)

mysql> CREATE TABLE IF NOT EXISTS `t2` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO `t1` (`id`)
    -> VALUES ('1'), ('2')
    -> ON DUPLICATE KEY UPDATE `id` = `id`;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `t2` (`id`)
    -> VALUES ('1')
    -> ON DUPLICATE KEY UPDATE `id` = `id`;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT t1.id AS `t1.id`, t2.id AS `t2.id`, t2.str AS `t2.str`
    -> FROM t1
    -> LEFT JOIN (
    ->   SELECT id, 'foo' AS str
    ->   FROM t2
    -> ) AS t2
    -> ON t1.id = t2.id
    -> ORDER BY t2.id;
+-------+-------+--------+
| t1.id | t2.id | t2.str |
+-------+-------+--------+
|     2 |  NULL | NULL   |
|     1 |     1 | NULL   |
+-------+-------+--------+
2 rows in set (0.00 sec)
[23 Jul 2020 17:24] MySQL Verification Team
Thank you for the bug report.