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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of no...@null.net 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
