xref: /sqlite-3.40.0/test/alias.test (revision 8b213899) 
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#
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21# A procedure to return a sequence of increasing integers.
22#
23namespace 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
37do_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}
47do_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}
54do_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}
60do_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}
66do_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}
74do_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}
80do_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}
86do_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}
92do_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}
98do_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
105unset -nocomplain random_int_list
106set random_int_list [db eval {
107   SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r
108}]
109do_test alias-1.11 {
110  lsort -integer $::random_int_list
111} $random_int_list
112
113
114do_test alias-2.1 {
115  db eval {
116    SELECT 4 UNION SELECT 1 ORDER BY 1
117  }
118} {1 4}
119do_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
125if 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
137finish_test
138