select4.test revision 2
2N/A
2N/A#pragma ident "%Z%%M% %I% %E% SMI"
2N/A
2N/A# 2001 September 15
2N/A#
2N/A# The author disclaims copyright to this source code. In place of
2N/A# a legal notice, here is a blessing:
2N/A#
2N/A# May you do good and not evil.
2N/A# May you find forgiveness for yourself and forgive others.
2N/A# May you share freely, never taking more than you give.
2N/A#
2N/A#***********************************************************************
2N/A# This file implements regression tests for SQLite library. The
2N/A# focus of this file is testing UNION, INTERSECT and EXCEPT operators
2N/A# in SELECT statements.
2N/A#
2N/A# $Id: select4.test,v 1.13 2003/02/02 12:41:27 drh Exp $
2N/A
2N/Aset testdir [file dirname $argv0]
2N/Asource $testdir/tester.tcl
2N/A
2N/A# Build some test data
2N/A#
2N/Aset fd [open data1.txt w]
2N/Afor {set i 1} {$i<32} {incr i} {
2N/A for {set j 0} {pow(2,$j)<$i} {incr j} {}
2N/A puts $fd "$i\t$j"
2N/A}
2N/Aclose $fd
2N/Aexecsql {
2N/A CREATE TABLE t1(n int, log int);
2N/A COPY t1 FROM 'data1.txt'
2N/A}
2N/Afile delete data1.txt
2N/A
2N/Ado_test select4-1.0 {
2N/A execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
2N/A} {0 1 2 3 4 5}
2N/A
2N/A# Union All operator
2N/A#
2N/Ado_test select4-1.1a {
2N/A lsort [execsql {SELECT DISTINCT log FROM t1}]
2N/A} {0 1 2 3 4 5}
2N/Ado_test select4-1.1b {
2N/A lsort [execsql {SELECT n FROM t1 WHERE log=3}]
2N/A} {5 6 7 8}
2N/Ado_test select4-1.1c {
2N/A execsql {
2N/A SELECT DISTINCT log FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }
2N/A} {0 1 2 3 4 5 5 6 7 8}
2N/Ado_test select4-1.1d {
2N/A execsql {
2N/A CREATE TABLE t2 AS
2N/A SELECT DISTINCT log FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A SELECT * FROM t2;
2N/A }
2N/A} {0 1 2 3 4 5 5 6 7 8}
2N/Aexecsql {DROP TABLE t2}
2N/Ado_test select4-1.1e {
2N/A execsql {
2N/A CREATE TABLE t2 AS
2N/A SELECT DISTINCT log FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log DESC;
2N/A SELECT * FROM t2;
2N/A }
2N/A} {8 7 6 5 5 4 3 2 1 0}
2N/Aexecsql {DROP TABLE t2}
2N/Ado_test select4-1.1f {
2N/A execsql {
2N/A SELECT DISTINCT log FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=2
2N/A }
2N/A} {0 1 2 3 4 5 3 4}
2N/Ado_test select4-1.1g {
2N/A execsql {
2N/A CREATE TABLE t2 AS
2N/A SELECT DISTINCT log FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=2;
2N/A SELECT * FROM t2;
2N/A }
2N/A} {0 1 2 3 4 5 3 4}
2N/Aexecsql {DROP TABLE t2}
2N/Ado_test select4-1.2 {
2N/A execsql {
2N/A SELECT log FROM t1 WHERE n IN
2N/A (SELECT DISTINCT log FROM t1 UNION ALL
2N/A SELECT n FROM t1 WHERE log=3)
2N/A ORDER BY log;
2N/A }
2N/A} {0 1 2 2 3 3 3 3}
2N/Ado_test select4-1.3 {
2N/A set v [catch {execsql {
2N/A SELECT DISTINCT log FROM t1 ORDER BY log
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }} msg]
2N/A lappend v $msg
2N/A} {1 {ORDER BY clause should come after UNION ALL not before}}
2N/A
2N/A# Union operator
2N/A#
2N/Ado_test select4-2.1 {
2N/A execsql {
2N/A SELECT DISTINCT log FROM t1
2N/A UNION
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }
2N/A} {0 1 2 3 4 5 6 7 8}
2N/Ado_test select4-2.2 {
2N/A execsql {
2N/A SELECT log FROM t1 WHERE n IN
2N/A (SELECT DISTINCT log FROM t1 UNION
2N/A SELECT n FROM t1 WHERE log=3)
2N/A ORDER BY log;
2N/A }
2N/A} {0 1 2 2 3 3 3 3}
2N/Ado_test select4-2.3 {
2N/A set v [catch {execsql {
2N/A SELECT DISTINCT log FROM t1 ORDER BY log
2N/A UNION
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }} msg]
2N/A lappend v $msg
2N/A} {1 {ORDER BY clause should come after UNION not before}}
2N/A
2N/A# Except operator
2N/A#
2N/Ado_test select4-3.1.1 {
2N/A execsql {
2N/A SELECT DISTINCT log FROM t1
2N/A EXCEPT
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }
2N/A} {0 1 2 3 4}
2N/Ado_test select4-3.1.2 {
2N/A execsql {
2N/A CREATE TABLE t2 AS
2N/A SELECT DISTINCT log FROM t1
2N/A EXCEPT
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A SELECT * FROM t2;
2N/A }
2N/A} {0 1 2 3 4}
2N/Aexecsql {DROP TABLE t2}
2N/Ado_test select4-3.1.3 {
2N/A execsql {
2N/A CREATE TABLE t2 AS
2N/A SELECT DISTINCT log FROM t1
2N/A EXCEPT
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log DESC;
2N/A SELECT * FROM t2;
2N/A }
2N/A} {4 3 2 1 0}
2N/Aexecsql {DROP TABLE t2}
2N/Ado_test select4-3.2 {
2N/A execsql {
2N/A SELECT log FROM t1 WHERE n IN
2N/A (SELECT DISTINCT log FROM t1 EXCEPT
2N/A SELECT n FROM t1 WHERE log=3)
2N/A ORDER BY log;
2N/A }
2N/A} {0 1 2 2}
2N/Ado_test select4-3.3 {
2N/A set v [catch {execsql {
2N/A SELECT DISTINCT log FROM t1 ORDER BY log
2N/A EXCEPT
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }} msg]
2N/A lappend v $msg
2N/A} {1 {ORDER BY clause should come after EXCEPT not before}}
2N/A
2N/A# Intersect operator
2N/A#
2N/Ado_test select4-4.1.1 {
2N/A execsql {
2N/A SELECT DISTINCT log FROM t1
2N/A INTERSECT
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }
2N/A} {5}
2N/Ado_test select4-4.1.2 {
2N/A execsql {
2N/A SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
2N/A INTERSECT
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }
2N/A} {5 6}
2N/Ado_test select4-4.1.3 {
2N/A execsql {
2N/A CREATE TABLE t2 AS
2N/A SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
2N/A INTERSECT
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A SELECT * FROM t2;
2N/A }
2N/A} {5 6}
2N/Aexecsql {DROP TABLE t2}
2N/Ado_test select4-4.1.4 {
2N/A execsql {
2N/A CREATE TABLE t2 AS
2N/A SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
2N/A INTERSECT
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log DESC;
2N/A SELECT * FROM t2;
2N/A }
2N/A} {6 5}
2N/Aexecsql {DROP TABLE t2}
2N/Ado_test select4-4.2 {
2N/A execsql {
2N/A SELECT log FROM t1 WHERE n IN
2N/A (SELECT DISTINCT log FROM t1 INTERSECT
2N/A SELECT n FROM t1 WHERE log=3)
2N/A ORDER BY log;
2N/A }
2N/A} {3}
2N/Ado_test select4-4.3 {
2N/A set v [catch {execsql {
2N/A SELECT DISTINCT log FROM t1 ORDER BY log
2N/A INTERSECT
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }} msg]
2N/A lappend v $msg
2N/A} {1 {ORDER BY clause should come after INTERSECT not before}}
2N/A
2N/A# Various error messages while processing UNION or INTERSECT
2N/A#
2N/Ado_test select4-5.1 {
2N/A set v [catch {execsql {
2N/A SELECT DISTINCT log FROM t2
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }} msg]
2N/A lappend v $msg
2N/A} {1 {no such table: t2}}
2N/Ado_test select4-5.2 {
2N/A set v [catch {execsql {
2N/A SELECT DISTINCT log AS "xyzzy" FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY xyzzy;
2N/A }} msg]
2N/A lappend v $msg
2N/A} {0 {0 1 2 3 4 5 5 6 7 8}}
2N/Ado_test select4-5.2b {
2N/A set v [catch {execsql {
2N/A SELECT DISTINCT log AS xyzzy FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY 'xyzzy';
2N/A }} msg]
2N/A lappend v $msg
2N/A} {0 {0 1 2 3 4 5 5 6 7 8}}
2N/Ado_test select4-5.2c {
2N/A set v [catch {execsql {
2N/A SELECT DISTINCT log FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY 'xyzzy';
2N/A }} msg]
2N/A lappend v $msg
2N/A} {1 {ORDER BY term number 1 does not match any result column}}
2N/Ado_test select4-5.2d {
2N/A set v [catch {execsql {
2N/A SELECT DISTINCT log FROM t1
2N/A INTERSECT
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY 'xyzzy';
2N/A }} msg]
2N/A lappend v $msg
2N/A} {1 {ORDER BY term number 1 does not match any result column}}
2N/Ado_test select4-5.2e {
2N/A set v [catch {execsql {
2N/A SELECT DISTINCT log FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY n;
2N/A }} msg]
2N/A lappend v $msg
2N/A} {0 {0 1 2 3 4 5 5 6 7 8}}
2N/Ado_test select4-5.2f {
2N/A catchsql {
2N/A SELECT DISTINCT log FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }
2N/A} {0 {0 1 2 3 4 5 5 6 7 8}}
2N/Ado_test select4-5.2g {
2N/A catchsql {
2N/A SELECT DISTINCT log FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY 1;
2N/A }
2N/A} {0 {0 1 2 3 4 5 5 6 7 8}}
2N/Ado_test select4-5.2h {
2N/A catchsql {
2N/A SELECT DISTINCT log FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY 2;
2N/A }
2N/A} {1 {ORDER BY position 2 should be between 1 and 1}}
2N/Ado_test select4-5.2i {
2N/A catchsql {
2N/A SELECT DISTINCT 1, log FROM t1
2N/A UNION ALL
2N/A SELECT 2, n FROM t1 WHERE log=3
2N/A ORDER BY 2, 1;
2N/A }
2N/A} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
2N/Ado_test select4-5.2j {
2N/A catchsql {
2N/A SELECT DISTINCT 1, log FROM t1
2N/A UNION ALL
2N/A SELECT 2, n FROM t1 WHERE log=3
2N/A ORDER BY 1, 2 DESC;
2N/A }
2N/A} {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
2N/Ado_test select4-5.2k {
2N/A catchsql {
2N/A SELECT DISTINCT 1, log FROM t1
2N/A UNION ALL
2N/A SELECT 2, n FROM t1 WHERE log=3
2N/A ORDER BY n, 1;
2N/A }
2N/A} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
2N/Ado_test select4-5.3 {
2N/A set v [catch {execsql {
2N/A SELECT DISTINCT log, n FROM t1
2N/A UNION ALL
2N/A SELECT n FROM t1 WHERE log=3
2N/A ORDER BY log;
2N/A }} msg]
2N/A lappend v $msg
2N/A} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
2N/Ado_test select4-5.4 {
2N/A set v [catch {execsql {
2N/A SELECT log FROM t1 WHERE n=2
2N/A UNION ALL
2N/A SELECT log FROM t1 WHERE n=3
2N/A UNION ALL
2N/A SELECT log FROM t1 WHERE n=4
2N/A UNION ALL
2N/A SELECT log FROM t1 WHERE n=5
2N/A ORDER BY log;
2N/A }} msg]
2N/A lappend v $msg
2N/A} {0 {1 2 2 3}}
2N/A
2N/Ado_test select4-6.1 {
2N/A execsql {
2N/A SELECT log, count(*) as cnt FROM t1 GROUP BY log
2N/A UNION
2N/A SELECT log, n FROM t1 WHERE n=7
2N/A ORDER BY cnt, log;
2N/A }
2N/A} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
2N/Ado_test select4-6.2 {
2N/A execsql {
2N/A SELECT log, count(*) FROM t1 GROUP BY log
2N/A UNION
2N/A SELECT log, n FROM t1 WHERE n=7
2N/A ORDER BY count(*), log;
2N/A }
2N/A} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
2N/A
2N/A# NULLs are indistinct for the UNION operator.
2N/A# Make sure the UNION operator recognizes this
2N/A#
2N/Ado_test select4-6.3 {
2N/A execsql {
2N/A SELECT NULL UNION SELECT NULL UNION
2N/A SELECT 1 UNION SELECT 2 AS 'x'
2N/A ORDER BY x;
2N/A }
2N/A} {{} 1 2}
2N/Ado_test select4-6.3.1 {
2N/A execsql {
2N/A SELECT NULL UNION ALL SELECT NULL UNION ALL
2N/A SELECT 1 UNION ALL SELECT 2 AS 'x'
2N/A ORDER BY x;
2N/A }
2N/A} {{} {} 1 2}
2N/A
2N/A# Make sure the DISTINCT keyword treats NULLs as indistinct.
2N/A#
2N/Ado_test select4-6.4 {
2N/A execsql {
2N/A SELECT * FROM (
2N/A SELECT NULL, 1 UNION ALL SELECT NULL, 1
2N/A );
2N/A }
2N/A} {{} 1 {} 1}
2N/Ado_test select4-6.5 {
2N/A execsql {
2N/A SELECT DISTINCT * FROM (
2N/A SELECT NULL, 1 UNION ALL SELECT NULL, 1
2N/A );
2N/A }
2N/A} {{} 1}
2N/Ado_test select4-6.6 {
2N/A execsql {
2N/A SELECT DISTINCT * FROM (
2N/A SELECT 1,2 UNION ALL SELECT 1,2
2N/A );
2N/A }
2N/A} {1 2}
2N/A
2N/A# Test distinctness of NULL in other ways.
2N/A#
2N/Ado_test select4-6.7 {
2N/A execsql {
2N/A SELECT NULL EXCEPT SELECT NULL
2N/A }
2N/A} {}
2N/A
2N/A
2N/A# Make sure column names are correct when a compound select appears as
2N/A# an expression in the WHERE clause.
2N/A#
2N/Ado_test select4-7.1 {
2N/A execsql {
2N/A CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;
2N/A SELECT * FROM t2 ORDER BY x;
2N/A }
2N/A} {0 1 1 1 2 2 3 4 4 8 5 15}
2N/Ado_test select4-7.2 {
2N/A execsql2 {
2N/A SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)
2N/A ORDER BY n
2N/A }
2N/A} {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}
2N/Ado_test select4-7.3 {
2N/A execsql2 {
2N/A SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)
2N/A ORDER BY n LIMIT 2
2N/A }
2N/A} {n 6 log 3 n 7 log 3}
2N/Ado_test select4-7.4 {
2N/A execsql2 {
2N/A SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
2N/A ORDER BY n LIMIT 2
2N/A }
2N/A} {n 1 log 0 n 2 log 1}
2N/A
2N/A# Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
2N/A#
2N/Ado_test select4-8.1 {
2N/A execsql {
2N/A BEGIN;
2N/A CREATE TABLE t3(a text, b float, c text);
2N/A INSERT INTO t3 VALUES(1, 1.1, '1.1');
2N/A INSERT INTO t3 VALUES(2, 1.10, '1.10');
2N/A INSERT INTO t3 VALUES(3, 1.10, '1.1');
2N/A INSERT INTO t3 VALUES(4, 1.1, '1.10');
2N/A INSERT INTO t3 VALUES(5, 1.2, '1.2');
2N/A INSERT INTO t3 VALUES(6, 1.3, '1.3');
2N/A COMMIT;
2N/A }
2N/A execsql {
2N/A SELECT DISTINCT b FROM t3 ORDER BY c;
2N/A }
2N/A} {1.1 1.2 1.3}
2N/Ado_test select4-8.2 {
2N/A execsql {
2N/A SELECT DISTINCT c FROM t3 ORDER BY c;
2N/A }
2N/A} {1.1 1.10 1.2 1.3}
2N/A
2N/A
2N/Afinish_test