MySQL Bugs: #72734: Wrong sort order when using view with order by

Bug #72734	Wrong sort order when using view with order by
Submitted:	24 May 2014 4:39	Modified:	5 May 2016 13:50
Reporter:	Yoshiaki Yamasaki	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.17, 5.7.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
The sort order of a view is incorrect under the condition below:

 -  The view is defined with "order by" clause
 -  The view is selected with a "where" clause

Here is a sample script which reproduces the behavior. See the attached file "mysql_bug.sql".
When the script is executed, the records should be sorted by column "sort_order",
but when a where clause is specified, the actual order is different as shown below.

 mysql> select * from v1 where c1 = "a"
     -> ;
 +-----------+------------+------+---+
 | table1_id | sort_order | c1   | x |
 +-----------+------------+------+---+
 |         7 |          6 | a    | 0 |         *** WRONG ***
 |         4 |          7 | a    | 0 |         *** WRONG ***
 |         1 |          2 | a    | 0 |         *** WRONG ***
 +-----------+------------+------+---+

This behavior happened at:
 - MySQL 5.6.17 / Windows7(64bit)

------------------------------------------------------------
CHECK LOG
------------------------------------------------------------
[MySQL 5.6.17 / Windows7(64bit)]  => This behavior happened

mysql> show global variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.17                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.17-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.01 sec)

mysql>
mysql> drop view if exists v1;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> create table t1 (
    ->   table1_id bigint not null auto_increment,
    ->   sort_order int(11),
    ->   c1 varchar(255),
    ->   primary key (`table1_id`)
    -> ) engine=InnoDB default charset=utf8
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> create view v1 as
    -> select t1.*, (select 0) x
    -> from t1
    -> order by sort_order
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into t1 (sort_order, c1) values
    -> (2, "a"),(9, "b"),(4, "c"),
    -> (7, "a"),(5, "b"),(3, "c"),
    -> (6, "a"),(1, "b"),(8, "c")
    -> ;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from v1
    -> ;
+-----------+------------+------+---+
| table1_id | sort_order | c1   | x |
+-----------+------------+------+---+
|         8 |          1 | b    | 0 |
|         1 |          2 | a    | 0 |
|         6 |          3 | c    | 0 |
|         3 |          4 | c    | 0 |
|         5 |          5 | b    | 0 |
|         7 |          6 | a    | 0 |
|         4 |          7 | a    | 0 |
|         9 |          8 | c    | 0 |
|         2 |          9 | b    | 0 |
+-----------+------------+------+---+
9 rows in set (0.00 sec)

mysql>
mysql> select * from v1 where c1 = "a"
    -> ;
+-----------+------------+------+---+
| table1_id | sort_order | c1   | x |
+-----------+------------+------+---+
|         7 |          6 | a    | 0 |         *** WRONG ***
|         4 |          7 | a    | 0 |         *** WRONG ***
|         1 |          2 | a    | 0 |         *** WRONG ***
+-----------+------------+------+---+
3 rows in set (0.00 sec)

mysql>

------------------------------------------------------------
[MySQL 5.6.16 / Windows7(64bit)]  => This behavior not happened

mysql> show global variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.16                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.16-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql>
mysql> drop view if exists v1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>
mysql> create table t1 (
    ->   table1_id bigint not null auto_increment,
    ->   sort_order int(11),
    ->   c1 varchar(255),
    ->   primary key (`table1_id`)
    -> ) engine=InnoDB default charset=utf8
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> create view v1 as
    -> select t1.*, (select 0) x
    -> from t1
    -> order by sort_order
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into t1 (sort_order, c1) values
    -> (2, "a"),(9, "b"),(4, "c"),
    -> (7, "a"),(5, "b"),(3, "c"),
    -> (6, "a"),(1, "b"),(8, "c")
    -> ;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from v1
    -> ;
+-----------+------------+------+---+
| table1_id | sort_order | c1   | x |
+-----------+------------+------+---+
|         8 |          1 | b    | 0 |
|         1 |          2 | a    | 0 |
|         6 |          3 | c    | 0 |
|         3 |          4 | c    | 0 |
|         5 |          5 | b    | 0 |
|         7 |          6 | a    | 0 |
|         4 |          7 | a    | 0 |
|         9 |          8 | c    | 0 |
|         2 |          9 | b    | 0 |
+-----------+------------+------+---+
9 rows in set (0.00 sec)

mysql>
mysql> select * from v1 where c1 = "a"
    -> ;
+-----------+------------+------+---+
| table1_id | sort_order | c1   | x |
+-----------+------------+------+---+
|         1 |          2 | a    | 0 |
|         7 |          6 | a    | 0 |
|         4 |          7 | a    | 0 |
+-----------+------------+------+---+
3 rows in set (0.00 sec)

mysql>

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

How to repeat:
Please check attached file.(mysql_bug.sql)

"mysql_bug.sql" is script for bug check

Attachment: mysql_bug.sql (application/octet-stream, text), 476 bytes.

I checked this behavior on CentOS

Attachment: check_another_environment.txt (text/plain), 5.44 KiB.

Hello Yoshiaki,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

5.1.73/5.5.38 returns the rows in the expected order but 5.6.17/5.7.5 returns wrong results.

Posted by developer:
 
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.

Posted by developer:

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.