sl@0: # 2001 September 15
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this file is testing UNION, INTERSECT and EXCEPT operators
sl@0: # in SELECT statements.
sl@0: #
sl@0: # $Id: select4.test,v 1.29 2008/08/04 03:51:24 danielk1977 Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: # Most tests in this file depend on compound-select. But there are a couple
sl@0: # right at the end that test DISTINCT, so we cannot omit the entire file.
sl@0: #
sl@0: ifcapable compound {
sl@0: 
sl@0: # Build some test data
sl@0: #
sl@0: execsql {
sl@0:   CREATE TABLE t1(n int, log int);
sl@0:   BEGIN;
sl@0: }
sl@0: for {set i 1} {$i<32} {incr i} {
sl@0:   for {set j 0} {(1<<$j)<$i} {incr j} {}
sl@0:   execsql "INSERT INTO t1 VALUES($i,$j)"
sl@0: }
sl@0: execsql {
sl@0:   COMMIT;
sl@0: }
sl@0: 
sl@0: do_test select4-1.0 {
sl@0:   execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
sl@0: } {0 1 2 3 4 5}
sl@0: 
sl@0: # Union All operator
sl@0: #
sl@0: do_test select4-1.1a {
sl@0:   lsort [execsql {SELECT DISTINCT log FROM t1}]
sl@0: } {0 1 2 3 4 5}
sl@0: do_test select4-1.1b {
sl@0:   lsort [execsql {SELECT n FROM t1 WHERE log=3}]
sl@0: } {5 6 7 8}
sl@0: do_test select4-1.1c {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY log;
sl@0:   }
sl@0: } {0 1 2 3 4 5 5 6 7 8}
sl@0: do_test select4-1.1d {
sl@0:   execsql {
sl@0:     CREATE TABLE t2 AS
sl@0:       SELECT DISTINCT log FROM t1
sl@0:       UNION ALL
sl@0:       SELECT n FROM t1 WHERE log=3
sl@0:       ORDER BY log;
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {0 1 2 3 4 5 5 6 7 8}
sl@0: execsql {DROP TABLE t2}
sl@0: do_test select4-1.1e {
sl@0:   execsql {
sl@0:     CREATE TABLE t2 AS
sl@0:       SELECT DISTINCT log FROM t1
sl@0:       UNION ALL
sl@0:       SELECT n FROM t1 WHERE log=3
sl@0:       ORDER BY log DESC;
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {8 7 6 5 5 4 3 2 1 0}
sl@0: execsql {DROP TABLE t2}
sl@0: do_test select4-1.1f {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=2
sl@0:   }
sl@0: } {0 1 2 3 4 5 3 4}
sl@0: do_test select4-1.1g {
sl@0:   execsql {
sl@0:     CREATE TABLE t2 AS 
sl@0:       SELECT DISTINCT log FROM t1
sl@0:       UNION ALL
sl@0:       SELECT n FROM t1 WHERE log=2;
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {0 1 2 3 4 5 3 4}
sl@0: execsql {DROP TABLE t2}
sl@0: ifcapable subquery {
sl@0:   do_test select4-1.2 {
sl@0:     execsql {
sl@0:       SELECT log FROM t1 WHERE n IN 
sl@0:         (SELECT DISTINCT log FROM t1 UNION ALL
sl@0:          SELECT n FROM t1 WHERE log=3)
sl@0:       ORDER BY log;
sl@0:     }
sl@0:   } {0 1 2 2 3 3 3 3}
sl@0: }
sl@0: do_test select4-1.3 {
sl@0:   set v [catch {execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY log
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY log;
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {1 {ORDER BY clause should come after UNION ALL not before}}
sl@0: 
sl@0: # Union operator
sl@0: #
sl@0: do_test select4-2.1 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     UNION
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY log;
sl@0:   }
sl@0: } {0 1 2 3 4 5 6 7 8}
sl@0: ifcapable subquery {
sl@0:   do_test select4-2.2 {
sl@0:     execsql {
sl@0:       SELECT log FROM t1 WHERE n IN 
sl@0:         (SELECT DISTINCT log FROM t1 UNION
sl@0:          SELECT n FROM t1 WHERE log=3)
sl@0:       ORDER BY log;
sl@0:     }
sl@0:   } {0 1 2 2 3 3 3 3}
sl@0: }
sl@0: do_test select4-2.3 {
sl@0:   set v [catch {execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY log
sl@0:     UNION
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY log;
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {1 {ORDER BY clause should come after UNION not before}}
sl@0: 
sl@0: # Except operator
sl@0: #
sl@0: do_test select4-3.1.1 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     EXCEPT
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY log;
sl@0:   }
sl@0: } {0 1 2 3 4}
sl@0: do_test select4-3.1.2 {
sl@0:   execsql {
sl@0:     CREATE TABLE t2 AS 
sl@0:       SELECT DISTINCT log FROM t1
sl@0:       EXCEPT
sl@0:       SELECT n FROM t1 WHERE log=3
sl@0:       ORDER BY log;
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {0 1 2 3 4}
sl@0: execsql {DROP TABLE t2}
sl@0: do_test select4-3.1.3 {
sl@0:   execsql {
sl@0:     CREATE TABLE t2 AS 
sl@0:       SELECT DISTINCT log FROM t1
sl@0:       EXCEPT
sl@0:       SELECT n FROM t1 WHERE log=3
sl@0:       ORDER BY log DESC;
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {4 3 2 1 0}
sl@0: execsql {DROP TABLE t2}
sl@0: ifcapable subquery {
sl@0:   do_test select4-3.2 {
sl@0:     execsql {
sl@0:       SELECT log FROM t1 WHERE n IN 
sl@0:         (SELECT DISTINCT log FROM t1 EXCEPT
sl@0:          SELECT n FROM t1 WHERE log=3)
sl@0:       ORDER BY log;
sl@0:     }
sl@0:   } {0 1 2 2}
sl@0: }
sl@0: do_test select4-3.3 {
sl@0:   set v [catch {execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY log
sl@0:     EXCEPT
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY log;
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {1 {ORDER BY clause should come after EXCEPT not before}}
sl@0: 
sl@0: # Intersect operator
sl@0: #
sl@0: do_test select4-4.1.1 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     INTERSECT
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY log;
sl@0:   }
sl@0: } {5}
sl@0: 
sl@0: do_test select4-4.1.2 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     UNION ALL
sl@0:     SELECT 6
sl@0:     INTERSECT
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY t1.log;
sl@0:   }
sl@0: } {5 6}
sl@0: 
sl@0: do_test select4-4.1.3 {
sl@0:   execsql {
sl@0:     CREATE TABLE t2 AS
sl@0:       SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
sl@0:       INTERSECT
sl@0:       SELECT n FROM t1 WHERE log=3
sl@0:       ORDER BY log;
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {5 6}
sl@0: execsql {DROP TABLE t2}
sl@0: do_test select4-4.1.4 {
sl@0:   execsql {
sl@0:     CREATE TABLE t2 AS
sl@0:       SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
sl@0:       INTERSECT
sl@0:       SELECT n FROM t1 WHERE log=3
sl@0:       ORDER BY log DESC;
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {6 5}
sl@0: execsql {DROP TABLE t2}
sl@0: ifcapable subquery {
sl@0:   do_test select4-4.2 {
sl@0:     execsql {
sl@0:       SELECT log FROM t1 WHERE n IN 
sl@0:         (SELECT DISTINCT log FROM t1 INTERSECT
sl@0:          SELECT n FROM t1 WHERE log=3)
sl@0:       ORDER BY log;
sl@0:     }
sl@0:   } {3}
sl@0: }
sl@0: do_test select4-4.3 {
sl@0:   set v [catch {execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY log
sl@0:     INTERSECT
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY log;
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {1 {ORDER BY clause should come after INTERSECT not before}}
sl@0: 
sl@0: # Various error messages while processing UNION or INTERSECT
sl@0: #
sl@0: do_test select4-5.1 {
sl@0:   set v [catch {execsql {
sl@0:     SELECT DISTINCT log FROM t2
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY log;
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {1 {no such table: t2}}
sl@0: do_test select4-5.2 {
sl@0:   set v [catch {execsql {
sl@0:     SELECT DISTINCT log AS "xyzzy" FROM t1
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY xyzzy;
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {0 {0 1 2 3 4 5 5 6 7 8}}
sl@0: do_test select4-5.2b {
sl@0:   set v [catch {execsql {
sl@0:     SELECT DISTINCT log AS xyzzy FROM t1
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY "xyzzy";
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {0 {0 1 2 3 4 5 5 6 7 8}}
sl@0: do_test select4-5.2c {
sl@0:   set v [catch {execsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY "xyzzy";
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {1 {1st ORDER BY term does not match any column in the result set}}
sl@0: do_test select4-5.2d {
sl@0:   set v [catch {execsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     INTERSECT
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY "xyzzy";
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {1 {1st ORDER BY term does not match any column in the result set}}
sl@0: do_test select4-5.2e {
sl@0:   set v [catch {execsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY n;
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {0 {0 1 2 3 4 5 5 6 7 8}}
sl@0: do_test select4-5.2f {
sl@0:   catchsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY log;
sl@0:   }
sl@0: } {0 {0 1 2 3 4 5 5 6 7 8}}
sl@0: do_test select4-5.2g {
sl@0:   catchsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY 1;
sl@0:   }
sl@0: } {0 {0 1 2 3 4 5 5 6 7 8}}
sl@0: do_test select4-5.2h {
sl@0:   catchsql {
sl@0:     SELECT DISTINCT log FROM t1
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY 2;
sl@0:   }
sl@0: } {1 {1st ORDER BY term out of range - should be between 1 and 1}}
sl@0: do_test select4-5.2i {
sl@0:   catchsql {
sl@0:     SELECT DISTINCT 1, log FROM t1
sl@0:     UNION ALL
sl@0:     SELECT 2, n FROM t1 WHERE log=3
sl@0:     ORDER BY 2, 1;
sl@0:   }
sl@0: } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
sl@0: do_test select4-5.2j {
sl@0:   catchsql {
sl@0:     SELECT DISTINCT 1, log FROM t1
sl@0:     UNION ALL
sl@0:     SELECT 2, n FROM t1 WHERE log=3
sl@0:     ORDER BY 1, 2 DESC;
sl@0:   }
sl@0: } {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
sl@0: do_test select4-5.2k {
sl@0:   catchsql {
sl@0:     SELECT DISTINCT 1, log FROM t1
sl@0:     UNION ALL
sl@0:     SELECT 2, n FROM t1 WHERE log=3
sl@0:     ORDER BY n, 1;
sl@0:   }
sl@0: } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
sl@0: do_test select4-5.3 {
sl@0:   set v [catch {execsql {
sl@0:     SELECT DISTINCT log, n FROM t1
sl@0:     UNION ALL
sl@0:     SELECT n FROM t1 WHERE log=3
sl@0:     ORDER BY log;
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
sl@0: do_test select4-5.4 {
sl@0:   set v [catch {execsql {
sl@0:     SELECT log FROM t1 WHERE n=2
sl@0:     UNION ALL
sl@0:     SELECT log FROM t1 WHERE n=3
sl@0:     UNION ALL
sl@0:     SELECT log FROM t1 WHERE n=4
sl@0:     UNION ALL
sl@0:     SELECT log FROM t1 WHERE n=5
sl@0:     ORDER BY log;
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {0 {1 2 2 3}}
sl@0: 
sl@0: do_test select4-6.1 {
sl@0:   execsql {
sl@0:     SELECT log, count(*) as cnt FROM t1 GROUP BY log
sl@0:     UNION
sl@0:     SELECT log, n FROM t1 WHERE n=7
sl@0:     ORDER BY cnt, log;
sl@0:   }
sl@0: } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
sl@0: do_test select4-6.2 {
sl@0:   execsql {
sl@0:     SELECT log, count(*) FROM t1 GROUP BY log
sl@0:     UNION
sl@0:     SELECT log, n FROM t1 WHERE n=7
sl@0:     ORDER BY count(*), log;
sl@0:   }
sl@0: } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
sl@0: 
sl@0: # NULLs are indistinct for the UNION operator.
sl@0: # Make sure the UNION operator recognizes this
sl@0: #
sl@0: do_test select4-6.3 {
sl@0:   execsql {
sl@0:     SELECT NULL UNION SELECT NULL UNION
sl@0:     SELECT 1 UNION SELECT 2 AS 'x'
sl@0:     ORDER BY x;
sl@0:   }
sl@0: } {{} 1 2}
sl@0: do_test select4-6.3.1 {
sl@0:   execsql {
sl@0:     SELECT NULL UNION ALL SELECT NULL UNION ALL
sl@0:     SELECT 1 UNION ALL SELECT 2 AS 'x'
sl@0:     ORDER BY x;
sl@0:   }
sl@0: } {{} {} 1 2}
sl@0: 
sl@0: # Make sure the DISTINCT keyword treats NULLs as indistinct.
sl@0: #
sl@0: ifcapable subquery {
sl@0:   do_test select4-6.4 {
sl@0:     execsql {
sl@0:       SELECT * FROM (
sl@0:          SELECT NULL, 1 UNION ALL SELECT NULL, 1
sl@0:       );
sl@0:     }
sl@0:   } {{} 1 {} 1}
sl@0:   do_test select4-6.5 {
sl@0:     execsql {
sl@0:       SELECT DISTINCT * FROM (
sl@0:          SELECT NULL, 1 UNION ALL SELECT NULL, 1
sl@0:       );
sl@0:     }
sl@0:   } {{} 1}
sl@0:   do_test select4-6.6 {
sl@0:     execsql {
sl@0:       SELECT DISTINCT * FROM (
sl@0:          SELECT 1,2  UNION ALL SELECT 1,2
sl@0:       );
sl@0:     }
sl@0:   } {1 2}
sl@0: }
sl@0: 
sl@0: # Test distinctness of NULL in other ways.
sl@0: #
sl@0: do_test select4-6.7 {
sl@0:   execsql {
sl@0:     SELECT NULL EXCEPT SELECT NULL
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: 
sl@0: # Make sure column names are correct when a compound select appears as
sl@0: # an expression in the WHERE clause.
sl@0: #
sl@0: do_test select4-7.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;
sl@0:     SELECT * FROM t2 ORDER BY x;
sl@0:   }
sl@0: } {0 1 1 1 2 2 3 4 4 8 5 15}  
sl@0: ifcapable subquery {
sl@0:   do_test select4-7.2 {
sl@0:     execsql2 {
sl@0:       SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)
sl@0:       ORDER BY n
sl@0:     }
sl@0:   } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}
sl@0:   do_test select4-7.3 {
sl@0:     execsql2 {
sl@0:       SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)
sl@0:       ORDER BY n LIMIT 2
sl@0:     }
sl@0:   } {n 6 log 3 n 7 log 3}
sl@0:   do_test select4-7.4 {
sl@0:     execsql2 {
sl@0:       SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
sl@0:       ORDER BY n LIMIT 2
sl@0:     }
sl@0:   } {n 1 log 0 n 2 log 1}
sl@0: } ;# ifcapable subquery
sl@0: 
sl@0: } ;# ifcapable compound
sl@0: 
sl@0: # Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
sl@0: do_test select4-8.1 {
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     CREATE TABLE t3(a text, b float, c text);
sl@0:     INSERT INTO t3 VALUES(1, 1.1, '1.1');
sl@0:     INSERT INTO t3 VALUES(2, 1.10, '1.10');
sl@0:     INSERT INTO t3 VALUES(3, 1.10, '1.1');
sl@0:     INSERT INTO t3 VALUES(4, 1.1, '1.10');
sl@0:     INSERT INTO t3 VALUES(5, 1.2, '1.2');
sl@0:     INSERT INTO t3 VALUES(6, 1.3, '1.3');
sl@0:     COMMIT;
sl@0:   }
sl@0:   execsql {
sl@0:     SELECT DISTINCT b FROM t3 ORDER BY c;
sl@0:   }
sl@0: } {1.1 1.2 1.3}
sl@0: do_test select4-8.2 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT c FROM t3 ORDER BY c;
sl@0:   }
sl@0: } {1.1 1.10 1.2 1.3}
sl@0: 
sl@0: # Make sure the names of columns are taken from the right-most subquery
sl@0: # right in a compound query.  Ticket #1721
sl@0: #
sl@0: ifcapable compound {
sl@0: 
sl@0: do_test select4-9.1 {
sl@0:   execsql2 {
sl@0:     SELECT x, y FROM t2 UNION SELECT a, b FROM t3 ORDER BY x LIMIT 1
sl@0:   }
sl@0: } {x 0 y 1}
sl@0: do_test select4-9.2 {
sl@0:   execsql2 {
sl@0:     SELECT x, y FROM t2 UNION ALL SELECT a, b FROM t3 ORDER BY x LIMIT 1
sl@0:   }
sl@0: } {x 0 y 1}
sl@0: do_test select4-9.3 {
sl@0:   execsql2 {
sl@0:     SELECT x, y FROM t2 EXCEPT SELECT a, b FROM t3 ORDER BY x LIMIT 1
sl@0:   }
sl@0: } {x 0 y 1}
sl@0: do_test select4-9.4 {
sl@0:   execsql2 {
sl@0:     SELECT x, y FROM t2 INTERSECT SELECT 0 AS a, 1 AS b;
sl@0:   }
sl@0: } {x 0 y 1}
sl@0: do_test select4-9.5 {
sl@0:   execsql2 {
sl@0:     SELECT 0 AS x, 1 AS y
sl@0:     UNION
sl@0:     SELECT 2 AS p, 3 AS q
sl@0:     UNION
sl@0:     SELECT 4 AS a, 5 AS b
sl@0:     ORDER BY x LIMIT 1
sl@0:   }
sl@0: } {x 0 y 1}
sl@0: 
sl@0: ifcapable subquery {
sl@0: do_test select4-9.6 {
sl@0:   execsql2 {
sl@0:     SELECT * FROM (
sl@0:       SELECT 0 AS x, 1 AS y
sl@0:       UNION
sl@0:       SELECT 2 AS p, 3 AS q
sl@0:       UNION
sl@0:       SELECT 4 AS a, 5 AS b
sl@0:     ) ORDER BY 1 LIMIT 1;
sl@0:   }
sl@0: } {x 0 y 1}
sl@0: do_test select4-9.7 {
sl@0:   execsql2 {
sl@0:     SELECT * FROM (
sl@0:       SELECT 0 AS x, 1 AS y
sl@0:       UNION
sl@0:       SELECT 2 AS p, 3 AS q
sl@0:       UNION
sl@0:       SELECT 4 AS a, 5 AS b
sl@0:     ) ORDER BY x LIMIT 1;
sl@0:   }
sl@0: } {x 0 y 1}
sl@0: } ;# ifcapable subquery
sl@0: 
sl@0: do_test select4-9.8 {
sl@0:   execsql {
sl@0:     SELECT 0 AS x, 1 AS y
sl@0:     UNION
sl@0:     SELECT 2 AS y, -3 AS x
sl@0:     ORDER BY x LIMIT 1;
sl@0:   }
sl@0: } {0 1}
sl@0: 
sl@0: do_test select4-9.9.1 {
sl@0:   execsql2 {
sl@0:     SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a
sl@0:   }
sl@0: } {a 1 b 2 a 3 b 4}
sl@0: 
sl@0: ifcapable subquery {
sl@0: do_test select4-9.9.2 {
sl@0:   execsql2 {
sl@0:     SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
sl@0:      WHERE b=3
sl@0:   }
sl@0: } {}
sl@0: do_test select4-9.10 {
sl@0:   execsql2 {
sl@0:     SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
sl@0:      WHERE b=2
sl@0:   }
sl@0: } {a 1 b 2}
sl@0: do_test select4-9.11 {
sl@0:   execsql2 {
sl@0:     SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
sl@0:      WHERE b=2
sl@0:   }
sl@0: } {a 1 b 2}
sl@0: do_test select4-9.12 {
sl@0:   execsql2 {
sl@0:     SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
sl@0:      WHERE b>0
sl@0:   }
sl@0: } {a 1 b 2 a 3 b 4}
sl@0: } ;# ifcapable subquery
sl@0: 
sl@0: # Try combining DISTINCT, LIMIT, and OFFSET.  Make sure they all work
sl@0: # together.
sl@0: #
sl@0: do_test select4-10.1 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY log
sl@0:   }
sl@0: } {0 1 2 3 4 5}
sl@0: do_test select4-10.2 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 4
sl@0:   }
sl@0: } {0 1 2 3}
sl@0: do_test select4-10.3 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0
sl@0:   }
sl@0: } {}
sl@0: do_test select4-10.4 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1
sl@0:   }
sl@0: } {0 1 2 3 4 5}
sl@0: do_test select4-10.5 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1 OFFSET 2
sl@0:   }
sl@0: } {2 3 4 5}
sl@0: do_test select4-10.6 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 3 OFFSET 2
sl@0:   }
sl@0: } {2 3 4}
sl@0: do_test select4-10.7 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY +log LIMIT 3 OFFSET 20
sl@0:   }
sl@0: } {}
sl@0: do_test select4-10.8 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0 OFFSET 3
sl@0:   }
sl@0: } {}
sl@0: do_test select4-10.9 {
sl@0:   execsql {
sl@0:     SELECT DISTINCT max(n), log FROM t1 ORDER BY +log; -- LIMIT 2 OFFSET 1
sl@0:   }
sl@0: } {31 5}
sl@0: 
sl@0: # Make sure compound SELECTs with wildly different numbers of columns
sl@0: # do not cause assertion faults due to register allocation issues.
sl@0: #
sl@0: do_test select4-11.1 {
sl@0:   catchsql {
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:     UNION
sl@0:     SELECT x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
sl@0: do_test select4-11.2 {
sl@0:   catchsql {
sl@0:     SELECT x FROM t2
sl@0:     UNION
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
sl@0: do_test select4-11.3 {
sl@0:   catchsql {
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:     UNION ALL
sl@0:     SELECT x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
sl@0: do_test select4-11.4 {
sl@0:   catchsql {
sl@0:     SELECT x FROM t2
sl@0:     UNION ALL
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
sl@0: do_test select4-11.5 {
sl@0:   catchsql {
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:     EXCEPT
sl@0:     SELECT x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
sl@0: do_test select4-11.6 {
sl@0:   catchsql {
sl@0:     SELECT x FROM t2
sl@0:     EXCEPT
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
sl@0: do_test select4-11.7 {
sl@0:   catchsql {
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:     INTERSECT
sl@0:     SELECT x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
sl@0: do_test select4-11.8 {
sl@0:   catchsql {
sl@0:     SELECT x FROM t2
sl@0:     INTERSECT
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
sl@0: 
sl@0: do_test select4-11.11 {
sl@0:   catchsql {
sl@0:     SELECT x FROM t2
sl@0:     UNION
sl@0:     SELECT x FROM t2
sl@0:     UNION ALL
sl@0:     SELECT x FROM t2
sl@0:     EXCEPT
sl@0:     SELECT x FROM t2
sl@0:     INTERSECT
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
sl@0: do_test select4-11.12 {
sl@0:   catchsql {
sl@0:     SELECT x FROM t2
sl@0:     UNION
sl@0:     SELECT x FROM t2
sl@0:     UNION ALL
sl@0:     SELECT x FROM t2
sl@0:     EXCEPT
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:     EXCEPT
sl@0:     SELECT x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
sl@0: do_test select4-11.13 {
sl@0:   catchsql {
sl@0:     SELECT x FROM t2
sl@0:     UNION
sl@0:     SELECT x FROM t2
sl@0:     UNION ALL
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:     UNION ALL
sl@0:     SELECT x FROM t2
sl@0:     EXCEPT
sl@0:     SELECT x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
sl@0: do_test select4-11.14 {
sl@0:   catchsql {
sl@0:     SELECT x FROM t2
sl@0:     UNION
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:     UNION
sl@0:     SELECT x FROM t2
sl@0:     UNION ALL
sl@0:     SELECT x FROM t2
sl@0:     EXCEPT
sl@0:     SELECT x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
sl@0: do_test select4-11.15 {
sl@0:   catchsql {
sl@0:     SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
sl@0:     UNION
sl@0:     SELECT x FROM t2
sl@0:     INTERSECT
sl@0:     SELECT x FROM t2
sl@0:     UNION ALL
sl@0:     SELECT x FROM t2
sl@0:     EXCEPT
sl@0:     SELECT x FROM t2
sl@0:   }
sl@0: } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
sl@0: 
sl@0: } ;# ifcapable compound
sl@0: 
sl@0: finish_test