1# 2020-12-16 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 flattening UNION ALL sub-queries. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix unionall 18 19do_execsql_test 1.0 { 20 CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT); 21 CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT); 22 CREATE TABLE t1_c(e INTEGER PRIMARY KEY, f TEXT); 23 24 INSERT INTO t1_a VALUES(1, 'one'), (4, 'four'); 25 INSERT INTO t1_b VALUES(2, 'two'), (5, 'five'); 26 INSERT INTO t1_c VALUES(3, 'three'), (6, 'six'); 27 28 CREATE VIEW t1 AS 29 SELECT a, b FROM t1_a UNION ALL 30 SELECT c, d FROM t1_b UNION ALL 31 SELECT e, f FROM t1_c; 32 33 CREATE TABLE i1(x); 34 INSERT INTO i1 VALUES(2), (5), (6), (1); 35} 36 37do_execsql_test 1.1 { 38 SELECT a, b FROM ( 39 SELECT a, b FROM t1_a UNION ALL 40 SELECT c, d FROM t1_b UNION ALL 41 SELECT e, f FROM t1_c 42 ) ORDER BY a 43} { 44 1 one 2 two 3 three 4 four 5 five 6 six 45} 46 47do_execsql_test 1.2 { 48 SELECT a, b FROM t1 ORDER BY a 49} { 50 1 one 2 two 3 three 4 four 5 five 6 six 51} 52 53do_execsql_test 1.3 { 54 SELECT a, b FROM i1, t1 WHERE a=x ORDER BY a 55} {1 one 2 two 5 five 6 six} 56 57 58# 2022-10-31 part of ticket 57c47526c34f01e8 59# The queries below were causing an assertion fault in 60# the comparison operators of the VDBE. 61# 62reset_db 63database_never_corrupt 64optimization_control db all 0 65do_execsql_test 1.10 { 66 CREATE TABLE t0(c0 INT); 67 INSERT INTO t0 VALUES(0); 68 CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT); 69 INSERT INTO t1_a VALUES(1,'one'); 70 CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT); 71 INSERT INTO t1_b VALUES(2,'two'); 72 CREATE VIEW t1 AS SELECT a, b FROM t1_a UNION ALL SELECT c, c FROM t1_b; 73 SELECT * FROM (SELECT t1.a, t1.b AS b, t0.c0 FROM t0, t1); 74} {1 one 0 2 2 0} 75do_execsql_test 1.11 { 76 SELECT * FROM (SELECT t1.a, t1.b AS b, t0.c0 FROM t0, t1) WHERE b=2; 77} {2 2 0} 78 79#------------------------------------------------------------------------- 80reset_db 81 82do_execsql_test 2.1.0 { 83 CREATE TABLE t1(x, y); 84 INSERT INTO t1 VALUES(1, 'one'); 85 INSERT INTO t1 VALUES(1, 'ONE'); 86 INSERT INTO t1 VALUES(2, 'two'); 87 INSERT INTO t1 VALUES(2, 'TWO'); 88 INSERT INTO t1 VALUES(3, 'three'); 89 INSERT INTO t1 VALUES(3, 'THREE'); 90} 91 92do_execsql_test 2.1.1 { 93 WITH s(i) AS ( 94 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<3 95 ) 96 SELECT * FROM ( 97 SELECT 0 AS i UNION ALL SELECT i FROM s UNION ALL SELECT 0 98 ), t1 WHERE x=i; 99} { 100 1 1 one 1 1 ONE 2 2 two 2 2 TWO 3 3 three 3 3 THREE 101} 102 103do_catchsql_test 2.1.2 { 104 WITH s(i) AS ( 105 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<3 UNION ALL SELECT 4 106 ) 107 SELECT * FROM s, t1 WHERE x=i; 108} {1 {circular reference: s}} 109 110do_execsql_test 2.2.0 { 111 CREATE TABLE t2_a(k INTEGER PRIMARY KEY, v TEXT); 112 CREATE TABLE t2_b(k INTEGER PRIMARY KEY, v TEXT); 113 114 CREATE VIEW t2 AS 115 SELECT * FROM t2_a 116 UNION ALL 117 SELECT * FROM t2_b; 118 119 CREATE TRIGGER t2_insert INSTEAD OF INSERT ON t2 BEGIN 120 INSERT INTO t2_a SELECT new.k, new.v WHERE (new.k%2)==0; 121 INSERT INTO t2_b SELECT new.k, new.v WHERE (new.k%2)==1; 122 END; 123 124 INSERT INTO t2 VALUES(5, 'v'), (4, 'iv'), (3, 'iii'), (2, 'ii'); 125} 126 127do_execsql_test 2.2.1 { 128 SELECT * FROM t1, t2 WHERE x=k; 129} { 130 2 two 2 ii 2 TWO 2 ii 3 three 3 iii 3 THREE 3 iii 131} 132 133do_execsql_test 2.2.2 { 134 SELECT * FROM t1 LEFT JOIN t2 ON (x=k); 135} { 136 1 one {} {} 137 1 ONE {} {} 138 2 two 2 ii 2 TWO 2 ii 3 three 3 iii 3 THREE 3 iii 139} 140 141do_execsql_test 2.2.3 { 142 SELECT x1.*, x2.* FROM t2 AS x1, t2 AS x2 WHERE x1.k=x2.k+1 143} { 144 4 iv 3 iii 145 3 iii 2 ii 146 5 v 4 iv 147} 148 149do_execsql_test 2.2.4 { 150 SELECT * FROM t1, t2 WHERE x=k ORDER BY y; 151} { 152 3 THREE 3 iii 153 2 TWO 2 ii 154 3 three 3 iii 155 2 two 2 ii 156} 157do_execsql_test 2.2.5 { 158 SELECT * FROM t1, t2 WHERE x=k ORDER BY y||''; 159} { 160 3 THREE 3 iii 161 2 TWO 2 ii 162 3 three 3 iii 163 2 two 2 ii 164} 165do_execsql_test 2.2.6 { 166 SELECT * FROM t1, t2 WHERE x=k ORDER BY v 167} { 168 2 two 2 ii 169 2 TWO 2 ii 170 3 three 3 iii 171 3 THREE 3 iii 172} 173do_execsql_test 2.2.7 { 174 SELECT * FROM t1, t2 WHERE x=k ORDER BY v||'' 175} { 176 2 two 2 ii 177 2 TWO 2 ii 178 3 three 3 iii 179 3 THREE 3 iii 180} 181do_execsql_test 2.2.8 { 182 SELECT * FROM t1, t2 WHERE x=k ORDER BY k,v||'' 183} { 184 2 two 2 ii 185 2 TWO 2 ii 186 3 three 3 iii 187 3 THREE 3 iii 188} 189do_execsql_test 2.2.9a { 190 SELECT * FROM t1, t2 ORDER BY +k 191} { 192 1 one 2 ii 1 ONE 2 ii 2 two 2 ii 193 2 TWO 2 ii 3 three 2 ii 3 THREE 2 ii 194 195 1 one 3 iii 1 ONE 3 iii 2 two 3 iii 196 2 TWO 3 iii 3 three 3 iii 3 THREE 3 iii 197 198 1 one 4 iv 1 ONE 4 iv 2 two 4 iv 199 2 TWO 4 iv 3 three 4 iv 3 THREE 4 iv 200 201 1 one 5 v 1 ONE 5 v 2 two 5 v 202 2 TWO 5 v 3 three 5 v 3 THREE 5 v 203} 204 205do_execsql_test 2.2.9b { 206 SELECT * FROM t1, t2 ORDER BY k 207} { 208 1 one 2 ii 1 ONE 2 ii 2 two 2 ii 209 2 TWO 2 ii 3 three 2 ii 3 THREE 2 ii 210 211 1 one 3 iii 1 ONE 3 iii 2 two 3 iii 212 2 TWO 3 iii 3 three 3 iii 3 THREE 3 iii 213 214 1 one 4 iv 1 ONE 4 iv 2 two 4 iv 215 2 TWO 4 iv 3 three 4 iv 3 THREE 4 iv 216 217 1 one 5 v 1 ONE 5 v 2 two 5 v 218 2 TWO 5 v 3 three 5 v 3 THREE 5 v 219} 220 221#------------------------------------------------------------------------- 222reset_db 223do_execsql_test 3.0 { 224 CREATE TABLE t1(c INTEGER PRIMARY KEY, d TEXT); 225 INSERT INTO t1 VALUES(1,2); 226 CREATE TABLE t3_a(k INTEGER PRIMARY KEY, v TEXT); 227 INSERT INTO t3_a VALUES(2,'ii'); 228 CREATE TABLE t3_b(k INTEGER PRIMARY KEY, v TEXT); 229 CREATE VIEW t3 AS 230 SELECT * FROM t3_a 231 UNION ALL 232 SELECT * FROM t3_b; 233} {} 234 235do_execsql_test 3.1 { 236 SELECT * FROM t1, t3 ORDER BY k; 237} {1 2 2 ii} 238 239reset_db 240do_execsql_test 4.0 { 241 242 CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT); 243 INSERT INTO t1_a VALUES(123, 't1_a'); 244 CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT); 245 246 CREATE VIEW t1 AS 247 SELECT a, b FROM t1_a 248 UNION ALL 249 SELECT c, d FROM t1_b; 250 251 CREATE TABLE t3_a(k INTEGER PRIMARY KEY, v TEXT); 252 INSERT INTO t3_a VALUES(456, 't3_a'); 253 CREATE TABLE t3_b(k INTEGER PRIMARY KEY, v TEXT); 254 255 CREATE VIEW t3 AS 256 SELECT * FROM t3_a 257 UNION ALL 258 SELECT * FROM t3_b; 259} 260 261do_execsql_test 4.1 { 262 SELECT * FROM t1, t3 ORDER BY k; 263} {123 t1_a 456 t3_a} 264 265do_execsql_test 4.2 { 266 SELECT * FROM (SELECT * FROM t1, t3) ORDER BY k; 267} {123 t1_a 456 t3_a} 268 269do_execsql_test 4.3 { 270 SELECT * FROM (SELECT * FROM t1, t3), ( 271 SELECT max(a) OVER () FROM t1 272 UNION ALL 273 SELECT min(a) OVER () FROM t1 274 ) 275 ORDER BY k; 276} { 277 123 t1_a 456 t3_a 123 278 123 t1_a 456 t3_a 123 279} 280 281do_execsql_test 4.3 { 282 SELECT * FROM (SELECT * FROM t1, t3), ( 283 SELECT group_concat(a) OVER (ORDER BY a), 284 group_concat(a) OVER (ORDER BY a), 285 group_concat(a) OVER (ORDER BY a), 286 group_concat(a) OVER (ORDER BY a), 287 group_concat(a) OVER (ORDER BY a), 288 group_concat(a) OVER (ORDER BY a), 289 group_concat(a) OVER (ORDER BY a), 290 group_concat(a) OVER (ORDER BY a), 291 group_concat(a) OVER (ORDER BY a) 292 FROM t1 293 ) 294 ORDER BY k; 295} { 296 123 t1_a 456 t3_a 123 123 123 123 123 123 123 123 123 297} 298 299do_execsql_test 4.3 { 300 SELECT * FROM (SELECT * FROM t1, t3) AS o, ( 301 SELECT * FROM t1 LEFT JOIN t3 ON a=k 302 ); 303} { 304 123 t1_a 456 t3_a 123 t1_a {} {} 305} 306 307# 2020-12-30: dbsqlfuzz find 308reset_db 309do_execsql_test 5.1 { 310 CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT); 311 INSERT INTO t1_a VALUES(1,'one'); 312 INSERT INTO t1_a VALUES(0,NULL); 313 CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT); 314 INSERT INTO t1_b VALUES(2,'two'); 315 INSERT INTO t1_b VALUES(5,'five'); 316 CREATE TABLE t1_c(e INTEGER PRIMARY KEY, f TEXT); 317 INSERT INTO t1_c VALUES(3,'three'); 318 INSERT INTO t1_c VALUES(6,'six'); 319 CREATE TABLE t2(k,v); 320 INSERT INTO t2 VALUES(5,'v'); 321 INSERT INTO t2 VALUES(4,'iv'); 322 INSERT INTO t2 VALUES(3,'iii'); 323 INSERT INTO t2 VALUES(2,'ii'); 324 CREATE TABLE t3_a(k INTEGER PRIMARY KEY, v TEXT); 325 INSERT INTO t3_a VALUES(2,'ii'); 326 INSERT INTO t3_a VALUES(4,'iv'); 327 CREATE TABLE t3_b(k INTEG5R PRIMARY KEY, v TEXT); 328 INSERT INTO t3_b VALUES(NULL,'iii'); 329 INSERT INTO t3_b VALUES(NULL,'v'); 330 CREATE VIEW t1 AS 331 SELECT a, b FROM t1_a UNION ALL 332 SELECT c, d FROM t1_b UNION ALL 333 SELECT e, f FROM t1_c; 334 CREATE VIEW t3 AS 335 SELECT * FROM t3_a 336 UNION ALL 337 SELECT * FROM t3_b; 338 CREATE TRIGGER t3_insert INSTEAD OF INSERT ON t3 BEGIN 339 INSERT INTO t3_a SELECT new.k, new.v WHERE (new.k%2)==0; 340 INSERT INTO t3_b SELECT new.k, new.v WHERE (new.k%2)==1; 341 END; 342} {} 343do_execsql_test 5.10 { 344 SELECT *, '+' FROM t1 LEFT JOIN t2 ON (a NOT IN(SELECT v FROM t1, t3 WHERE a=k)=NOT EXISTS(SELECT 1 FROM t1 LEFT JOIN t3 ON (a=k))); 345} {0 {} {} {} + 1 one {} {} + 2 two {} {} + 5 five {} {} + 3 three {} {} + 6 six {} {} +} 346do_execsql_test 5.20 { 347 SELECT *, '+' FROM t1 LEFT JOIN t3 ON (a NOT IN(SELECT v FROM t1 LEFT JOIN t2 ON (a=k))=k); 348} {0 {} {} {} + 1 one {} {} + 2 two {} {} + 5 five {} {} + 3 three {} {} + 6 six {} {} +} 349 350reset_db 351do_execsql_test 6.0 { 352 CREATE TABLE t1(a,b); 353 INSERT INTO t1 VALUES(1,2); 354 CREATE TABLE t2(a,b); 355 INSERT INTO t2 VALUES(3,4); 356 357 CREATE TABLE t3(a,b); 358 INSERT INTO t3 VALUES(5,6); 359 CREATE TABLE t4(a,b); 360 INSERT INTO t4 VALUES(7,8); 361 362 CREATE TABLE t5(a,b); 363 INSERT INTO t5 VALUES(9,10); 364} 365 366do_execsql_test 6.1 { 367 WITH x(c) AS ( 368 SELECT 1000 FROM t1 UNION ALL SELECT 800 FROM t2 369 ), 370 y(d) AS ( 371 SELECT 100 FROM t3 UNION ALL SELECT 400 FROM t4 372 ) 373 SELECT * FROM t5, x, y; 374} { 375 9 10 1000 100 9 10 1000 400 376 9 10 800 100 9 10 800 400 377} 378 379# 2021-04-26 dbsqlfuzz 88ed5c66789fced139d148aed823cba7c0926dd7 380reset_db 381do_execsql_test 7.1 { 382 WITH c1(x) AS (VALUES(0) UNION ALL SELECT 100+x FROM c1 WHERE x<100 UNION ALL SELECT 1+x FROM c1 WHERE x<1) 383 SELECT x, y, '|' 384 FROM c1 AS x1, (SELECT x+1 AS y FROM c1 WHERE x<1 UNION ALL SELECT 1+x FROM c1 WHERE 1<x) AS x2 385 ORDER BY x, y; 386} {0 1 | 0 101 | 0 102 | 1 1 | 1 101 | 1 102 | 100 1 | 100 101 | 100 102 | 101 1 | 101 101 | 101 102 |} 387 388# 2022-10-31 ticket https://sqlite.org/src/info/57c47526c34f01e8 389# dbsqlfuzz 37230460b46b3b6049f0d768eb801f3428189382 390# UNION ALL subqueries or views which have arms with different 391# affinities should not be flattened. 392# 393reset_db 394do_execsql_test 8.1 { 395 CREATE TABLE t0(c0 INT); 396 INSERT INTO t0 VALUES(0); 397 CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT); 398 INSERT INTO t1_a VALUES(1,'one'); 399 INSERT INTO t1_a VALUES(4,'four'); 400 CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT); 401 INSERT INTO t1_b VALUES(2,'two'); 402 INSERT INTO t1_b VALUES(5,'five'); 403 CREATE TABLE t1_c(e INTEGER PRIMARY KEY, f TEXT); 404 INSERT INTO t1_c VALUES(3,'three'); 405 INSERT INTO t1_c VALUES(6,'six'); 406 CREATE VIEW v0(c0) AS SELECT CAST(t0.c0 AS INTEGER) FROM t0; 407 CREATE VIEW t1 AS 408 SELECT a, b FROM t1_a UNION ALL 409 SELECT c, c FROM t1_b UNION ALL 410 SELECT e, f FROM t1_c; 411} 412optimization_control db all 1 413do_execsql_test 8.2 { 414 SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2; 415} {2 2 0 {}} 416do_execsql_test 8.3 { 417 SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2.0; 418} {} 419do_execsql_test 8.4 { 420 SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b='2'; 421} {2 2 0 {}} 422optimization_control db query-flattener,push-down 0 423do_execsql_test 8.5 { 424 SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2; 425} {2 2 0 {}} 426do_execsql_test 8.6 { 427 SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2.0; 428} {} 429do_execsql_test 8.7 { 430 SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b='2'; 431} {2 2 0 {}} 432optimization_control db all 0 433do_execsql_test 8.8 { 434 SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2; 435} {2 2 0 {}} 436do_execsql_test 8.9 { 437 SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2.0; 438} {} 439do_execsql_test 8.10 { 440 SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b='2'; 441} {2 2 0 {}} 442 443 444finish_test 445