Also order broken in sub-query
SELECT ordering FROM (SELECT ordering FROM test ORDER BY ordering) t1 |
See Documentation:
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 an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway
> UNION by default produces an unordered set of rows
strange logic.
I use follow SQL code for menu generation:
SELECT |
m.id_menu,
|
m.description,
|
'' active, |
'parent' mnemonic, |
c.tmpl,
|
m.id_role acl,
|
m.ord,
|
c.icon
|
FROM `ui_menu` m |
JOIN `#components` c ON (c.id_component = m.id_component) |
LEFT JOIN `#user_acls` pr ON (pr.id_role = m.id_role) |
WHERE id_menu = var_id_parent |
AND (m.id_role IS NULL OR pr.id_profile=par_id_profile) |
UNION ALL |
(SELECT |
m.id_menu,
|
m.description,
|
IF(m.`id_menu` = var_id_menu, 'active', '') active, |
IF(EXISTS(SELECT 1 FROM ui_menu WHERE id_parent = m.id_menu), 'dir', 'file') mnemonic, |
c.tmpl,
|
m.id_role acl,
|
m.ord,
|
c.icon
|
FROM `ui_menu` m |
JOIN `#components` c ON (c.id_component = m.id_component) |
LEFT JOIN `#user_acls` pr ON (pr.id_role = m.id_role) |
WHERE m.id_parent <=> var_id_parent |
AND (m.id_role IS NULL OR pr.id_profile=par_id_profile) |
ORDER BY `ord`); |
First statement retrieve always parent record if exists, second statement retrieve ordered child's records.
For my case workaround would be adding LIMIT 1000 after "ORDER BY `ord`"
But I am afraid in another case I can't do it.
For my case workaround would be adding LIMIT 1000 after "ORDER BY `ord`"
It won't be a workaround even for your case. See again the quote above:
the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result
That is, there is no guarantee that those rows will end up ordered. It can happen by pure luck in certain versions, but you cannot rely on it.
That is, there is no guarantee that those rows will end up ordered. It can happen by pure luck in certain versions, but you cannot rely on it.
Thanks for explanation, it's too bad for me 
Can we discuss changing this behavior?
I don't think that someone want add order and expect that it wouldn't work.
Who really needs in speed would not use order in sub queries and union.
Normally I would suggest converting the bug report to a task (feature request), but in this case I'm not even sure it is possible, it is probably the SQL standard that defines this behavior. Maybe you would want to discuss it on the mailing list first? maria-discuss@lists.launchpad.net
A SQL-standard compliant solution is to use the ORDER BY on the top level. You can do it like this:
SELECT
|
1 select_no,
|
m.id_menu,
|
m.description,
|
'' active, |
'parent' mnemonic, |
c.tmpl,
|
m.id_role acl,
|
m.ord,
|
c.icon
|
FROM `ui_menu` m |
JOIN `#components` c ON (c.id_component = m.id_component) |
LEFT JOIN `#user_acls` pr ON (pr.id_role = m.id_role) |
WHERE id_menu = var_id_parent |
AND (m.id_role IS NULL OR pr.id_profile=par_id_profile) |
UNION ALL |
SELECT
|
2,
|
m.id_menu,
|
m.description,
|
IF(m.`id_menu` = var_id_menu, 'active', '') active, |
IF(EXISTS(SELECT 1 FROM ui_menu WHERE id_parent = m.id_menu), 'dir', 'file') mnemonic, |
c.tmpl,
|
m.id_role acl,
|
m.ord,
|
c.icon
|
FROM `ui_menu` m |
JOIN `#components` c ON (c.id_component = m.id_component) |
LEFT JOIN `#user_acls` pr ON (pr.id_role = m.id_role) |
WHERE m.id_parent <=> var_id_parent |
AND (m.id_role IS NULL OR pr.id_profile=par_id_profile) |
ORDER BY select_no, ord; |
Sergei, thank you for joining the discussion of the problem.
Yes, my particular problem can be solved with your workaround.
But not everything can be rewritten so.
For example the case when the UNION must unite two SELECT with different directions of the sort.
(SELECT ordering FROM test ORDER BY ordering LIMIT 3) |
UNION ALL |
(SELECT ordering FROM test ORDER BY ordering DESC LIMIT 3) |
I just do not understand from whom this protection. As I said above, developers which needed in merging large dataset without ordering simple will not use ORDER statement in subqueries.
Exactly. With different sort directions you can do something like
(SELECT 1 number, ordering as sort, ordering FROM test) |
UNION ALL |
(SELECT 2, -ordering as sort, ordering FROM test) |
ORDER BY number, sort |
It is not a "protection". It is simply how SQL standard defines the syntax.
Here (simplified):
<direct select statement: multiple rows> ::= <cursor specification>
|
|
|
<cursor specification> ::= <query expression> [ <order by clause> ]
|
|
|
<query expression body> ::= <query term>
|
| <query expression body> UNION [ ALL | DISTINCT ] <query term>
|
|
|
<query term> ::= <query primary>
|
|
|
<query primary> ::= <simple table>
|
|
|
<simple table> ::= <query specification>
|
|
|
<query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression>
|
As you can see, according to the SQL standard, ORDER BY applies to the whole <query expression>. And on both sides of UNION one can only put <query term>s. SELECT clause starts a <query specification> which is a <simple table>. And tables do not have any specific ordering of rows, so UNION works with two unordered sets of rows.
That's the standard.
Nice try, but even not work with my data set:
(SELECT 1 number, ordering AS sort, ordering FROM test) |
UNION ALL |
(SELECT 2, -ordering AS sort, ordering FROM test) |
ORDER BY number, sort |
number sort ordering
|
------ ------ ----------
|
1 2 2
|
1 3 3
|
1 4 4
|
1 5 5
|
1 8 8
|
1 9 9
|
2 0 8
|
2 0 5
|
2 0 2
|
2 0 4
|
2 0 3
|
2 0 9
|
CREATE DATABASE `test9`CHARACTER SET utf8 COLLATE utf8_general_ci; |
USE `test9`; |
CREATE TABLE `test`( `ordering` INT UNSIGNED ); |
INSERT INTO test(ordering) VALUES (5),(2),(4),(3),(9),(8); |
I also respect the standards, but seems this issue cannot be solved without temporary tables and stored procedures.
What about sub-queries?
SELECT number, ordering FROM (SELECT 1 number, ordering FROM test ORDER BY ordering) t1
|
UNION ALL
|
SELECT number, ordering FROM (SELECT 2 number, ordering FROM test ORDER BY ordering DESC) t2
|
I have always believed that all sub-queries are treated as separate requests.
why do you get "sort" as 0 in the second part of the result set? may be 'sort' is unsigned? try casting it to signed (in the first select, of course).
You don't understand. see how the standard defines it (above). ORDER BY applies to the result set. It cannot be applied to a SELECT inside a UNION, it cannot be applied to a subquery. According to the standard iy is a syntax error to use ORDER BY the way you did.
why do you get "sort" as 0 in the second part of the result set? may be 'sort' is unsigned? try casting it to signed (in the first select, of course).
I specifically show that this variant is completely dependent on the type of sorting field. And will be not worked with VARCHAR data.
You don't understand. see how the standard defines it (above). ORDER BY applies to the result set.
Why can not consider that the result set and also have a sub-query?
All sub-query having result set and we can sort, group and make calculations in this result sets.
I retest my test case in Postgre SQL 9.4 and construction
SELECT 0 |
UNION ALL |
(SELECT ordering FROM test ORDER BY ordering) |
work as expected.
select VERSION() |
"PostgreSQL 9.4.1 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.9.2 20150107 (Red Hat 4.9.2-5), 64-bit"
|
Can you accept this improvement for MariaDB?
When added "(" ")" to query, this query must be treat as subquery.
Of course, I am agree that this statement
SELECT 0 |
UNION ALL |
SELECT ordering FROM test ORDER BY ordering |
erroneously.