From cf9095061c8c13bd720c79396fcb5afb9b85a6c1 Mon Sep 17 00:00:00 2001
From: drh <drh@noemail.net>
Date: Mon, 18 Feb 2002 13:35:33 +0000
Subject: [PATCH] Additional tests for the sub-query feature. (CVS 375)

FossilOrigin-Name: a0019fce701fc858134f0a33bda9a511e41a09f8
---
 manifest          |  14 ++--
 manifest.uuid     |   2 +-
 test/select6.test | 159 +++++++++++++++++++++++++++++++++++++++-------
 www/changes.tcl   |   4 +-
 4 files changed, 147 insertions(+), 32 deletions(-)

diff --git a/manifest b/manifest
index 160d77d9b7..b11f365268 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Enhancement\sto\sWindows\s"file-exists"\sfunction\sby\sJoel\sLucsy.\s(CVS\s374)
-D 2002-02-18T12:48:46
+C Additional\stests\sfor\sthe\ssub-query\sfeature.\s(CVS\s375)
+D 2002-02-18T13:35:33
 F Makefile.in 9fa4277413bf1d9cf91365f07d4108d7d87ed2af
 F Makefile.template 3372d45f8853afdb70bd30cc6fb50a3cd9069834
 F README a4c0ba11354ef6ba0776b400d057c59da47a4cc0
@@ -84,7 +84,7 @@ F test/select2.test ed2c1882857106b85478f54f67000e14966be4c4
 F test/select3.test 9469c332250a75a0ef1771fb5da62dc04ec77f18
 F test/select4.test 29a2ffb187f3d8b6ca42a0a6b619e9cabe12e228
 F test/select5.test c2a6c4a003316ee42cbbd689eebef8fdce0db2ac
-F test/select6.test 510377ac09e3b5de9a164eea7732395c6813f0ca
+F test/select6.test d9fb417d6cab75a072b547ba6303120f327fd6fd
 F test/sort.test 3b996ce7ca385f9cd559944ac0f4027a23aa546b
 F test/subselect.test 335d3dad8d585726c447dfee8d9c4f7383c76b78
 F test/table.test 3ef4254d62ece31a3872ab11cdaec846f6fa8fd1
@@ -108,7 +108,7 @@ F www/arch.fig d5f9752a4dbf242e9cfffffd3f5762b6c63b3bcf
 F www/arch.png 82ef36db1143828a7abc88b1e308a5f55d4336f4
 F www/arch.tcl 72a0c80e9054cc7025a50928d28d9c75c02c2b8b
 F www/c_interface.tcl 82a026b1681757f13b3f62e035f3a31407c1d353
-F www/changes.tcl f10f6552731c9b11a8f73d6d900320b29b56b656
+F www/changes.tcl 836ec1f2e87e5d21c4cbc1f7531194e84962dd94
 F www/conflict.tcl 81dd21f9a679e60aae049e9dd8ab53d59570cda2
 F www/crosscompile.tcl 3622ebbe518927a3854a12de51344673eb2dd060
 F www/download.tcl a6d75b8b117cd33dcb090bef7e80d7556d28ebe0
@@ -123,7 +123,7 @@ F www/speed.tcl 83457b2bf6bb430900bd48ca3dd98264d9a916a5
 F www/sqlite.tcl 8b5884354cb615049aed83039f8dfe1552a44279
 F www/tclsqlite.tcl 829b393d1ab187fd7a5e978631b3429318885c49
 F www/vdbe.tcl 2013852c27a02a091d39a766bc87cff329f21218
-P 607c0c49b2098771020514198cb1076de8245a62
-R 3af6afadbeb571c12afffc634ba291bc
+P d3d59261da9bef3250e99444167ad6ef98764574
+R 76e39ed07dfb31018506c2491ca420cc
 U drh
-Z 12bafaf1d5ee9acb4349af632ae545b5
+Z 6f65c1d3be6a2b9892d59e07f4ed69c8
diff --git a/manifest.uuid b/manifest.uuid
index acc602e4b3..d27a29919f 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-d3d59261da9bef3250e99444167ad6ef98764574
\ No newline at end of file
+a0019fce701fc858134f0a33bda9a511e41a09f8
\ No newline at end of file
diff --git a/test/select6.test b/test/select6.test
index 305066eeb4..fbad950c29 100644
--- a/test/select6.test
+++ b/test/select6.test
@@ -12,45 +12,158 @@
 # focus of this file is testing SELECT statements that contain
 # subqueries in their FROM clause.
 #
