# 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}}
# 2016-10-27: https://www.sqlite.org/src/tktview/fef4bb4bd9185ec8f
# Incorrect result from a LEFT JOIN with a row-value constraint
#
do_execsql_test 12.1 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(x,y); INSERT INTO t2 VALUES(3,4);
SELECT *,'x' FROM t1 LEFT JOIN t2 ON (a,b)=(x,y);
} {1 2 {} {} x}
foreach {tn sql} {
0 "SELECT (1,2) AS x WHERE x=3"
1 "SELECT (1,2) BETWEEN 1 AND 2"
2 "SELECT 1 BETWEEN (1,2) AND 2"
3 "SELECT 2 BETWEEN 1 AND (1,2)"
4 "SELECT (1,2) FROM (SELECT 1) ORDER BY 1"
5 "SELECT (1,2) FROM (SELECT 1) GROUP BY 1"
} {
do_catchsql_test 13.$tn $sql {1 {row value misused}}
}
do_execsql_test 14.0 {
CREATE TABLE t12(x);
INSERT INTO t12 VALUES(2), (4);
}
do_execsql_test 14.1 "SELECT 1 WHERE (2,2) BETWEEN (1,1) AND (3,3)" 1
do_execsql_test 14.2 "SELECT CASE (2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1
do_execsql_test 14.3 "SELECT CASE (SELECT 2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1
do_execsql_test 14.4 "SELECT 1 WHERE (SELECT 2,2) BETWEEN (1,1) AND (3,3)" 1
do_execsql_test 14.5 "SELECT 1 FROM t12 WHERE (x,1) BETWEEN (1,1) AND (3,3)" 1
do_execsql_test 14.6 {
SELECT 1 FROM t12 WHERE (1,x) BETWEEN (1,1) AND (3,3)
} {1 1}
#-------------------------------------------------------------------------
# Test that errors are not concealed by the SELECT flattening or
# WHERE-clause push-down optimizations.
do_execsql_test 14.1 {
CREATE TABLE x1(a PRIMARY KEY, b);
CREATE TABLE x2(a INTEGER PRIMARY KEY, b);
}
foreach {tn n sql} {
1 0 "SELECT * FROM (SELECT (1, 1) AS c FROM x1) WHERE c=1"
2 2 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9) AS y) WHERE y<1"
3 3 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9,10) AS y) WHERE y<1"
4 0 "SELECT * FROM (SELECT (a, b) AS c FROM x1), x2 WHERE c=a"
5 0 "SELECT * FROM (SELECT a AS c, (1, 2, 3) FROM x1), x2 WHERE c=a"
6 0 "SELECT * FROM (SELECT 1 AS c, (1, 2, 3) FROM x1) WHERE c=1"
} {
if {$n==0} {
set err "row value misused"
} else {
set err "sub-select returns $n columns - expected 1"
}
do_catchsql_test 14.2.$tn $sql [list 1 $err]
}
#--------------------------------------------------------------------------
# Test for vector size mismatches concealed by unexpanded subqueries.
#
do_catchsql_test 15.1 {
DETACH (SELECT * FROM (SELECT 1,2))<3;
} {1 {row value misused}}
do_catchsql_test 15.2 {
UPDATE x1 SET a=(SELECT * FROM (SELECT b,2))<3;
} {1 {row value misused}}
do_catchsql_test 15.3 {
UPDATE x1 SET a=NULL WHERE a<(SELECT * FROM (SELECT b,2));
} {1 {sub-select returns 2 columns - expected 1}}
do_catchsql_test 15.4 {
DELETE FROM x1 WHERE a<(SELECT * FROM (SELECT b,2));
} {1 {sub-select returns 2 columns - expected 1}}
do_catchsql_test 15.5 {
INSERT INTO x1(a,b) VALUES(1,(SELECT * FROM (SELECT 1,2))<3);
} {1 {row value misused}}
#-------------------------------------------------------------------------
# Row-values used in UPDATE statements within TRIGGERs
#
# Ticket https://www.sqlite.org/src/info/8c9458e703666e1a
#
do_execsql_test 16.1 {
CREATE TABLE t16a(a,b,c);
INSERT INTO t16a VALUES(1,2,3);
CREATE TABLE t16b(x);
INSERT INTO t16b(x) VALUES(1);
CREATE TRIGGER t16r AFTER UPDATE ON t16b BEGIN
UPDATE t16a SET (a,b,c)=(SELECT new.x,new.x+1,new.x+2);
END;
UPDATE t16b SET x=7;
SELECT * FROM t16a;
} {7 8 9}
do_execsql_test 16.2 {
UPDATE t16b SET x=97;
SELECT * FROM t16a;
} {97 98 99}
do_execsql_test 16.3 {
CREATE TABLE t16c(a, b, c, d, e);
INSERT INTO t16c VALUES(1, 'a', 'b', 'c', 'd');
CREATE TRIGGER t16c1 AFTER INSERT ON t16c BEGIN
UPDATE t16c SET (c, d) = (SELECT 'A', 'B'), (e, b) = (SELECT 'C', 'D')
WHERE a = new.a-1;
END;
SELECT * FROM t16c;
} {1 a b c d}
do_execsql_test 16.4 {
INSERT INTO t16c VALUES(2, 'w', 'x', 'y', 'z');
SELECT * FROM t16c;
} {
1 D A B C
2 w x y z
}
do_execsql_test 16.5 {
DROP TRIGGER t16c1;
PRAGMA recursive_triggers = 1;
INSERT INTO t16c VALUES(3, 'i', 'ii', 'iii', 'iv');
CREATE TRIGGER t16c1 AFTER UPDATE ON t16c WHEN new.a>1 BEGIN
UPDATE t16c SET (e, d) = (
SELECT b, c FROM t16c WHERE a = new.a-1
), (c, b) = (
SELECT d, e FROM t16c WHERE a = new.a-1
) WHERE a = new.a-1;
END;
UPDATE t16c SET a=a WHERE a=3;
SELECT * FROM t16c;
} {
1 C B A D
2 z y x w
3 i ii iii iv
}
finish_test