xref: /titanic_52/usr/src/lib/libsqlite/test/subselect.test (revision 0dc974a9a2e66d676505db23524ebff105fb36a9) 
1
2#pragma ident	"%Z%%M%	%I%	%E% SMI"
3
4# 2001 September 15
5#
6# The author disclaims copyright to this source code.  In place of
7# a legal notice, here is a blessing:
8#
9#    May you do good and not evil.
10#    May you find forgiveness for yourself and forgive others.
11#    May you share freely, never taking more than you give.
12#
13#***********************************************************************
14# This file implements regression tests for SQLite library.  The
15# focus of this file is testing SELECT statements that are part of
16# expressions.
17#
18# $Id: subselect.test,v 1.7 2002/07/15 18:55:26 drh Exp $
19
20set testdir [file dirname $argv0]
21source $testdir/tester.tcl
22
23# Basic sanity checking.  Try a simple subselect.
24#
25do_test subselect-1.1 {
26  execsql {
27    CREATE TABLE t1(a int, b int);
28    INSERT INTO t1 VALUES(1,2);
29    INSERT INTO t1 VALUES(3,4);
30    INSERT INTO t1 VALUES(5,6);
31  }
32  execsql {SELECT * FROM t1 WHERE a = (SELECT count(*) FROM t1)}
33} {3 4}
34
35# Try a select with more than one result column.
36#
37do_test subselect-1.2 {
38  set v [catch {execsql {SELECT * FROM t1 WHERE a = (SELECT * FROM t1)}} msg]
39  lappend v $msg
40} {1 {only a single result allowed for a SELECT that is part of an expression}}
41
42# A subselect without an aggregate.
43#
44do_test subselect-1.3a {
45  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=2)}
46} {2}
47do_test subselect-1.3b {
48  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=4)}
49} {4}
50do_test subselect-1.3c {
51  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=6)}
52} {6}
53do_test subselect-1.3c {
54  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=8)}
55} {}
56
57# What if the subselect doesn't return any value.  We should get
58# NULL as the result.  Check it out.
59#
60do_test subselect-1.4 {
61  execsql {SELECT b from t1 where a = coalesce((SELECT a FROM t1 WHERE b=5),1)}
62} {2}
63
64# Try multiple subselects within a single expression.
65#
66do_test subselect-1.5 {
67  execsql {
68    CREATE TABLE t2(x int, y int);
69    INSERT INTO t2 VALUES(1,2);
70    INSERT INTO t2 VALUES(2,4);
71    INSERT INTO t2 VALUES(3,8);
72    INSERT INTO t2 VALUES(4,16);
73  }
74  execsql {
75    SELECT y from t2
76    WHERE x = (SELECT sum(b) FROM t1 where a notnull) - (SELECT sum(a) FROM t1)
77  }
78} {8}
79
80# Try something useful.  Delete every entry from t2 where the
81# x value is less than half of the maximum.
82#
83do_test subselect-1.6 {
84  execsql {DELETE FROM t2 WHERE x < 0.5*(SELECT max(x) FROM t2)}
85  execsql {SELECT x FROM t2 ORDER BY x}
86} {2 3 4}
87
88# Make sure sorting works for SELECTs there used as a scalar expression.
89#
90do_test subselect-2.1 {
91  execsql {
92    SELECT (SELECT a FROM t1 ORDER BY a), (SELECT a FROM t1 ORDER BY a DESC)
93  }
94} {1 5}
95do_test subselect-2.2 {
96  execsql {
97    SELECT 1 IN (SELECT a FROM t1 ORDER BY a);
98  }
99} {1}
100do_test subselect-2.3 {
101  execsql {
102    SELECT 2 IN (SELECT a FROM t1 ORDER BY a DESC);
103  }
104} {0}
105
106# Verify that the ORDER BY clause is honored in a subquery.
107#
108do_test subselect-3.1 {
109  execsql {
110    CREATE TABLE t3(x int);
111    INSERT INTO t3 SELECT a FROM t1 UNION ALL SELECT b FROM t1;
112    SELECT * FROM t3 ORDER BY x;
113  }
114} {1 2 3 4 5 6}
115do_test subselect-3.2 {
116  execsql {
117    SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x LIMIT 2);
118  }
119} {3}
120do_test subselect-3.3 {
121  execsql {
122    SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x DESC LIMIT 2);
123  }
124} {11}
125do_test subselect-3.4 {
126  execsql {
127    SELECT (SELECT x FROM t3 ORDER BY x);
128  }
129} {1}
130do_test subselect-3.5 {
131  execsql {
132    SELECT (SELECT x FROM t3 ORDER BY x DESC);
133  }
134} {6}
135do_test subselect-3.6 {
136  execsql {
137    SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1);
138  }
139} {1}
140do_test subselect-3.7 {
141  execsql {
142    SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1);
143  }
144} {6}
145do_test subselect-3.8 {
146  execsql {
147    SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1 OFFSET 2);
148  }
149} {3}
150do_test subselect-3.9 {
151  execsql {
152    SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2);
153  }
154} {4}
155
156
157
158finish_test
159