-# $Id: select6.test,v 1.1 2002/02/18 03:21:47 drh Exp $
+# $Id: select6.test,v 1.2 2002/02/18 13:35:33 drh Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 
-# Build some test data
-#
-set fd [open data1.txt w]
-for {set i 1} {$i<32} {incr i} {
-  for {set j 0} {pow(2,$j)<$i} {incr j} {}
-  puts $fd "[expr {32-$i}]\t[expr {10-$j}]"
-}
-close $fd
-execsql {
-  CREATE TABLE t1(x int, y int);
-  COPY t1 FROM 'data1.txt'
-}
-file delete data1.txt
-
 do_test select6-1.0 {
-  execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
-} {5 6 7 8 9 10}
+  execsql {
+    BEGIN;
+    CREATE TABLE t1(x, y);
+    INSERT INTO t1 VALUES(1,1);
+    INSERT INTO t1 VALUES(2,2);
+    INSERT INTO t1 VALUES(3,2);
+    INSERT INTO t1 VALUES(4,3);
+    INSERT INTO t1 VALUES(5,3);
+    INSERT INTO t1 VALUES(6,3);
+    INSERT INTO t1 VALUES(7,3);
+    INSERT INTO t1 VALUES(8,4);
+    INSERT INTO t1 VALUES(9,4);
+    INSERT INTO t1 VALUES(10,4);
+    INSERT INTO t1 VALUES(11,4);
+    INSERT INTO t1 VALUES(12,4);
+    INSERT INTO t1 VALUES(13,4);
+    INSERT INTO t1 VALUES(14,4);
+    INSERT INTO t1 VALUES(15,4);
+    INSERT INTO t1 VALUES(16,5);
+    INSERT INTO t1 VALUES(17,5);
+    INSERT INTO t1 VALUES(18,5);
+    INSERT INTO t1 VALUES(19,5);
+    INSERT INTO t1 VALUES(20,5);
+    COMMIT;
+    SELECT DISTINCT y FROM t1 ORDER BY y;
+  }
+} {1 2 3 4 5}
 
 do_test select6-1.1 {
-  execsql2 {SELECT * FROM (SELECT x, y FROM t1 ORDER BY x LIMIT 1)}
-} {x 31 y 10}
+  execsql2 {SELECT * FROM (SELECT x, y FROM t1 WHERE x<2)}
+} {x 1 y 1}
 do_test select6-1.2 {
   execsql {SELECT count(*) FROM (SELECT y FROM t1)}
-} {31}
+} {20}
 do_test select6-1.3 {
   execsql {SELECT count(*) FROM (SELECT DISTINCT y FROM t1)}
-} {6}
+} {5}
 do_test select6-1.4 {
   execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT y FROM t1))}
-} {6}
+} {5}
 do_test select6-1.5 {
   execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT y FROM t1))}
-} {6}
+} {5}
+
+do_test select6-1.6 {
+  execsql {
+    SELECT * 
+    FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a,
+         (SELECT max(x),y FROM t1 GROUP BY y) as b
+    WHERE a.y=b.y ORDER BY a.y
+  }
+} {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5}
+do_test select6-1.7 {
+  execsql {
+    SELECT a.y, a.[count(*)], [max(x)], [count(*)]
+    FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a,
+         (SELECT max(x),y FROM t1 GROUP BY y) as b
+    WHERE a.y=b.y ORDER BY a.y
+  }
+} {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5}
+do_test select6-1.8 {
+  execsql {
+    SELECT q, p, r
+    FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
+         (SELECT max(x) as r, y as s FROM t1 GROUP BY y) as b
+    WHERE q=s ORDER BY s
+  }
+} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
+do_test select6-1.9 {
+  execsql {
+    SELECT q, p, r, b.[min(x)+y]
+    FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
+         (SELECT max(x) as r, y as s, min(x)+y FROM t1 GROUP BY y) as b
+    WHERE q=s ORDER BY s
+  }
+} {1 1 1 2 2 2 3 4 3 4 7 7 4 8 15 12 5 5 20 21}
 
+do_test select6-2.0 {
+  execsql {
+    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
+    INSERT INTO t2 SELECT * FROM t1;
+    SELECT DISTINCT b FROM t2 ORDER BY b;
+  }
+} {1 2 3 4 5}
+do_test select6-2.1 {
+  execsql2 {SELECT * FROM (SELECT a, b FROM t2 WHERE a<2)}
+} {a 1 b 1}
+do_test select6-2.2 {
+  execsql {SELECT count(*) FROM (SELECT b FROM t2)}
+} {20}
+do_test select6-2.3 {
+  execsql {SELECT count(*) FROM (SELECT DISTINCT b FROM t2)}
+} {5}
+do_test select6-2.4 {
+  execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT b FROM t2))}
+} {5}
+do_test select6-2.5 {
+  execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT b FROM t2))}
+} {5}
 
+do_test select6-2.6 {
+  execsql {
+    SELECT * 
+    FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a,
+         (SELECT max(a),b FROM t2 GROUP BY b) as b
+    WHERE a.b=b.b ORDER BY a.b
+  }
+} {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5}
+do_test select6-2.7 {
+  execsql {
+    SELECT a.b, a.[count(*)], [max(a)], [count(*)]
+    FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a,
+         (SELECT max(a),b FROM t2 GROUP BY b) as b
+    WHERE a.b=b.b ORDER BY a.b
+  }
+} {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5}
+do_test select6-2.8 {
+  execsql {
+    SELECT q, p, r
+    FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY b) AS a,
+         (SELECT max(a) as r, b as s FROM t2 GROUP BY b) as b
+    WHERE q=s ORDER BY s
+  }
+} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
+do_test select6-2.9 {
+  execsql {
+    SELECT a.q, a.p, b.r
+    FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
+         (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
+    WHERE a.q=b.s ORDER BY a.q
+  }
+} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
 
+do_test sqlite6-3.1 {
+  execsql2 {
+    SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE x=3));
+  }
+} {x 3 y 2}
+do_test sqlite6-3.2 {
+  execsql {
+    SELECT * FROM
+      (SELECT a.q, a.p, b.r
+       FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
+            (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
+       WHERE a.q=b.s ORDER BY a.q)
+    ORDER BY [a.q]
+  }
+} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
 finish_test
diff --git a/www/changes.tcl b/www/changes.tcl
index 1032aef323..d6b6c8e662 100644
--- a/www/changes.tcl
+++ b/www/changes.tcl
@@ -20,7 +20,9 @@ proc chng {date desc} {
 chng {2002 Feb * (2.3.3)} {
 <li>Allow identifiers to be quoted in square brackets, for compatibility
     with MS-Access.</li>
-<li>Added support for sub-queries in the FROM clause of a SELECT</li>
+<li>Added support for sub-queries in the FROM clause of a SELECT.</li>
+<li>More efficient implementation of sqliteFileExists() under Windows.
+    (by Joel Luscy)</li>
 }
 
 chng {2002 Feb 14 (2.3.2)} {
-- 
2.47.3