Index: src/select.c
==================================================================
--- src/select.c
+++ src/select.c
@@ -3258,10 +3258,73 @@
     }
     pFrom->pIndex = pIdx;
   }
   return SQLITE_OK;
 }
+/*
+** Detect compound SELECT statements that use an ORDER BY clause with 
+** an alternative collating sequence.
+**
+**    SELECT ... FROM t1 EXCEPT SELECT ... FROM t2 ORDER BY .. COLLATE ...
+**
+** These are rewritten as a subquery:
+**
+**    SELECT * FROM (SELECT ... FROM t1 EXCEPT SELECT ... FROM t2)
+**     ORDER BY ... COLLATE ...
+**
+** This transformation is necessary because the multiSelectOrderBy() routine
+** above that generates the code for a compound SELECT with an ORDER BY clause
+** uses a merge algorithm that requires the same collating sequence on the
+** result columns as on the ORDER BY clause.  See ticket
+** http://www.sqlite.org/src/info/6709574d2a
+**
+** This transformation is only needed for EXCEPT, INTERSECT, and UNION.
+** The UNION ALL operator works fine with multiSelectOrderBy() even when
+** there are COLLATE terms in the ORDER BY.
+*/
+static int convertCompoundSelectToSubquery(Walker *pWalker, Select *p){
+  int i;
+  Select *pNew;
+  Select *pX;
+  sqlite3 *db;
+  struct ExprList_item *a;
+  SrcList *pNewSrc;
+  Parse *pParse;
+  Token dummy;
+
+  if( p->pPrior==0 ) return WRC_Continue;
+  if( p->pOrderBy==0 ) return WRC_Continue;
+  for(pX=p; pX && (pX->op==TK_ALL || pX->op==TK_SELECT); pX=pX->pPrior){}
+  if( pX==0 ) return WRC_Continue;
+  a = p->pOrderBy->a;
+  for(i=p->pOrderBy->nExpr-1; i>=0; i--){
+    if( a[i].pExpr->flags & EP_Collate ) break;
+  }
+  if( i<0 ) return WRC_Continue;
+
+  /* If we reach this point, that means the transformation is required. */
+
+  pParse = pWalker->pParse;
+  db = pParse->db;
+  pNew = sqlite3DbMallocZero(db, sizeof(*pNew) );
+  if( pNew==0 ) return WRC_Abort;
+  memset(&dummy, 0, sizeof(dummy));
+  pNewSrc = sqlite3SrcListAppendFromTerm(pParse,0,0,0,&dummy,pNew,0,0);
+  if( pNewSrc==0 ) return WRC_Abort;
+  *pNew = *p;
+  p->pSrc = pNewSrc;
+  p->pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ALL, 0));
+  p->op = TK_SELECT;
+  p->pWhere = 0;
+  pNew->pGroupBy = 0;
+  pNew->pHaving = 0;
+  pNew->pOrderBy = 0;
+  p->pPrior = 0;
+  pNew->pLimit = 0;
+  pNew->pOffset = 0;
+  return WRC_Continue;
+}
 
 /*
 ** This routine is a Walker callback for "expanding" a SELECT statement.
 ** "Expanding" means to do the following:
 **
@@ -3575,13 +3638,15 @@
 ** and/or pParse->db->mallocFailed.
 */
 static void sqlite3SelectExpand(Parse *pParse, Select *pSelect){
   Walker w;
   memset(&w, 0, sizeof(w));
-  w.xSelectCallback = selectExpander;
+  w.xSelectCallback = convertCompoundSelectToSubquery;
   w.xExprCallback = exprWalkNoop;
   w.pParse = pParse;
+  sqlite3WalkSelect(&w, pSelect);
+  w.xSelectCallback = selectExpander;
   sqlite3WalkSelect(&w, pSelect);
 }
 
 
 #ifndef SQLITE_OMIT_SUBQUERY

Index: test/selectA.test
==================================================================
--- test/selectA.test
+++ test/selectA.test
@@ -279,17 +279,17 @@
   }
 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
 do_test selectA-2.35 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY b COLLATE NOCASE,a,c
