1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/alias.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,137 @@
1.4 +# 2008 August 28
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 +# This file implements regression tests for SQLite library. The
1.16 +# focus of this script is correct code generation of aliased result-set
1.17 +# values. See ticket #3343.
1.18 +#
1.19 +# $Id: alias.test,v 1.1 2008/08/29 02:14:03 drh Exp $
1.20 +#
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +# A procedure to return a sequence of increasing integers.
1.25 +#
1.26 +namespace eval ::seq {
1.27 + variable counter 0
1.28 + proc value {args} {
1.29 + variable counter
1.30 + incr counter
1.31 + return $counter
1.32 + }
1.33 + proc reset {} {
1.34 + variable counter
1.35 + set counter 0
1.36 + }
1.37 +}
1.38 +
1.39 +
1.40 +do_test alias-1.1 {
1.41 + db function sequence ::seq::value
1.42 + db eval {
1.43 + CREATE TABLE t1(x);
1.44 + INSERT INTO t1 VALUES(9);
1.45 + INSERT INTO t1 VALUES(8);
1.46 + INSERT INTO t1 VALUES(7);
1.47 + SELECT x, sequence() FROM t1;
1.48 + }
1.49 +} {9 1 8 2 7 3}
1.50 +do_test alias-1.2 {
1.51 + ::seq::reset
1.52 + db eval {
1.53 +--pragma vdbe_listing=on; pragma vdbe_trace=on;
1.54 + SELECT x, sequence() AS y FROM t1 WHERE y>0
1.55 + }
1.56 +} {9 1 8 2 7 3}
1.57 +do_test alias-1.3 {
1.58 + ::seq::reset
1.59 + db eval {
1.60 + SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99
1.61 + }
1.62 +} {9 1 8 2 7 3}
1.63 +do_test alias-1.4 {
1.64 + ::seq::reset
1.65 + db eval {
1.66 + SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=55
1.67 + }
1.68 +} {9 1 8 2 7 3}
1.69 +do_test alias-1.5 {
1.70 + ::seq::reset
1.71 + db eval {
1.72 + SELECT x, sequence() AS y FROM t1
1.73 + WHERE y>0 AND y<99 AND y!=55 AND y NOT IN (56,57,58)
1.74 + AND y NOT LIKE 'abc%' AND y%10==2
1.75 + }
1.76 +} {8 2}
1.77 +do_test alias-1.6 {
1.78 + ::seq::reset
1.79 + db eval {
1.80 + SELECT x, sequence() AS y FROM t1 WHERE y BETWEEN 0 AND 99
1.81 + }
1.82 +} {9 1 8 2 7 3}
1.83 +do_test alias-1.7 {
1.84 + ::seq::reset
1.85 + db eval {
1.86 + SELECT x, sequence() AS y FROM t1 WHERE y IN (55,66,3)
1.87 + }
1.88 +} {7 3}
1.89 +do_test alias-1.8 {
1.90 + ::seq::reset
1.91 + db eval {
1.92 + SELECT x, 1-sequence() AS y FROM t1 ORDER BY y
1.93 + }
1.94 +} {7 -2 8 -1 9 0}
1.95 +do_test alias-1.9 {
1.96 + ::seq::reset
1.97 + db eval {
1.98 + SELECT x, sequence() AS y FROM t1 ORDER BY -y
1.99 + }
1.100 +} {7 3 8 2 9 1}
1.101 +do_test alias-1.10 {
1.102 + ::seq::reset
1.103 + db eval {
1.104 + SELECT x, sequence() AS y FROM t1 ORDER BY x%2, y
1.105 + }
1.106 +} {8 2 9 1 7 3}
1.107 +
1.108 +unset -nocomplain random_int_list
1.109 +set random_int_list [db eval {
1.110 + SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r
1.111 +}]
1.112 +do_test alias-1.11 {
1.113 + lsort -integer $::random_int_list
1.114 +} $random_int_list
1.115 +
1.116 +
1.117 +do_test alias-2.1 {
1.118 + db eval {
1.119 + SELECT 4 UNION SELECT 1 ORDER BY 1
1.120 + }
1.121 +} {1 4}
1.122 +do_test alias-2.2 {
1.123 + db eval {
1.124 + SELECT 4 UNION SELECT 1 UNION SELECT 9 ORDER BY 1
1.125 + }
1.126 +} {1 4 9}
1.127 +
1.128 +if 0 {
1.129 + # Aliases in the GROUP BY clause cause the expression to be evaluated
1.130 + # twice in the current implementation. This might change in the future.
1.131 + #
1.132 + do_test alias-3.1 {
1.133 + ::seq::reset
1.134 + db eval {
1.135 + SELECT sequence(*) AS y, count(*) AS z FROM t1 GROUP BY y ORDER BY z, y
1.136 + }
1.137 + } {1 1 2 1 3 1}
1.138 +}
1.139 +
1.140 +finish_test