/ Check-in [1b6405d9]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add a few more CTE test cases to closure.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:1b6405d9788c1bb89761b2bcdce560a5020ff503
User & Date: drh 2014-01-24 15:42:51
Context
2014-01-24
16:36
Use an unsigned integer to accumulate the string hash. Avoids compiler warnings. check-in: b1824344 user: drh tags: trunk
15:42
Add a few more CTE test cases to closure.test. check-in: 1b6405d9 user: drh tags: trunk
14:37
Add test cases that compare the performance of the transitive_closure virtual table again common table expressions for walking a tree. check-in: 9a23f020 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/closure01.test.

    43     43         SELECT t1.x, below.depth+1
    44     44           FROM t1 JOIN below on t1.y=below.id
    45     45       )
    46     46     SELECT count(*), depth FROM below GROUP BY depth ORDER BY 1;
    47     47   } {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/}
    48     48   
    49     49   # descendents of 32768
    50         -do_execsql_test 1.2 {
           50  +do_timed_execsql_test 1.2 {
    51     51     SELECT * FROM cx WHERE root=32768 ORDER BY id;
    52     52   } {32768 0 65536 1 65537 1 131072 2}
           53  +do_timed_execsql_test 1.2-cte {
           54  +  WITH RECURSIVE
           55  +    below(id,depth) AS (
           56  +      VALUES(32768,0)
           57  +       UNION ALL
           58  +      SELECT t1.x, below.depth+1
           59  +        FROM t1 JOIN below on t1.y=below.id
           60  +       WHERE below.depth<2
           61  +    )
           62  +  SELECT id, depth FROM below ORDER BY id;
           63  +} {32768 0 65536 1 65537 1 131072 2}
    53     64   
    54     65   # descendents of 16384
    55     66   do_timed_execsql_test 1.3 {
    56     67     SELECT * FROM cx WHERE root=16384 AND depth<=2 ORDER BY id;
    57     68   } {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2}
    58     69   do_timed_execsql_test 1.3-cte {
    59     70     WITH RECURSIVE
................................................................................
    72     83     SELECT id, depth, root, tablename, idcolumn, parentcolumn FROM cx
    73     84      WHERE root=16384
    74     85        AND depth=1
    75     86      ORDER BY id;
    76     87   } {32768 1 {} t1 x y 32769 1 {} t1 x y}
    77     88   
    78     89   # great-grandparent of 16384
    79         -do_execsql_test 1.5 {
           90  +do_timed_execsql_test 1.5 {
    80     91     SELECT id, depth, root, tablename, idcolumn, parentcolumn FROM cx
    81     92      WHERE root=16384
    82     93        AND depth=3
    83     94        AND idcolumn='Y'
    84     95        AND parentcolumn='X';
    85     96   } {2048 3 {} t1 Y X}
           97  +do_timed_execsql_test 1.5-cte {
           98  +  WITH RECURSIVE
           99  +    above(id,depth) AS (
          100  +      VALUES(16384,0)
          101  +      UNION ALL
          102  +      SELECT t1.y, above.depth+1
          103  +        FROM t1 JOIN above ON t1.x=above.id
          104  +       WHERE above.depth<3
          105  +    )
          106  +  SELECT id FROM above WHERE depth=3;
          107  +} {2048}
    86    108   
    87    109   # depth<5
    88         -do_execsql_test 1.6 {
          110  +do_timed_execsql_test 1.6 {
    89    111     SELECT count(*), depth FROM cx WHERE root=1 AND depth<5
    90    112      GROUP BY depth ORDER BY 1;
    91    113   } {1 0 2 1 4 2 8 3 16 4}
          114  +do_timed_execsql_test 1.6-cte {
          115  +  WITH RECURSIVE
          116  +    below(id,depth) AS (
          117  +      VALUES(1,0)
          118  +      UNION ALL
          119  +      SELECT t1.x, below.depth+1
          120  +        FROM t1 JOIN below ON t1.y=below.id
          121  +       WHERE below.depth<4
          122  +    )
          123  +  SELECT count(*), depth FROM below GROUP BY depth ORDER BY 1;
          124  +} {1 0 2 1 4 2 8 3 16 4}
    92    125   
    93    126   # depth<=5
    94    127   do_execsql_test 1.7 {
    95    128     SELECT count(*), depth FROM cx WHERE root=1 AND depth<=5
    96    129      GROUP BY depth ORDER BY 1;
    97    130   } {1 0 2 1 4 2 8 3 16 4 32 5}
    98    131   
................................................................................
   105    138   # depth BETWEEN 3 AND 5
   106    139   do_execsql_test 1.9 {
   107    140     SELECT count(*), depth FROM cx WHERE root=1 AND depth BETWEEN 3 AND 5
   108    141      GROUP BY depth ORDER BY 1;
   109    142   } {8 3 16 4 32 5}
   110    143   
   111    144   # depth==5 with min() and max()
   112         -do_execsql_test 1.10 {
          145  +do_timed_execsql_test 1.10 {
   113    146     SELECT count(*), min(id), max(id) FROM cx WHERE root=1 AND depth=5;
   114    147   } {32 32 63}
          148  +do_timed_execsql_test 1.10-cte {
          149  +  WITH RECURSIVE
          150  +    below(id,depth) AS (
          151  +      VALUES(1,0)
          152  +      UNION ALL
          153  +      SELECT t1.x, below.depth+1
          154  +        FROM t1 JOIN below ON t1.y=below.id
          155  +       WHERE below.depth<5
          156  +    )
          157  +  SELECT count(*), min(id), max(id) FROM below WHERE depth=5;
          158  +} {32 32 63}
   115    159   
   116    160   # Create a much smaller table t2 with only 32 elements 
   117    161   db eval {
   118    162     CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
   119    163     INSERT INTO t2 SELECT x, y FROM t1 WHERE x<32;
   120    164     CREATE INDEX t2y ON t2(y);
   121    165     CREATE VIRTUAL TABLE c2