/ Check-in [a0019fce]
Login

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:a0019fce701fc858134f0a33bda9a511e41a09f8
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
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.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>