/ 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 Unified Diffs Ignore Whitespace Patch

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