1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/cse.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,160 @@
1.4 +# 2008 April 1
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +#
1.15 +# Test cases designed to exercise and verify the logic for
1.16 +# factoring constant expressions out of loops and for
1.17 +# common subexpression eliminations.
1.18 +#
1.19 +# $Id: cse.test,v 1.6 2008/08/04 03:51:24 danielk1977 Exp $
1.20 +#
1.21 +
1.22 +set testdir [file dirname $argv0]
1.23 +source $testdir/tester.tcl
1.24 +
1.25 +do_test cse-1.1 {
1.26 + execsql {
1.27 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d, e, f);
1.28 + INSERT INTO t1 VALUES(1,11,12,13,14,15);
1.29 + INSERT INTO t1 VALUES(2,21,22,23,24,25);
1.30 + }
1.31 + execsql {
1.32 + SELECT b, -b, ~b, NOT b, NOT NOT b, b-b, b+b, b*b, b/b, b FROM t1
1.33 + }
1.34 +} {11 -11 -12 0 1 0 22 121 1 11 21 -21 -22 0 1 0 42 441 1 21}
1.35 +do_test cse-1.2 {
1.36 + execsql {
1.37 + SELECT b, b%b, b==b, b!=b, b<b, b<=b, b IS NULL, b NOT NULL, b FROM t1
1.38 + }
1.39 +} {11 0 1 0 0 1 0 1 11 21 0 1 0 0 1 0 1 21}
1.40 +do_test cse-1.3 {
1.41 + execsql {
1.42 + SELECT b, abs(b), coalesce(b,-b,NOT b,c,NOT c), c, -c FROM t1;
1.43 + }
1.44 +} {11 11 11 12 -12 21 21 21 22 -22}
1.45 +do_test cse-1.4 {
1.46 + execsql {
1.47 + SELECT CASE WHEN a==1 THEN b ELSE c END, b, c FROM t1
1.48 + }
1.49 +} {11 11 12 22 21 22}
1.50 +do_test cse-1.5 {
1.51 + execsql {
1.52 + SELECT CASE a WHEN 1 THEN b WHEN 2 THEN c ELSE d END, b, c, d FROM t1
1.53 + }
1.54 +} {11 11 12 13 22 21 22 23}
1.55 +do_test cse-1.6.1 {
1.56 + execsql {
1.57 + SELECT CASE b WHEN 11 THEN -b WHEN 21 THEN -c ELSE -d END, b, c, d FROM t1
1.58 + }
1.59 +} {-11 11 12 13 -22 21 22 23}
1.60 +do_test cse-1.6.2 {
1.61 + execsql {
1.62 + SELECT CASE b+1 WHEN c THEN d WHEN e THEN f ELSE 999 END, b, c, d FROM t1
1.63 + }
1.64 +} {13 11 12 13 23 21 22 23}
1.65 +do_test cse-1.6.3 {
1.66 + execsql {
1.67 + SELECT CASE WHEN b THEN d WHEN e THEN f ELSE 999 END, b, c, d FROM t1
1.68 + }
1.69 +} {13 11 12 13 23 21 22 23}
1.70 +do_test cse-1.6.4 {
1.71 + execsql {
1.72 + SELECT b, c, d, CASE WHEN b THEN d WHEN e THEN f ELSE 999 END FROM t1
1.73 + }
1.74 +} {11 12 13 13 21 22 23 23}
1.75 +do_test cse-1.6.5 {
1.76 + execsql {
1.77 + SELECT b, c, d, CASE WHEN 0 THEN d WHEN e THEN f ELSE 999 END FROM t1
1.78 + }
1.79 +} {11 12 13 15 21 22 23 25}
1.80 +do_test cse-1.7 {
1.81 + execsql {
1.82 + SELECT a, -a, ~a, NOT a, NOT NOT a, a-a, a+a, a*a, a/a, a FROM t1
1.83 + }
1.84 +} {1 -1 -2 0 1 0 2 1 1 1 2 -2 -3 0 1 0 4 4 1 2}
1.85 +do_test cse-1.8 {
1.86 + execsql {
1.87 + SELECT a, a%a, a==a, a!=a, a<a, a<=a, a IS NULL, a NOT NULL, a FROM t1
1.88 + }
1.89 +} {1 0 1 0 0 1 0 1 1 2 0 1 0 0 1 0 1 2}
1.90 +do_test cse-1.9 {
1.91 + execsql {
1.92 + SELECT NOT b, ~b, NOT NOT b, b FROM t1
1.93 + }
1.94 +} {0 -12 1 11 0 -22 1 21}
1.95 +do_test cse-1.10 {
1.96 + execsql {
1.97 + SELECT CAST(b AS integer), typeof(b), CAST(b AS text), typeof(b) FROM t1
1.98 + }
1.99 +} {11 integer 11 integer 21 integer 21 integer}
1.100 +ifcapable compound {
1.101 + do_test cse-1.11 {
1.102 + execsql {
1.103 + SELECT *,* FROM t1 WHERE a=2
1.104 + UNION ALL
1.105 + SELECT *,* FROM t1 WHERE a=1
1.106 + }
1.107 + } {2 21 22 23 24 25 2 21 22 23 24 25 1 11 12 13 14 15 1 11 12 13 14 15}
1.108 + do_test cse-1.12 {
1.109 + execsql {
1.110 + SELECT coalesce(b,c,d,e), a, b, c, d, e FROM t1 WHERE a=2
1.111 + UNION ALL
1.112 + SELECT coalesce(e,d,c,b), e, d, c, b, a FROM t1 WHERE a=1
1.113 + }
1.114 + } {21 2 21 22 23 24 14 14 13 12 11 1}
1.115 +}
1.116 +do_test cse-1.13 {
1.117 + execsql {
1.118 + SELECT upper(b), typeof(b), b FROM t1
1.119 + }
1.120 +} {11 integer 11 21 integer 21}
1.121 +do_test cse-1.14 {
1.122 + execsql {
1.123 + SELECT b, typeof(b), upper(b), typeof(b), b FROM t1
1.124 + }
1.125 +} {11 integer 11 integer 11 21 integer 21 integer 21}
1.126 +
1.127 +# Overflow the column cache. Create queries involving more and more
1.128 +# columns until the cache overflows. Verify correct operation throughout.
1.129 +#
1.130 +do_test cse-2.1 {
1.131 + execsql {
1.132 + CREATE TABLE t2(a0,a1,a2,a3,a4,a5,a6,a7,a8,a9,
1.133 + a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,
1.134 + a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,
1.135 + a30,a31,a32,a33,a34,a35,a36,a37,a38,a39,
1.136 + a40,a41,a42,a43,a44,a45,a46,a47,a48,a49);
1.137 + INSERT INTO t2 VALUES(0,1,2,3,4,5,6,7,8,9,
1.138 + 10,11,12,13,14,15,16,17,18,19,
1.139 + 20,21,22,23,24,25,26,27,28,29,
1.140 + 30,31,32,33,34,35,36,37,38,39,
1.141 + 40,41,42,43,44,45,46,47,48,49);
1.142 + SELECT * FROM t2;
1.143 + }
1.144 +} {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49}
1.145 +
1.146 +for {set i 1} {$i<100} {incr i} {
1.147 + set n [expr {int(rand()*44)+5}]
1.148 + set colset {}
1.149 + set answer {}
1.150 + for {set j 0} {$j<$n} {incr j} {
1.151 + set r [expr {$j+int(rand()*5)}]
1.152 + if {$r>49} {set r [expr {99-$r}]}
1.153 + lappend colset a$j a$r
1.154 + lappend answer $j $r
1.155 + }
1.156 + set sql "SELECT [join $colset ,] FROM t2"
1.157 + do_test cse-2.2.$i {
1.158 + # explain $::sql
1.159 + execsql $::sql
1.160 + } $answer
1.161 +}
1.162 +
1.163 +finish_test