MySQL Bugs: #81235: Incorrect sorting temporary results after Where

Bug #81235	Incorrect sorting temporary results after Where
Submitted:	29 Apr 2016 11:43	Modified:	5 May 2016 14:04
Reporter:	Stanislav Blank	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	order by, temporary, where

Description:
Description:
This test below fails:

CREATE TABLE IF NOT EXISTS tmp_tst (
  id int(11) NOT NULL AUTO_INCREMENT,
  products_id int(11) NOT NULL,
  qty int(11) NOT NULL DEFAULT '0',
  price decimal(15,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (id),
  KEY products_id (products_id)
);

INSERT INTO tmp_tst (products_id, qty, price)  VALUES
(1, 0, 2.7900),
(2, 0, 10.2260),
(2, 0, 11.9287),
(2, 0, 11.9287),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.4738),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7600),
(2, 0, 12.4738),
(2, 0, 12.2900),
(1, 0, 2.7600),
(2, 0, 12.2900),
(1, 0, 0.0000),
(1, 0, 2.7600),
(1, 0, 0.0000),
(1, 0, 2.7600),
(2, 0, 0.0000),
(2, 0, 12.2900),
(2, 0, 12.2900),
(2, 0, 12.2900),
(2, 0, 12.2900),
(2, 0, 12.2900),
(2, 0, 12.2900),
(1, 0, 2.7600),
(1, 0, 2.7600),
(1, 0, 2.7600),
(1, 0, 0.0000),
(1, 0, 2.7600),
(2, 0, 12.2900),
(2, 0, 0.0000),
(2, 0, 12.2900),
(1, 58, 2.7900),
(2, 30, 12.2900);

mysql> 
SELECT *
FROM (
	SELECT products_id, id, qty, price
	FROM tmp_tst
	WHERE products_id IN ( 1, 2 )
	ORDER BY products_id ASC, id desc
)tmp
WHERE products_id IN ( 2)

I want (v. 5.5).
+--------------------------------+
| products_id | id | qty | price |
+--------------------------------+
|            2|  80|   30|12.2900|
|            2|  78|    0|12.2900|
|            2|  77|    0|0.0000|
|            2|  76|    0|12.2900|
|            2|  70|    0|12.2900|
|            2|  69|    0|12.2900|
|            2|  68|    0|12.2900|
|            2|  67|    0|12.2900|
|            2|  66|    0|12.2900|
|            2|  65|    0|12.2900|
|            2|  64|    0| 0.0000|
|            2|  59|    0|12.2900|
|            2|  57|    0|12.2900|
|            2|  56|    0|12.4738|
|            2|  53|    0|12.7800|
|            2|  51|    0|12.4738|
|            2|  49|    0|12.7800|
|            2|  41|    0|12.7800|
|            2|  39|    0|12.7800|
|            2|  38|    0|12.7800|
|            2|  30|    0|12.7800|
|            2|  28|    0|12.7800|
|            2|  25|    0|12.7800|
|            2|  23|    0|12.7800|
|            2|  21|    0|12.7800|
|            2|  19|    0|12.7800|
|            2|  17|    0|12.7800|
|            2|  14|    0|12.7800|
|            2|  12|    0|12.7800|
|            2|   8|    0|12.7800|
|            2|   5|    0|12.7800|
|            2|   4|    0|11.9287|
|            2|   3|    0|11.9287|
|            2|   2|    0|10.2260|
+--------------------------------+

