xref: /sqlite-3.40.0/test/selectC.test (revision f44ed027) 
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.2 2008/09/16 18:02:47 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}
105do_test selectC-1.12 {
106  execsql {
107    SELECT DISTINCT upper(b) AS x
108      FROM t1
109     ORDER BY x
110  }
111} {AAA CCC}
112do_test selectC-1.13 {
113  execsql {
114    SELECT upper(b) AS x
115      FROM t1
116     GROUP BY x
117     ORDER BY x
118  }
119} {AAA CCC}
120
121
122finish_test
123