MySQL Bugs: #66845: Wrong result (extra row) on a FROM subquery with a variable and ORDER BY

Bug #66845	Wrong result (extra row) on a FROM subquery with a variable and ORDER BY
Submitted:	17 Sep 2012 12:53	Modified:	18 Jan 2013 1:46
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Initially reported by Kazuhiko at https://bugs.launchpad.net/maria/+bug/1050806, below is a simplified version.

The following query

SELECT * FROM (
  SELECT node_uid, date, mirror_date, @result := 0 AS result
  FROM stock
  WHERE date < '2012-12-12 12:12:12'
    AND node_uid in (2085, 2084)
  ORDER BY mirror_date ASC
) AS calculated_result;

on the test data produces 3 rows on 5.5.27 and 2 rows on 5.5.24 (I haven't found 5.5.25 in archives). The correct result is 2 rows, which is obvious because the third row has node_uid which is neither 2084 nor 2085.

Expected result:
node_uid        date    mirror_date     result
2085    2012-01-01 00:00:00     2013-01-01 00:00:00     0
2084    2012-02-01 00:00:00     2013-01-01 00:00:00     0

Actual result:
node_uid        date    mirror_date     result
2085    2012-01-01 00:00:00     2013-01-01 00:00:00     0
2084    2012-02-01 00:00:00     2013-01-01 00:00:00     0
2088    2012-03-01 00:00:00     2013-01-01 00:00:00     0

How to repeat:
DROP TABLE IF EXISTS `stock`;

CREATE TABLE `stock` (
  `node_uid` bigint(20) unsigned DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `mirror_date` datetime DEFAULT NULL,
  KEY `date` (`date`)
) ENGINE=MyISAM;

INSERT INTO `stock` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
INSERT INTO `stock` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
INSERT INTO `stock` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');

SELECT * FROM (
  SELECT node_uid, date, mirror_date, @result := 0 AS result
  FROM stock
  WHERE date < '2012-12-12 12:12:12'
    AND node_uid in (2085, 2084)
  ORDER BY mirror_date ASC
) AS calculated_result;

Thank you for the bug report. Verified as described.

d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.29 Source distribution

Copyright (c) 2000, 2012, 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 5.5 >use test
Database changed
mysql 5.5 >DROP TABLE IF EXISTS `stock`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 >
mysql 5.5 >CREATE TABLE `stock` (
    ->   `node_uid` bigint(20) unsigned DEFAULT NULL,
    ->   `date` datetime DEFAULT NULL,
    ->   `mirror_date` datetime DEFAULT NULL,
    ->   KEY `date` (`date`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql 5.5 >
mysql 5.5 >INSERT INTO `stock` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
Query OK, 1 row affected (0.01 sec)

mysql 5.5 >INSERT INTO `stock` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >INSERT INTO `stock` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >
mysql 5.5 >SELECT * FROM (
    ->   SELECT node_uid, date, mirror_date, @result := 0 AS result
    ->   FROM stock
    ->   WHERE date < '2012-12-12 12:12:12'
    ->     AND node_uid in (2085, 2084)
    ->   ORDER BY mirror_date ASC
    -> ) AS calculated_result;
+----------+---------------------+---------------------+--------+
| node_uid | date                | mirror_date         | result |
+----------+---------------------+---------------------+--------+
|     2085 | 2012-01-01 00:00:00 | 2013-01-01 00:00:00 |      0 |
|     2084 | 2012-02-01 00:00:00 | 2013-01-01 00:00:00 |      0 |
|     2088 | 2012-03-01 00:00:00 | 2013-01-01 00:00:00 |      0 |
+----------+---------------------+---------------------+--------+
3 rows in set (0.00 sec)

----------------------------------------------------------------

d:\dbs>5.6\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.0-m10 Source distribution

Copyright (c) 2000, 2012, 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 `stock` (
    ->   `node_uid` bigint(20) unsigned DEFAULT NULL,
    ->   `date` datetime DEFAULT NULL,
    ->   `mirror_date` datetime DEFAULT NULL,
    ->   KEY `date` (`date`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO `stock` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `stock` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `stock` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM (
    ->   SELECT node_uid, date, mirror_date, @result := 0 AS result
    ->   FROM stock
    ->   WHERE date < '2012-12-12 12:12:12'
    ->     AND node_uid in (2085, 2084)
    ->   ORDER BY mirror_date ASC
    -> ) AS calculated_result;
+----------+---------------------+---------------------+--------+
| node_uid | date                | mirror_date         | result |
+----------+---------------------+---------------------+--------+
|     2085 | 2012-01-01 00:00:00 | 2013-01-01 00:00:00 |      0 |
|     2084 | 2012-02-01 00:00:00 | 2013-01-01 00:00:00 |      0 |
+----------+---------------------+---------------------+--------+
2 rows in set (0.00 sec)

Noted in 5.5.30, 5.6.11, 5.7.1 changelogs.

For subqueries executing using a filesort, the optimizer could
produce an incorrect result containing an extra row.