# 2001 September 15 # # 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 interaction of manifest types, type affinity # and comparison expressions. # # $Id: types2.test,v 1.1 2004/05/16 11:15:42 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Tests in this file are organized roughly as follows: # # types2-1.*: The '=' operator in the absence of an index. # types2-2.*: The '=' operator implemented using an index. # types2-2.*: The '<' operator implemented using an index. # types2-3.*: The '>' operator in the absense of an index. # execsql { CREATE TABLE t1( i1 INTEGER, i2 INTEGER, n1 NUMERIC, n2 NUMERIC, t1 TEXT, t2 TEXT, o1, o2 ); INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); } proc test_bool {testname vars expr res} { if { $vars != "" } { execsql "UPDATE t1 SET $vars" } foreach {t e r} [list $testname $expr $res] {} do_test $t.1 "execsql {SELECT $e FROM t1}" $r do_test $t.2 "execsql {SELECT 1 FROM t1 WHERE $expr}" [expr $r?"1":""] do_test $t.3 "execsql {SELECT 1 FROM t1 WHERE NOT ($e)}" [expr $r?"":"1"] } # Compare literals against literals test_bool types2-1.1 "" {500 = 500.0} 1 test_bool types2-1.2 "" {'500' = 500.0} 1 test_bool types2-1.3 "" {500 = '500.0'} 1 test_bool types2-1.4 "" {'500' = '500.0'} 0 # Compare literals against a column with TEXT affinity test_bool types2-1.5 {t1=500} {500 = t1} 1 test_bool types2-1.6 {t1=500} {'500' = t1} 1 test_bool types2-1.7 {t1=500} {500.0 = t1} 0 test_bool types2-1.8 {t1=500} {'500.0' = t1} 0 test_bool types2-1.9 {t1='500'} {500 = t1} 1 test_bool types2-1.10 {t1='500'} {'500' = t1} 1 test_bool types2-1.11 {t1='500'} {500.0 = t1} 0 test_bool types2-1.12 {t1='500'} {'500.0' = t1} 0 # Compare literals against a column with NUMERIC affinity test_bool types2-1.13 {n1=500} {500 = n1} 1 test_bool types2-1.14 {n1=500} {'500' = n1} 1 test_bool types2-1.15 {n1=500} {500.0 = n1} 1 test_bool types2-1.16 {n1=500} {'500.0' = n1} 1 test_bool types2-1.17 {n1='500'} {500 = n1} 1 test_bool types2-1.18 {n1='500'} {'500' = n1} 1 test_bool types2-1.19 {n1='500'} {500.0 = n1} 1 test_bool types2-1.20 {n1='500'} {'500.0' = n1} 1 # Compare literals against a column with affinity NONE test_bool types2-1.21 {o1=500} {500 = o1} 1 test_bool types2-1.22 {o1=500} {'500' = o1} 0 test_bool types2-1.23 {o1=500} {500.0 = o1} 1 test_bool types2-1.24 {o1=500} {'500.0' = o1} 0 test_bool types2-1.25 {o1='500'} {500 = o1} 0 test_bool types2-1.26 {o1='500'} {'500' = o1} 1 test_bool types2-1.27 {o1='500'} {500.0 = o1} 0 test_bool types2-1.28 {o1='500'} {'500.0' = o1} 0 set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0'] # 1 2 3 4 5 6 7 8 9 10 11 12 execsql { CREATE TABLE t2(i INTEGER, n NUMERIC, t TEXT, o); CREATE INDEX t2i1 ON t2(i); CREATE INDEX t2i2 ON t2(n); CREATE INDEX t2i3 ON t2(t); CREATE INDEX t2i4 ON t2(o); } foreach v $vals { execsql "INSERT INTO t2 VALUES($v, $v, $v, $v);" } proc test_boolset {testname where set} { set ::tb_sql "SELECT rowid FROM t2 WHERE $where" do_test $testname { lsort -integer [execsql $::tb_sql] } $set } test_boolset types2-2.1 {i = 10} {1 2 3 4} test_boolset types2-2.2 {i = 10.0} {1 2 3 4} test_boolset types2-2.3 {i = '10'} {1 2 3 4} test_boolset types2-2.4 {i = '10.0'} {1 2 3 4} test_boolset types2-2.5 {n = 20} {5 6 7 8} test_boolset types2-2.6 {n = 20.0} {5 6 7 8} test_boolset types2-2.7 {n = '20'} {5 6 7 8} test_boolset types2-2.8 {n = '20.0'} {5 6 7 8} test_boolset types2-2.9 {t = 20} {5 7} test_boolset types2-2.10 {t = 20.0} {6 8} test_boolset types2-2.11 {t = '20'} {5 7} test_boolset types2-2.12 {t = '20.0'} {6 8} test_boolset types2-2.10 {o = 30} {9 10} test_boolset types2-2.11 {o = 30.0} {9 10} test_boolset types2-2.12 {o = '30'} 11 test_boolset types2-2.13 {o = '30.0'} 12 test_boolset types2-3.1 {i < 20} {1 2 3 4} test_boolset types2-3.2 {i < 20.0} {1 2 3 4} test_boolset types2-3.3 {i < '20'} {1 2 3 4} test_boolset types2-3.4 {i < '20.0'} {1 2 3 4} test_boolset types2-3.1 {n < 20} {1 2 3 4} test_boolset types2-3.2 {n < 20.0} {1 2 3 4} test_boolset types2-3.3 {n < '20'} {1 2 3 4} test_boolset types2-3.4 {n < '20.0'} {1 2 3 4} test_boolset types2-3.1 {t < 20} {1 2 3 4} test_boolset types2-3.2 {t < 20.0} {1 2 3 4 5 7} test_boolset types2-3.3 {t < '20'} {1 2 3 4} test_boolset types2-3.4 {t < '20.0'} {1 2 3 4 5 7} test_boolset types2-3.1 {o < 20} {1 2} test_boolset types2-3.2 {o < 20.0} {1 2} test_boolset types2-3.3 {o < '20'} {1 2 3 4 5 6 9 10} test_boolset types2-3.3 {o < '20.0'} {1 2 3 4 5 6 7 9 10} # Compare literals against literals test_bool types2-4.1 "" {500 > 60.0} 1 test_bool types2-4.2 "" {'500' > 60.0} 1 test_bool types2-4.3 "" {500 > '60.0'} 1 test_bool types2-4.4 "" {'500' > '60.0'} 0 # Compare literals against a column with TEXT affinity test_bool types2-4.5 {t1=500.0} {t1 > 500} 1 test_bool types2-4.6 {t1=500.0} {t1 > '500' } 1 test_bool types2-4.7 {t1=500.0} {t1 > 500.0 } 0 test_bool types2-4.8 {t1=500.0} {t1 > '500.0' } 0 test_bool types2-4.9 {t1='500.0'} {t1 > 500 } 1 test_bool types2-4.10 {t1='500.0'} {t1 > '500' } 1 test_bool types2-4.11 {t1='500.0'} {t1 > 500.0 } 0 test_bool types2-4.12 {t1='500.0'} {t1 > '500.0' } 0 # Compare literals against a column with NUMERIC affinity test_bool types2-4.13 {n1=400} {500 > n1} 1 test_bool types2-4.14 {n1=400} {'500' > n1} 1 test_bool types2-4.15 {n1=400} {500.0 > n1} 1 test_bool types2-4.16 {n1=400} {'500.0' > n1} 1 test_bool types2-4.17 {n1='400'} {500 > n1} 1 test_bool types2-4.18 {n1='400'} {'500' > n1} 1 test_bool types2-4.19 {n1='400'} {500.0 > n1} 1 test_bool types2-4.20 {n1='400'} {'500.0' > n1} 1 # Compare literals against a column with affinity NONE test_bool types2-4.21 {o1=500} {500 > o1} 0 test_bool types2-4.22 {o1=500} {'500' > o1} 1 test_bool types2-4.23 {o1=500} {500.0 > o1} 0 test_bool types2-4.24 {o1=500} {'500.0' > o1} 1 test_bool types2-4.25 {o1='500'} {500 > o1} 0 test_bool types2-4.26 {o1='500'} {'500' > o1} 0 test_bool types2-4.27 {o1='500'} {500.0 > o1} 0 test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1 finish_test