Documentation of valid ORDER BY terms after UNION?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Documentation of valid ORDER BY terms after UNION?

nomad
This works:

    SELECT
        1 AS col
    ORDER BY
        col > 0 DESC;

The following fails with "Error: 1st ORDER BY term does not match any
column in the result set."

    SELECT
        1 AS col
    UNION ALL
    SELECT
        0 AS col
    ORDER BY
        col > 0 DESC;

I've read "The ORDER BY clause" of [1] and I *think* that the following
is what I am running into:

    "However, if the SELECT is a compound SELECT, then ORDER BY
    expressions that are not aliases to output columns must be exactly
    the same as an expression used as an output column. "

If so, that paragraph could perhaps be written differently, more
clearly indicating that ORDER BY terms can *only* be raw column names
with no complex expressions. One could also perhaps explicitly suggest
what appears to be a valid workaround:

    SELECT
        *
    FROM
        (SELECT
            1 AS col,
            1 > 0 AS truth
        UNION ALL
        SELECT
            0 AS col,
            0 > 0 AS truth
        ) x
    ORDER BY
        x.col > 0 DESC;

[1] https://www.sqlite.org/lang_select.html

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Documentation of valid ORDER BY terms after UNION?

Simon Slavin-3

On 15 May 2017, at 9:30pm, [hidden email] wrote:

>    SELECT
>        1 AS col
>    UNION ALL
>    SELECT
>        0 AS col
>    ORDER BY
>        col > 0 DESC;

Out of interest, intuitively rather than reading documentation, which do you think should be done first ?  Should SQL do both SELECTs and the UNION ALL, then ORDER the result ?  Or should SQL apply the ORDER BY to the second SELECT only ?

> I've read "The ORDER BY clause" of [1] and I *think* that the following
> is what I am running into:
>
>    "However, if the SELECT is a compound SELECT, then ORDER BY
>    expressions that are not aliases to output columns must be exactly
>    the same as an expression used as an output column. "

Right.  So the problem is that the "AS col" clauses apply to the individual SELECT queries, not to the results of the UNION.  The UNION command unites the two individual queries but SQL doesn’t provide any way to name the resulting column(s).

Here’s another question about intuition, rather than reading documentation.  How many columns should this query return ?  Or should it result in an error ?

>    SELECT
>        1 AS betty
>    UNION ALL
>    SELECT
>        0 AS carlos;


Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Documentation of valid ORDER BY terms after UNION?

nomad
On Mon May 15, 2017 at 09:58:31PM +0100, Simon Slavin wrote:

>
> On 15 May 2017, at 9:30pm, [hidden email] wrote:
>
> >    SELECT
> >        1 AS col
> >    UNION ALL
> >    SELECT
> >        0 AS col
> >    ORDER BY
> >        col > 0 DESC;
>
> Out of interest, intuitively rather than reading documentation, which
> do you think should be done first ?  Should SQL do both SELECTs and
> the UNION ALL, then ORDER the result ?  Or should SQL apply the ORDER
> BY to the second SELECT only ?

Intuitively (or according the union knowledge I can usually hold in my
brain) I think of the above as follows, purely because I know there can
only be one ORDER by statement:

    (
        SELECT
            1 AS col
        UNION ALL
        SELECT
            0 AS col
    )
    ORDER BY
        col > 0 DESC;

> > I've read "The ORDER BY clause" of [1] and I *think* that the following
> > is what I am running into:
> >
> >    "However, if the SELECT is a compound SELECT, then ORDER BY
> >    expressions that are not aliases to output columns must be exactly
> >    the same as an expression used as an output column. "
>
> Right.  So the problem is that the "AS col" clauses apply to the
> individual SELECT queries, not to the results of the UNION.  The
> UNION command unites the two individual queries but SQL doesn’t
> provide any way to name the resulting column(s).

Except that you *can* use the first SELECT aliases on their own...
which shouldn't be possible at all if SQL doesn't provide a way to name
the resulting columns.

> Here’s another question about intuition, rather than reading
> documentation.  How many columns should this query return ?  Or
> should it result in an error ?
>
> >    SELECT
> >        1 AS betty
> >    UNION ALL
> >    SELECT
> >        0 AS carlos;

Ideally this would be an error, but I already know that it isn't so I
can't really say what my intuition thinks :-)

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Documentation of valid ORDER BY terms after UNION?

David Raymond
In reply to this post by nomad
The order by terms can still be complex expressions, they just have to match exactly (to all sub-queries in the join I believe)

create table table1 (field1, field2);
select field1 > 0 as foo, field2 + 42 as bar from table1
union all
select field1 > 0 as foo, field2 - 42 as bar from table1...

order by foo; -> OK as simple column name.
order by foo > 0; -> Not OK as an expression on a column name.
order by field1 > 0; -> OK as it exactly matches a term in all of the sub queries, (so basically the same as ordering by a column name)


Ok, hmm, apparently it still works if it's only used in 1 of the unions.

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table table1 (field1 int, field2 int);
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> insert into table1 values (1, 1), (2, 2), (-1, -1), (7, 7);
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> select field1 > 0, field2, 1 as term from table1 union all select field1 > 1, field2, 2 as term from table1 order by field1 > 1, term, field2;
--EQP-- 1,0,0,SCAN TABLE table1
--EQP-- 1,0,0,USE TEMP B-TREE FOR ORDER BY
--EQP-- 2,0,0,SCAN TABLE table1
--EQP-- 2,0,0,USE TEMP B-TREE FOR ORDER BY
--EQP-- 0,0,0,COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
field1 > 0|field2|term
0|-1|1
0|-1|2
0|1|2
1|1|1
1|2|1
1|7|1
1|2|2
1|7|2
Run Time: real 0.016 user 0.000000 sys 0.000000

So it named the final result column based on the name given in the first subquery,
let us put an expression from the second sub query into the order by term,
and used it simply as "order by the first column" when it unioned everything.

So yes, it looks like you can *only* order by straight up columns any not any expression of their values.

However... you can put "an expression" in there, but rather than being an actual expression, it serves only to identify which final result column you want to order by. To do so it goes through the various sub queries in order, trying to match the text of your order term to a result field name, then to a result field expression, and if finds it, it translates that to a final result column of the union for the ordering. And if it doesn't find a match it gives the error you received.

(Brain is melted at end of the day, so hopefully that all made a little sense. I'm sure someone can re-word that better than I just did.)


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of [hidden email]
Sent: Monday, May 15, 2017 4:31 PM
To: SQLite mailing list
Subject: [sqlite] Documentation of valid ORDER BY terms after UNION?

I've read "The ORDER BY clause" of [1] and I *think* that the following
is what I am running into:

    "However, if the SELECT is a compound SELECT, then ORDER BY
    expressions that are not aliases to output columns must be exactly
    the same as an expression used as an output column. "

If so, that paragraph could perhaps be written differently, more
clearly indicating that ORDER BY terms can *only* be raw column names
with no complex expressions.


[1] https://www.sqlite.org/lang_select.html

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users