I have (v. 5.6)
+--------------------------------+
| products_id | id | qty | price |
+--------------------------------+
|            2|  39|    0|12.7800|
|            2|   8|    0|12.7800|
|            2|  66|    0|12.2900|
|            2|  38|    0|12.7800|
|            2|   5|    0|12.7800|
|            2|  65|    0|12.2900|
|            2|  30|    0|12.7800|
|            2|   4|    0|11.9287|
|            2|  64|    0| 0.0000|
|            2|  28|    0|12.7800|
|            2|  80|   30|12.2900|
|            2|   3|    0|11.9287|
|            2|  59|    0|12.2900|
|            2|  25|    0|12.7800|
|            2|  78|    0|12.2900|
|            2|   2|    0|10.2260|
|            2|  57|    0|12.2900|
|            2|  23|    0|12.7800|
|            2|  77|    0| 0.0000|
|            2|  56|    0|12.4738|
|            2|  21|    0|12.7800|
|            2|  76|    0|12.2900|
|            2|  53|    0|12.7800|
|            2|  19|    0|12.7800|
|            2|  70|    0|12.2900|
|            2|  51|    0|12.4738|
|            2|  17|    0|12.7800|
|            2|  69|    0|12.2900|
|            2|  49|    0|12.7800|
|            2|  14|    0|12.7800|
|            2|  68|    0|12.2900|
|            2|  41|    0|12.7800|
|            2|  12|    0|12.7800|
|            2|  67|    0|12.2900|
+--------------------------------+

Anyone know why?

