MySQL Bugs: #27786: Inconsistent Operation Performing UNION On View With ORDER BY

Bug #27786	Inconsistent Operation Performing UNION On View With ORDER BY
Submitted:	12 Apr 2007 15:43	Modified:	24 Apr 2007 17:29
Reporter:	Devin Withers	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.40-BK, 5.0.37	OS:	Linux
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	Contribution, regression

Description:
Performing a UNION on two views when the views have ORDER BY clauses results in an 'Unknown column' error. Replacing even one of the views with an equivalent subselect, or even a subselect which selects * from that view, does not result in an error. I'm not sure which result is correct, but the fact that they are different is concerning. The error started appearing after an upgrade from MySQL 5.0.18.

How to repeat:
CREATE TABLE test.thetable (a INT);
CREATE VIEW test.theview AS SELECT a FROM test.thetable ORDER BY a;
SELECT * FROM test.theview UNION SELECT * FROM test.theview; -- Results in: Unknown column 'test.thetable.a' in 'order clause'
SELECT * FROM (SELECT a FROM test.thetable ORDER BY a) a UNION SELECT * FROM (SELECT a FROM test.thetable ORDER BY a) a; -- No Error
SELECT * FROM (SELECT * FROM test.theview) a UNION SELECT * FROM (SELECT * FROM test.theview) a; -- No Error
(SELECT a FROM test.thetable ORDER BY a) UNION (SELECT a FROM test.thetable ORDER BY a); -- No Error

Thank you for a bug report. Verified just as described with latest 5.0.40-BK on Linux.

possible patch and testcase

Attachment: union_view.patch (text/x-patch), 11.57 KiB.

A possible patch for the issue/ Explanation of the patch

The problem is, that if the last part of a union is not in braces, then global_parameters will point to the last select_lex. (see sql_yacc.yy "selen_paren:" line 4277 and "union_list:" line 9311 (mysql_new_select()))

This is correct and important to catch an order-by in cases like 
 select a from t1 union select b from t2 order by a;
so the order by is applied to the whole union.

However the order by of a view should not apply to the whole union. (It is private to the view)

So when optimizing the view, I added checks for this.
If we are in a union, then a fake_select_lex has always been created (mysql_new_select(move_down=false) ).
And it will always point to the last (not-braced) select-part of the union.

The other parts of a union (not last) are not affected, because global_param has moved to the last part, by the time any view is unfolded.

The part to which global_param point will now take appropriate actions.
If it is not the last part, then there can be no additional order or limit. This is not allowed by as it is not in braces.

Similiar for limits

Martin,

Thank you for a patch.

some more test cases. Italso works if the view is in braces and an order to the view is given inside the braces

Attachment: union_view_tests.patch (text/x-patch), 2.10 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24970

ChangeSet@1.2457, 2007-04-20 09:42:52+03:00, gkodinov@magare.gmz +4 -0
  Bug #27786:
  When merging views into the enclosing statement
  the ORDER BY clause of the view is merged to the
  parent's ORDER BY clause.
  However when the VIEW is merged into an UNION
  branch the ORDER BY should be ignored. 
  Use of ORDER BY for individual SELECT statements
  implies nothing about the order in which the rows
  appear in the final result because UNION by default
  produces unordered set of rows.
  Fixed by ignoring the ORDER BY clause from the merge
  view when expanded in an UNION branch.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24971

ChangeSet@1.2457, 2007-04-20 10:49:45+03:00, gkodinov@magare.gmz +4 -0
  Bug #27786:
  When merging views into the enclosing statement
  the ORDER BY clause of the view is merged to the
  parent's ORDER BY clause.
  However when the VIEW is merged into an UNION
  branch the ORDER BY should be ignored. 
  Use of ORDER BY for individual SELECT statements
  implies nothing about the order in which the rows
  appear in the final result because UNION by default
  produces unordered set of rows.
  Fixed by ignoring the ORDER BY clause from the merge
  view when expanded in an UNION branch.

I am away, and have no means of testing, but reading the patch I was wondering.

the patch always ignores the order of the view, if merging with the union. How does that work with

create view v1 select * from t1 order by a desc limit 2; 

If it woks, a testcase should be added.

Pushed into 5.1.18-beta

Pushed into 5.0.42

Noted in 5.0.42, 5.1.18 changelogs.

Performing a UNION on two views that had had ORDER BY clauses
resulted in an Unknown column error.