Subscribe to this thread
Home - General / All posts - Bug in Union operator?
artlembo


3,400 post(s)
#12-May-15 14:12

Can someone confirm if this is an actual bug?

I run a query like the following with no problem:

SELECT TOP 20 PERCENT NAME

FROM STATES

Union

(SELECT TOP 20 PERCENT NAME

FROM STATES

)

That works fine. But, if I add an ORDER BY, I get an error:

SELECT TOP 20 PERCENT NAME

FROM STATES

ORDER BY [2000]

Union

(SELECT TOP 20 PERCENT NAME

FROM STATES

ORDER BY [2009]

)

firsttube


1,439 post(s)
#12-May-15 15:43

I got the same error.

How about this:

--sql

select * from 

(

SELECT TOP 20 PERCENT NAME

FROM STATES

ORDER BY [2000]

)

Union

select * from 

(

SELECT TOP 20 PERCENT NAME

FROM STATES

ORDER BY [2009]

)


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

Gustavo Palminha

1,010 post(s)
#12-May-15 16:24

From your query I see that you are using different column names [2000] and [2009] so the order operator may not work as expected.

I believe that what you see is somehow related to this...

http://stackoverflow.com/questions/213851/sql-query-using-order-by-in-union

... therefore it's not a Manifold Bug and you have there a couple of suggestions about how to write your query.


Stay connected:

Linkedin >> http://www.linkedin.com/in/gustavopalminha

Twitter >> http://twitter.com/gustavopalminha

Other >> http://www.spatialmentor.com

artlembo


3,400 post(s)
#12-May-15 17:46

that got me closer. What I was doing was writing a query that sought to find those states in the US that were in the top 20 percent in obesity for the three years (in other words, these are the states with systemic obesity problems).

I adjusted what you did to get the answer:

SELECT * FROM

(

SELECT count(*) AS CT, NAME FROM 

(select * FROM

(

SELECT TOP 20 PERCENT NAME

FROM STATES

ORDER BY [2000] DESC

)

Union All

select * from 

(

SELECT TOP 20 PERCENT NAME

FROM STATES

ORDER BY [2009] DESC

)

Union All

select * from 

(

SELECT TOP 20 PERCENT NAME

FROM STATES

ORDER BY [1995] DESC

))

GROUP BY NAME

)

WHERE CT > 2

I had originally written like this (as a workaround):

SELECT NAME FROM

(SELECT TOP 20 PERCENT NAME

FROM STATES

ORDER BY [1995] DESC)

WHERE NAME IN 

(SELECT TOP 20 PERCENT NAME

FROM STATES

ORDER BY [2009] DESC

)

AND NAME IN

(SELECT TOP 20 PERCENT NAME

FROM STATES

ORDER BY [2000] DESC

)

I like my version because it is more terse, but I like your version because it seems more straightforward and perhaps more universal in it's implementation for other uses.

tjhb
10,094 post(s)
#13-May-15 00:53

I like your version too Art. It's clever.

It's not a workaround though since there's no bug.

After all, ORDER BY operates on the result of a SELECT query.

A query like—

SELECT x FROM T

UNION

SELECT y FROM U;

—is only one query. It produces only one table (which is why the number and type of the columns in the two projections must exactly match), and it can only be ORDERed BY once, as a whole.

Putting parenthesis around one of the SELECT expressions doesn't change that. This is still one query (there are no subqueries), and all the parentheses are completely redundant (though valid):

SELECT x FROM T

UNION

(SELECT y FROM U)

UNION

((SELECT z FROM V));

To have multiple ORDERings, we need multiple queries, producing separate tables (which can then be combined).

Exactly as you have done with—

SELECT x FROM (SELECT... ORDER BY...)

WHERE x IN (SELECT... ORDER BY...)

AND x IN (SELECT... ORDER BY...);

This might be faster—

SELECT x FROM (SELECT... ORDER BY...)

WHERE x IN 

(

SELECT y FROM (SELECT... ORDER BY...)

WHERE y IN (SELECT... ORDER BY...)

);

—because it reduces the filtering comparisons to the minimum necessary (divide and conquer).

There are probably faster ways again though?

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.