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)