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