1# 2020-10-19 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. The 12# focus of this file is recursive common table expressions with 13# multiple recursive terms in the compound select. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set ::testprefix with5 19 20ifcapable {!cte} { 21 finish_test 22 return 23} 24 25do_execsql_test 100 { 26 CREATE TABLE link(aa INT, bb INT); 27 CREATE INDEX link_f ON link(aa,bb); 28 CREATE INDEX link_t ON link(bb,aa); 29 INSERT INTO link(aa,bb) VALUES 30 (1,3), 31 (5,3), 32 (7,1), 33 (7,9), 34 (9,9), 35 (5,11), 36 (11,7), 37 (2,4), 38 (4,6), 39 (8,6); 40} {} 41do_execsql_test 110 { 42 WITH RECURSIVE closure(x) AS ( 43 VALUES(1) 44 UNION 45 SELECT aa FROM closure, link WHERE link.bb=closure.x 46 UNION 47 SELECT bb FROM closure, link WHERE link.aa=closure.x 48 ) 49 SELECT x FROM closure ORDER BY x; 50} {1 3 5 7 9 11} 51do_execsql_test 111 { 52 WITH RECURSIVE closure(x) AS ( 53 VALUES(1) 54 UNION 55 SELECT aa FROM link, closure WHERE link.bb=closure.x 56 UNION 57 SELECT bb FROM closure, link WHERE link.aa=closure.x 58 ) 59 SELECT x FROM closure ORDER BY x; 60} {1 3 5 7 9 11} 61do_execsql_test 112 { 62 WITH RECURSIVE closure(x) AS ( 63 VALUES(1) 64 UNION 65 SELECT bb FROM closure, link WHERE link.aa=closure.x 66 UNION 67 SELECT aa FROM link, closure WHERE link.bb=closure.x 68 ) 69 SELECT x FROM closure ORDER BY x; 70} {1 3 5 7 9 11} 71do_execsql_test 113 { 72 WITH RECURSIVE closure(x) AS ( 73 VALUES(1),(200),(300),(400) 74 INTERSECT 75 VALUES(1) 76 UNION 77 SELECT bb FROM closure, link WHERE link.aa=closure.x 78 UNION 79 SELECT aa FROM link, closure WHERE link.bb=closure.x 80 ) 81 SELECT x FROM closure ORDER BY x; 82} {1 3 5 7 9 11} 83do_execsql_test 114 { 84 WITH RECURSIVE closure(x) AS ( 85 VALUES(1),(200),(300),(400) 86 UNION ALL 87 VALUES(2) 88 UNION 89 SELECT bb FROM closure, link WHERE link.aa=closure.x 90 UNION 91 SELECT aa FROM link, closure WHERE link.bb=closure.x 92 ) 93 SELECT x FROM closure ORDER BY x; 94} {1 2 3 4 5 6 7 8 9 11 200 300 400} 95 96do_catchsql_test 120 { 97 WITH RECURSIVE closure(x) AS ( 98 VALUES(1),(200),(300),(400) 99 UNION ALL 100 VALUES(2) 101 UNION ALL 102 SELECT bb FROM closure, link WHERE link.aa=closure.x 103 UNION 104 SELECT aa FROM link, closure WHERE link.bb=closure.x 105 ) 106 SELECT x FROM closure ORDER BY x; 107} {1 {circular reference: closure}} 108do_catchsql_test 121 { 109 WITH RECURSIVE closure(x) AS ( 110 VALUES(1),(200),(300),(400) 111 UNION ALL 112 VALUES(2) 113 UNION 114 SELECT bb FROM closure, link WHERE link.aa=closure.x 115 UNION ALL 116 SELECT aa FROM link, closure WHERE link.bb=closure.x 117 ) 118 SELECT x FROM closure ORDER BY x; 119} {1 {circular reference: closure}} 120 121do_execsql_test 130 { 122 WITH RECURSIVE closure(x) AS ( 123 SELECT 1 AS x 124 UNION 125 SELECT aa FROM link JOIN closure ON bb=x 126 UNION 127 SELECT bb FROM link JOIN closure on aa=x 128 ORDER BY x LIMIT 4 129 ) 130 SELECT * FROM closure; 131} {1 3 5 7} 132do_execsql_test 131 { 133 WITH RECURSIVE closure(x) AS ( 134 SELECT 1 AS x 135 UNION ALL 136 SELECT 2 137 UNION 138 SELECT aa FROM link JOIN closure ON bb=x 139 UNION 140 SELECT bb FROM link JOIN closure on aa=x 141 ORDER BY x LIMIT 4 142 ) 143 SELECT * FROM closure; 144} {1 2 3 4} 145 146do_execsql_test 200 { 147 CREATE TABLE linkA(aa1,aa2); 148 INSERT INTO linkA(aa1,aa2) VALUES(1,3),(5,7),(9,11); 149 CREATE TABLE linkB(bb1,bb2); 150 INSERT INTO linkB(bb1,bb2) VALUES(7,9),(11,13),(3,5); 151 CREATE TABLE linkC(cc1,cc2); 152 INSERT INTO linkC(cc1,cc2) VALUES(1,2),(2,4),(6,8); 153 CREATE TABLE linkD(dd1,dd2); 154 INSERT INTO linkD(dd1,dd2) VALUES(4,6),(100,110); 155} {} 156do_execsql_test 210 { 157 WITH RECURSIVE closure(x) AS ( 158 VALUES(1) 159 UNION ALL 160 SELECT aa2 FROM linkA JOIN closure ON x=aa1 161 UNION ALL 162 SELECT bb2 FROM linkB JOIN closure ON x=bb1 163 UNION ALL 164 SELECT cc2 FROM linkC JOIN closure ON x=cc1 165 UNION ALL 166 SELECT dd2 FROM linkD JOIN closure ON x=dd1 167 ) 168 SELECT x FROM closure ORDER BY +x; 169} {1 2 3 4 5 6 7 8 9 11 13} 170do_execsql_test 220 { 171 CREATE TABLE linkA_ipk(aa1 INTEGER PRIMARY KEY,aa2); 172 INSERT INTO linkA_ipk(aa1,aa2) SELECT aa1, aa2 FROM linkA; 173 CREATE TABLE linkB_ipk(bb1 INTEGER PRIMARY KEY,bb2); 174 INSERT INTO linkB_ipk(bb1,bb2) SELECT bb1, bb2 FROM linkB; 175 CREATE TABLE linkC_ipk(cc1 INTEGER PRIMARY KEY,cc2); 176 INSERT INTO linkC_ipk(cc1,cc2) SELECT cc1, cc2 FROM linkC; 177 CREATE TABLE linkD_ipk(dd1 INTEGER PRIMARY KEY,dd2); 178 INSERT INTO linkD_ipk(dd1,dd2) SELECT dd1, dd2 FROM linkD; 179 WITH RECURSIVE closure(x) AS ( 180 VALUES(1) 181 UNION ALL 182 SELECT aa2 FROM linkA_ipk JOIN closure ON x=aa1 183 UNION ALL 184 SELECT bb2 FROM linkB_ipk JOIN closure ON x=bb1 185 UNION ALL 186 SELECT cc2 FROM linkC_ipk JOIN closure ON x=cc1 187 UNION ALL 188 SELECT dd2 FROM linkD_ipk JOIN closure ON x=dd1 189 ) 190 SELECT x FROM closure ORDER BY +x; 191} {1 2 3 4 5 6 7 8 9 11 13} 192 193 194finish_test 195