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 # 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 21 # Aliases are currently evaluated twice. We might try to change this 22 # in the future. But not now. 23 return 24 25 # A procedure to return a sequence of increasing integers. 26 # 27 namespace 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 41 do_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} 51 do_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} 57 do_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} 63 do_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} 69 do_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} 77 do_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} 89 do_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} 95 do_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} 101 do_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 108 unset -nocomplain random_int_list 109 set random_int_list [db eval { 110 SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r 111 }] 112 do_test alias-1.11 { 113 lsort -integer $::random_int_list 114 } $random_int_list 115 116 117 do_test alias-2.1 { 118 db eval { 119 SELECT 4 UNION SELECT 1 ORDER BY 1 120 } 121 } {1 4} 122 do_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 128 if 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 140 finish_test 141
