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