MySQL Bugs: #28011: UNION returns wrong result, if order by must not be removed

Bug #28011	UNION returns wrong result, if order by must not be removed
Submitted:	22 Apr 2007 6:57	Modified:	11 Jul 2007 17:42
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.44-BK, 5.0.42bk	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	functions, order by, qc, UNION, variables, VIEW

Description:
Unions can in most case ignore the "order by" of thee individual selects, as the order (if any) has to be determined by the union itself.
The only current exception from this is, if the select has an order by and a limit:
(select a from t1 order by a desc limit 2) UNION (....)

There are other cases, where an order by can not be ignored but currently is ignored). If the select contains calculations with user variables that depend on the order. Or if the union contains similar functions. (Maybe also compiled UDF?)

Using functions this can also affect views in unions. (For MERGE views the order is dropped in sql_view.cc not in sql_union.cc see bug #27786)

See sql how to repeat.
 
# result outside union
set @a= null; select * from v1;
+------+--------+
| a    | prv(a) |
+------+--------+
|   30 |   NULL |
|   20 |     30 |
|   20 |     20 |
|   10 |     20 |
+------+--------+

result with union
set @a = null; select 0,0 UNION select * from v1;
+------+--------+
| a    | prv(a) |
+------+--------+
|   10 |   NULL | # the view never returned 10,0
|   20 |     10 | # no row 20,10 in view
|   20 |     20 |
|   30 |     20 | # no row 30,20 in view
|    0 |      0 |
+------+--------+

similar results for the other examples in how to repeat

How to repeat:
drop view if exists v1;
drop table if exists t1;
drop function if exists prv;

set GLOBAL log_bin_trust_function_creators=1;
delimiter |
create function prv (v int) returns int
 begin
  declare p  int;
  set p =@a;
  set @a =v;
  return p;
end|
delimiter ;

create table t1 (a int);
insert into t1 values (10), (20), (20), (30);

set @a = null; select a, prv(a) from t1 order by a;
set @a = null; select a, prv(a) from t1 order by a desc;

create ALGORITHM=MERGE view v1 as select a, prv(a) from t1 order by a desc;
set @a= null; select * from v1;

set @a = null; select * from v1 UNION select 0,0;
set @a = null; select 0,0 UNION select * from v1;
set @a = null; (select 0,0) UNION (select * from v1);

------------------------------------------------
drop view if exists v1;
drop table if exists t1;

create table t1 (a int);
insert into t1 values (10), (20), (20), (30);

create view v1 as select a-@a, @a, @a:=a from t1 order by a desc;

set @a=null; select a-@a, @a, @a:=a from t1 order by a desc;
set @a=null; (select a-@a, @a, @a:=a from t1 order by a desc) UNION (select 9,9,9);

set @a=null; (select * from v1 order by a desc) UNION (select 9,9,9);
set @a=null; (select 9,9,9) UNION (select * from v1 order by a desc);

set @a=null; select 9,9,9 UNION select * from v1 order by a desc;

Suggested fix:
-

just seen I copied and pasted to much. the view in the 2nd example is invalid. 

the 2nd part of how to repaet should only be:
--------
drop table if exists t1;

create table t1 (a int);
insert into t1 values (10), (20), (20), (30);

set @a=null; select a-@a, @a, @a:=a from t1 order by a desc;
set @a=null; (select a-@a, @a, @a:=a from t1 order by a desc) UNION (select 9,9,9);

The view is only broken in the first part, with the function.

Verified just as described with latest 5.0.44-BK on Linux. In particular:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.44-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

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

mysql> drop function if exists prv;

Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> set GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter |
mysql> create function prv (v int) returns int
    ->  begin
    ->   declare p  int;
    ->   set p =@a;
    ->   set @a =v;
    ->   return p;
    -> end|
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql>
mysql> create table t1 (a int);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (10), (20), (20), (30);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> set @a = null; select a, prv(a) from t1 order by a;
Query OK, 0 rows affected (0.00 sec)

+------+--------+
| a    | prv(a) |
+------+--------+
|   10 |   NULL |
|   20 |     10 |
|   20 |     20 |
|   30 |     20 |
+------+--------+
4 rows in set (0.02 sec)

@amysql> set @a = null; select a, prv(a) from t1 order by a desc;
Query OK, 0 rows affected (0.00 sec)

+------+--------+
| a    | prv(a) |
+------+--------+
|   30 |   NULL |
|   20 |     30 |
|   20 |     20 |
|   10 |     20 |
+------+--------+
4 rows in set (0.00 sec)

mysql> create ALGORITHM=MERGE view v1 as select a, prv(a) from t1 order by a desc;
Query OK, 0 rows affected (0.01 sec)

mysql> set @a= null; select * from v1;
Query OK, 0 rows affected (0.01 sec)

+------+--------+
| a    | prv(a) |
+------+--------+
|   30 |   NULL |
|   20 |     30 |
|   20 |     20 |
|   10 |     20 |
+------+--------+
4 rows in set (0.00 sec)

mysql>
mysql> set @a = null; select * from v1 UNION select 0,0;
Query OK, 0 rows affected (0.00 sec)

+------+--------+
| a    | prv(a) |
+------+--------+
|   10 |   NULL |
|   20 |     10 |
|   20 |     20 |
|   30 |     20 |
|    0 |      0 |
+------+--------+
5 rows in set (0.01 sec)

mysql> set @a = null; select 0,0 UNION select * from v1;
Query OK, 0 rows affected (0.00 sec)

+------+------+
| 0    | 0    |
+------+------+
|    0 |    0 |
|   10 | NULL |
|   20 |   10 |
|   20 |   20 |
|   30 |   20 |
+------+------+
5 rows in set (0.00 sec)

mysql> set @a = null; (select 0,0) UNION (select * from v1);
Query OK, 0 rows affected (0.00 sec)

+------+------+
| 0    | 0    |
+------+------+
|    0 |    0 |
|   10 | NULL |
|   20 |   10 |
|   20 |   20 |
|   30 |   20 |
+------+------+
5 rows in set (0.00 sec)

This is not a bug.

The manual says that the ORDER BY clause will be optimized away in UNION unless the LIMIT clause is present. http://dev.mysql.com/doc/refman/5.0/en/union.html
This is exactly what happens here. When a view is merged its ORDER BY clause is appended to the embedding select. So this rule is applicable here.
To prevent dropping of the ORDER BY clause you can recreate view with the ALGORITHM=TEMPTABLE option.

Changing a user variable in a select is a side effect and the side effects are implementation dependent. Consistent handling of them isn't guaranteed.