Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Additional tests of the new flattener added. Ticket #272. (CVS 949) |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
8d9ee45ab487614191e8d6aef845141f |
| User & Date: | drh 2003-05-02 16:44:25.000 |
Context
|
2003-05-03
| ||
| 04:55 | - added entry for ATTACH DATABASE. - added entry for DETACH DATABASE. - added soundex() function. - added some new TEMP modifiers. - added new VACUUM behavior. - added the Oracle8 outer join "(+)" syntax. - documented the modulus/remainder operator %. - added ALL as alternative to DISTINCT in SELECT. - fixed assignment list in UPDATE to use * metacharacter. - fixed value in PRAGMA to be optional. - added link from INSERT topic to REPLACE. - added optional CONSTRAINT <name> before column constraints. - skip 2 lines before each Section in the TCL source. (CVS 950) (check-in: fd28c5229e user: jplyon tags: trunk) | |
|
2003-05-02
| ||
| 16:44 | Additional tests of the new flattener added. Ticket #272. (CVS 949) (check-in: 8d9ee45ab4 user: drh tags: trunk) | |
| 16:04 | Enhance the query flattener to handle subqueries that are joins. All regressions pass but new tests need to be added before release. Ticket #272. (CVS 948) (check-in: ad57693e9f 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 |
# 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.10 2003/05/02 16:44:25 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
do_test select6-1.0 {
execsql {
BEGIN;
|
| ︙ | ︙ | |||
354 355 356 357 358 359 360 361 362 |
}
} {}
do_test select6-7.4 {
execsql2 {
SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
}
} {c abc b 2 a 1 a 1 b 2 c abc}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 |
}
} {}
do_test select6-7.4 {
execsql2 {
SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
}
} {c abc b 2 a 1 a 1 b 2 c abc}
# The following procedure compiles the SQL given as an argument and returns
# TRUE if that SQL uses any transient tables and returns FALSE if no
# transient tables are used. This is used to make sure that the
# sqliteFlattenSubquery() routine in select.c is doing its job.
#
proc is_flat {sql} {
return [expr 0>[lsearch [execsql "EXPLAIN $sql"] OpenTemp]]
}
# Check that the flattener works correctly for deeply nested subqueries
# involving joins.
#
do_test select6-8.1 {
execsql {
BEGIN;
CREATE TABLE t3(p,q);
INSERT INTO t3 VALUES(1,11);
INSERT INTO t3 VALUES(2,22);
CREATE TABLE t4(q,r);
INSERT INTO t4 VALUES(11,111);
INSERT INTO t4 VALUES(22,222);
COMMIT;
SELECT * FROM t3 NATURAL JOIN t4;
}
} {1 11 111 2 22 222}
do_test select6-8.2 {
execsql {
SELECT y, p, q, r FROM
(SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
(SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
WHERE y=p
}
} {1 1 11 111 2 2 22 222 2 2 22 222}
do_test select6-8.3 {
is_flat {
SELECT y, p, q, r FROM
(SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
(SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
WHERE y=p
}
} {1}
do_test select6-8.4 {
execsql {
SELECT DISTINCT y, p, q, r FROM
(SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
(SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
WHERE y=p
}
} {1 1 11 111 2 2 22 222}
do_test select6-8.5 {
execsql {
SELECT * FROM
(SELECT y, p, q, r FROM
(SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
(SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
WHERE y=p) AS e,
(SELECT r AS z FROM t4 WHERE q=11) AS f
WHERE e.r=f.z
}
} {1 1 11 111 111}
do_test select6-8.6 {
is_flat {
SELECT * FROM
(SELECT y, p, q, r FROM
(SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
(SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
WHERE y=p) AS e,
(SELECT r AS z FROM t4 WHERE q=11) AS f
WHERE e.r=f.z
}
} {1}
finish_test
|