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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
a0019fce701fc858134f0a33bda9a511 |
User & Date: | drh 2002-02-18 13:35:33.000 |
Context
2002-02-18
| ||
13:56 | Allow general expressions in the VALUES clause of an INSERT statement. (CVS 376) (check-in: ec1f3fae6f user: drh tags: trunk) | |
13:35 | Additional tests for the sub-query feature. (CVS 375) (check-in: a0019fce70 user: drh tags: trunk) | |
12:48 | Enhancement to Windows "file-exists" function by Joel Lucsy. (CVS 374) (check-in: d3d59261da user: drh tags: trunk) | |
Changes
Changes to test/select6.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # subqueries in their FROM clause. # | | | < < < < < < < > | | < > > > > > > > | | | > > > > > > > > > > | > | | | | | | | > > > > > > | > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # subqueries in their FROM clause. # # $Id: select6.test,v 1.2 2002/02/18 13:35:33 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test select6-1.0 { 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 WHERE x<2)} } {x 1 y 1} do_test select6-1.2 { execsql {SELECT count(*) FROM (SELECT y FROM t1)} } {20} do_test select6-1.3 { execsql {SELECT count(*) FROM (SELECT DISTINCT y FROM t1)} } {5} do_test select6-1.4 { execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT y FROM t1))} } {5} do_test select6-1.5 { execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT y FROM t1))} } {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 |
Changes to www/changes.tcl.
︙ | ︙ | |||
16 17 18 19 20 21 22 | puts "<DT><B>$date</B></DT>" puts "<DD><P><UL>$desc</UL></P></DD>" } chng {2002 Feb * (2.3.3)} { <li>Allow identifiers to be quoted in square brackets, for compatibility with MS-Access.</li> | | > > | 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | puts "<DT><B>$date</B></DT>" puts "<DD><P><UL>$desc</UL></P></DD>" } 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>More efficient implementation of sqliteFileExists() under Windows. (by Joel Luscy)</li> } chng {2002 Feb 14 (2.3.2)} { <li>Bug fix: There was an incorrect assert() in pager.c. The real code was all correct (as far as is known) so everything should work OK if you compile with -DNDEBUG=1. But without disability the assertions, there could be a fault.</li> |
︙ | ︙ |