[sqlite] UNION with ORDER BY collate gives 'Error: no such column:'

[Jerry Stralko]

Hello,

With the latest version of sqlite 3.8.5 and above we have notice some
of our existing queries are failing.  We noticed this with the iOS 8.2
release.

The schema example we are using for our test case

CREATE TABLE Table2
(
id INTEGER PRIMARY KEY,
name TEXT
);

CREATE TABLE Table3
(
id INTEGER,
foo_id INTEGER,
name TEXT,
PRIMARY KEY (id,foo_id)
);

Running the query below fails (using newer version of sqlite)

sqlite> select id from Table2 union select id from Table3 order by
Table2.id collate nocase asc;
Error: no such column: Table2.id

However changing the query to this works:

sqlite> select id table2_id from Table2 union select id from Table3
order by table2_id collate nocase asc;

And this works (removing the collate nocase):

sqlite> select id from Table2 union select id from Table3 order by Table2.id

Doing a fossil bisect between version 3.7.13 (good) and version 3.8.5 (bad)
$ fossil bisect log
  1 BAD     2014-06-04 14:06:34 b1ed4f2a34ba66c2
  2 GOOD    2012-06-11 02:05:22 f5b5a13f7394dc14
  3 BAD     2013-08-29 23:36:49 30d38cc44904d935
  4 GOOD    2013-03-05 01:46:26 8e0ced1af78d8c25
  5 BAD     2013-06-03 12:47:43 3bd5ad095b23102d
  6 GOOD    2013-04-19 12:32:52 514adbbd8cf3e296
  7 BAD     2013-05-09 14:20:11 1128575d0ab24f70
  8 GOOD    2013-04-29 07:01:23 e81e9ca11db09424
  9 GOOD    2013-05-03 20:08:16 9314b08099e7ac99
 10 GOOD    2013-05-07 12:16:48 0ba67b64de258883
 11 BAD     2013-05-08 17:06:28 1fa8c457394c9486
 12 BAD     2013-05-08 16:57:48 ce853a75068073d6
 13 GOOD    2013-05-08 14:20:28 7227b61512a53464
 14 CURRENT 2013-05-08 14:20:28 7227b61512a53464

The commit that broke the query was ce853a75068073d6:

$ fossil info ce853a75068073d6
uuid:         ce853a75068073d6acc8bec0819505a22c4c7e69 2013-05-08 16:57:48 UTC
parent:       7227b61512a534645c73883c2e37b4e647592e5d 2013-05-08 14:20:28 UTC
merged-from:  fc3630cdef6e2cdbfb4e7b373d1a094753e55016 2013-05-07 17:49:08 UTC
child:        1fa8c457394c94864f7584e4c893ec09e685fba4 2013-05-08 17:06:28 UTC
leaf:         no
tags:         trunk
comment:      Make sure the ORDER BY collating sequences are
compatible with the comparison collations before using the merge
algorithm for compound SELECT statements. Fix for
              ticket [6709574d2a8d8]. (user: drh)

So is my question is: Is it my query that is broken or is this a bug?

Thanks,

Jerry