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 contain 147c478bd9Sstevel@tonic-gate# subqueries in their FROM clause. 157c478bd9Sstevel@tonic-gate# 167c478bd9Sstevel@tonic-gate# $Id: select6.test,v 1.11 2004/01/24 20:18:13 drh Exp $ 177c478bd9Sstevel@tonic-gate 187c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 197c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 207c478bd9Sstevel@tonic-gate 217c478bd9Sstevel@tonic-gatedo_test select6-1.0 { 227c478bd9Sstevel@tonic-gate execsql { 237c478bd9Sstevel@tonic-gate BEGIN; 247c478bd9Sstevel@tonic-gate CREATE TABLE t1(x, y); 257c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,1); 267c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(2,2); 277c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(3,2); 287c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(4,3); 297c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(5,3); 307c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(6,3); 317c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(7,3); 327c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(8,4); 337c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(9,4); 347c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(10,4); 357c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(11,4); 367c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(12,4); 377c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(13,4); 387c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(14,4); 397c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(15,4); 407c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(16,5); 417c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(17,5); 427c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(18,5); 437c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(19,5); 447c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(20,5); 457c478bd9Sstevel@tonic-gate COMMIT; 467c478bd9Sstevel@tonic-gate SELECT DISTINCT y FROM t1 ORDER BY y; 477c478bd9Sstevel@tonic-gate } 487c478bd9Sstevel@tonic-gate} {1 2 3 4 5} 497c478bd9Sstevel@tonic-gate 507c478bd9Sstevel@tonic-gatedo_test select6-1.1 { 517c478bd9Sstevel@tonic-gate execsql2 {SELECT * FROM (SELECT x, y FROM t1 WHERE x<2)} 527c478bd9Sstevel@tonic-gate} {x 1 y 1} 537c478bd9Sstevel@tonic-gatedo_test select6-1.2 { 547c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM (SELECT y FROM t1)} 557c478bd9Sstevel@tonic-gate} {20} 567c478bd9Sstevel@tonic-gatedo_test select6-1.3 { 577c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM (SELECT DISTINCT y FROM t1)} 587c478bd9Sstevel@tonic-gate} {5} 597c478bd9Sstevel@tonic-gatedo_test select6-1.4 { 607c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT y FROM t1))} 617c478bd9Sstevel@tonic-gate} {5} 627c478bd9Sstevel@tonic-gatedo_test select6-1.5 { 637c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT y FROM t1))} 647c478bd9Sstevel@tonic-gate} {5} 657c478bd9Sstevel@tonic-gate 667c478bd9Sstevel@tonic-gatedo_test select6-1.6 { 677c478bd9Sstevel@tonic-gate execsql { 68*1da57d55SToomas Soome SELECT * 697c478bd9Sstevel@tonic-gate FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a, 707c478bd9Sstevel@tonic-gate (SELECT max(x),y FROM t1 GROUP BY y) as b 717c478bd9Sstevel@tonic-gate WHERE a.y=b.y ORDER BY a.y 727c478bd9Sstevel@tonic-gate } 737c478bd9Sstevel@tonic-gate} {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5} 747c478bd9Sstevel@tonic-gatedo_test select6-1.7 { 757c478bd9Sstevel@tonic-gate execsql { 767c478bd9Sstevel@tonic-gate SELECT a.y, a.[count(*)], [max(x)], [count(*)] 777c478bd9Sstevel@tonic-gate FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a, 787c478bd9Sstevel@tonic-gate (SELECT max(x),y FROM t1 GROUP BY y) as b 797c478bd9Sstevel@tonic-gate WHERE a.y=b.y ORDER BY a.y 807c478bd9Sstevel@tonic-gate } 817c478bd9Sstevel@tonic-gate} {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5} 827c478bd9Sstevel@tonic-gatedo_test select6-1.8 { 837c478bd9Sstevel@tonic-gate execsql { 847c478bd9Sstevel@tonic-gate SELECT q, p, r 857c478bd9Sstevel@tonic-gate FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a, 867c478bd9Sstevel@tonic-gate (SELECT max(x) as r, y as s FROM t1 GROUP BY y) as b 877c478bd9Sstevel@tonic-gate WHERE q=s ORDER BY s 887c478bd9Sstevel@tonic-gate } 897c478bd9Sstevel@tonic-gate} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20} 907c478bd9Sstevel@tonic-gatedo_test select6-1.9 { 917c478bd9Sstevel@tonic-gate execsql { 927c478bd9Sstevel@tonic-gate SELECT q, p, r, b.[min(x)+y] 937c478bd9Sstevel@tonic-gate FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a, 947c478bd9Sstevel@tonic-gate (SELECT max(x) as r, y as s, min(x)+y FROM t1 GROUP BY y) as b 957c478bd9Sstevel@tonic-gate WHERE q=s ORDER BY s 967c478bd9Sstevel@tonic-gate } 977c478bd9Sstevel@tonic-gate} {1 1 1 2 2 2 3 4 3 4 7 7 4 8 15 12 5 5 20 21} 987c478bd9Sstevel@tonic-gate 997c478bd9Sstevel@tonic-gatedo_test select6-2.0 { 1007c478bd9Sstevel@tonic-gate execsql { 1017c478bd9Sstevel@tonic-gate CREATE TABLE t2(a INTEGER PRIMARY KEY, b); 1027c478bd9Sstevel@tonic-gate INSERT INTO t2 SELECT * FROM t1; 1037c478bd9Sstevel@tonic-gate SELECT DISTINCT b FROM t2 ORDER BY b; 1047c478bd9Sstevel@tonic-gate } 1057c478bd9Sstevel@tonic-gate} {1 2 3 4 5} 1067c478bd9Sstevel@tonic-gatedo_test select6-2.1 { 1077c478bd9Sstevel@tonic-gate execsql2 {SELECT * FROM (SELECT a, b FROM t2 WHERE a<2)} 1087c478bd9Sstevel@tonic-gate} {a 1 b 1} 1097c478bd9Sstevel@tonic-gatedo_test select6-2.2 { 1107c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM (SELECT b FROM t2)} 1117c478bd9Sstevel@tonic-gate} {20} 1127c478bd9Sstevel@tonic-gatedo_test select6-2.3 { 1137c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM (SELECT DISTINCT b FROM t2)} 1147c478bd9Sstevel@tonic-gate} {5} 1157c478bd9Sstevel@tonic-gatedo_test select6-2.4 { 1167c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT b FROM t2))} 1177c478bd9Sstevel@tonic-gate} {5} 1187c478bd9Sstevel@tonic-gatedo_test select6-2.5 { 1197c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT b FROM t2))} 1207c478bd9Sstevel@tonic-gate} {5} 1217c478bd9Sstevel@tonic-gate 1227c478bd9Sstevel@tonic-gatedo_test select6-2.6 { 1237c478bd9Sstevel@tonic-gate execsql { 124*1da57d55SToomas Soome SELECT * 1257c478bd9Sstevel@tonic-gate FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a, 1267c478bd9Sstevel@tonic-gate (SELECT max(a),b FROM t2 GROUP BY b) as b 1277c478bd9Sstevel@tonic-gate WHERE a.b=b.b ORDER BY a.b 1287c478bd9Sstevel@tonic-gate } 1297c478bd9Sstevel@tonic-gate} {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5} 1307c478bd9Sstevel@tonic-gatedo_test select6-2.7 { 1317c478bd9Sstevel@tonic-gate execsql { 1327c478bd9Sstevel@tonic-gate SELECT a.b, a.[count(*)], [max(a)], [count(*)] 1337c478bd9Sstevel@tonic-gate FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a, 1347c478bd9Sstevel@tonic-gate (SELECT max(a),b FROM t2 GROUP BY b) as b 1357c478bd9Sstevel@tonic-gate WHERE a.b=b.b ORDER BY a.b 1367c478bd9Sstevel@tonic-gate } 1377c478bd9Sstevel@tonic-gate} {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5} 1387c478bd9Sstevel@tonic-gatedo_test select6-2.8 { 1397c478bd9Sstevel@tonic-gate execsql { 1407c478bd9Sstevel@tonic-gate SELECT q, p, r 1417c478bd9Sstevel@tonic-gate FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY b) AS a, 1427c478bd9Sstevel@tonic-gate (SELECT max(a) as r, b as s FROM t2 GROUP BY b) as b 1437c478bd9Sstevel@tonic-gate WHERE q=s ORDER BY s 1447c478bd9Sstevel@tonic-gate } 1457c478bd9Sstevel@tonic-gate} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20} 1467c478bd9Sstevel@tonic-gatedo_test select6-2.9 { 1477c478bd9Sstevel@tonic-gate execsql { 1487c478bd9Sstevel@tonic-gate SELECT a.q, a.p, b.r 1497c478bd9Sstevel@tonic-gate FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a, 1507c478bd9Sstevel@tonic-gate (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b 1517c478bd9Sstevel@tonic-gate WHERE a.q=b.s ORDER BY a.q 1527c478bd9Sstevel@tonic-gate } 1537c478bd9Sstevel@tonic-gate} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20} 1547c478bd9Sstevel@tonic-gate 1557c478bd9Sstevel@tonic-gatedo_test sqlite6-3.1 { 1567c478bd9Sstevel@tonic-gate execsql2 { 1577c478bd9Sstevel@tonic-gate SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE x=3)); 1587c478bd9Sstevel@tonic-gate } 1597c478bd9Sstevel@tonic-gate} {x 3 y 2} 1607c478bd9Sstevel@tonic-gatedo_test sqlite6-3.2 { 1617c478bd9Sstevel@tonic-gate execsql { 1627c478bd9Sstevel@tonic-gate SELECT * FROM 1637c478bd9Sstevel@tonic-gate (SELECT a.q, a.p, b.r 1647c478bd9Sstevel@tonic-gate FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a, 1657c478bd9Sstevel@tonic-gate (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b 1667c478bd9Sstevel@tonic-gate WHERE a.q=b.s ORDER BY a.q) 1677c478bd9Sstevel@tonic-gate ORDER BY q 1687c478bd9Sstevel@tonic-gate } 1697c478bd9Sstevel@tonic-gate} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20} 1707c478bd9Sstevel@tonic-gatedo_test select6-3.3 { 1717c478bd9Sstevel@tonic-gate execsql { 1727c478bd9Sstevel@tonic-gate SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1) 1737c478bd9Sstevel@tonic-gate } 1747c478bd9Sstevel@tonic-gate} {10.5 3.7 14.2} 1757c478bd9Sstevel@tonic-gatedo_test select6-3.4 { 1767c478bd9Sstevel@tonic-gate execsql { 1777c478bd9Sstevel@tonic-gate SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4) 1787c478bd9Sstevel@tonic-gate } 1797c478bd9Sstevel@tonic-gate} {11.5 4 15.5} 1807c478bd9Sstevel@tonic-gatedo_test select6-3.5 { 1817c478bd9Sstevel@tonic-gate execsql { 1827c478bd9Sstevel@tonic-gate SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4) 1837c478bd9Sstevel@tonic-gate } 1847c478bd9Sstevel@tonic-gate} {4 3 7} 1857c478bd9Sstevel@tonic-gatedo_test select6-3.6 { 1867c478bd9Sstevel@tonic-gate execsql { 1877c478bd9Sstevel@tonic-gate SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1) 1887c478bd9Sstevel@tonic-gate WHERE a>10 1897c478bd9Sstevel@tonic-gate } 1907c478bd9Sstevel@tonic-gate} {10.5 3.7 14.2} 1917c478bd9Sstevel@tonic-gatedo_test select6-3.7 { 1927c478bd9Sstevel@tonic-gate execsql { 1937c478bd9Sstevel@tonic-gate SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1) 1947c478bd9Sstevel@tonic-gate WHERE a<10 1957c478bd9Sstevel@tonic-gate } 1967c478bd9Sstevel@tonic-gate} {} 1977c478bd9Sstevel@tonic-gatedo_test select6-3.8 { 1987c478bd9Sstevel@tonic-gate execsql { 1997c478bd9Sstevel@tonic-gate SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4) 2007c478bd9Sstevel@tonic-gate WHERE a>10 2017c478bd9Sstevel@tonic-gate } 2027c478bd9Sstevel@tonic-gate} {11.5 4 15.5} 2037c478bd9Sstevel@tonic-gatedo_test select6-3.9 { 2047c478bd9Sstevel@tonic-gate execsql { 2057c478bd9Sstevel@tonic-gate SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4) 2067c478bd9Sstevel@tonic-gate WHERE a<10 2077c478bd9Sstevel@tonic-gate } 2087c478bd9Sstevel@tonic-gate} {} 2097c478bd9Sstevel@tonic-gatedo_test select6-3.10 { 2107c478bd9Sstevel@tonic-gate execsql { 2117c478bd9Sstevel@tonic-gate SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b) 2127c478bd9Sstevel@tonic-gate ORDER BY a 2137c478bd9Sstevel@tonic-gate } 2147c478bd9Sstevel@tonic-gate} {1 1 2 2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23} 2157c478bd9Sstevel@tonic-gatedo_test select6-3.11 { 2167c478bd9Sstevel@tonic-gate execsql { 217*1da57d55SToomas Soome SELECT a,b,a+b FROM 2187c478bd9Sstevel@tonic-gate (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b) 2197c478bd9Sstevel@tonic-gate WHERE b<4 ORDER BY a 2207c478bd9Sstevel@tonic-gate } 2217c478bd9Sstevel@tonic-gate} {1 1 2 2.5 2 4.5 5.5 3 8.5} 2227c478bd9Sstevel@tonic-gatedo_test select6-3.12 { 2237c478bd9Sstevel@tonic-gate execsql { 224*1da57d55SToomas Soome SELECT a,b,a+b FROM 2257c478bd9Sstevel@tonic-gate (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1) 2267c478bd9Sstevel@tonic-gate WHERE b<4 ORDER BY a 2277c478bd9Sstevel@tonic-gate } 2287c478bd9Sstevel@tonic-gate} {2.5 2 4.5 5.5 3 8.5} 2297c478bd9Sstevel@tonic-gatedo_test select6-3.13 { 2307c478bd9Sstevel@tonic-gate execsql { 231*1da57d55SToomas Soome SELECT a,b,a+b FROM 2327c478bd9Sstevel@tonic-gate (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1) 2337c478bd9Sstevel@tonic-gate ORDER BY a 2347c478bd9Sstevel@tonic-gate } 2357c478bd9Sstevel@tonic-gate} {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23} 2367c478bd9Sstevel@tonic-gatedo_test select6-3.14 { 2377c478bd9Sstevel@tonic-gate execsql { 2387c478bd9Sstevel@tonic-gate SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y) 2397c478bd9Sstevel@tonic-gate ORDER BY [count(*)] 2407c478bd9Sstevel@tonic-gate } 2417c478bd9Sstevel@tonic-gate} {1 1 2 2 4 3 5 5 8 4} 2427c478bd9Sstevel@tonic-gatedo_test select6-3.15 { 2437c478bd9Sstevel@tonic-gate execsql { 2447c478bd9Sstevel@tonic-gate SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y) 2457c478bd9Sstevel@tonic-gate ORDER BY y 2467c478bd9Sstevel@tonic-gate } 2477c478bd9Sstevel@tonic-gate} {1 1 2 2 4 3 8 4 5 5} 2487c478bd9Sstevel@tonic-gate 2497c478bd9Sstevel@tonic-gatedo_test select6-4.1 { 2507c478bd9Sstevel@tonic-gate execsql { 251*1da57d55SToomas Soome SELECT a,b,c FROM 2527c478bd9Sstevel@tonic-gate (SELECT x AS 'a', y AS 'b', x+y AS 'c' FROM t1 WHERE y=4) 2537c478bd9Sstevel@tonic-gate WHERE a<10 ORDER BY a; 2547c478bd9Sstevel@tonic-gate } 2557c478bd9Sstevel@tonic-gate} {8 4 12 9 4 13} 2567c478bd9Sstevel@tonic-gatedo_test select6-4.2 { 2577c478bd9Sstevel@tonic-gate execsql { 2587c478bd9Sstevel@tonic-gate SELECT y FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y 2597c478bd9Sstevel@tonic-gate } 2607c478bd9Sstevel@tonic-gate} {1 2 3 4} 2617c478bd9Sstevel@tonic-gatedo_test select6-4.3 { 2627c478bd9Sstevel@tonic-gate execsql { 2637c478bd9Sstevel@tonic-gate SELECT DISTINCT y FROM (SELECT y FROM t1) WHERE y<5 ORDER BY y 2647c478bd9Sstevel@tonic-gate } 2657c478bd9Sstevel@tonic-gate} {1 2 3 4} 2667c478bd9Sstevel@tonic-gatedo_test select6-4.4 { 2677c478bd9Sstevel@tonic-gate execsql { 2687c478bd9Sstevel@tonic-gate SELECT avg(y) FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y 2697c478bd9Sstevel@tonic-gate } 2707c478bd9Sstevel@tonic-gate} {2.5} 2717c478bd9Sstevel@tonic-gatedo_test select6-4.5 { 2727c478bd9Sstevel@tonic-gate execsql { 2737c478bd9Sstevel@tonic-gate SELECT avg(y) FROM (SELECT DISTINCT y FROM t1 WHERE y<5) ORDER BY y 2747c478bd9Sstevel@tonic-gate } 2757c478bd9Sstevel@tonic-gate} {2.5} 2767c478bd9Sstevel@tonic-gate 2777c478bd9Sstevel@tonic-gatedo_test select6-5.1 { 2787c478bd9Sstevel@tonic-gate execsql { 2797c478bd9Sstevel@tonic-gate SELECT a,x,b FROM 2807c478bd9Sstevel@tonic-gate (SELECT x+3 AS 'a', x FROM t1 WHERE y=3) AS 'p', 2817c478bd9Sstevel@tonic-gate (SELECT x AS 'b' FROM t1 WHERE y=4) AS 'q' 2827c478bd9Sstevel@tonic-gate WHERE a=b 2837c478bd9Sstevel@tonic-gate ORDER BY a 2847c478bd9Sstevel@tonic-gate } 2857c478bd9Sstevel@tonic-gate} {8 5 8 9 6 9 10 7 10} 2867c478bd9Sstevel@tonic-gatedo_test select6-5.2 { 2877c478bd9Sstevel@tonic-gate execsql { 2887c478bd9Sstevel@tonic-gate SELECT a,x,b FROM 2897c478bd9Sstevel@tonic-gate (SELECT x+3 AS 'a', x FROM t1 WHERE y=3), 2907c478bd9Sstevel@tonic-gate (SELECT x AS 'b' FROM t1 WHERE y=4) 2917c478bd9Sstevel@tonic-gate WHERE a=b 2927c478bd9Sstevel@tonic-gate ORDER BY a 2937c478bd9Sstevel@tonic-gate } 2947c478bd9Sstevel@tonic-gate} {8 5 8 9 6 9 10 7 10} 2957c478bd9Sstevel@tonic-gate 2967c478bd9Sstevel@tonic-gate# Tests of compound sub-selects 2977c478bd9Sstevel@tonic-gate# 2987c478bd9Sstevel@tonic-gatedo_test select5-6.1 { 2997c478bd9Sstevel@tonic-gate execsql { 3007c478bd9Sstevel@tonic-gate DELETE FROM t1 WHERE x>4; 3017c478bd9Sstevel@tonic-gate SELECT * FROM t1 3027c478bd9Sstevel@tonic-gate } 3037c478bd9Sstevel@tonic-gate} {1 1 2 2 3 2 4 3} 3047c478bd9Sstevel@tonic-gatedo_test select6-6.2 { 3057c478bd9Sstevel@tonic-gate execsql { 3067c478bd9Sstevel@tonic-gate SELECT * FROM ( 3077c478bd9Sstevel@tonic-gate SELECT x AS 'a' FROM t1 UNION ALL SELECT x+10 AS 'a' FROM t1 3087c478bd9Sstevel@tonic-gate ) ORDER BY a; 3097c478bd9Sstevel@tonic-gate } 3107c478bd9Sstevel@tonic-gate} {1 2 3 4 11 12 13 14} 3117c478bd9Sstevel@tonic-gatedo_test select6-6.3 { 3127c478bd9Sstevel@tonic-gate execsql { 3137c478bd9Sstevel@tonic-gate SELECT * FROM ( 3147c478bd9Sstevel@tonic-gate SELECT x AS 'a' FROM t1 UNION ALL SELECT x+1 AS 'a' FROM t1 3157c478bd9Sstevel@tonic-gate ) ORDER BY a; 3167c478bd9Sstevel@tonic-gate } 3177c478bd9Sstevel@tonic-gate} {1 2 2 3 3 4 4 5} 3187c478bd9Sstevel@tonic-gatedo_test select6-6.4 { 3197c478bd9Sstevel@tonic-gate execsql { 3207c478bd9Sstevel@tonic-gate SELECT * FROM ( 3217c478bd9Sstevel@tonic-gate SELECT x AS 'a' FROM t1 UNION SELECT x+1 AS 'a' FROM t1 3227c478bd9Sstevel@tonic-gate ) ORDER BY a; 3237c478bd9Sstevel@tonic-gate } 3247c478bd9Sstevel@tonic-gate} {1 2 3 4 5} 3257c478bd9Sstevel@tonic-gatedo_test select6-6.5 { 3267c478bd9Sstevel@tonic-gate execsql { 3277c478bd9Sstevel@tonic-gate SELECT * FROM ( 3287c478bd9Sstevel@tonic-gate SELECT x AS 'a' FROM t1 INTERSECT SELECT x+1 AS 'a' FROM t1 3297c478bd9Sstevel@tonic-gate ) ORDER BY a; 3307c478bd9Sstevel@tonic-gate } 3317c478bd9Sstevel@tonic-gate} {2 3 4} 3327c478bd9Sstevel@tonic-gatedo_test select6-6.6 { 3337c478bd9Sstevel@tonic-gate execsql { 3347c478bd9Sstevel@tonic-gate SELECT * FROM ( 3357c478bd9Sstevel@tonic-gate SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1 3367c478bd9Sstevel@tonic-gate ) ORDER BY a; 3377c478bd9Sstevel@tonic-gate } 3387c478bd9Sstevel@tonic-gate} {1 3} 3397c478bd9Sstevel@tonic-gate 3407c478bd9Sstevel@tonic-gate# Subselects with no FROM clause 3417c478bd9Sstevel@tonic-gate# 3427c478bd9Sstevel@tonic-gatedo_test select6-7.1 { 3437c478bd9Sstevel@tonic-gate execsql { 3447c478bd9Sstevel@tonic-gate SELECT * FROM (SELECT 1) 3457c478bd9Sstevel@tonic-gate } 3467c478bd9Sstevel@tonic-gate} {1} 3477c478bd9Sstevel@tonic-gatedo_test select6-7.2 { 3487c478bd9Sstevel@tonic-gate execsql { 3497c478bd9Sstevel@tonic-gate SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c') 3507c478bd9Sstevel@tonic-gate } 3517c478bd9Sstevel@tonic-gate} {abc 2 1 1 2 abc} 3527c478bd9Sstevel@tonic-gatedo_test select6-7.3 { 3537c478bd9Sstevel@tonic-gate execsql { 3547c478bd9Sstevel@tonic-gate SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 0) 3557c478bd9Sstevel@tonic-gate } 3567c478bd9Sstevel@tonic-gate} {} 3577c478bd9Sstevel@tonic-gatedo_test select6-7.4 { 3587c478bd9Sstevel@tonic-gate execsql2 { 3597c478bd9Sstevel@tonic-gate SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1) 3607c478bd9Sstevel@tonic-gate } 3617c478bd9Sstevel@tonic-gate} {c abc b 2 a 1 a 1 b 2 c abc} 3627c478bd9Sstevel@tonic-gate 3637c478bd9Sstevel@tonic-gate# The following procedure compiles the SQL given as an argument and returns 3647c478bd9Sstevel@tonic-gate# TRUE if that SQL uses any transient tables and returns FALSE if no 3657c478bd9Sstevel@tonic-gate# transient tables are used. This is used to make sure that the 3667c478bd9Sstevel@tonic-gate# sqliteFlattenSubquery() routine in select.c is doing its job. 3677c478bd9Sstevel@tonic-gate# 3687c478bd9Sstevel@tonic-gateproc is_flat {sql} { 3697c478bd9Sstevel@tonic-gate return [expr 0>[lsearch [execsql "EXPLAIN $sql"] OpenTemp]] 3707c478bd9Sstevel@tonic-gate} 3717c478bd9Sstevel@tonic-gate 3727c478bd9Sstevel@tonic-gate# Check that the flattener works correctly for deeply nested subqueries 3737c478bd9Sstevel@tonic-gate# involving joins. 3747c478bd9Sstevel@tonic-gate# 3757c478bd9Sstevel@tonic-gatedo_test select6-8.1 { 3767c478bd9Sstevel@tonic-gate execsql { 3777c478bd9Sstevel@tonic-gate BEGIN; 3787c478bd9Sstevel@tonic-gate CREATE TABLE t3(p,q); 3797c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(1,11); 3807c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(2,22); 3817c478bd9Sstevel@tonic-gate CREATE TABLE t4(q,r); 3827c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(11,111); 3837c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(22,222); 3847c478bd9Sstevel@tonic-gate COMMIT; 3857c478bd9Sstevel@tonic-gate SELECT * FROM t3 NATURAL JOIN t4; 3867c478bd9Sstevel@tonic-gate } 3877c478bd9Sstevel@tonic-gate} {1 11 111 2 22 222} 3887c478bd9Sstevel@tonic-gatedo_test select6-8.2 { 3897c478bd9Sstevel@tonic-gate execsql { 3907c478bd9Sstevel@tonic-gate SELECT y, p, q, r FROM 3917c478bd9Sstevel@tonic-gate (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m, 3927c478bd9Sstevel@tonic-gate (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n 3937c478bd9Sstevel@tonic-gate WHERE y=p 3947c478bd9Sstevel@tonic-gate } 3957c478bd9Sstevel@tonic-gate} {1 1 11 111 2 2 22 222 2 2 22 222} 3967c478bd9Sstevel@tonic-gatedo_test select6-8.3 { 3977c478bd9Sstevel@tonic-gate is_flat { 3987c478bd9Sstevel@tonic-gate SELECT y, p, q, r FROM 3997c478bd9Sstevel@tonic-gate (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m, 4007c478bd9Sstevel@tonic-gate (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n 4017c478bd9Sstevel@tonic-gate WHERE y=p 4027c478bd9Sstevel@tonic-gate } 4037c478bd9Sstevel@tonic-gate} {1} 4047c478bd9Sstevel@tonic-gatedo_test select6-8.4 { 4057c478bd9Sstevel@tonic-gate execsql { 4067c478bd9Sstevel@tonic-gate SELECT DISTINCT y, p, q, r FROM 4077c478bd9Sstevel@tonic-gate (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m, 4087c478bd9Sstevel@tonic-gate (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n 4097c478bd9Sstevel@tonic-gate WHERE y=p 4107c478bd9Sstevel@tonic-gate } 4117c478bd9Sstevel@tonic-gate} {1 1 11 111 2 2 22 222} 4127c478bd9Sstevel@tonic-gatedo_test select6-8.5 { 4137c478bd9Sstevel@tonic-gate execsql { 414*1da57d55SToomas Soome SELECT * FROM 4157c478bd9Sstevel@tonic-gate (SELECT y, p, q, r FROM 4167c478bd9Sstevel@tonic-gate (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m, 4177c478bd9Sstevel@tonic-gate (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n 4187c478bd9Sstevel@tonic-gate WHERE y=p) AS e, 4197c478bd9Sstevel@tonic-gate (SELECT r AS z FROM t4 WHERE q=11) AS f 4207c478bd9Sstevel@tonic-gate WHERE e.r=f.z 4217c478bd9Sstevel@tonic-gate } 4227c478bd9Sstevel@tonic-gate} {1 1 11 111 111} 4237c478bd9Sstevel@tonic-gatedo_test select6-8.6 { 4247c478bd9Sstevel@tonic-gate is_flat { 425*1da57d55SToomas Soome SELECT * FROM 4267c478bd9Sstevel@tonic-gate (SELECT y, p, q, r FROM 4277c478bd9Sstevel@tonic-gate (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m, 4287c478bd9Sstevel@tonic-gate (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n 4297c478bd9Sstevel@tonic-gate WHERE y=p) AS e, 4307c478bd9Sstevel@tonic-gate (SELECT r AS z FROM t4 WHERE q=11) AS f 4317c478bd9Sstevel@tonic-gate WHERE e.r=f.z 4327c478bd9Sstevel@tonic-gate } 4337c478bd9Sstevel@tonic-gate} {1} 4347c478bd9Sstevel@tonic-gate 4357c478bd9Sstevel@tonic-gate 4367c478bd9Sstevel@tonic-gatefinish_test 437