Import SQLite 3.10.2.

third_party/sqlite/src/test/select4.test

 # 2001 September 15
 #
 # 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.
 #
 #***********************************************************************
@@ skipping 87 matching lines @@
 ifcapable subquery {
   do_test select4-1.2 {
     execsql {
       SELECT log FROM t1 WHERE n IN 
         (SELECT DISTINCT log FROM t1 UNION ALL
          SELECT n FROM t1 WHERE log=3)
       ORDER BY log;
     }
   } {0 1 2 2 3 3 3 3}
 }
+
+# EVIDENCE-OF: R-02644-22131 In a compound SELECT statement, only the
+# last or right-most simple SELECT may have an ORDER BY clause.
+#
 do_test select4-1.3 {
   set v [catch {execsql {
     SELECT DISTINCT log FROM t1 ORDER BY log
     UNION ALL
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }} msg]
   lappend v $msg
 } {1 {ORDER BY clause should come after UNION ALL not before}}
+do_catchsql_test select4-1.4 {
+  SELECT (VALUES(0) INTERSECT SELECT(0) UNION SELECT(0) ORDER BY 1 UNION
+          SELECT 0 UNION SELECT 0 ORDER BY 1);
+} {1 {ORDER BY clause should come after UNION not before}}
 
 # Union operator
 #
 do_test select4-2.1 {
   execsql {
     SELECT DISTINCT log FROM t1
     UNION
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }
@@ skipping 10 matching lines @@
 }
 do_test select4-2.3 {
   set v [catch {execsql {
     SELECT DISTINCT log FROM t1 ORDER BY log
     UNION
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }} msg]
   lappend v $msg
 } {1 {ORDER BY clause should come after UNION not before}}
+do_test select4-2.4 {
+  set v [catch {execsql {
+    SELECT 0 ORDER BY (SELECT 0) UNION SELECT 0;
+  }} msg]
+  lappend v $msg
+} {1 {ORDER BY clause should come after UNION not before}}
+do_execsql_test select4-2.5 {
+  SELECT 123 AS x ORDER BY (SELECT x ORDER BY 1);
+} {123}
 
 # Except operator
 #
 do_test select4-3.1.1 {
   execsql {
     SELECT DISTINCT log FROM t1
     EXCEPT
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }
@@ skipping 96 matching lines @@
 }
 do_test select4-4.3 {
   set v [catch {execsql {
     SELECT DISTINCT log FROM t1 ORDER BY log
     INTERSECT
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }} msg]
   lappend v $msg
 } {1 {ORDER BY clause should come after INTERSECT not before}}
+do_catchsql_test select4-4.4 {
+  SELECT 3 IN (
+    SELECT 0 ORDER BY 1
+    INTERSECT
+    SELECT 1
+    INTERSECT 
+    SELECT 2
+    ORDER BY 1
+  );
+} {1 {ORDER BY clause should come after INTERSECT not before}}
 
 # Various error messages while processing UNION or INTERSECT
 #
 do_test select4-5.1 {
   set v [catch {execsql {
     SELECT DISTINCT log FROM t2
     UNION ALL
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }} msg]
@@ skipping 515 matching lines @@
     UNION
     SELECT x FROM t2
     INTERSECT
     SELECT x FROM t2
     UNION ALL
     SELECT x FROM t2
     EXCEPT
     SELECT x FROM t2
   }
 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
+do_test select4-11.16 {
+  catchsql {
+    INSERT INTO t2(rowid) VALUES(2) UNION SELECT 3,4 UNION SELECT 5,6 ORDER BY 1;
+  }
+} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
 
 do_test select4-12.1 {
   sqlite3 db2 :memory:
   catchsql {
     SELECT 1 UNION SELECT 2,3 UNION SELECT 4,5 ORDER BY 1;
   } db2
 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
 
 } ;# ifcapable compound
 
@@ skipping 44 matching lines @@
 } {4 5 6}
 do_execsql_test select4-14.7 {
   SELECT * FROM t14 EXCEPT VALUES(1,2,3) EXCEPT VALUES(4,5,6)
 } {}
 do_execsql_test select4-14.8 {
   SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6)
 } {1 2 3}
 do_execsql_test select4-14.9 {
   SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
 } {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3}
+do_execsql_test select4-14.10 {
+  SELECT (VALUES(1),(2),(3),(4))
+} {1}
+do_execsql_test select4-14.11 {
+  SELECT (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4)
+} {1}
+do_execsql_test select4-14.12 {
+  VALUES(1) UNION VALUES(2);
+} {1 2}
+do_execsql_test select4-14.13 {
+  VALUES(1),(2),(3) EXCEPT VALUES(2);
+} {1 3}
+do_execsql_test select4-14.14 {
+  VALUES(1),(2),(3) EXCEPT VALUES(1),(3);
+} {2}
+do_execsql_test select4-14.15 {
+  SELECT * FROM (SELECT 123), (SELECT 456) ON likely(0 OR 1) OR 0;
+} {123 456}
+do_execsql_test select4-14.16 {
+  VALUES(1),(2),(3),(4) UNION ALL SELECT 5 LIMIT 99;
+} {1 2 3 4 5}
+do_execsql_test select4-14.17 {
+  VALUES(1),(2),(3),(4) UNION ALL SELECT 5 LIMIT 3;
+} {1 2 3}
 
 finish_test