|
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.1 2008/08/29 02:14:03 drh Exp $ |
|
17 # |
|
18 set testdir [file dirname $argv0] |
|
19 source $testdir/tester.tcl |
|
20 |
|
21 # A procedure to return a sequence of increasing integers. |
|
22 # |
|
23 namespace eval ::seq { |
|
24 variable counter 0 |
|
25 proc value {args} { |
|
26 variable counter |
|
27 incr counter |
|
28 return $counter |
|
29 } |
|
30 proc reset {} { |
|
31 variable counter |
|
32 set counter 0 |
|
33 } |
|
34 } |
|
35 |
|
36 |
|
37 do_test alias-1.1 { |
|
38 db function sequence ::seq::value |
|
39 db eval { |
|
40 CREATE TABLE t1(x); |
|
41 INSERT INTO t1 VALUES(9); |
|
42 INSERT INTO t1 VALUES(8); |
|
43 INSERT INTO t1 VALUES(7); |
|
44 SELECT x, sequence() FROM t1; |
|
45 } |
|
46 } {9 1 8 2 7 3} |
|
47 do_test alias-1.2 { |
|
48 ::seq::reset |
|
49 db eval { |
|
50 --pragma vdbe_listing=on; pragma vdbe_trace=on; |
|
51 SELECT x, sequence() AS y FROM t1 WHERE y>0 |
|
52 } |
|
53 } {9 1 8 2 7 3} |
|
54 do_test alias-1.3 { |
|
55 ::seq::reset |
|
56 db eval { |
|
57 SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 |
|
58 } |
|
59 } {9 1 8 2 7 3} |
|
60 do_test alias-1.4 { |
|
61 ::seq::reset |
|
62 db eval { |
|
63 SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=55 |
|
64 } |
|
65 } {9 1 8 2 7 3} |
|
66 do_test alias-1.5 { |
|
67 ::seq::reset |
|
68 db eval { |
|
69 SELECT x, sequence() AS y FROM t1 |
|
70 WHERE y>0 AND y<99 AND y!=55 AND y NOT IN (56,57,58) |
|
71 AND y NOT LIKE 'abc%' AND y%10==2 |
|
72 } |
|
73 } {8 2} |
|
74 do_test alias-1.6 { |
|
75 ::seq::reset |
|
76 db eval { |
|
77 SELECT x, sequence() AS y FROM t1 WHERE y BETWEEN 0 AND 99 |
|
78 } |
|
79 } {9 1 8 2 7 3} |
|
80 do_test alias-1.7 { |
|
81 ::seq::reset |
|
82 db eval { |
|
83 SELECT x, sequence() AS y FROM t1 WHERE y IN (55,66,3) |
|
84 } |
|
85 } {7 3} |
|
86 do_test alias-1.8 { |
|
87 ::seq::reset |
|
88 db eval { |
|
89 SELECT x, 1-sequence() AS y FROM t1 ORDER BY y |
|
90 } |
|
91 } {7 -2 8 -1 9 0} |
|
92 do_test alias-1.9 { |
|
93 ::seq::reset |
|
94 db eval { |
|
95 SELECT x, sequence() AS y FROM t1 ORDER BY -y |
|
96 } |
|
97 } {7 3 8 2 9 1} |
|
98 do_test alias-1.10 { |
|
99 ::seq::reset |
|
100 db eval { |
|
101 SELECT x, sequence() AS y FROM t1 ORDER BY x%2, y |
|
102 } |
|
103 } {8 2 9 1 7 3} |
|
104 |
|
105 unset -nocomplain random_int_list |
|
106 set random_int_list [db eval { |
|
107 SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r |
|
108 }] |
|
109 do_test alias-1.11 { |
|
110 lsort -integer $::random_int_list |
|
111 } $random_int_list |
|
112 |
|
113 |
|
114 do_test alias-2.1 { |
|
115 db eval { |
|
116 SELECT 4 UNION SELECT 1 ORDER BY 1 |
|
117 } |
|
118 } {1 4} |
|
119 do_test alias-2.2 { |
|
120 db eval { |
|
121 SELECT 4 UNION SELECT 1 UNION SELECT 9 ORDER BY 1 |
|
122 } |
|
123 } {1 4 9} |
|
124 |
|
125 if 0 { |
|
126 # Aliases in the GROUP BY clause cause the expression to be evaluated |
|
127 # twice in the current implementation. This might change in the future. |
|
128 # |
|
129 do_test alias-3.1 { |
|
130 ::seq::reset |
|
131 db eval { |
|
132 SELECT sequence(*) AS y, count(*) AS z FROM t1 GROUP BY y ORDER BY z, y |
|
133 } |
|
134 } {1 1 2 1 3 1} |
|
135 } |
|
136 |
|
137 finish_test |