# 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 } { 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] } finish_test