1# 2008 August 28 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# 12# This file implements regression tests for SQLite library. The 13# focus of this script is correct code generation of aliased result-set 14# values. See ticket #3343. 15# 16# $Id: alias.test,v 1.3 2009/04/23 13:22:44 drh Exp $ 17# 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21# Aliases are currently evaluated twice. We might try to change this 22# in the future. But not now. 23return 24 25# A procedure to return a sequence of increasing integers. 26# 27namespace eval ::seq { 28 variable counter 0 29 proc value {args} { 30 variable counter 31 incr counter 32 return $counter 33 } 34 proc reset {} { 35 variable counter 36 set counter 0 37 } 38} 39 40 41do_test alias-1.1 { 42 db function sequence ::seq::value 43 db eval { 44 CREATE TABLE t1(x); 45 INSERT INTO t1 VALUES(9); 46 INSERT INTO t1 VALUES(8); 47 INSERT INTO t1 VALUES(7); 48 SELECT x, sequence() FROM t1; 49 } 50} {9 1 8 2 7 3} 51do_test alias-1.2 { 52 ::seq::reset 53 db eval { 54 SELECT x, sequence() AS y FROM t1 WHERE y>0 55 } 56} {9 1 8 2 7 3} 57do_test alias-1.3 { 58 ::seq::reset 59 db eval { 60 SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 61 } 62} {9 1 8 2 7 3} 63do_test alias-1.4 { 64 ::seq::reset 65 db eval { 66 SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=55 67 } 68} {9 1 8 2 7 3} 69do_test alias-1.5 { 70 ::seq::reset 71 db eval { 72 SELECT x, sequence() AS y FROM t1 73 WHERE y>0 AND y<99 AND y!=55 AND y NOT IN (56,57,58) 74 AND y NOT LIKE 'abc%' AND y%10==2 75 } 76} {8 2} 77do_test alias-1.6 { 78 ::seq::reset 79 db eval { 80 SELECT x, sequence() AS y FROM t1 WHERE y BETWEEN 0 AND 99 81 } 82} {9 1 8 2 7 3} 83#do_test alias-1.7 { 84# ::seq::reset 85# db eval { 86# SELECT x, sequence() AS y FROM t1 WHERE y IN (55,66,3) 87# } 88#} {7 3} 89do_test alias-1.8 { 90 ::seq::reset 91 db eval { 92 SELECT x, 1-sequence() AS y FROM t1 ORDER BY y 93 } 94} {7 -2 8 -1 9 0} 95do_test alias-1.9 { 96 ::seq::reset 97 db eval { 98 SELECT x, sequence() AS y FROM t1 ORDER BY -y 99 } 100} {7 3 8 2 9 1} 101do_test alias-1.10 { 102 ::seq::reset 103 db eval { 104 SELECT x, sequence() AS y FROM t1 ORDER BY x%2, y 105 } 106} {8 2 9 1 7 3} 107 108unset -nocomplain random_int_list 109set random_int_list [db eval { 110 SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r 111}] 112do_test alias-1.11 { 113 lsort -integer $::random_int_list 114} $random_int_list 115 116 117do_test alias-2.1 { 118 db eval { 119 SELECT 4 UNION SELECT 1 ORDER BY 1 120 } 121} {1 4} 122do_test alias-2.2 { 123 db eval { 124 SELECT 4 UNION SELECT 1 UNION SELECT 9 ORDER BY 1 125 } 126} {1 4 9} 127 128if 0 { 129 # Aliases in the GROUP BY clause cause the expression to be evaluated 130 # twice in the current implementation. This might change in the future. 131 # 132 do_test alias-3.1 { 133 ::seq::reset 134 db eval { 135 SELECT sequence(*) AS y, count(*) AS z FROM t1 GROUP BY y ORDER BY z, y 136 } 137 } {1 1 2 1 3 1} 138} 139 140finish_test 141