test/select4.test - external/github.com/pwnall/sqlite - Git at Google

 # 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.
 #
 #***********************************************************************
 # This file implements regression tests for SQLite library.  The
 # focus of this file is testing UNION, INTERSECT and EXCEPT operators
 # in SELECT statements.
 #
 # $Id: select4.test,v 1.13 2003/02/02 12:41:27 drh Exp $

 set testdir [file dirname $argv0]
 source $testdir/tester.tcl

 # Build some test data
 #
 set fd [open data1.txt w]
 for {set i 1} {$i<32} {incr i} {
   for {set j 0} {pow(2,$j)<$i} {incr j} {}
   puts $fd "$i\t$j"
 }
 close $fd
 execsql {
   CREATE TABLE t1(n int, log int);
   COPY t1 FROM 'data1.txt'
 }
 file delete data1.txt

 do_test select4-1.0 {
   execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
 } {0 1 2 3 4 5}

 # Union All operator
 #
 do_test select4-1.1a {
   lsort [execsql {SELECT DISTINCT log FROM t1}]
 } {0 1 2 3 4 5}
 do_test select4-1.1b {
   lsort [execsql {SELECT n FROM t1 WHERE log=3}]
 } {5 6 7 8}
 do_test select4-1.1c {
   execsql {
     SELECT DISTINCT log FROM t1
     UNION ALL
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }
 } {0 1 2 3 4 5 5 6 7 8}
 do_test select4-1.1d {
   execsql {
     CREATE TABLE t2 AS
       SELECT DISTINCT log FROM t1
       UNION ALL
       SELECT n FROM t1 WHERE log=3
       ORDER BY log;
     SELECT * FROM t2;
   }
 } {0 1 2 3 4 5 5 6 7 8}
 execsql {DROP TABLE t2}
 do_test select4-1.1e {
   execsql {
     CREATE TABLE t2 AS
       SELECT DISTINCT log FROM t1
       UNION ALL
       SELECT n FROM t1 WHERE log=3
       ORDER BY log DESC;
     SELECT * FROM t2;
   }
 } {8 7 6 5 5 4 3 2 1 0}
 execsql {DROP TABLE t2}
 do_test select4-1.1f {
   execsql {
     SELECT DISTINCT log FROM t1
     UNION ALL
     SELECT n FROM t1 WHERE log=2
   }
 } {0 1 2 3 4 5 3 4}
 do_test select4-1.1g {
   execsql {
     CREATE TABLE t2 AS
       SELECT DISTINCT log FROM t1
       UNION ALL
       SELECT n FROM t1 WHERE log=2;
     SELECT * FROM t2;
   }
 } {0 1 2 3 4 5 3 4}
 execsql {DROP TABLE t2}
 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}
 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}}

 # Union operator
 #
 do_test select4-2.1 {
   execsql {
     SELECT DISTINCT log FROM t1
     UNION
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }
 } {0 1 2 3 4 5 6 7 8}
 do_test select4-2.2 {
   execsql {
     SELECT log FROM t1 WHERE n IN
       (SELECT DISTINCT log FROM t1 UNION
        SELECT n FROM t1 WHERE log=3)
     ORDER BY log;
   }
 } {0 1 2 2 3 3 3 3}
 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}}

 # 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;
   }
 } {0 1 2 3 4}
 do_test select4-3.1.2 {
   execsql {
     CREATE TABLE t2 AS
       SELECT DISTINCT log FROM t1
       EXCEPT
       SELECT n FROM t1 WHERE log=3
       ORDER BY log;
     SELECT * FROM t2;
   }
 } {0 1 2 3 4}
 execsql {DROP TABLE t2}
 do_test select4-3.1.3 {
   execsql {
     CREATE TABLE t2 AS
       SELECT DISTINCT log FROM t1
       EXCEPT
       SELECT n FROM t1 WHERE log=3
       ORDER BY log DESC;
     SELECT * FROM t2;
   }
 } {4 3 2 1 0}
 execsql {DROP TABLE t2}
 do_test select4-3.2 {
   execsql {
     SELECT log FROM t1 WHERE n IN
       (SELECT DISTINCT log FROM t1 EXCEPT
        SELECT n FROM t1 WHERE log=3)
     ORDER BY log;
   }
 } {0 1 2 2}
 do_test select4-3.3 {
   set v [catch {execsql {
     SELECT DISTINCT log FROM t1 ORDER BY log
     EXCEPT
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }} msg]
   lappend v $msg
 } {1 {ORDER BY clause should come after EXCEPT not before}}

 # Intersect operator
 #
 do_test select4-4.1.1 {
   execsql {
     SELECT DISTINCT log FROM t1
     INTERSECT
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }
 } {5}
 do_test select4-4.1.2 {
   execsql {
     SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
     INTERSECT
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }
 } {5 6}
 do_test select4-4.1.3 {
   execsql {
     CREATE TABLE t2 AS
       SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
       INTERSECT
       SELECT n FROM t1 WHERE log=3
       ORDER BY log;
     SELECT * FROM t2;
   }
 } {5 6}
 execsql {DROP TABLE t2}
 do_test select4-4.1.4 {
   execsql {
     CREATE TABLE t2 AS
       SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
       INTERSECT
       SELECT n FROM t1 WHERE log=3
       ORDER BY log DESC;
     SELECT * FROM t2;
   }
 } {6 5}
 execsql {DROP TABLE t2}
 do_test select4-4.2 {
   execsql {
     SELECT log FROM t1 WHERE n IN
       (SELECT DISTINCT log FROM t1 INTERSECT
        SELECT n FROM t1 WHERE log=3)
     ORDER BY log;
   }
 } {3}
 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}}

 # 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]
   lappend v $msg
 } {1 {no such table: t2}}
 do_test select4-5.2 {
   set v [catch {execsql {
     SELECT DISTINCT log AS "xyzzy" FROM t1
     UNION ALL
     SELECT n FROM t1 WHERE log=3
     ORDER BY xyzzy;
   }} msg]
   lappend v $msg
 } {0 {0 1 2 3 4 5 5 6 7 8}}
 do_test select4-5.2b {
   set v [catch {execsql {
     SELECT DISTINCT log AS xyzzy FROM t1
     UNION ALL
     SELECT n FROM t1 WHERE log=3
     ORDER BY 'xyzzy';
   }} msg]
   lappend v $msg
 } {0 {0 1 2 3 4 5 5 6 7 8}}
 do_test select4-5.2c {
   set v [catch {execsql {
     SELECT DISTINCT log FROM t1
     UNION ALL
     SELECT n FROM t1 WHERE log=3
     ORDER BY 'xyzzy';
   }} msg]
   lappend v $msg
 } {1 {ORDER BY term number 1 does not match any result column}}
 do_test select4-5.2d {
   set v [catch {execsql {
     SELECT DISTINCT log FROM t1
     INTERSECT
     SELECT n FROM t1 WHERE log=3
     ORDER BY 'xyzzy';
   }} msg]
   lappend v $msg
 } {1 {ORDER BY term number 1 does not match any result column}}
 do_test select4-5.2e {
   set v [catch {execsql {
     SELECT DISTINCT log FROM t1
     UNION ALL
     SELECT n FROM t1 WHERE log=3
     ORDER BY n;
   }} msg]
   lappend v $msg
 } {0 {0 1 2 3 4 5 5 6 7 8}}
 do_test select4-5.2f {
   catchsql {
     SELECT DISTINCT log FROM t1
     UNION ALL
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }
 } {0 {0 1 2 3 4 5 5 6 7 8}}
 do_test select4-5.2g {
   catchsql {
     SELECT DISTINCT log FROM t1
     UNION ALL
     SELECT n FROM t1 WHERE log=3
     ORDER BY 1;
   }
 } {0 {0 1 2 3 4 5 5 6 7 8}}
 do_test select4-5.2h {
   catchsql {
     SELECT DISTINCT log FROM t1
     UNION ALL
     SELECT n FROM t1 WHERE log=3
     ORDER BY 2;
   }
 } {1 {ORDER BY position 2 should be between 1 and 1}}
 do_test select4-5.2i {
   catchsql {
     SELECT DISTINCT 1, log FROM t1
     UNION ALL
     SELECT 2, n FROM t1 WHERE log=3
     ORDER BY 2, 1;
   }
 } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
 do_test select4-5.2j {
   catchsql {
     SELECT DISTINCT 1, log FROM t1
     UNION ALL
     SELECT 2, n FROM t1 WHERE log=3
     ORDER BY 1, 2 DESC;
   }
 } {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
 do_test select4-5.2k {
   catchsql {
     SELECT DISTINCT 1, log FROM t1
     UNION ALL
     SELECT 2, n FROM t1 WHERE log=3
     ORDER BY n, 1;
   }
 } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
 do_test select4-5.3 {
   set v [catch {execsql {
     SELECT DISTINCT log, n FROM t1
     UNION ALL
     SELECT n FROM t1 WHERE log=3
     ORDER BY log;
   }} msg]
   lappend v $msg
 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
 do_test select4-5.4 {
   set v [catch {execsql {
     SELECT log FROM t1 WHERE n=2
     UNION ALL
     SELECT log FROM t1 WHERE n=3
     UNION ALL
     SELECT log FROM t1 WHERE n=4
     UNION ALL
     SELECT log FROM t1 WHERE n=5
     ORDER BY log;
   }} msg]
   lappend v $msg
 } {0 {1 2 2 3}}

 do_test select4-6.1 {
   execsql {
     SELECT log, count(*) as cnt FROM t1 GROUP BY log
     UNION
     SELECT log, n FROM t1 WHERE n=7
     ORDER BY cnt, log;
   }
 } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
 do_test select4-6.2 {
   execsql {
     SELECT log, count(*) FROM t1 GROUP BY log
     UNION
     SELECT log, n FROM t1 WHERE n=7
     ORDER BY count(*), log;
   }
 } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}

 # NULLs are indistinct for the UNION operator.
 # Make sure the UNION operator recognizes this
 #
 do_test select4-6.3 {
   execsql {
     SELECT NULL UNION SELECT NULL UNION
     SELECT 1 UNION SELECT 2 AS 'x'
     ORDER BY x;
   }
 } {{} 1 2}
 do_test select4-6.3.1 {
   execsql {
     SELECT NULL UNION ALL SELECT NULL UNION ALL
     SELECT 1 UNION ALL SELECT 2 AS 'x'
     ORDER BY x;
   }
 } {{} {} 1 2}

 # Make sure the DISTINCT keyword treats NULLs as indistinct.
 #
 do_test select4-6.4 {
   execsql {
     SELECT * FROM (
        SELECT NULL, 1 UNION ALL SELECT NULL, 1
     );
   }
 } {{} 1 {} 1}
 do_test select4-6.5 {
   execsql {
     SELECT DISTINCT * FROM (
        SELECT NULL, 1 UNION ALL SELECT NULL, 1
     );
   }
 } {{} 1}
 do_test select4-6.6 {
   execsql {
     SELECT DISTINCT * FROM (
        SELECT 1,2  UNION ALL SELECT 1,2
     );
   }
 } {1 2}

 # Test distinctness of NULL in other ways.
 #
 do_test select4-6.7 {
   execsql {
     SELECT NULL EXCEPT SELECT NULL
   }
 } {}


 # Make sure column names are correct when a compound select appears as
 # an expression in the WHERE clause.
 #
 do_test select4-7.1 {
   execsql {
     CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;
     SELECT * FROM t2 ORDER BY x;
   }
 } {0 1 1 1 2 2 3 4 4 8 5 15}
 do_test select4-7.2 {
   execsql2 {
     SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)
     ORDER BY n
   }
 } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}
 do_test select4-7.3 {
   execsql2 {
     SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)
     ORDER BY n LIMIT 2
   }
 } {n 6 log 3 n 7 log 3}
 do_test select4-7.4 {
   execsql2 {
     SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
     ORDER BY n LIMIT 2
   }
 } {n 1 log 0 n 2 log 1}

 # Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
 #
 do_test select4-8.1 {
   execsql {
     BEGIN;
     CREATE TABLE t3(a text, b float, c text);
     INSERT INTO t3 VALUES(1, 1.1, '1.1');
     INSERT INTO t3 VALUES(2, 1.10, '1.10');
     INSERT INTO t3 VALUES(3, 1.10, '1.1');
     INSERT INTO t3 VALUES(4, 1.1, '1.10');
     INSERT INTO t3 VALUES(5, 1.2, '1.2');
     INSERT INTO t3 VALUES(6, 1.3, '1.3');
     COMMIT;
   }
   execsql {
     SELECT DISTINCT b FROM t3 ORDER BY c;
   }
 } {1.1 1.2 1.3}
 do_test select4-8.2 {
   execsql {
     SELECT DISTINCT c FROM t3 ORDER BY c;
   }
 } {1.1 1.10 1.2 1.3}


 finish_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.
	#
	#***********************************************************************
	# This file implements regression tests for SQLite library. The
	# focus of this file is testing UNION, INTERSECT and EXCEPT operators
	# in SELECT statements.
	#
	# $Id: select4.test,v 1.13 2003/02/02 12:41:27 drh Exp $

	set testdir [file dirname $argv0]
	source $testdir/tester.tcl

	# Build some test data
	#
	set fd [open data1.txt w]
	for {set i 1} {$i<32} {incr i} {
	for {set j 0} {pow(2,$j)<$i} {incr j} {}
	puts $fd "$i\t$j"
	}
	close $fd
	execsql {
	CREATE TABLE t1(n int, log int);
	COPY t1 FROM 'data1.txt'
	}
	file delete data1.txt

	do_test select4-1.0 {
	execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
	} {0 1 2 3 4 5}

	# Union All operator
	#
	do_test select4-1.1a {
	lsort [execsql {SELECT DISTINCT log FROM t1}]
	} {0 1 2 3 4 5}
	do_test select4-1.1b {
	lsort [execsql {SELECT n FROM t1 WHERE log=3}]
	} {5 6 7 8}
	do_test select4-1.1c {
	execsql {
	SELECT DISTINCT log FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=3
	ORDER BY log;
	}
	} {0 1 2 3 4 5 5 6 7 8}
	do_test select4-1.1d {
	execsql {
	CREATE TABLE t2 AS
	SELECT DISTINCT log FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=3
	ORDER BY log;
	SELECT * FROM t2;
	}
	} {0 1 2 3 4 5 5 6 7 8}
	execsql {DROP TABLE t2}
	do_test select4-1.1e {
	execsql {
	CREATE TABLE t2 AS
	SELECT DISTINCT log FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=3
	ORDER BY log DESC;
	SELECT * FROM t2;
	}
	} {8 7 6 5 5 4 3 2 1 0}
	execsql {DROP TABLE t2}
	do_test select4-1.1f {
	execsql {
	SELECT DISTINCT log FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=2
	}
	} {0 1 2 3 4 5 3 4}
	do_test select4-1.1g {
	execsql {
	CREATE TABLE t2 AS
	SELECT DISTINCT log FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=2;
	SELECT * FROM t2;
	}
	} {0 1 2 3 4 5 3 4}
	execsql {DROP TABLE t2}
	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}
	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}}

	# Union operator
	#
	do_test select4-2.1 {
	execsql {
	SELECT DISTINCT log FROM t1
	UNION
	SELECT n FROM t1 WHERE log=3
	ORDER BY log;
	}
	} {0 1 2 3 4 5 6 7 8}
	do_test select4-2.2 {
	execsql {
	SELECT log FROM t1 WHERE n IN
	(SELECT DISTINCT log FROM t1 UNION
	SELECT n FROM t1 WHERE log=3)
	ORDER BY log;
	}
	} {0 1 2 2 3 3 3 3}
	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}}

	# 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;
	}
	} {0 1 2 3 4}
	do_test select4-3.1.2 {
	execsql {
	CREATE TABLE t2 AS
	SELECT DISTINCT log FROM t1
	EXCEPT
	SELECT n FROM t1 WHERE log=3
	ORDER BY log;
	SELECT * FROM t2;
	}
	} {0 1 2 3 4}
	execsql {DROP TABLE t2}
	do_test select4-3.1.3 {
	execsql {
	CREATE TABLE t2 AS
	SELECT DISTINCT log FROM t1
	EXCEPT
	SELECT n FROM t1 WHERE log=3
	ORDER BY log DESC;
	SELECT * FROM t2;
	}
	} {4 3 2 1 0}
	execsql {DROP TABLE t2}
	do_test select4-3.2 {
	execsql {
	SELECT log FROM t1 WHERE n IN
	(SELECT DISTINCT log FROM t1 EXCEPT
	SELECT n FROM t1 WHERE log=3)
	ORDER BY log;
	}
	} {0 1 2 2}
	do_test select4-3.3 {
	set v [catch {execsql {
	SELECT DISTINCT log FROM t1 ORDER BY log
	EXCEPT
	SELECT n FROM t1 WHERE log=3
	ORDER BY log;
	}} msg]
	lappend v $msg
	} {1 {ORDER BY clause should come after EXCEPT not before}}

	# Intersect operator
	#
	do_test select4-4.1.1 {
	execsql {
	SELECT DISTINCT log FROM t1
	INTERSECT
	SELECT n FROM t1 WHERE log=3
	ORDER BY log;
	}
	} {5}
	do_test select4-4.1.2 {
	execsql {
	SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
	INTERSECT
	SELECT n FROM t1 WHERE log=3
	ORDER BY log;
	}
	} {5 6}
	do_test select4-4.1.3 {
	execsql {
	CREATE TABLE t2 AS
	SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
	INTERSECT
	SELECT n FROM t1 WHERE log=3
	ORDER BY log;
	SELECT * FROM t2;
	}
	} {5 6}
	execsql {DROP TABLE t2}
	do_test select4-4.1.4 {
	execsql {
	CREATE TABLE t2 AS
	SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
	INTERSECT
	SELECT n FROM t1 WHERE log=3
	ORDER BY log DESC;
	SELECT * FROM t2;
	}
	} {6 5}
	execsql {DROP TABLE t2}
	do_test select4-4.2 {
	execsql {
	SELECT log FROM t1 WHERE n IN
	(SELECT DISTINCT log FROM t1 INTERSECT
	SELECT n FROM t1 WHERE log=3)
	ORDER BY log;
	}
	} {3}
	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}}

	# 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]
	lappend v $msg
	} {1 {no such table: t2}}
	do_test select4-5.2 {
	set v [catch {execsql {
	SELECT DISTINCT log AS "xyzzy" FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=3
	ORDER BY xyzzy;
	}} msg]
	lappend v $msg
	} {0 {0 1 2 3 4 5 5 6 7 8}}
	do_test select4-5.2b {
	set v [catch {execsql {
	SELECT DISTINCT log AS xyzzy FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=3
	ORDER BY 'xyzzy';
	}} msg]
	lappend v $msg
	} {0 {0 1 2 3 4 5 5 6 7 8}}
	do_test select4-5.2c {
	set v [catch {execsql {
	SELECT DISTINCT log FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=3
	ORDER BY 'xyzzy';
	}} msg]
	lappend v $msg
	} {1 {ORDER BY term number 1 does not match any result column}}
	do_test select4-5.2d {
	set v [catch {execsql {
	SELECT DISTINCT log FROM t1
	INTERSECT
	SELECT n FROM t1 WHERE log=3
	ORDER BY 'xyzzy';
	}} msg]
	lappend v $msg
	} {1 {ORDER BY term number 1 does not match any result column}}
	do_test select4-5.2e {
	set v [catch {execsql {
	SELECT DISTINCT log FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=3
	ORDER BY n;
	}} msg]
	lappend v $msg
	} {0 {0 1 2 3 4 5 5 6 7 8}}
	do_test select4-5.2f {
	catchsql {
	SELECT DISTINCT log FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=3
	ORDER BY log;
	}
	} {0 {0 1 2 3 4 5 5 6 7 8}}
	do_test select4-5.2g {
	catchsql {
	SELECT DISTINCT log FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=3
	ORDER BY 1;
	}
	} {0 {0 1 2 3 4 5 5 6 7 8}}
	do_test select4-5.2h {
	catchsql {
	SELECT DISTINCT log FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=3
	ORDER BY 2;
	}
	} {1 {ORDER BY position 2 should be between 1 and 1}}
	do_test select4-5.2i {
	catchsql {
	SELECT DISTINCT 1, log FROM t1
	UNION ALL
	SELECT 2, n FROM t1 WHERE log=3
	ORDER BY 2, 1;
	}
	} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
	do_test select4-5.2j {
	catchsql {
	SELECT DISTINCT 1, log FROM t1
	UNION ALL
	SELECT 2, n FROM t1 WHERE log=3
	ORDER BY 1, 2 DESC;
	}
	} {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
	do_test select4-5.2k {
	catchsql {
	SELECT DISTINCT 1, log FROM t1
	UNION ALL
	SELECT 2, n FROM t1 WHERE log=3
	ORDER BY n, 1;
	}
	} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
	do_test select4-5.3 {
	set v [catch {execsql {
	SELECT DISTINCT log, n FROM t1
	UNION ALL
	SELECT n FROM t1 WHERE log=3
	ORDER BY log;
	}} msg]
	lappend v $msg
	} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
	do_test select4-5.4 {
	set v [catch {execsql {
	SELECT log FROM t1 WHERE n=2
	UNION ALL
	SELECT log FROM t1 WHERE n=3
	UNION ALL
	SELECT log FROM t1 WHERE n=4
	UNION ALL
	SELECT log FROM t1 WHERE n=5
	ORDER BY log;
	}} msg]
	lappend v $msg
	} {0 {1 2 2 3}}

	do_test select4-6.1 {
	execsql {
	SELECT log, count(*) as cnt FROM t1 GROUP BY log
	UNION
	SELECT log, n FROM t1 WHERE n=7
	ORDER BY cnt, log;
	}
	} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
	do_test select4-6.2 {
	execsql {
	SELECT log, count(*) FROM t1 GROUP BY log
	UNION
	SELECT log, n FROM t1 WHERE n=7
	ORDER BY count(*), log;
	}
	} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}

	# NULLs are indistinct for the UNION operator.
	# Make sure the UNION operator recognizes this
	#
	do_test select4-6.3 {
	execsql {
	SELECT NULL UNION SELECT NULL UNION
	SELECT 1 UNION SELECT 2 AS 'x'
	ORDER BY x;
	}
	} {{} 1 2}
	do_test select4-6.3.1 {
	execsql {
	SELECT NULL UNION ALL SELECT NULL UNION ALL
	SELECT 1 UNION ALL SELECT 2 AS 'x'
	ORDER BY x;
	}
	} {{} {} 1 2}

	# Make sure the DISTINCT keyword treats NULLs as indistinct.
	#
	do_test select4-6.4 {
	execsql {
	SELECT * FROM (
	SELECT NULL, 1 UNION ALL SELECT NULL, 1
	);
	}
	} {{} 1 {} 1}
	do_test select4-6.5 {
	execsql {
	SELECT DISTINCT * FROM (
	SELECT NULL, 1 UNION ALL SELECT NULL, 1
	);
	}
	} {{} 1}
	do_test select4-6.6 {
	execsql {
	SELECT DISTINCT * FROM (
	SELECT 1,2 UNION ALL SELECT 1,2
	);
	}
	} {1 2}

	# Test distinctness of NULL in other ways.
	#
	do_test select4-6.7 {
	execsql {
	SELECT NULL EXCEPT SELECT NULL
	}
	} {}


	# Make sure column names are correct when a compound select appears as
	# an expression in the WHERE clause.
	#
	do_test select4-7.1 {
	execsql {
	CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;
	SELECT * FROM t2 ORDER BY x;
	}
	} {0 1 1 1 2 2 3 4 4 8 5 15}
	do_test select4-7.2 {
	execsql2 {
	SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)
	ORDER BY n
	}
	} {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}
	do_test select4-7.3 {
	execsql2 {
	SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)
	ORDER BY n LIMIT 2
	}
	} {n 6 log 3 n 7 log 3}
	do_test select4-7.4 {
	execsql2 {
	SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
	ORDER BY n LIMIT 2
	}
	} {n 1 log 0 n 2 log 1}

	# Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
	#
	do_test select4-8.1 {
	execsql {
	BEGIN;
	CREATE TABLE t3(a text, b float, c text);
	INSERT INTO t3 VALUES(1, 1.1, '1.1');
	INSERT INTO t3 VALUES(2, 1.10, '1.10');
	INSERT INTO t3 VALUES(3, 1.10, '1.1');
	INSERT INTO t3 VALUES(4, 1.1, '1.10');
	INSERT INTO t3 VALUES(5, 1.2, '1.2');
	INSERT INTO t3 VALUES(6, 1.3, '1.3');
	COMMIT;
	}
	execsql {
	SELECT DISTINCT b FROM t3 ORDER BY c;
	}
	} {1.1 1.2 1.3}
	do_test select4-8.2 {
	execsql {
	SELECT DISTINCT c FROM t3 ORDER BY c;
	}
	} {1.1 1.10 1.2 1.3}


	finish_test