+    ORDER BY y COLLATE NOCASE,x,z
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-2.36 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY b COLLATE NOCASE DESC,a,c
+    ORDER BY y COLLATE NOCASE DESC,x,z
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-2.37 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
@@ -309,11 +309,11 @@
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-2.40 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY c COLLATE BINARY DESC,a,b
+    ORDER BY z COLLATE BINARY DESC,x,y
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
 do_test selectA-2.41 {
   execsql {
     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
@@ -600,11 +600,11 @@
   }
 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
 do_test selectA-2.86 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
-    ORDER BY b COLLATE NOCASE,a,c
+    ORDER BY y COLLATE NOCASE,x,z
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-2.87 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
@@ -630,11 +630,11 @@
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-2.91 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
-    ORDER BY c COLLATE BINARY DESC,a,b
+    ORDER BY z COLLATE BINARY DESC,x,y
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
 do_test selectA-2.92 {
   execsql {
     SELECT x,y,z FROM t2
@@ -891,17 +891,17 @@
   }
 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
 do_test selectA-3.35 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY b COLLATE NOCASE,a,c
+    ORDER BY y COLLATE NOCASE,x,z
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-3.36 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY b COLLATE NOCASE DESC,a,c
+    ORDER BY y COLLATE NOCASE DESC,x,z
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-3.37 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
@@ -921,11 +921,11 @@
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-3.40 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
-    ORDER BY c COLLATE BINARY DESC,a,b
+    ORDER BY z COLLATE BINARY DESC,x,y
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
 do_test selectA-3.41 {
   execsql {
     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
@@ -1212,11 +1212,11 @@
   }
 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
 do_test selectA-3.86 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
-    ORDER BY b COLLATE NOCASE,a,c
+    ORDER BY y COLLATE NOCASE,x,z
   }
 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
 do_test selectA-3.87 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
@@ -1242,11 +1242,11 @@
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
 do_test selectA-3.91 {
   execsql {
     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
-    ORDER BY c COLLATE BINARY DESC,a,b
+    ORDER BY z COLLATE BINARY DESC,x,y
   }
 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
 do_test selectA-3.92 {
   execsql {
     SELECT x,y,z FROM t2

ADDED   test/selectE.test
Index: test/selectE.test
==================================================================
--- /dev/null
+++ test/selectE.test
@@ -0,0 +1,95 @@
+# 2013-05-07
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for compound SELECT statements
+# that have ORDER BY clauses with collating sequences that differ
+# from the collating sequence used for comparison in the compound.
+# 
+# Ticket 6709574d2a8d8b9be3a9cb1afbf4ff2de48ea4e7:
+# drh added on 2013-05-06 15:21:16:
+#
+# In the code shown below (which is intended to be run from the
+# sqlite3.exe command-line tool) the three SELECT statements should all
+# generate the same answer. But the third one does not. It is as if the
+# COLLATE clause on the ORDER BY somehow got pulled into the EXCEPT
+# operator. Note that the ".print" commands are instructions to the
+# sqlite3.exe shell program to output delimiter lines so that you can more
+# easily tell where the output of one query ends and the next query
+# begins. 
+# 
+#     CREATE TABLE t1(a);
+#     INSERT INTO t1 VALUES('abc'),('def');
+#     CREATE TABLE t2(a);
+#     INSERT INTO t2 VALUES('DEF');
+# 
+#     SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
+#     .print -----
+#     SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
+#      ORDER BY a COLLATE nocase;
+#     .print -----
+#     SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
+# 
+# Bisecting shows that this problem was introduced in SQLite version 3.6.0
+# by check-in [8bbfa97837a74ef] on 2008-06-15. 
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+do_test selectE-1.0 {
+  db eval {
+    CREATE TABLE t1(a);
+    INSERT INTO t1 VALUES('abc'),('def'),('ghi');
+    CREATE TABLE t2(a);
+    INSERT INTO t2 VALUES('DEF'),('abc');
+    CREATE TABLE t3(a);
+    INSERT INTO t3 VALUES('def'),('jkl');
+
+    SELECT a FROM t1 EXCEPT SELECT a FROM t2
+     ORDER BY a COLLATE nocase;
+  }
+} {def ghi}
+do_test selectE-1.1 {
+  db eval {
+    SELECT a FROM t2 EXCEPT SELECT a FROM t3
+     ORDER BY a COLLATE nocase;
+  }
+} {abc DEF}
+do_test selectE-1.2 {
+  db eval {
+    SELECT a FROM t2 EXCEPT SELECT a FROM t3
+     ORDER BY a COLLATE binary;
+  }
+} {DEF abc}
+do_test selectE-1.3 {
+  db eval {
+    SELECT a FROM t2 EXCEPT SELECT a FROM t3
+     ORDER BY a;
+  }
+} {DEF abc}
+
+do_test selectE-2.1 {
+  db eval {
+    DELETE FROM t2;
+    DELETE FROM t3;
+    INSERT INTO t2 VALUES('ABC'),('def'),('GHI'),('jkl');
+    INSERT INTO t3 SELECT lower(a) FROM t2;
+    SELECT a COLLATE nocase FROM t2 EXCEPT SELECT a FROM t3
+     ORDER BY 1
+  }
+} {}
+do_test selectE-2.2 {
+  db eval {
+    SELECT a COLLATE nocase FROM t2 EXCEPT SELECT a FROM t3
+     ORDER BY 1 COLLATE binary
+  }
+} {}
+
+finish_test