Date: 2010-07-13 11:11:32 +0200
From: @drstmane
To: SQL devs <>
Version: 2.38.3 (Jun2010-SP1) [obsolete]

Last updated: 2010-08-30 09:18:07 +0200

Comment 14373

Date: 2010-07-13 11:11:32 +0200
From: @drstmane

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.9) Gecko/20100330 Fedora/3.5.9-2.fc12 Firefox/3.5.9
Build Identifier:

In queries like
(SELECT ...) {UNION|EXCEPT|INTERSECT} (SELECT ...) ORDER BY ...;
the ORDER BY statement is ignored.

Reproducible: Always

Steps to Reproduce:

sql>create table t1 (a int);
operation successful
sql>insert into t1 values (1);
1 affected row
sql>insert into t1 values (2);
1 affected row
sql>select * from t1;
+------+
| a |
+======+
| 1 |
| 2 |
+------+
2 tuples

ORDER BY is ignored:

sql>plan select * from t1 union all select * from t1 order by a;
% .plan table_name
% rel name
% clob type
% 44 length
union (
| project (
| | table(sys.t1) [ t1.a, t1.%TID% NOT NULL ]
| ) [ t1.a ],
| project (
| | table(sys.t1) [ t1.a, t1.%TID% NOT NULL ]
| ) [ t1.a ]
) [ a ]
sql>select * from t1 union all select * from t1 order by a;
+------+
| a |
+======+
| 1 |
| 2 |
| 1 |
| 2 |
+------+
4 tuples

sql>plan (select * from t1 union all select * from t1) order by a;
% .plan table_name
% rel name
% clob type
% 44 length
union (
| project (
| | table(sys.t1) [ t1.a, t1.%TID% NOT NULL ]
| ) [ t1.a ],
| project (
| | table(sys.t1) [ t1.a, t1.%TID% NOT NULL ]
| ) [ t1.a ]
) [ a ]
sql>(select * from t1 union all select * from t1) order by a;
+------+
| a |
+======+
| 1 |
| 2 |
| 1 |
| 2 |
+------+
4 tuples

sql>plan (select * from t1) union all (select * from t1) order by a;
% .plan table_name
% rel name
% clob type
% 44 length
union (
| project (
| | table(sys.t1) [ t1.a, t1.%TID% NOT NULL ]
| ) [ t1.a ],
| project (
| | table(sys.t1) [ t1.a, t1.%TID% NOT NULL ]
| ) [ t1.a ]
) [ a ]
sql>(select * from t1) union all (select * from t1) order by a;
+------+
| a |
+======+
| 1 |
| 2 |
| 1 |
| 2 |
+------+
4 tuples

ORDER BY is respected:

sql>plan select * from (select * from t1 union all select * from t1) as t
order by a;
% .plan table_name
% rel name
% clob type
% 46 length
project (
| union (
| | project (
| | | table(sys.t1) [ t1.a, t1.%TID% NOT NULL ]
| | ) [ t1.a ],
| | project (
| | | table(sys.t1) [ t1.a, t1.%TID% NOT NULL ]
| | ) [ t1.a ]
| ) [ a as t.a ]
) [ t.a ASC ] [ t.a ]
sql>select * from (select * from t1 union all select * from t1) as t order
by a;
+------+
| a |
+======+
| 1 |
| 1 |
| 2 |
| 2 |
+------+
4 tuples

Actual Results:

ORDER BY is ignored

Expected Results:

ORDER BY should be respected

The Feb2010 release showed the same behavior.

These two recent changes by Niels disabled these (and possibly other) query constructs completely, resulting in errors like
"ORDER BY: missing select operator"
http://dev.monetdb.org/hg/MonetDB/rev/bbe7f874d29b
http://dev.monetdb.org/hg/MonetDB/rev/77151d685024

While I believe that these query constructs are syntactically correct, Niels' intensions to disable them (and might have been that they are not yet supported by MonetDB.

To restore the Feb2010 behavior (for the UNION, EXCEPT & INTERSECT cases), I applied these changes:
http://dev.monetdb.org/hg/MonetDB/rev/35ebc65eb74c
http://dev.monetdb.org/hg/MonetDB/rev/e399a0ffae31
(the latter accidentally hidden in an other checkin).

Comment 14374

Date: 2010-07-13 11:46:06 +0200
From: @drstmane

Changeset 43b62989158b made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=43b62989158b

Changeset description:

test for bug #2606 "ORDER BY over UNION / EXCEPT / INTERSECT is ignored"

Comment 14560

Date: 2010-07-30 08:24:28 +0200
From: @drstmane

Fixed by Niels in http://dev.monetdb.org/hg/MonetDB/rev/3d5b55f4aebd

Thanks!

Comment 14808

Date: 2010-08-30 09:18:07 +0200
From: @sjoerdmullender

The Jun2010-SP2 version has been released.