Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Additional tests for the sub-query feature. (CVS 375) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | a0019fce701fc858134f0a33bda9a511 |
| User & Date: | drh 2002-02-18 13:35:33 |
Context
|
2002-02-18
| ||
| 13:56 | Allow general expressions in the VALUES clause of an INSERT statement. (CVS 376) check-in: ec1f3fae user: drh tags: trunk | |
| 13:35 | Additional tests for the sub-query feature. (CVS 375) check-in: a0019fce user: drh tags: trunk | |
| 12:48 | Enhancement to Windows "file-exists" function by Joel Lucsy. (CVS 374) check-in: d3d59261 user: drh tags: trunk | |
Changes
Changes to test/select6.test.
8 8 # May you share freely, never taking more than you give. 9 9 # 10 10 #*********************************************************************** 11 11 # This file implements regression tests for SQLite library. The 12 12 # focus of this file is testing SELECT statements that contain 13 13 # subqueries in their FROM clause. 14 14 # 15 -# $Id: select6.test,v 1.1 2002/02/18 03:21:47 drh Exp $ 15 +# $Id: select6.test,v 1.2 2002/02/18 13:35:33 drh Exp $ 16 16 17 17 set testdir [file dirname $argv0] 18 18 source $testdir/tester.tcl 19 19 20 -# Build some test data 21 -# 22 -set fd [open data1.txt w] 23 -for {set i 1} {$i<32} {incr i} { 24 - for {set j 0} {pow(2,$j)<$i} {incr j} {} 25 - puts $fd "[expr {32-$i}]\t[expr {10-$j}]" 26 -} 27 -close $fd 28 -execsql { 29 - CREATE TABLE t1(x int, y int); 30 - COPY t1 FROM 'data1.txt' 31 -} 32 -file delete data1.txt 33 - 34 20 do_test select6-1.0 { 35 - execsql {SELECT DISTINCT y FROM t1 ORDER BY y} 36 -} {5 6 7 8 9 10} 21 + execsql { 22 + BEGIN; 23 + CREATE TABLE t1(x, y); 24 + INSERT INTO t1 VALUES(1,1); 25 + INSERT INTO t1 VALUES(2,2); 26 + INSERT INTO t1 VALUES(3,2); 27 + INSERT INTO t1 VALUES(4,3); 28 + INSERT INTO t1 VALUES(5,3); 29 + INSERT INTO t1 VALUES(6,3); 30 + INSERT INTO t1 VALUES(7,3); 31 + INSERT INTO t1 VALUES(8,4); 32 + INSERT INTO t1 VALUES(9,4); 33 + INSERT INTO t1 VALUES(10,4); 34 + INSERT INTO t1 VALUES(11,4); 35 + INSERT INTO t1 VALUES(12,4); 36 + INSERT INTO t1 VALUES(13,4); 37 + INSERT INTO t1 VALUES(14,4); 38 + INSERT INTO t1 VALUES(15,4); 39 + INSERT INTO t1 VALUES(16,5); 40 + INSERT INTO t1 VALUES(17,5); 41 + INSERT INTO t1 VALUES(18,5); 42 + INSERT INTO t1 VALUES(19,5); 43 + INSERT INTO t1 VALUES(20,5); 44 + COMMIT; 45 + SELECT DISTINCT y FROM t1 ORDER BY y; 46 + } 47 +} {1 2 3 4 5} 37 48 38 49 do_test select6-1.1 { 39 - execsql2 {SELECT * FROM (SELECT x, y FROM t1 ORDER BY x LIMIT 1)} 40 -} {x 31 y 10} 50 + execsql2 {SELECT * FROM (SELECT x, y FROM t1 WHERE x<2)} 51 +} {x 1 y 1} 41 52 do_test select6-1.2 { 42 53 execsql {SELECT count(*) FROM (SELECT y FROM t1)} 43 -} {31} 54 +} {20} 44 55 do_test select6-1.3 { 45 56 execsql {SELECT count(*) FROM (SELECT DISTINCT y FROM t1)} 46 -} {6} 57 +} {5} 47 58 do_test select6-1.4 { 48 59 execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT y FROM t1))} 49 -} {6} 60 +} {5} 50 61 do_test select6-1.5 { 51 62 execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT y FROM t1))} 52 -} {6} 63 +} {5} 64 + 65 +do_test select6-1.6 { 66 + execsql { 67 + SELECT * 68 + FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a, 69 + (SELECT max(x),y FROM t1 GROUP BY y) as b 70 + WHERE a.y=b.y ORDER BY a.y 71 + } 72 +} {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5} 73 +do_test select6-1.7 { 74 + execsql { 75 + SELECT a.y, a.[count(*)], [max(x)], [count(*)] 76 + FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a, 77 + (SELECT max(x),y FROM t1 GROUP BY y) as b 78 + WHERE a.y=b.y ORDER BY a.y 79 + } 80 +} {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5} 81 +do_test select6-1.8 { 82 + execsql { 83 + SELECT q, p, r 84 + FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a, 85 + (SELECT max(x) as r, y as s FROM t1 GROUP BY y) as b 86 + WHERE q=s ORDER BY s 87 + } 88 +} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20} 89 +do_test select6-1.9 { 90 + execsql { 91 + SELECT q, p, r, b.[min(x)+y] 92 + FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a, 93 + (SELECT max(x) as r, y as s, min(x)+y FROM t1 GROUP BY y) as b 94 + WHERE q=s ORDER BY s 95 + } 96 +} {1 1 1 2 2 2 3 4 3 4 7 7 4 8 15 12 5 5 20 21} 97 + 98 +do_test select6-2.0 { 99 + execsql { 100 + CREATE TABLE t2(a INTEGER PRIMARY KEY, b); 101 + INSERT INTO t2 SELECT * FROM t1; 102 + SELECT DISTINCT b FROM t2 ORDER BY b; 103 + } 104 +} {1 2 3 4 5} 105 +do_test select6-2.1 { 106 + execsql2 {SELECT * FROM (SELECT a, b FROM t2 WHERE a<2)} 107 +} {a 1 b 1} 108 +do_test select6-2.2 { 109 + execsql {SELECT count(*) FROM (SELECT b FROM t2)} 110 +} {20} 111 +do_test select6-2.3 { 112 + execsql {SELECT count(*) FROM (SELECT DISTINCT b FROM t2)} 113 +} {5} 114 +do_test select6-2.4 { 115 + execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT b FROM t2))} 116 +} {5} 117 +do_test select6-2.5 { 118 + execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT b FROM t2))} 119 +} {5} 53 120 121 +do_test select6-2.6 { 122 + execsql { 123 + SELECT * 124 + FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a, 125 + (SELECT max(a),b FROM t2 GROUP BY b) as b 126 + WHERE a.b=b.b ORDER BY a.b 127 + } 128 +} {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5} 129 +do_test select6-2.7 { 130 + execsql { 131 + SELECT a.b, a.[count(*)], [max(a)], [count(*)] 132 + FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a, 133 + (SELECT max(a),b FROM t2 GROUP BY b) as b 134 + WHERE a.b=b.b ORDER BY a.b 135 + } 136 +} {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5} 137 +do_test select6-2.8 { 138 + execsql { 139 + SELECT q, p, r 140 + FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY b) AS a, 141 + (SELECT max(a) as r, b as s FROM t2 GROUP BY b) as b 142 + WHERE q=s ORDER BY s 143 + } 144 +} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20} 145 +do_test select6-2.9 { 146 + execsql { 147 + SELECT a.q, a.p, b.r 148 + FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a, 149 + (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b 150 + WHERE a.q=b.s ORDER BY a.q 151 + } 152 +} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20} 54 153 55 - 154 +do_test sqlite6-3.1 { 155 + execsql2 { 156 + SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE x=3)); 157 + } 158 +} {x 3 y 2} 159 +do_test sqlite6-3.2 { 160 + execsql { 161 + SELECT * FROM 162 + (SELECT a.q, a.p, b.r 163 + FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a, 164 + (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b 165 + WHERE a.q=b.s ORDER BY a.q) 166 + ORDER BY [a.q] 167 + } 168 +} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20} 56 169 finish_test
Changes to www/changes.tcl.
16 16 puts "<DT><B>$date</B></DT>" 17 17 puts "<DD><P><UL>$desc</UL></P></DD>" 18 18 } 19 19 20 20 chng {2002 Feb * (2.3.3)} { 21 21 <li>Allow identifiers to be quoted in square brackets, for compatibility 22 22 with MS-Access.</li> 23 -<li>Added support for sub-queries in the FROM clause of a SELECT</li> 23 +<li>Added support for sub-queries in the FROM clause of a SELECT.</li> 24 +<li>More efficient implementation of sqliteFileExists() under Windows. 25 + (by Joel Luscy)</li> 24 26 } 25 27 26 28 chng {2002 Feb 14 (2.3.2)} { 27 29 <li>Bug fix: There was an incorrect assert() in pager.c. The real code was 28 30 all correct (as far as is known) so everything should work OK if you 29 31 compile with -DNDEBUG=1. But without disability the assertions, there 30 32 could be a fault.</li>