xref: /sqlite-3.40.0/test/alias.test (revision 31daa63f) 
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.2 2008/10/25 15:03:21 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    SELECT x, sequence() AS y FROM t1 WHERE y>0
51  }
52} {9 1 8 2 7 3}
53do_test alias-1.3 {
54  ::seq::reset
55  db eval {
56    SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99
57  }
58} {9 1 8 2 7 3}
59do_test alias-1.4 {
60  ::seq::reset
61  db eval {
62    SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=55
63  }
64} {9 1 8 2 7 3}
65do_test alias-1.5 {
66  ::seq::reset
67  db eval {
68    SELECT x, sequence() AS y FROM t1
69     WHERE y>0 AND y<99 AND y!=55 AND y NOT IN (56,57,58)
70       AND y NOT LIKE 'abc%' AND y%10==2
71  }
72} {8 2}
73do_test alias-1.6 {
74  ::seq::reset
75  db eval {
76    SELECT x, sequence() AS y FROM t1 WHERE y BETWEEN 0 AND 99
77  }
78} {9 1 8 2 7 3}
79#do_test alias-1.7 {
80#  ::seq::reset
81#  db eval {
82#    SELECT x, sequence() AS y FROM t1 WHERE y IN (55,66,3)
83#  }
84#} {7 3}
85do_test alias-1.8 {
86  ::seq::reset
87  db eval {
88    SELECT x, 1-sequence() AS y FROM t1 ORDER BY y
89  }
90} {7 -2 8 -1 9 0}
91do_test alias-1.9 {
92  ::seq::reset
93  db eval {
94    SELECT x, sequence() AS y FROM t1 ORDER BY -y
95  }
96} {7 3 8 2 9 1}
97do_test alias-1.10 {
98  ::seq::reset
99  db eval {
100    SELECT x, sequence() AS y FROM t1 ORDER BY x%2, y
101  }
102} {8 2 9 1 7 3}
103
104unset -nocomplain random_int_list
105set random_int_list [db eval {
106   SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r
107}]
108do_test alias-1.11 {
109  lsort -integer $::random_int_list
110} $random_int_list
111
112
113do_test alias-2.1 {
114  db eval {
115    SELECT 4 UNION SELECT 1 ORDER BY 1
116  }
117} {1 4}
118do_test alias-2.2 {
119  db eval {
120    SELECT 4 UNION SELECT 1 UNION SELECT 9 ORDER BY 1
121  }
122} {1 4 9}
123
124if 0 {
125  # Aliases in the GROUP BY clause cause the expression to be evaluated
126  # twice in the current implementation.  This might change in the future.
127  #
128  do_test alias-3.1 {
129    ::seq::reset
130    db eval {
131      SELECT sequence(*) AS y, count(*) AS z FROM t1 GROUP BY y ORDER BY z, y
132    }
133  } {1 1 2 1 3 1}
134}
135
136finish_test
137