1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2001 September 15 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. The 137c478bd9Sstevel@tonic-gate# focus of this file is testing SELECT statements that are part of 147c478bd9Sstevel@tonic-gate# expressions. 157c478bd9Sstevel@tonic-gate# 167c478bd9Sstevel@tonic-gate# $Id: subselect.test,v 1.7 2002/07/15 18:55:26 drh Exp $ 177c478bd9Sstevel@tonic-gate 187c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 197c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 207c478bd9Sstevel@tonic-gate 217c478bd9Sstevel@tonic-gate# Basic sanity checking. Try a simple subselect. 227c478bd9Sstevel@tonic-gate# 237c478bd9Sstevel@tonic-gatedo_test subselect-1.1 { 247c478bd9Sstevel@tonic-gate execsql { 257c478bd9Sstevel@tonic-gate CREATE TABLE t1(a int, b int); 267c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2); 277c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(3,4); 287c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(5,6); 297c478bd9Sstevel@tonic-gate } 307c478bd9Sstevel@tonic-gate execsql {SELECT * FROM t1 WHERE a = (SELECT count(*) FROM t1)} 317c478bd9Sstevel@tonic-gate} {3 4} 327c478bd9Sstevel@tonic-gate 337c478bd9Sstevel@tonic-gate# Try a select with more than one result column. 347c478bd9Sstevel@tonic-gate# 357c478bd9Sstevel@tonic-gatedo_test subselect-1.2 { 367c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT * FROM t1 WHERE a = (SELECT * FROM t1)}} msg] 377c478bd9Sstevel@tonic-gate lappend v $msg 387c478bd9Sstevel@tonic-gate} {1 {only a single result allowed for a SELECT that is part of an expression}} 397c478bd9Sstevel@tonic-gate 407c478bd9Sstevel@tonic-gate# A subselect without an aggregate. 417c478bd9Sstevel@tonic-gate# 427c478bd9Sstevel@tonic-gatedo_test subselect-1.3a { 437c478bd9Sstevel@tonic-gate execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=2)} 447c478bd9Sstevel@tonic-gate} {2} 457c478bd9Sstevel@tonic-gatedo_test subselect-1.3b { 467c478bd9Sstevel@tonic-gate execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=4)} 477c478bd9Sstevel@tonic-gate} {4} 487c478bd9Sstevel@tonic-gatedo_test subselect-1.3c { 497c478bd9Sstevel@tonic-gate execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=6)} 507c478bd9Sstevel@tonic-gate} {6} 517c478bd9Sstevel@tonic-gatedo_test subselect-1.3c { 527c478bd9Sstevel@tonic-gate execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=8)} 537c478bd9Sstevel@tonic-gate} {} 547c478bd9Sstevel@tonic-gate 557c478bd9Sstevel@tonic-gate# What if the subselect doesn't return any value. We should get 567c478bd9Sstevel@tonic-gate# NULL as the result. Check it out. 577c478bd9Sstevel@tonic-gate# 587c478bd9Sstevel@tonic-gatedo_test subselect-1.4 { 597c478bd9Sstevel@tonic-gate execsql {SELECT b from t1 where a = coalesce((SELECT a FROM t1 WHERE b=5),1)} 607c478bd9Sstevel@tonic-gate} {2} 617c478bd9Sstevel@tonic-gate 627c478bd9Sstevel@tonic-gate# Try multiple subselects within a single expression. 637c478bd9Sstevel@tonic-gate# 647c478bd9Sstevel@tonic-gatedo_test subselect-1.5 { 657c478bd9Sstevel@tonic-gate execsql { 667c478bd9Sstevel@tonic-gate CREATE TABLE t2(x int, y int); 677c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(1,2); 687c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(2,4); 697c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(3,8); 707c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(4,16); 717c478bd9Sstevel@tonic-gate } 727c478bd9Sstevel@tonic-gate execsql { 73*1da57d55SToomas Soome SELECT y from t2 747c478bd9Sstevel@tonic-gate WHERE x = (SELECT sum(b) FROM t1 where a notnull) - (SELECT sum(a) FROM t1) 757c478bd9Sstevel@tonic-gate } 767c478bd9Sstevel@tonic-gate} {8} 777c478bd9Sstevel@tonic-gate 787c478bd9Sstevel@tonic-gate# Try something useful. Delete every entry from t2 where the 797c478bd9Sstevel@tonic-gate# x value is less than half of the maximum. 807c478bd9Sstevel@tonic-gate# 817c478bd9Sstevel@tonic-gatedo_test subselect-1.6 { 827c478bd9Sstevel@tonic-gate execsql {DELETE FROM t2 WHERE x < 0.5*(SELECT max(x) FROM t2)} 837c478bd9Sstevel@tonic-gate execsql {SELECT x FROM t2 ORDER BY x} 847c478bd9Sstevel@tonic-gate} {2 3 4} 857c478bd9Sstevel@tonic-gate 867c478bd9Sstevel@tonic-gate# Make sure sorting works for SELECTs there used as a scalar expression. 877c478bd9Sstevel@tonic-gate# 887c478bd9Sstevel@tonic-gatedo_test subselect-2.1 { 897c478bd9Sstevel@tonic-gate execsql { 907c478bd9Sstevel@tonic-gate SELECT (SELECT a FROM t1 ORDER BY a), (SELECT a FROM t1 ORDER BY a DESC) 917c478bd9Sstevel@tonic-gate } 927c478bd9Sstevel@tonic-gate} {1 5} 937c478bd9Sstevel@tonic-gatedo_test subselect-2.2 { 947c478bd9Sstevel@tonic-gate execsql { 957c478bd9Sstevel@tonic-gate SELECT 1 IN (SELECT a FROM t1 ORDER BY a); 967c478bd9Sstevel@tonic-gate } 977c478bd9Sstevel@tonic-gate} {1} 987c478bd9Sstevel@tonic-gatedo_test subselect-2.3 { 997c478bd9Sstevel@tonic-gate execsql { 1007c478bd9Sstevel@tonic-gate SELECT 2 IN (SELECT a FROM t1 ORDER BY a DESC); 1017c478bd9Sstevel@tonic-gate } 1027c478bd9Sstevel@tonic-gate} {0} 1037c478bd9Sstevel@tonic-gate 1047c478bd9Sstevel@tonic-gate# Verify that the ORDER BY clause is honored in a subquery. 1057c478bd9Sstevel@tonic-gate# 1067c478bd9Sstevel@tonic-gatedo_test subselect-3.1 { 1077c478bd9Sstevel@tonic-gate execsql { 1087c478bd9Sstevel@tonic-gate CREATE TABLE t3(x int); 1097c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT a FROM t1 UNION ALL SELECT b FROM t1; 1107c478bd9Sstevel@tonic-gate SELECT * FROM t3 ORDER BY x; 1117c478bd9Sstevel@tonic-gate } 1127c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6} 1137c478bd9Sstevel@tonic-gatedo_test subselect-3.2 { 1147c478bd9Sstevel@tonic-gate execsql { 1157c478bd9Sstevel@tonic-gate SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x LIMIT 2); 1167c478bd9Sstevel@tonic-gate } 1177c478bd9Sstevel@tonic-gate} {3} 1187c478bd9Sstevel@tonic-gatedo_test subselect-3.3 { 1197c478bd9Sstevel@tonic-gate execsql { 1207c478bd9Sstevel@tonic-gate SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x DESC LIMIT 2); 1217c478bd9Sstevel@tonic-gate } 1227c478bd9Sstevel@tonic-gate} {11} 1237c478bd9Sstevel@tonic-gatedo_test subselect-3.4 { 1247c478bd9Sstevel@tonic-gate execsql { 1257c478bd9Sstevel@tonic-gate SELECT (SELECT x FROM t3 ORDER BY x); 1267c478bd9Sstevel@tonic-gate } 1277c478bd9Sstevel@tonic-gate} {1} 1287c478bd9Sstevel@tonic-gatedo_test subselect-3.5 { 1297c478bd9Sstevel@tonic-gate execsql { 1307c478bd9Sstevel@tonic-gate SELECT (SELECT x FROM t3 ORDER BY x DESC); 1317c478bd9Sstevel@tonic-gate } 1327c478bd9Sstevel@tonic-gate} {6} 1337c478bd9Sstevel@tonic-gatedo_test subselect-3.6 { 1347c478bd9Sstevel@tonic-gate execsql { 1357c478bd9Sstevel@tonic-gate SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1); 1367c478bd9Sstevel@tonic-gate } 1377c478bd9Sstevel@tonic-gate} {1} 1387c478bd9Sstevel@tonic-gatedo_test subselect-3.7 { 1397c478bd9Sstevel@tonic-gate execsql { 1407c478bd9Sstevel@tonic-gate SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1); 1417c478bd9Sstevel@tonic-gate } 1427c478bd9Sstevel@tonic-gate} {6} 1437c478bd9Sstevel@tonic-gatedo_test subselect-3.8 { 1447c478bd9Sstevel@tonic-gate execsql { 1457c478bd9Sstevel@tonic-gate SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1 OFFSET 2); 1467c478bd9Sstevel@tonic-gate } 1477c478bd9Sstevel@tonic-gate} {3} 1487c478bd9Sstevel@tonic-gatedo_test subselect-3.9 { 1497c478bd9Sstevel@tonic-gate execsql { 1507c478bd9Sstevel@tonic-gate SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2); 1517c478bd9Sstevel@tonic-gate } 1527c478bd9Sstevel@tonic-gate} {4} 1537c478bd9Sstevel@tonic-gate 1547c478bd9Sstevel@tonic-gate 1557c478bd9Sstevel@tonic-gate 1567c478bd9Sstevel@tonic-gatefinish_test 157