summaryrefslogtreecommitdiffstats
path: root/test/rowvalue2.test
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--test/rowvalue2.test279
1 files changed, 279 insertions, 0 deletions
diff --git a/test/rowvalue2.test b/test/rowvalue2.test
new file mode 100644
index 0000000..1502e31
--- /dev/null
+++ b/test/rowvalue2.test
@@ -0,0 +1,279 @@
+# 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 rowvalue2
+
+do_execsql_test 1.0 {
+ CREATE TABLE t1(a, b, c);
+ INSERT INTO t1 VALUES(0, 0, 0);
+ INSERT INTO t1 VALUES(0, 1, 1);
+ INSERT INTO t1 VALUES(1, 0, 2);
+ INSERT INTO t1 VALUES(1, 1, 3);
+
+ CREATE INDEX i1 ON t1(a, b);
+}
+
+do_execsql_test 1.1.1 { SELECT c FROM t1 WHERE (a, b) >= (1, 0) } {2 3}
+do_execsql_test 1.1.2 { SELECT c FROM t1 WHERE (a, b) > (1, 0) } {3}
+
+#-------------------------------------------------------------------------
+
+do_execsql_test 2.0.1 {
+ CREATE TABLE t2(a INTEGER, b INTEGER, c INTEGER, d INTEGER);
+ CREATE INDEX i2 ON t2(a, b, c);
+}
+do_test 2.0.2 {
+ foreach a {0 1 2 3} {
+ foreach b {0 1 2 3} {
+ foreach c {0 1 2 3} {
+ execsql { INSERT INTO t2 VALUES($a, $b, $c, $c + $b*4 + $a*16); }
+ }}}
+} {}
+
+do_execsql_test 2.1 {
+ SELECT d FROM t2 WHERE (a, b) > (2, 2);
+} [db eval { SELECT d FROM t2 WHERE a>2 OR (a=2 AND b>2) }]
+
+do_execsql_test 2.2 {
+ SELECT d FROM t2 WHERE (a, b) >= (2, 2);
+} [db eval { SELECT d FROM t2 WHERE a>2 OR (a=2 AND b>=2) }]
+
+do_execsql_test 2.3 {
+ SELECT d FROM t2 WHERE a=1 AND (b, c) >= (1, 2);
+} [db eval { SELECT d FROM t2 WHERE +a=1 AND (b>1 OR (b==1 AND c>=2)) }]
+
+do_execsql_test 2.4 {
+ SELECT d FROM t2 WHERE a=1 AND (b, c) > (1, 2);
+} [db eval { SELECT d FROM t2 WHERE +a=1 AND (b>1 OR (b==1 AND c>2)) }]
+
+#-------------------------------------------------------------------------
+
+set words {
+airfare airfield airfields airflow airfoil
+airfoils airframe airframes airily airing
+airings airless airlift airlifts airline
+airliner airlines airlock airlocks airmail
+airmails airman airmen airplane airplanes
+
+arraignment arraignments arraigns arrange arranged
+arrangement arrangements arranger arrangers arranges
+arranging arrant array arrayed arrays
+arrears arrest arrested arrester arresters
+arresting arrestingly arrestor arrestors arrests
+
+edifices edit edited editing edition
+editions editor editorial editorially editorials
+editors edits educable educate educated
+educates educating education educational educationally
+educations educator educators eel eelgrass
+}
+
+do_test 3.0 {
+ execsql { CREATE TABLE t3(a, b, c, w); }
+ foreach w $words {
+ set a [string range $w 0 2]
+ set b [string range $w 3 5]
+ set c [string range $w 6 end]
+ execsql { INSERT INTO t3 VALUES($a, $b, $c, $w) }
+ }
+} {}
+
+
+foreach {tn idx} {
+ IDX1 {}
+ IDX2 { CREATE INDEX i3 ON t3(a, b, c); }
+ IDX3 { CREATE INDEX i3 ON t3(a, b); }
+ IDX4 { CREATE INDEX i3 ON t3(a); }
+} {
+ execsql { DROP INDEX IF EXISTS i3 }
+ execsql $idx
+
+ foreach w $words {
+ set a [string range $w 0 2]
+ set b [string range $w 3 5]
+ set c [string range $w 6 end]
+
+ foreach op [list > >= < <= == IS] {
+ do_execsql_test 3.1.$tn.$w.$op [subst -novar {
+ SELECT rowid FROM t3 WHERE (a, b, c) [set op] ($a, $b, $c)
+ ORDER BY +rowid
+ }] [db eval [subst -novar {
+ SELECT rowid FROM t3 WHERE w [set op] $w ORDER BY +rowid
+ }]]
+
+ do_execsql_test 3.1.$tn.$w.$op.subselect [subst -novar {
+ SELECT rowid FROM t3 WHERE (a, b, c) [set op] (
+ SELECT a, b, c FROM t3 WHERE w = $w
+ )
+ ORDER BY +rowid
+ }] [db eval [subst -novar {
+ SELECT rowid FROM t3 WHERE w [set op] $w ORDER BY +rowid
+ }]]
+ }
+
+ }
+}
+
+#-------------------------------------------------------------------------
+#
+
+do_execsql_test 4.0 {
+ CREATE TABLE t4(a, b, c);
+ INSERT INTO t4 VALUES(NULL, NULL, NULL);
+ INSERT INTO t4 VALUES(NULL, NULL, 0);
+ INSERT INTO t4 VALUES(NULL, NULL, 1);
+ INSERT INTO t4 VALUES(NULL, 0, NULL);
+ INSERT INTO t4 VALUES(NULL, 0, 0);
+ INSERT INTO t4 VALUES(NULL, 0, 1);
+ INSERT INTO t4 VALUES(NULL, 1, NULL);
+ INSERT INTO t4 VALUES(NULL, 1, 0);
+ INSERT INTO t4 VALUES(NULL, 1, 1);
+
+ INSERT INTO t4 VALUES( 0, NULL, NULL);
+ INSERT INTO t4 VALUES( 0, NULL, 0);
+ INSERT INTO t4 VALUES( 0, NULL, 1);
+ INSERT INTO t4 VALUES( 0, 0, NULL);
+ INSERT INTO t4 VALUES( 0, 0, 0);
+ INSERT INTO t4 VALUES( 0, 0, 1);
+ INSERT INTO t4 VALUES( 0, 1, NULL);
+ INSERT INTO t4 VALUES( 0, 1, 0);
+ INSERT INTO t4 VALUES( 0, 1, 1);
+
+ INSERT INTO t4 VALUES( 1, NULL, NULL);
+ INSERT INTO t4 VALUES( 1, NULL, 0);
+ INSERT INTO t4 VALUES( 1, NULL, 1);
+ INSERT INTO t4 VALUES( 1, 0, NULL);
+ INSERT INTO t4 VALUES( 1, 0, 0);
+ INSERT INTO t4 VALUES( 1, 0, 1);
+ INSERT INTO t4 VALUES( 1, 1, NULL);
+ INSERT INTO t4 VALUES( 1, 1, 0);
+ INSERT INTO t4 VALUES( 1, 1, 1);
+}
+
+proc make_expr1 {cList vList op} {
+ return "([join $cList ,]) $op ([join $vList ,])"
+}
+
+proc make_expr3 {cList vList op} {
+ set n [llength $cList]
+
+ set aList [list]
+ foreach c [lrange $cList 0 end-1] v [lrange $vList 0 end-1] {
+ lappend aList "$c == $v"
+ }
+ lappend aList "[lindex $cList end] $op [lindex $vList end]"
+
+ return "([join $aList { AND }])"
+}
+
+proc make_expr2 {cList vList op} {
+ set ret ""
+
+ switch -- $op {
+ == - IS {
+ set aList [list]
+ foreach c $cList v $vList { lappend aList "($c $op $v)" }
+ set ret [join $aList " AND "]
+ }
+
+ < - > {
+ set oList [list]
+ for {set i 0} {$i < [llength $cList]} {incr i} {
+ lappend oList [make_expr3 [lrange $cList 0 $i] [lrange $vList 0 $i] $op]
+ }
+ set ret [join $oList " OR "]
+ }
+
+ <= - >= {
+ set o2 [string range $op 0 0]
+ set oList [list]
+ for {set i 0} {$i < [llength $cList]-1} {incr i} {
+ lappend oList [make_expr3 [lrange $cList 0 $i] [lrange $vList 0 $i] $o2]
+ }
+ lappend oList [make_expr3 $cList $vList $op]
+ set ret [join $oList " OR "]
+ }
+
+
+ default {
+ error "Unknown op: $op"
+ }
+ }
+
+ set ret
+}
+
+foreach {tn idx} {
+ IDX1 {}
+ IDX2 { CREATE INDEX i4 ON t4(a, b, c); }
+ IDX3 { CREATE INDEX i4 ON t4(a, b); }
+ IDX4 { CREATE INDEX i4 ON t4(a); }
+} {
+ execsql { DROP INDEX IF EXISTS i4 }
+ execsql $idx
+
+ foreach {tn2 vector} {
+ 1 {0 0 0}
+ 2 {1 1 1}
+ 3 {0 0 NULL}
+ 4 {0 NULL 0}
+ 5 {NULL 0 0}
+ 6 {1 1 NULL}
+ 7 {1 NULL 1}
+ 8 {NULL 1 1}
+ } {
+ foreach op { IS == < <= > >= } {
+ set e1 [make_expr1 {a b c} $vector $op]
+ set e2 [make_expr2 {a b c} $vector $op]
+
+ do_execsql_test 4.$tn.$tn2.$op \
+ "SELECT rowid FROM t4 WHERE $e2 ORDER BY +rowid" [
+ db eval "SELECT rowid FROM t4 WHERE $e1 ORDER BY +rowid"
+ ]
+ }
+ }
+}
+
+do_execsql_test 5.0 {
+ CREATE TABLE r1(a TEXT, iB TEXT);
+ CREATE TABLE r2(x TEXT, zY INTEGER);
+ CREATE INDEX r1ab ON r1(a, iB);
+
+ INSERT INTO r1 VALUES(35, 35);
+ INSERT INTO r2 VALUES(35, 36);
+ INSERT INTO r2 VALUES(35, 4);
+ INSERT INTO r2 VALUES(35, 35);
+} {}
+
+foreach {tn lhs rhs} {
+ 1 {x +zY} {a iB}
+ 2 {x zY} {a iB}
+ 3 {x zY} {a +iB}
+ 4 {+x zY} {a iB}
+ 5 {x zY} {+a iB}
+} {
+ foreach op { IS == < <= > >= } {
+ set e1 [make_expr1 $lhs $rhs $op]
+ set e2 [make_expr2 $lhs $rhs $op]
+ do_execsql_test 5.$tn.$op \
+ "SELECT * FROM r1, r2 WHERE $e2 ORDER BY iB" [db eval \
+ "SELECT * FROM r1, r2 WHERE $e1 ORDER BY iB"
+ ]
+ }
+}
+
+
+finish_test
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-10 08:26:42 +0000