/ Check-in [8d9ee45a]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8d9ee45ab487614191e8d6aef845141fdddf832a
User & Date: drh 2003-05-02 16:44:25
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: fd28c522 user: jplyon tags: trunk
2003-05-02
16:44
Additional tests of the new flattener added. Ticket #272. (CVS 949) check-in: 8d9ee45a 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: ad57693e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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.9 2002/04/30 19:20:29 drh Exp $
           15  +# $Id: select6.test,v 1.10 2003/05/02 16:44:25 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   do_test select6-1.0 {
    21     21     execsql {
    22     22       BEGIN;
................................................................................
   354    354     }
   355    355   } {}
   356    356   do_test select6-7.4 {
   357    357     execsql2 {
   358    358       SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
   359    359     }
   360    360   } {c abc b 2 a 1 a 1 b 2 c abc}
          361  +
          362  +# The following procedure compiles the SQL given as an argument and returns
          363  +# TRUE if that SQL uses any transient tables and returns FALSE if no
          364  +# transient tables are used.  This is used to make sure that the
          365  +# sqliteFlattenSubquery() routine in select.c is doing its job.
          366  +#
          367  +proc is_flat {sql} {
          368  +  return [expr 0>[lsearch [execsql "EXPLAIN $sql"] OpenTemp]]
          369  +}
          370  +
          371  +# Check that the flattener works correctly for deeply nested subqueries
          372  +# involving joins.
          373  +#
          374  +do_test select6-8.1 {
          375  +  execsql {
          376  +    BEGIN;
          377  +    CREATE TABLE t3(p,q);
          378  +    INSERT INTO t3 VALUES(1,11);
          379  +    INSERT INTO t3 VALUES(2,22);
          380  +    CREATE TABLE t4(q,r);
          381  +    INSERT INTO t4 VALUES(11,111);
          382  +    INSERT INTO t4 VALUES(22,222);
          383  +    COMMIT;
          384  +    SELECT * FROM t3 NATURAL JOIN t4;
          385  +  }
          386  +} {1 11 111 2 22 222}
          387  +do_test select6-8.2 {
          388  +  execsql {
          389  +    SELECT y, p, q, r FROM
          390  +       (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
          391  +       (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
          392  +    WHERE  y=p
          393  +  }
          394  +} {1 1 11 111 2 2 22 222 2 2 22 222}
          395  +do_test select6-8.3 {
          396  +  is_flat {
          397  +    SELECT y, p, q, r FROM
          398  +       (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
          399  +       (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
          400  +    WHERE  y=p
          401  +  }
          402  +} {1}
          403  +do_test select6-8.4 {
          404  +  execsql {
          405  +    SELECT DISTINCT y, p, q, r FROM
          406  +       (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
          407  +       (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
          408  +    WHERE  y=p
          409  +  }
          410  +} {1 1 11 111 2 2 22 222}
          411  +do_test select6-8.5 {
          412  +  execsql {
          413  +    SELECT * FROM 
          414  +      (SELECT y, p, q, r FROM
          415  +         (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
          416  +         (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
          417  +      WHERE  y=p) AS e,
          418  +      (SELECT r AS z FROM t4 WHERE q=11) AS f
          419  +    WHERE e.r=f.z
          420  +  }
          421  +} {1 1 11 111 111}
          422  +do_test select6-8.6 {
          423  +  is_flat {
          424  +    SELECT * FROM 
          425  +      (SELECT y, p, q, r FROM
          426  +         (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
          427  +         (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
          428  +      WHERE  y=p) AS e,
          429  +      (SELECT r AS z FROM t4 WHERE q=11) AS f
          430  +    WHERE e.r=f.z
          431  +  }
          432  +} {1}
          433  +
   361    434   
   362    435   finish_test