/ Check-in [9a23f020]
Login

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

Overview
Comment:Add test cases that compare the performance of the transitive_closure virtual table again common table expressions for walking a tree.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:9a23f020e8ed0e7a1ad227b4ab379fdf5e2de222
User & Date: drh 2014-01-24 14:37:44
Context
2014-01-24
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
11:16
Add test cases showing the use of ORDER BY on a recursive query to control depth-first versus breath-first search of a tree. check-in: 83b0b291 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/closure01.test.

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
# 
# Test cases for transitive_closure virtual table.

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix closure01

ifcapable !vtab { finish_test ; return }

load_static_extension db closure

do_execsql_test 1.0 {
  BEGIN;
  CREATE TABLE t1(x INTEGER PRIMARY KEY, y INTEGER);



  CREATE INDEX t1y ON t1(y);
  INSERT INTO t1(x) VALUES(1),(2);
  INSERT INTO t1(x) SELECT x+2 FROM t1;
  INSERT INTO t1(x) SELECT x+4 FROM t1;
  INSERT INTO t1(x) SELECT x+8 FROM t1;
  INSERT INTO t1(x) SELECT x+16 FROM t1;
  INSERT INTO t1(x) SELECT x+32 FROM t1;
  INSERT INTO t1(x) SELECT x+64 FROM t1;
  INSERT INTO t1(x) SELECT x+128 FROM t1;
  INSERT INTO t1(x) SELECT x+256 FROM t1;
  INSERT INTO t1(x) SELECT x+512 FROM t1;
  INSERT INTO t1(x) SELECT x+1024 FROM t1;
  INSERT INTO t1(x) SELECT x+2048 FROM t1;
  INSERT INTO t1(x) SELECT x+4096 FROM t1;
  INSERT INTO t1(x) SELECT x+8192 FROM t1;
  INSERT INTO t1(x) SELECT x+16384 FROM t1;
  INSERT INTO t1(x) SELECT x+32768 FROM t1;
  INSERT INTO t1(x) SELECT x+65536 FROM t1;
  UPDATE t1 SET y=x/2 WHERE x>1;
  COMMIT;
  CREATE VIRTUAL TABLE cx 
   USING transitive_closure(tablename=t1, idcolumn=x, parentcolumn=y);
} {}

# The entire table
do_execsql_test 1.1 {
  SELECT count(*), depth FROM cx WHERE root=1 GROUP BY depth ORDER BY 1;
} {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/}











# descendents of 32768
do_execsql_test 1.2 {
  SELECT * FROM cx WHERE root=32768 ORDER BY id;
} {32768 0 65536 1 65537 1 131072 2}

# descendents of 16384
do_execsql_test 1.3 {
  SELECT * FROM cx WHERE root=16384 AND depth<=2 ORDER BY id;
} {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2}












