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