# 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.15 2004/05/14 11:00:53 danielk1977 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