# 2016 June 17 # # 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 the SELECT statement. # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix rowvalue do_execsql_test 0.0 { CREATE TABLE one(o); INSERT INTO one VALUES(1); } foreach {tn v1 v2 eq ne is isnot} { 1 "1, 2, 3" "1, 2, 3" 1 0 1 0 2 "1, 0, 3" "1, 2, 3" 0 1 0 1 3 "1, 2, NULL" "1, 2, 3" {} {} 0 1 4 "1, 2, NULL" "1, 2, NULL" {} {} 1 0 5 "NULL, NULL, NULL" "NULL, NULL, NULL" {} {} 1 0 6 "1, NULL, 1" "1, 1, 1" {} {} 0 1 7 "1, NULL, 1" "1, 1, 2" 0 1 0 1 } { do_execsql_test 1.$tn.eq "SELECT ($v1) == ($v2)" [list $eq] do_execsql_test 1.$tn.ne "SELECT ($v1) != ($v2)" [list $ne] do_execsql_test 1.$tn.is "SELECT ($v1) IS ($v2)" [list $is] do_execsql_test 1.$tn.isnot "SELECT ($v1) IS NOT ($v2)" [list $isnot] do_execsql_test 1.$tn.2.eq "SELECT (SELECT $v1) == (SELECT $v2)" [list $eq] do_execsql_test 1.$tn.2.ne "SELECT (SELECT $v1) != (SELECT $v2)" [list $ne] } foreach {tn v1 v2 lt gt le ge} { 1 "(1, 1, 3)" "(1, 2, 3)" 1 0 1 0 2 "(1, 2, 3)" "(1, 2, 3)" 0 0 1 1 3 "(1, 3, 3)" "(1, 2, 3)" 0 1 0 1 4 "(1, NULL, 3)" "(1, 2, 3)" {} {} {} {} 5 "(1, 3, 3)" "(1, NULL, 3)" {} {} {} {} 6 "(1, NULL, 3)" "(1, NULL, 3)" {} {} {} {} } { foreach {tn2 expr res} [list \ 2.$tn.lt "$v1 < $v2" $lt \ 2.$tn.gt "$v1 > $v2" $gt \ 2.$tn.le "$v1 <= $v2" $le \ 2.$tn.ge "$v1 >= $v2" $ge \ ] { do_execsql_test $tn2 "SELECT $expr" [list $res] set map(0) [list] set map() [list] set map(1) [list 1] do_execsql_test $tn2.where1 "SELECT * FROM one WHERE $expr" $map($res) set map(0) [list 1] set map() [list] set map(1) [list] do_execsql_test $tn2.where2 "SELECT * FROM one WHERE NOT $expr" $map($res) } } do_execsql_test 3.0 { CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(1, 1); INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(2, 3); INSERT INTO t1 VALUES(2, 4); INSERT INTO t1 VALUES(3, 5); INSERT INTO t1 VALUES(3, 6); } foreach {tn r order} { 1 "(1, 1)" "ORDER BY y" 2 "(1, 1)" "ORDER BY x, y" 3 "(1, 2)" "ORDER BY x, y DESC" 4 "(3, 6)" "ORDER BY x DESC, y DESC" 5 "((3, 5))" "ORDER BY x DESC, y" 6 "(SELECT 3, 5)" "ORDER BY x DESC, y" } { do_execsql_test 3.$tn.1 "SELECT $r == (SELECT x,y FROM t1 $order)" 1 do_execsql_test 3.$tn.2 "SELECT $r == (SELECT * FROM t1 $order)" 1 do_execsql_test 3.$tn.3 " SELECT (SELECT * FROM t1 $order) == (SELECT * FROM t1 $order) " 1 do_execsql_test 3.$tn.4 " SELECT (SELECT 0, 0) == (SELECT * FROM t1 $order) " 0 } foreach {tn expr res} { 1 {(2, 2) BETWEEN (2, 2) AND (3, 3)} 1 2 {(2, 2) BETWEEN (2, NULL) AND (3, 3)} {} 3 {(2, 2) BETWEEN (3, NULL) AND (3, 3)} 0 } { do_execsql_test 4.$tn "SELECT $expr" [list $res] } foreach {tn expr res} { 1 {(2, 4) IN (SELECT * FROM t1)} 1 2 {(3, 4) IN (SELECT * FROM t1)} 0 3 {(NULL, 4) IN (SELECT * FROM t1)} {} 4 {(NULL, 0) IN (SELECT * FROM t1)} 0 5 {(NULL, 4) NOT IN (SELECT * FROM t1)} {} 6 {(NULL, 0) NOT IN (SELECT * FROM t1)} 1 } { do_execsql_test 5.$tn "SELECT $expr" [list $res] } do_execsql_test 6.0 { CREATE TABLE hh(a, b, c); INSERT INTO hh VALUES('abc', 1, 'i'); INSERT INTO hh VALUES('ABC', 1, 'ii'); INSERT INTO hh VALUES('def', 2, 'iii'); INSERT INTO hh VALUES('DEF', 2, 'iv'); INSERT INTO hh VALUES('GHI', 3, 'v'); INSERT INTO hh VALUES('ghi', 3, 'vi'); CREATE INDEX hh_ab ON hh(a, b); } do_execsql_test 6.1 { SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1); } {i} do_execsql_test 6.2 { SELECT c FROM hh WHERE (a, b) = (SELECT 'abc' COLLATE nocase, 1); } {i} do_execsql_test 6.3 { SELECT c FROM hh WHERE a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1); } {i} do_execsql_test 6.4 { SELECT c FROM hh WHERE +a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1); } {i} do_execsql_test 6.5 { SELECT c FROM hh WHERE a = (SELECT 'abc') COLLATE nocase AND b = (SELECT 1); } {i ii} do_catchsql_test 6.6 { SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1) COLLATE nocase; } {1 {row value misused}} do_catchsql_test 6.7 { SELECT c FROM hh WHERE (a, b) = 1; } {1 {row value misused}} do_execsql_test 6.8 { SELECT c FROM hh WHERE (a COLLATE nocase, b) = (SELECT 'def', 2); } {iii iv} do_execsql_test 6.9 { SELECT c FROM hh WHERE (a COLLATE nocase, b) IS NOT (SELECT 'def', 2); } {i ii v vi} do_execsql_test 6.10 { SELECT c FROM hh WHERE (b, a) = (SELECT 2, 'def'); } {iii} do_execsql_test 7.0 { CREATE TABLE xy(i INTEGER PRIMARY KEY, j, k); INSERT INTO xy VALUES(1, 1, 1); INSERT INTO xy VALUES(2, 2, 2); INSERT INTO xy VALUES(3, 3, 3); INSERT INTO xy VALUES(4, 4, 4); } foreach {tn sql res eqp} { 1 "SELECT * FROM xy WHERE (i, j) IS (2, 2)" {2 2 2} "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid=?)}" 2 "SELECT * FROM xy WHERE (k, j) < (2, 3)" {1 1 1 2 2 2} "0 0 0 {SCAN TABLE xy}" 3 "SELECT * FROM xy WHERE (i, j) < (2, 3)" {1 1 1 2 2 2} "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid<?)}" 4 "SELECT * FROM xy WHERE (i, j) > (2, 1)" {2 2 2 3 3 3 4 4 4} "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}" 5 "SELECT * FROM xy WHERE (i, j) > ('2', 1)" {2 2 2 3 3 3 4 4 4} "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}" } { do_eqp_test 7.$tn.1 $sql $eqp do_execsql_test 7.$tn.2 $sql $res } do_execsql_test 8.0 { CREATE TABLE j1(a); } do_execsql_test 8.1 { SELECT * FROM j1 WHERE (select min(a) FROM j1) IN (?, ?, ?) } do_execsql_test 9.0 { CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c); INSERT INTO t2 VALUES(1, 1, 1); INSERT INTO t2 VALUES(2, 2, 2); INSERT INTO t2 VALUES(3, 3, 3); INSERT INTO t2 VALUES(4, 4, 4); INSERT INTO t2 VALUES(5, 5, 5); } foreach {tn q res} { 1 "(a, b) > (2, 1)" {2 3 4 5} 2 "(a, b) > (2, 2)" {3 4 5} 3 "(a, b) < (4, 5)" {1 2 3 4} 4 "(a, b) < (4, 3)" {1 2 3} } { do_execsql_test 9.$tn "SELECT c FROM t2 WHERE $q" $res } do_execsql_test 10.0 { CREATE TABLE dual(dummy); INSERT INTO dual(dummy) VALUES('X'); CREATE TABLE t3(a TEXT,b TEXT,c TEXT,d TEXT,e TEXT,f TEXT); CREATE INDEX t3x ON t3(b,c,d,e,f); SELECT a FROM t3 WHERE (c,d) IN (SELECT 'c','d' FROM dual) AND (a,b,e) IN (SELECT 'a','b','d' FROM dual); } do_catchsql_test 11.1 { CREATE TABLE t11(a); SELECT * FROM t11 WHERE (a,a)<=1; } {1 {row value misused}} do_catchsql_test 11.2 { SELECT * FROM t11 WHERE (a,a)<1; } {1 {row value misused}} do_catchsql_test 11.3 { SELECT * FROM t11 WHERE (a,a)>=1; } {1 {row value misused}} do_catchsql_test 11.4 { SELECT * FROM t11 WHERE (a,a)>1; } {1 {row value misused}} do_catchsql_test 11.5 { SELECT * FROM t11 WHERE (a,a)==1; } {1 {row value misused}} do_catchsql_test 11.6 { SELECT * FROM t11 WHERE (a,a)<>1; } {1 {row value misused}} do_catchsql_test 11.7 { SELECT * FROM t11 WHERE (a,a) IS 1; } {1 {row value misused}} do_catchsql_test 11.8 { SELECT * FROM t11 WHERE (a,a) IS NOT 1; } {1 {row value misused}} finish_test