How to repeat:
CREATE TABLE IF NOT EXISTS tmp_tst (
  id int(11) NOT NULL AUTO_INCREMENT,
  products_id int(11) NOT NULL,
  qty int(11) NOT NULL DEFAULT '0',
  price decimal(15,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (id),
  KEY products_id (products_id)
);

INSERT INTO tmp_tst (products_id, qty, price)  VALUES
(1, 0, 2.7900),
(2, 0, 10.2260),
(2, 0, 11.9287),
(2, 0, 11.9287),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(2, 0, 12.4738),
(1, 0, 2.7900),
(2, 0, 12.7800),
(1, 0, 2.7900),
(1, 0, 2.7600),
(2, 0, 12.4738),
(2, 0, 12.2900),
(1, 0, 2.7600),
(2, 0, 12.2900),
(1, 0, 0.0000),
(1, 0, 2.7600),
(1, 0, 0.0000),
(1, 0, 2.7600),
(2, 0, 0.0000),
(2, 0, 12.2900),
(2, 0, 12.2900),
(2, 0, 12.2900),
(2, 0, 12.2900),
(2, 0, 12.2900),
(2, 0, 12.2900),
(1, 0, 2.7600),
(1, 0, 2.7600),
(1, 0, 2.7600),
(1, 0, 0.0000),
(1, 0, 2.7600),
(2, 0, 12.2900),
(2, 0, 0.0000),
(2, 0, 12.2900),
(1, 58, 2.7900),
(2, 30, 12.2900);

SELECT *
FROM (
	SELECT products_id, id, qty, price
	FROM tmp_tst
	WHERE products_id IN ( 1, 2 )
	ORDER BY products_id ASC, id desc
)tmp
WHERE products_id IN (2);

Thank you for the bug report. Only 5.6 version affected.

mysql 5.6 > SELECT *
    -> FROM (
    ->  SELECT products_id, id, qty, price
    ->  FROM tmp_tst
    ->  WHERE products_id IN ( 1, 2 )
    ->  ORDER BY products_id ASC, id desc
    -> )tmp
    -> WHERE products_id IN ( 2);
+-------------+----+-----+---------+
| products_id | id | qty | price   |
+-------------+----+-----+---------+
|           2 | 30 |   0 | 12.7800 |
|           2 |  4 |   0 | 11.9287 |
|           2 | 64 |   0 |  0.0000 |
|           2 | 28 |   0 | 12.7800 |
|           2 | 80 |  30 | 12.2900 |
|           2 |  3 |   0 | 11.9287 |
|           2 | 59 |   0 | 12.2900 |
|           2 | 25 |   0 | 12.7800 |
|           2 | 78 |   0 | 12.2900 |
|           2 |  2 |   0 | 10.2260 |
|           2 | 57 |   0 | 12.2900 |
|           2 | 23 |   0 | 12.7800 |
|           2 | 77 |   0 |  0.0000 |
|           2 | 56 |   0 | 12.4738 |
|           2 | 21 |   0 | 12.7800 |
|           2 | 76 |   0 | 12.2900 |
|           2 | 53 |   0 | 12.7800 |
|           2 | 19 |   0 | 12.7800 |
|           2 | 70 |   0 | 12.2900 |
|           2 | 51 |   0 | 12.4738 |
|           2 | 17 |   0 | 12.7800 |
|           2 | 69 |   0 | 12.2900 |
|           2 | 49 |   0 | 12.7800 |
|           2 | 14 |   0 | 12.7800 |
|           2 | 68 |   0 | 12.2900 |
|           2 | 41 |   0 | 12.7800 |
|           2 | 12 |   0 | 12.7800 |
|           2 | 67 |   0 | 12.2900 |
|           2 | 39 |   0 | 12.7800 |
|           2 |  8 |   0 | 12.7800 |
|           2 | 66 |   0 | 12.2900 |
|           2 | 38 |   0 | 12.7800 |
|           2 |  5 |   0 | 12.7800 |
|           2 | 65 |   0 | 12.2900 |
+-------------+----+-----+---------+
34 rows in set (0.00 sec)

mysql 5.6 > show variables like "%version%";
+-------------------------+---------------------------------------+
| Variable_name           | Value                                 |
+-------------------------+---------------------------------------+
| innodb_version          | 5.6.31                                |
| protocol_version        | 10                                    |
| slave_type_conversions  |                                       |
| version                 | 5.6.31                                |
| version_comment         | Source distribution PULL: 2016-APR-07 |
| version_compile_machine | x86_64                                |
| version_compile_os      | Win64                                 |
+-------------------------+---------------------------------------+
7 rows in set (0.00 sec)
-----------------------------------------------------------------------
mysql 5.5 > SELECT *
    -> FROM (
    ->  SELECT products_id, id, qty, price
    ->  FROM tmp_tst
    ->  WHERE products_id IN ( 1, 2 )
    ->  ORDER BY products_id ASC, id desc
    -> )tmp
    -> WHERE products_id IN ( 2);
+-------------+----+-----+---------+
| products_id | id | qty | price   |
+-------------+----+-----+---------+
|           2 | 80 |  30 | 12.2900 |
|           2 | 78 |   0 | 12.2900 |
|           2 | 77 |   0 |  0.0000 |
|           2 | 76 |   0 | 12.2900 |
|           2 | 70 |   0 | 12.2900 |
|           2 | 69 |   0 | 12.2900 |
|           2 | 68 |   0 | 12.2900 |
|           2 | 67 |   0 | 12.2900 |
|           2 | 66 |   0 | 12.2900 |
|           2 | 65 |   0 | 12.2900 |
|           2 | 64 |   0 |  0.0000 |
|           2 | 59 |   0 | 12.2900 |
|           2 | 57 |   0 | 12.2900 |
|           2 | 56 |   0 | 12.4738 |
|           2 | 53 |   0 | 12.7800 |
|           2 | 51 |   0 | 12.4738 |
|           2 | 49 |   0 | 12.7800 |
|           2 | 41 |   0 | 12.7800 |
|           2 | 39 |   0 | 12.7800 |
|           2 | 38 |   0 | 12.7800 |
|           2 | 30 |   0 | 12.7800 |
|           2 | 28 |   0 | 12.7800 |
|           2 | 25 |   0 | 12.7800 |
|           2 | 23 |   0 | 12.7800 |
|           2 | 21 |   0 | 12.7800 |
|           2 | 19 |   0 | 12.7800 |
|           2 | 17 |   0 | 12.7800 |
|           2 | 14 |   0 | 12.7800 |
|           2 | 12 |   0 | 12.7800 |
|           2 |  8 |   0 | 12.7800 |
|           2 |  5 |   0 | 12.7800 |
|           2 |  4 |   0 | 11.9287 |
|           2 |  3 |   0 | 11.9287 |
|           2 |  2 |   0 | 10.2260 |
+-------------+----+-----+---------+
34 rows in set (0.02 sec)

mysql 5.5 > show variables like "%version%";
+-------------------------+---------------------------------------+
| Variable_name           | Value                                 |
+-------------------------+---------------------------------------+
| innodb_version          | 5.5.50                                |
| protocol_version        | 10                                    |
| slave_type_conversions  |                                       |
| version                 | 5.5.50                                |
| version_comment         | Source distribution PULL: 2016-APR-07 |
| version_compile_machine | AMD64                                 |
| version_compile_os      | Win64                                 |
+-------------------------+---------------------------------------+
7 rows in set (0.00 sec)
-----------------------------------------------------------------------
mysql 5.7 > SELECT *
    -> FROM (
    ->  SELECT products_id, id, qty, price
    ->  FROM tmp_tst
    ->  WHERE products_id IN ( 1, 2 )
    ->  ORDER BY products_id ASC, id desc
    -> )tmp
    -> WHERE products_id IN ( 2);
+-------------+----+-----+---------+
| products_id | id | qty | price   |
+-------------+----+-----+---------+
|           2 | 80 |  30 | 12.2900 |
|           2 | 78 |   0 | 12.2900 |
|           2 | 77 |   0 |  0.0000 |
|           2 | 76 |   0 | 12.2900 |
|           2 | 70 |   0 | 12.2900 |
|           2 | 69 |   0 | 12.2900 |
|           2 | 68 |   0 | 12.2900 |
|           2 | 67 |   0 | 12.2900 |
|           2 | 66 |   0 | 12.2900 |
|           2 | 65 |   0 | 12.2900 |
|           2 | 64 |   0 |  0.0000 |
|           2 | 59 |   0 | 12.2900 |
|           2 | 57 |   0 | 12.2900 |
|           2 | 56 |   0 | 12.4738 |
|           2 | 53 |   0 | 12.7800 |
|           2 | 51 |   0 | 12.4738 |
|           2 | 49 |   0 | 12.7800 |
|           2 | 41 |   0 | 12.7800 |
|           2 | 39 |   0 | 12.7800 |
|           2 | 38 |   0 | 12.7800 |
|           2 | 30 |   0 | 12.7800 |
|           2 | 28 |   0 | 12.7800 |
|           2 | 25 |   0 | 12.7800 |
|           2 | 23 |   0 | 12.7800 |
|           2 | 21 |   0 | 12.7800 |
|           2 | 19 |   0 | 12.7800 |
|           2 | 17 |   0 | 12.7800 |
|           2 | 14 |   0 | 12.7800 |
|           2 | 12 |   0 | 12.7800 |
|           2 |  8 |   0 | 12.7800 |
|           2 |  5 |   0 | 12.7800 |
|           2 |  4 |   0 | 11.9287 |
|           2 |  3 |   0 | 11.9287 |
|           2 |  2 |   0 | 10.2260 |
+-------------+----+-----+---------+
34 rows in set (0.00 sec)

mysql 5.7 > show variables like "%version%";
+-------------------------+---------------------------------------+
| Variable_name           | Value                                 |
+-------------------------+---------------------------------------+
| innodb_version          | 5.7.13                                |
| protocol_version        | 10                                    |
| slave_type_conversions  |                                       |
| tls_version             | 1                                     |
| version                 | 5.7.13                                |
| version_comment         | Source distribution PULL: 2016-APR-07 |
| version_compile_machine | x86_64                                |
| version_compile_os      | Win64                                 |
+-------------------------+---------------------------------------+
8 rows in set (0.02 sec)

mysql 5.7 >

Duplicate of Bug#72734.

Noted in 5.7.6 changelog.

Sort order of output from a view could be incorrect when the view
definition includes an ORDER BY clause but the view is selected from
using a WHERE clause.

 
The fix does not apply to 5.6. For 5.6, we have modified http://dev.mysql.com/doc/refman/5.6/en/create-view.html as follows to point out a workaround:

From:

ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.

To:

ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY or filtering or grouping. When ORDER BY is combined with LIMIT or OFFSET in a view definition, the ordering is always enforced before the query result is used by the outer query, but it does not guarantee that the same ordering is used in the end result. As a workaround, add an ORDER BY clause to the outer query.