test/with5.test

   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
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18 set ::testprefix with5
  19
  20 ifcapable {!cte} {
  21   finish_test
  22   return
  23 }
  24
  25 do_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 } {}
  41 do_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}
  51 do_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}
  61 do_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}
  71 do_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}
  83 do_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
  96 do_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}}
 108 do_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
 121 do_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}
 132 do_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
 146 do_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 } {}
 156 do_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}
 170 do_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
 194 finish_test