1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/select2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,190 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.
1.7 +#
1.8 +# The author disclaims copyright to this source code. In place of
1.9 +# a legal notice, here is a blessing:
1.10 +#
1.11 +# May you do good and not evil.
1.12 +# May you find forgiveness for yourself and forgive others.
1.13 +# May you share freely, never taking more than you give.
1.14 +#
1.15 +#***********************************************************************
1.16 +# This file implements regression tests for SQLite library. The
1.17 +# focus of this file is testing the SELECT statement.
1.18 +#
1.19 +# $Id: select2.test,v 1.27 2008/07/12 14:52:20 drh Exp $
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +# Create a table with some data
1.25 +#
1.26 +execsql {CREATE TABLE tbl1(f1 int, f2 int)}
1.27 +execsql {BEGIN}
1.28 +for {set i 0} {$i<=30} {incr i} {
1.29 + execsql "INSERT INTO tbl1 VALUES([expr {$i%9}],[expr {$i%10}])"
1.30 +}
1.31 +execsql {COMMIT}
1.32 +
1.33 +# Do a second query inside a first.
1.34 +#
1.35 +do_test select2-1.1 {
1.36 + set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1}
1.37 + set r {}
1.38 + catch {unset data}
1.39 + db eval $sql data {
1.40 + set f1 $data(f1)
1.41 + lappend r $f1:
1.42 + set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"
1.43 + db eval $sql2 d2 {
1.44 + lappend r $d2(f2)
1.45 + }
1.46 + }
1.47 + set r
1.48 +} {0: 0 7 8 9 1: 0 1 8 9 2: 0 1 2 9 3: 0 1 2 3 4: 2 3 4 5: 3 4 5 6: 4 5 6 7: 5 6 7 8: 6 7 8}
1.49 +
1.50 +do_test select2-1.2 {
1.51 + set sql {SELECT DISTINCT f1 FROM tbl1 WHERE f1>3 AND f1<5}
1.52 + set r {}
1.53 + db eval $sql data {
1.54 + set f1 $data(f1)
1.55 + lappend r $f1:
1.56 + set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"
1.57 + db eval $sql2 d2 {
1.58 + lappend r $d2(f2)
1.59 + }
1.60 + }
1.61 + set r
1.62 +} {4: 2 3 4}
1.63 +unset data
1.64 +
1.65 +# Create a largish table. Do this twice, once using the TCL cache and once
1.66 +# without. Compare the performance to make sure things go faster with the
1.67 +# cache turned on.
1.68 +#
1.69 +ifcapable tclvar {
1.70 + do_test select2-2.0.1 {
1.71 + set t1 [time {
1.72 + execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}
1.73 + for {set i 1} {$i<=30000} {incr i} {
1.74 + set i2 [expr {$i*2}]
1.75 + set i3 [expr {$i*3}]
1.76 + db eval {INSERT INTO tbl2 VALUES($i,$i2,$i3)}
1.77 + }
1.78 + execsql {COMMIT}
1.79 + }]
1.80 + list
1.81 + } {}
1.82 + puts "time with cache: $::t1"
1.83 +}
1.84 +catch {execsql {DROP TABLE tbl2}}
1.85 +do_test select2-2.0.2 {
1.86 + set t2 [time {
1.87 + execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}
1.88 + for {set i 1} {$i<=30000} {incr i} {
1.89 + set i2 [expr {$i*2}]
1.90 + set i3 [expr {$i*3}]
1.91 + execsql "INSERT INTO tbl2 VALUES($i,$i2,$i3)"
1.92 + }
1.93 + execsql {COMMIT}
1.94 + }]
1.95 + list
1.96 +} {}
1.97 +puts "time without cache: $t2"
1.98 +#Symbian OS: This is a tclsqlite3.exe test. We do not use the TCL cache.
1.99 +if {$::tcl_platform(platform)!="symbian"} {
1.100 + ifcapable tclvar {
1.101 + do_test select2-2.0.3 {
1.102 + expr {[lindex $t1 0]<[lindex $t2 0]}
1.103 + } 1
1.104 + }
1.105 +}
1.106 +
1.107 +do_test select2-2.1 {
1.108 + execsql {SELECT count(*) FROM tbl2}
1.109 +} {30000}
1.110 +do_test select2-2.2 {
1.111 + execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
1.112 +} {29500}
1.113 +
1.114 +do_test select2-3.1 {
1.115 + execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
1.116 +} {500}
1.117 +
1.118 +do_test select2-3.2a {
1.119 + execsql {CREATE INDEX idx1 ON tbl2(f2)}
1.120 +} {}
1.121 +do_test select2-3.2b {
1.122 + execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
1.123 +} {500}
1.124 +do_test select2-3.2c {
1.125 + execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
1.126 +} {500}
1.127 +do_test select2-3.2d {
1.128 + set sqlite_search_count 0
1.129 + execsql {SELECT * FROM tbl2 WHERE 1000=f2}
1.130 + set sqlite_search_count
1.131 +} {3}
1.132 +do_test select2-3.2e {
1.133 + set sqlite_search_count 0
1.134 + execsql {SELECT * FROM tbl2 WHERE f2=1000}
1.135 + set sqlite_search_count
1.136 +} {3}
1.137 +
1.138 +# Make sure queries run faster with an index than without
1.139 +#
1.140 +do_test select2-3.3 {
1.141 + execsql {DROP INDEX idx1}
1.142 + set sqlite_search_count 0
1.143 + execsql {SELECT f1 FROM tbl2 WHERE f2==2000}
1.144 + set sqlite_search_count
1.145 +} {29999}
1.146 +
1.147 +# Make sure we can optimize functions in the WHERE clause that
1.148 +# use fields from two or more different table. (Bug #6)
1.149 +#
1.150 +do_test select2-4.1 {
1.151 + execsql {
1.152 + CREATE TABLE aa(a);
1.153 + CREATE TABLE bb(b);
1.154 + INSERT INTO aa VALUES(1);
1.155 + INSERT INTO aa VALUES(3);
1.156 + INSERT INTO bb VALUES(2);
1.157 + INSERT INTO bb VALUES(4);
1.158 + SELECT * FROM aa, bb WHERE max(a,b)>2;
1.159 + }
1.160 +} {1 4 3 2 3 4}
1.161 +do_test select2-4.2 {
1.162 + execsql {
1.163 + INSERT INTO bb VALUES(0);
1.164 + SELECT * FROM aa, bb WHERE b;
1.165 + }
1.166 +} {1 2 1 4 3 2 3 4}
1.167 +do_test select2-4.3 {
1.168 + execsql {
1.169 + SELECT * FROM aa, bb WHERE NOT b;
1.170 + }
1.171 +} {1 0 3 0}
1.172 +do_test select2-4.4 {
1.173 + execsql {
1.174 + SELECT * FROM aa, bb WHERE min(a,b);
1.175 + }
1.176 +} {1 2 1 4 3 2 3 4}
1.177 +do_test select2-4.5 {
1.178 + execsql {
1.179 + SELECT * FROM aa, bb WHERE NOT min(a,b);
1.180 + }
1.181 +} {1 0 3 0}
1.182 +do_test select2-4.6 {
1.183 + execsql {
1.184 + SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 1 END;
1.185 + }
1.186 +} {1 2 3 4}
1.187 +do_test select2-4.7 {
1.188 + execsql {
1.189 + SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 0 ELSE 1 END;
1.190 + }
1.191 +} {1 4 1 0 3 2 3 0}
1.192 +
1.193 +finish_test