xref: /sqlite-3.40.0/test/selectC.test (revision 75cbd984) 
1# 2008 September 16
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# This file implements regression tests for SQLite library.
12#
13# $Id: selectC.test,v 1.5 2009/05/17 15:26:21 drh Exp $
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17
18# Ticket #
19do_test selectC-1.1 {
20  execsql {
21    CREATE TABLE t1(a, b, c);
22    INSERT INTO t1 VALUES(1,'aaa','bbb');
23    INSERT INTO t1 SELECT * FROM t1;
24    INSERT INTO t1 VALUES(2,'ccc','ddd');
25
26    SELECT DISTINCT a AS x, b||c AS y
27      FROM t1
28     WHERE y IN ('aaabbb','xxx');
29  }
30} {1 aaabbb}
31do_test selectC-1.2 {
32  execsql {
33    SELECT DISTINCT a AS x, b||c AS y
34      FROM t1
35     WHERE b||c IN ('aaabbb','xxx');
36  }
37} {1 aaabbb}
38do_test selectC-1.3 {
39  execsql {
40    SELECT DISTINCT a AS x, b||c AS y
41      FROM t1
42     WHERE y='aaabbb'
43  }
44} {1 aaabbb}
45do_test selectC-1.4 {
46  execsql {
47    SELECT DISTINCT a AS x, b||c AS y
48      FROM t1
49     WHERE b||c='aaabbb'
50  }
51} {1 aaabbb}
52do_test selectC-1.5 {
53  execsql {
54    SELECT DISTINCT a AS x, b||c AS y
55      FROM t1
56     WHERE x=2
57  }
58} {2 cccddd}
59do_test selectC-1.6 {
60  execsql {
61    SELECT DISTINCT a AS x, b||c AS y
62      FROM t1
63     WHERE a=2
64  }
65} {2 cccddd}
66do_test selectC-1.7 {
67  execsql {
68    SELECT DISTINCT a AS x, b||c AS y
69      FROM t1
70     WHERE +y='aaabbb'
71  }
72} {1 aaabbb}
73do_test selectC-1.8 {
74  execsql {
75    SELECT a AS x, b||c AS y
76      FROM t1
77     GROUP BY x, y
78    HAVING y='aaabbb'
79  }
80} {1 aaabbb}
81do_test selectC-1.9 {
82  execsql {
83    SELECT a AS x, b||c AS y
84      FROM t1
85     GROUP BY x, y
86    HAVING b||c='aaabbb'
87  }
88} {1 aaabbb}
89do_test selectC-1.10 {
90  execsql {
91    SELECT a AS x, b||c AS y
92      FROM t1
93     WHERE y='aaabbb'
94     GROUP BY x, y
95  }
96} {1 aaabbb}
97do_test selectC-1.11 {
98  execsql {
99    SELECT a AS x, b||c AS y
100      FROM t1
101     WHERE b||c='aaabbb'
102     GROUP BY x, y
103  }
104} {1 aaabbb}
105proc longname_toupper x {return [string toupper $x]}
106db function uppercaseconversionfunctionwithaverylongname longname_toupper
107do_test selectC-1.12.1 {
108  execsql {
109    SELECT DISTINCT upper(b) AS x
110      FROM t1
111     ORDER BY x
112  }
113} {AAA CCC}
114do_test selectC-1.12.2 {
115  execsql {
116    SELECT DISTINCT uppercaseconversionfunctionwithaverylongname(b) AS x
117      FROM t1
118     ORDER BY x
119  }
120} {AAA CCC}
121do_test selectC-1.13.1 {
122  execsql {
123    SELECT upper(b) AS x
124      FROM t1
125     GROUP BY x
126     ORDER BY x
127  }
128} {AAA CCC}
129do_test selectC-1.13.2 {
130  execsql {
131    SELECT uppercaseconversionfunctionwithaverylongname(b) AS x
132      FROM t1
133     GROUP BY x
134     ORDER BY x
135  }
136} {AAA CCC}
137do_test selectC-1.14.1 {
138  execsql {
139    SELECT upper(b) AS x
140      FROM t1
141     ORDER BY x DESC
142  }
143} {CCC AAA AAA}
144do_test selectC-1.14.2 {
145  execsql {
146    SELECT uppercaseconversionfunctionwithaverylongname(b) AS x
147      FROM t1
148     ORDER BY x DESC
149  }
150} {CCC AAA AAA}
151
152# The following query used to leak memory.  Verify that has been fixed.
153#
154ifcapable trigger {
155  do_test selectC-2.1 {
156    catchsql {
157      CREATE TABLE t21a(a,b);
158      INSERT INTO t21a VALUES(1,2);
159      CREATE TABLE t21b(n);
160      CREATE TRIGGER r21 AFTER INSERT ON t21b BEGIN
161        SELECT a FROM t21a WHERE a>new.x UNION ALL
162        SELECT b FROM t21a WHERE b>new.x ORDER BY 1 LIMIT 2;
163      END;
164      INSERT INTO t21b VALUES(6);
165    }
166  } {1 {no such column: new.x}}
167}
168
169finish_test
170