| # 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 |