# children of 16384
do_execsql_test 1.4 {
  SELECT id, depth, root, tablename, idcolumn, parentcolumn FROM cx
   WHERE root=16384
     AND depth=1
   ORDER BY id;







|






>
>
>

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<






|


>
>
>
>
>
>
>
>
>
>







|


>
>
>
>
>
>
>
>
>
>
>







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
# 
# Test cases for transitive_closure virtual table.

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix closure01

ifcapable !vtab||!cte { finish_test ; return }

load_static_extension db closure

do_execsql_test 1.0 {
  BEGIN;
  CREATE TABLE t1(x INTEGER PRIMARY KEY, y INTEGER);
  WITH RECURSIVE
    cnt(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM cnt LIMIT 131072)
  INSERT INTO t1(x, y) SELECT i, nullif(i,1)/2 FROM cnt;
  CREATE INDEX t1y ON t1(y);


















  COMMIT;
  CREATE VIRTUAL TABLE cx 
   USING transitive_closure(tablename=t1, idcolumn=x, parentcolumn=y);
} {}

# The entire table
do_timed_execsql_test 1.1 {
  SELECT count(*), depth FROM cx WHERE root=1 GROUP BY depth ORDER BY 1;
} {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/}
do_timed_execsql_test 1.1-cte {
  WITH RECURSIVE
    below(id,depth) AS (
      VALUES(1,0)
       UNION ALL
      SELECT t1.x, below.depth+1
        FROM t1 JOIN below on t1.y=below.id
    )
  SELECT count(*), depth FROM below GROUP BY depth ORDER BY 1;
} {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/}

# descendents of 32768
do_execsql_test 1.2 {
  SELECT * FROM cx WHERE root=32768 ORDER BY id;
} {32768 0 65536 1 65537 1 131072 2}

# descendents of 16384
do_timed_execsql_test 1.3 {
  SELECT * FROM cx WHERE root=16384 AND depth<=2 ORDER BY id;
} {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2}
do_timed_execsql_test 1.3-cte {
  WITH RECURSIVE
    below(id,depth) AS (
      VALUES(16384,0)
       UNION ALL
      SELECT t1.x, below.depth+1
        FROM t1 JOIN below on t1.y=below.id
       WHERE below.depth<2
    )
  SELECT id, depth FROM below ORDER BY id;
} {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2}

# children of 16384
do_execsql_test 1.4 {
  SELECT id, depth, root, tablename, idcolumn, parentcolumn FROM cx
   WHERE root=16384
     AND depth=1
   ORDER BY id;

Changes to test/tester.tcl.

55
56
57
58
59
60
61

62
63
64
65
66
67
68
...
713
714
715
716
717
718
719





720
721
722
723
724
725
726
....
1008
1009
1010
1011
1012
1013
1014








1015
1016
1017
1018
1019
1020
1021
#      do_ioerr_test          TESTNAME ARGS...
#      crashsql               ARGS...
#      integrity_check        TESTNAME ?DB?
#      verify_ex_errcode      TESTNAME EXPECTED ?DB?
#      do_test                TESTNAME SCRIPT EXPECTED
#      do_execsql_test        TESTNAME SQL EXPECTED
#      do_catchsql_test       TESTNAME SQL EXPECTED

#
# Commands providing a lower level interface to the global test counters:
#
#      set_test_counter       COUNTER ?VALUE?
#      omit_test              TESTNAME REASON ?APPEND?
#      fail_test              TESTNAME
#      incr_ntest
................................................................................
  fix_testname testname
  uplevel do_test [list $testname] [list "execsql {$sql}"] [list [list {*}$result]]
}
proc do_catchsql_test {testname sql result} {
  fix_testname testname
  uplevel do_test [list $testname] [list "catchsql {$sql}"] [list $result]
}





proc do_eqp_test {name sql res} {
  uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res]
}

#-------------------------------------------------------------------------
#   Usage: do_select_tests PREFIX ?SWITCHES? TESTLIST
#
................................................................................
}

# A procedure to execute SQL
#
proc execsql {sql {db db}} {
  # puts "SQL = $sql"
  uplevel [list $db eval $sql]








}

# Execute SQL and catch exceptions.
#
proc catchsql {sql {db db}} {
  # puts "SQL = $sql"
  set r [catch [list uplevel [list $db eval $sql]] msg]







>







 







>
>
>
>
>







 







>
>
>
>
>
>
>
>







55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
...
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
....
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
#      do_ioerr_test          TESTNAME ARGS...
#      crashsql               ARGS...
#      integrity_check        TESTNAME ?DB?
#      verify_ex_errcode      TESTNAME EXPECTED ?DB?
#      do_test                TESTNAME SCRIPT EXPECTED
#      do_execsql_test        TESTNAME SQL EXPECTED
#      do_catchsql_test       TESTNAME SQL EXPECTED
#      do_timed_execsql_test  TESTNAME SQL EXPECTED
#
# Commands providing a lower level interface to the global test counters:
#
#      set_test_counter       COUNTER ?VALUE?
#      omit_test              TESTNAME REASON ?APPEND?
#      fail_test              TESTNAME
#      incr_ntest
................................................................................
  fix_testname testname
  uplevel do_test [list $testname] [list "execsql {$sql}"] [list [list {*}$result]]
}
proc do_catchsql_test {testname sql result} {
  fix_testname testname
  uplevel do_test [list $testname] [list "catchsql {$sql}"] [list $result]
}
proc do_timed_execsql_test {testname sql {result {}}} {
  fix_testname testname
  uplevel do_test [list $testname] [list "execsql_timed {$sql}"]\
                                   [list [list {*}$result]]
}
proc do_eqp_test {name sql res} {
  uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res]
}

#-------------------------------------------------------------------------
#   Usage: do_select_tests PREFIX ?SWITCHES? TESTLIST
#
................................................................................
}

# A procedure to execute SQL
#
proc execsql {sql {db db}} {
  # puts "SQL = $sql"
  uplevel [list $db eval $sql]
}
proc execsql_timed {sql {db db}} {
  set tm [time {
    set x [uplevel [list $db eval $sql]]
  } 1]
  set tm [lindex $tm 0]
  puts -nonewline " ([expr {$tm*0.001}]ms) "
  set x
}

# Execute SQL and catch exceptions.
#
proc catchsql {sql {db db}} {
  # puts "SQL = $sql"
  set r [catch [list uplevel [list $db eval $sql]] msg]