xref: /sqlite-3.40.0/test/subselect.test (revision 600b1b2f) 
1# Copyright (c) 1999, 2000 D. Richard Hipp
2#
3# This program is free software; you can redistribute it and/or
4# modify it under the terms of the GNU General Public
5# License as published by the Free Software Foundation; either
6# version 2 of the License, or (at your option) any later version.
7#
8# This program is distributed in the hope that it will be useful,
9# but WITHOUT ANY WARRANTY; without even the implied warranty of
10# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
11# General Public License for more details.
12#
13# You should have received a copy of the GNU General Public
14# License along with this library; if not, write to the
15# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
16# Boston, MA  02111-1307, USA.
17#
18# Author contact information:
19#   [email protected]
20#   http://www.hwaci.com/drh/
21#
22#***********************************************************************
23# This file implements regression tests for SQLite library.  The
24# focus of this file is testing SELECT statements that are part of
25# expressions.
26#
27# $Id: subselect.test,v 1.3 2000/06/05 21:39:49 drh Exp $
28
29set testdir [file dirname $argv0]
30source $testdir/tester.tcl
31
32# Basic sanity checking.  Try a simple subselect.
33#
34do_test subselect-1.1 {
35  execsql {
36    CREATE TABLE t1(a int, b int);
37    INSERT INTO t1 VALUES(1,2);
38    INSERT INTO t1 VALUES(3,4);
39    INSERT INTO t1 VALUES(5,6);
40  }
41  execsql {SELECT * FROM t1 WHERE a = (SELECT count(*) FROM t1)}
42} {3 4}
43
44# Try a select with more than one result column.
45#
46do_test subselect-1.2 {
47  set v [catch {execsql {SELECT * FROM t1 WHERE a = (SELECT * FROM t1)}} msg]
48  lappend v $msg
49} {1 {only a single result allowed for a SELECT that is part of an expression}}
50
51# A subselect without an aggregate.
52#
53do_test subselect-1.3a {
54  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=2)}
55} {2}
56do_test subselect-1.3b {
57  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=4)}
58} {4}
59do_test subselect-1.3c {
60  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=6)}
61} {6}
62do_test subselect-1.3c {
63  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=8)}
64} {}
65
66# What if the subselect doesn't return any value.  We should get
67# NULL as the result.  Check it out.
68#
69do_test subselect-1.4 {
70  execsql {INSERT INTO t1 VALUES(NULL,8)}
71  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=5)}
72} {8}
73
74# Try multiple subselects within a single expression.
75#
76do_test subselect-1.5 {
77  execsql {
78    CREATE TABLE t2(x int, y int);
79    INSERT INTO t2 VALUES(1,2);
80    INSERT INTO t2 VALUES(2,4);
81    INSERT INTO t2 VALUES(3,8);
82    INSERT INTO t2 VALUES(4,16);
83  }
84  execsql {
85    SELECT y from t2
86    WHERE x = (SELECT sum(b) FROM t1 where a notnull) - (SELECT sum(a) FROM t1)
87  }
88} {8}
89
90# Try something useful.  Delete every entry from t2 where the
91# x value is less than half of the maximum.
92#
93do_test subselect-1.6 {
94  execsql {DELETE FROM t2 WHERE x < 0.5*(SELECT max(x) FROM t2)}
95  execsql {SELECT x FROM t2 ORDER BY x}
96} {2 3 4}
97
98finish_test
99