| 1 |
# 2019 January 23 |
| 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 |
# |
| 12 |
|
| 13 |
set testdir [file dirname $argv0] |
| 14 |
source $testdir/tester.tcl |
| 15 |
set testprefix altertab3 |
| 16 |
|
| 17 |
# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. |
| 18 |
ifcapable !altertable { |
| 19 |
finish_test |
| 20 |
return |
| 21 |
} |
| 22 |
|
| 23 |
ifcapable windowfunc { |
| 24 |
do_execsql_test 1.0 { |
| 25 |
CREATE TABLE t1(a, b); |
| 26 |
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN |
| 27 |
SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY a); |
| 28 |
END; |
| 29 |
} |
| 30 |
|
| 31 |
do_execsql_test 1.1 { |
| 32 |
ALTER TABLE t1 RENAME a TO aaa; |
| 33 |
} |
| 34 |
|
| 35 |
do_execsql_test 1.2 { |
| 36 |
SELECT sql FROM sqlite_master WHERE name='tr1' |
| 37 |
} {{CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN |
| 38 |
SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY aaa); |
| 39 |
END}} |
| 40 |
|
| 41 |
do_execsql_test 1.3 { |
| 42 |
INSERT INTO t1 VALUES(1, 2); |
| 43 |
} |
| 44 |
} ;# windowfunc |
| 45 |
|
| 46 |
#------------------------------------------------------------------------- |
| 47 |
reset_db |
| 48 |
do_execsql_test 2.0 { |
| 49 |
CREATE TABLE t1(a,b,c); |
| 50 |
CREATE TABLE t2(a,b,c); |
| 51 |
CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN |
| 52 |
SELECT a,b, a name FROM t1 |
| 53 |
INTERSECT |
| 54 |
SELECT a,b,c FROM t1 WHERE b>='d' ORDER BY name; |
| 55 |
SELECT new.c; |
| 56 |
END; |
| 57 |
} |
| 58 |
|
| 59 |
do_execsql_test 2.1 { |
| 60 |
ALTER TABLE t1 RENAME TO t1x; |
| 61 |
SELECT sql FROM sqlite_master WHERE name = 'r1'; |
| 62 |
} {{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN |
| 63 |
SELECT a,b, a name FROM "t1x" |
| 64 |
INTERSECT |
| 65 |
SELECT a,b,c FROM "t1x" WHERE b>='d' ORDER BY name; |
| 66 |
SELECT new.c; |
| 67 |
END}} |
| 68 |
|
| 69 |
#------------------------------------------------------------------------- |
| 70 |
reset_db |
| 71 |
do_execsql_test 3.0 { |
| 72 |
CREATE TABLE t1(a, b, c, d); |
| 73 |
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a=1 OR (b IN ()); |
| 74 |
} |
| 75 |
|
| 76 |
do_execsql_test 3.1 { |
| 77 |
ALTER TABLE t1 RENAME b TO bbb; |
| 78 |
} |
| 79 |
|
| 80 |
do_execsql_test 3.2 { |
| 81 |
SELECT sql FROM sqlite_master WHERE name = 'v1' |
| 82 |
} {{CREATE VIEW v1 AS SELECT * FROM t1 WHERE a=1 OR (b IN ())}} |
| 83 |
|
| 84 |
#------------------------------------------------------------------------- |
| 85 |
reset_db |
| 86 |
do_execsql_test 4.0 { |
| 87 |
CREATE TABLE t1(a, b); |
| 88 |
CREATE TABLE t3(e, f); |
| 89 |
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN |
| 90 |
INSERT INTO t2 VALUES(new.a, new.b); |
| 91 |
END; |
| 92 |
} |
| 93 |
|
| 94 |
do_catchsql_test 4.1.2 { |
| 95 |
BEGIN; |
| 96 |
ALTER TABLE t3 RENAME TO t4; |
| 97 |
} {1 {error in trigger tr1: no such table: main.t2}} |
| 98 |
do_execsql_test 4.1.2 { |
| 99 |
COMMIT; |
| 100 |
} |
| 101 |
do_execsql_test 4.1.3 { |
| 102 |
SELECT type, name, tbl_name, sql |
| 103 |
FROM sqlite_master WHERE type='table' AND name!='t1'; |
| 104 |
} {table t3 t3 {CREATE TABLE t3(e, f)}} |
| 105 |
|
| 106 |
|
| 107 |
do_catchsql_test 4.2.1 { |
| 108 |
BEGIN; |
| 109 |
ALTER TABLE t3 RENAME e TO eee; |
| 110 |
} {1 {error in trigger tr1: no such table: main.t2}} |
| 111 |
do_execsql_test 4.2.2 { |
| 112 |
COMMIT; |
| 113 |
} |
| 114 |
do_execsql_test 4.2.3 { |
| 115 |
SELECT type, name, tbl_name, sql |
| 116 |
FROM sqlite_master WHERE type='table' AND name!='t1'; |
| 117 |
} {table t3 t3 {CREATE TABLE t3(e, f)}} |
| 118 |
|
| 119 |
#------------------------------------------------------------------------- |
| 120 |
reset_db |
| 121 |
do_execsql_test 5.0 { |
| 122 |
CREATE TABLE t1 ( |
| 123 |
c1 integer, c2, PRIMARY KEY(c1 collate rtrim), |
| 124 |
UNIQUE(c2) |
| 125 |
) |
| 126 |
} |
| 127 |
do_execsql_test 5.1 { |
| 128 |
ALTER TABLE t1 RENAME c1 TO c3; |
| 129 |
} |
| 130 |
|
| 131 |
#------------------------------------------------------------------------- |
| 132 |
reset_db |
| 133 |
do_execsql_test 6.0 { |
| 134 |
CREATE TEMPORARY TABLE Table0 ( |
| 135 |
Col0 INTEGER, |
| 136 |
PRIMARY KEY(Col0 COLLATE RTRIM), |
| 137 |
FOREIGN KEY (Col0) REFERENCES Table0 |
| 138 |
); |
| 139 |
} |
| 140 |
|
| 141 |
do_execsql_test 6.1 { |
| 142 |
ALTER TABLE Table0 RENAME Col0 TO Col0; |
| 143 |
} |
| 144 |
|
| 145 |
#------------------------------------------------------------------------- |
| 146 |
reset_db |
| 147 |
do_execsql_test 7.1.0 { |
| 148 |
CREATE TABLE t1(a,b,c); |
| 149 |
CREATE TRIGGER AFTER INSERT ON t1 BEGIN |
| 150 |
SELECT a, rank() OVER w1 FROM t1 |
| 151 |
WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1); |
| 152 |
END; |
| 153 |
} |
| 154 |
|
| 155 |
do_execsql_test 7.1.2 { |
| 156 |
ALTER TABLE t1 RENAME TO t1x; |
| 157 |
SELECT sql FROM sqlite_master; |
| 158 |
} { |
| 159 |
{CREATE TABLE "t1x"(a,b,c)} |
| 160 |
{CREATE TRIGGER AFTER INSERT ON "t1x" BEGIN |
| 161 |
SELECT a, rank() OVER w1 FROM "t1x" |
| 162 |
WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1); |
| 163 |
END} |
| 164 |
} |
| 165 |
|
| 166 |
do_execsql_test 7.2.1 { |
| 167 |
DROP TRIGGER after; |
| 168 |
CREATE TRIGGER AFTER INSERT ON t1x BEGIN |
| 169 |
SELECT a, rank() OVER w1 FROM t1x |
| 170 |
WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1 ORDER BY d); |
| 171 |
END; |
| 172 |
} |
| 173 |
|
| 174 |
do_catchsql_test 7.2.2 { |
| 175 |
ALTER TABLE t1x RENAME TO t1; |
| 176 |
} {1 {error in trigger AFTER: no such column: d}} |
| 177 |
|
| 178 |
#------------------------------------------------------------------------- |
| 179 |
reset_db |
| 180 |
do_execsql_test 8.0 { |
| 181 |
CREATE TABLE t0(c0); |
| 182 |
CREATE INDEX i0 ON t0('1' IN ()); |
| 183 |
} |
| 184 |
do_execsql_test 8.1 { |
| 185 |
ALTER TABLE t0 RENAME TO t1; |
| 186 |
SELECT sql FROM sqlite_master; |
| 187 |
} { |
| 188 |
{CREATE TABLE "t1"(c0)} |
| 189 |
{CREATE INDEX i0 ON "t1"('1' IN ())} |
| 190 |
} |
| 191 |
do_execsql_test 8.2.1 { |
| 192 |
CREATE TABLE t2 (c0); |
| 193 |
CREATE INDEX i2 ON t2((LIKELIHOOD(c0, 1.0) IN ())); |
| 194 |
ALTER TABLE t2 RENAME COLUMN c0 TO c1; |
| 195 |
} |
| 196 |
do_execsql_test 8.2.2 { |
| 197 |
SELECT sql FROM sqlite_master WHERE tbl_name = 't2'; |
| 198 |
} { |
| 199 |
{CREATE TABLE t2 (c1)} |
| 200 |
{CREATE INDEX i2 ON t2((LIKELIHOOD(c1, 1.0) IN ()))} |
| 201 |
} |
| 202 |
do_test 8.2.3 { |
| 203 |
sqlite3 db2 test.db |
| 204 |
db2 eval { INSERT INTO t2 VALUES (1), (2), (3) } |
| 205 |
db close |
| 206 |
} {} |
| 207 |
db2 close |
| 208 |
|
| 209 |
#------------------------------------------------------------------------- |
| 210 |
reset_db |
| 211 |
do_execsql_test 9.1 { |
| 212 |
CREATE TABLE t1(a,b,c); |
| 213 |
CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN |
| 214 |
SELECT true WHERE (SELECT a, b FROM (t1)) IN (); |
| 215 |
END; |
| 216 |
} |
| 217 |
do_execsql_test 9.2 { |
| 218 |
ALTER TABLE t1 RENAME TO t1x; |
| 219 |
} |
| 220 |
|
| 221 |
#------------------------------------------------------------------------- |
| 222 |
reset_db |
| 223 |
do_execsql_test 10.1 { |
| 224 |
CREATE TABLE t1(a, b, c); |
| 225 |
CREATE TABLE t2(a, b, c); |
| 226 |
CREATE VIEW v1 AS SELECT * FROM t1 WHERE ( |
| 227 |
SELECT t1.a FROM t1, t2 |
| 228 |
) IN () OR t1.a=5; |
| 229 |
} |
| 230 |
|
| 231 |
do_execsql_test 10.2 { |
| 232 |
ALTER TABLE t2 RENAME TO t3; |
| 233 |
SELECT sql FROM sqlite_master WHERE name='v1'; |
| 234 |
} { |
| 235 |
{CREATE VIEW v1 AS SELECT * FROM t1 WHERE ( |
| 236 |
SELECT t1.a FROM t1, t2 |
| 237 |
) IN () OR t1.a=5} |
| 238 |
} |
| 239 |
|
| 240 |
#------------------------------------------------------------------------- |
| 241 |
reset_db |
| 242 |
do_execsql_test 11.1 { |
| 243 |
CREATE TABLE t1( |
| 244 |
a,b,c,d,e,f,g,h,j,jj,jjb,k,aa,bb,cc,dd,ee DEFAULT 3.14, |
| 245 |
ff DEFAULT('hiccup'),Wg NOD NULL DEFAULT(false) |
| 246 |
); |
| 247 |
|
| 248 |
CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN |
| 249 |
SELECT a, sum() w3 FROM t1 |
| 250 |
WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM abc)); |
| 251 |
END; |
| 252 |
} |
| 253 |
|
| 254 |
do_catchsql_test 11.2 { |
| 255 |
ALTER TABLE t1 RENAME TO t1x; |
| 256 |
} {1 {error in trigger b: no such table: main.abc}} |
| 257 |
|
| 258 |
do_execsql_test 11.3 { |
| 259 |
DROP TRIGGER b; |
| 260 |
CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN |
| 261 |
SELECT a, sum() w3 FROM t1 |
| 262 |
WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM t1)); |
| 263 |
END; |
| 264 |
} {} |
| 265 |
|
| 266 |
do_execsql_test 11.4 { |
| 267 |
ALTER TABLE t1 RENAME TO t1x; |
| 268 |
SELECT sql FROM sqlite_master WHERE name = 'b'; |
| 269 |
} { |
| 270 |
{CREATE TRIGGER b AFTER INSERT ON "t1x" WHEN new.a BEGIN |
| 271 |
SELECT a, sum() w3 FROM "t1x" |
| 272 |
WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM "t1x")); |
| 273 |
END} |
| 274 |
} |
| 275 |
|
| 276 |
#------------------------------------------------------------------------- |
| 277 |
reset_db |
| 278 |
do_execsql_test 12.1 { |
| 279 |
CREATE TABLE t1(a,b,c,d,e,f,g,h,j,jj,Zjj,k,aQ,bb,cc,dd,ee DEFAULT 3.14, |
| 280 |
ff DEFAULT('hiccup'),gg NOD NULL DEFAULT(false)); |
| 281 |
CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN |
| 282 |
|
| 283 |
SELECT b () OVER , dense_rank() OVER d, d () OVER w1 |
| 284 |
FROM t1 |
| 285 |
WINDOW |
| 286 |
w1 AS |
| 287 |
( w1 ORDER BY d |
| 288 |
ROWS BETWEEN 2 NOT IN(SELECT a, sum(d) w2,max(d)OVER FROM t1 |
| 289 |
WINDOW |
| 290 |
w1 AS |
| 291 |
(PARTITION BY d |
| 292 |
ROWS BETWEEN '' PRECEDING AND false FOLLOWING), |
| 293 |
d AS |
| 294 |
(PARTITION BY b ORDER BY d |
| 295 |
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
| 296 |
) PRECEDING AND 1 FOLLOWING), |
| 297 |
w2 AS |
| 298 |
(PARTITION BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), |
| 299 |
w3 AS |
| 300 |
(PARTITION BY b ORDER BY d |
| 301 |
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) |
| 302 |
; |
| 303 |
SELECT a, sum(d) w2,max(d)OVER FROM t1 |
| 304 |
WINDOW |
| 305 |
w1 AS |
| 306 |
(PARTITION BY d |
| 307 |
ROWS BETWEEN '' PRECEDING AND false FOLLOWING), |
| 308 |
d AS |
| 309 |
(PARTITION BY b ORDER BY d |
| 310 |
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
| 311 |
; |
| 312 |
|
| 313 |
END; |
| 314 |
} |
| 315 |
|
| 316 |
do_execsql_test 12.2 { |
| 317 |
ALTER TABLE t1 RENAME TO t1x; |
| 318 |
} |
| 319 |
|
| 320 |
#------------------------------------------------------------------------- |
| 321 |
reset_db |
| 322 |
do_execsql_test 13.1 { |
| 323 |
CREATE TABLE t1(a); |
| 324 |
CREATE TRIGGER r1 INSERT ON t1 BEGIN |
| 325 |
SELECT a(*) OVER (ORDER BY (SELECT 1)) FROM t1; |
| 326 |
END; |
| 327 |
} |
| 328 |
|
| 329 |
do_execsql_test 13.2 { |
| 330 |
ALTER TABLE t1 RENAME TO t1x; |
| 331 |
} |
| 332 |
|
| 333 |
#------------------------------------------------------------------------- |
| 334 |
reset_db |
| 335 |
do_execsql_test 14.1 { |
| 336 |
CREATE TABLE t1(a); |
| 337 |
CREATE TABLE t2(b); |
| 338 |
CREATE TRIGGER AFTER INSERT ON t1 BEGIN |
| 339 |
SELECT sum() FILTER (WHERE (SELECT sum() FILTER (WHERE 0)) AND a); |
| 340 |
END; |
| 341 |
} |
| 342 |
|
| 343 |
do_catchsql_test 14.2 { |
| 344 |
ALTER TABLE t1 RENAME TO t1x; |
| 345 |
} {1 {error in trigger AFTER: no such column: a}} |
| 346 |
|
| 347 |
#------------------------------------------------------------------------- |
| 348 |
reset_db |
| 349 |
|
| 350 |
do_execsql_test 16.1 { |
| 351 |
CREATE TABLE t1(x); |
| 352 |
CREATE TRIGGER AFTER INSERT ON t1 BEGIN |
| 353 |
SELECT (WITH t2 AS (WITH t3 AS (SELECT true) |
| 354 |
SELECT * FROM t3 ORDER BY true COLLATE nocase) |
| 355 |
SELECT 11); |
| 356 |
|
| 357 |
WITH t4 AS (SELECT * FROM t1) SELECT 33; |
| 358 |
END; |
| 359 |
} |
| 360 |
do_execsql_test 16.2 { |
| 361 |
ALTER TABLE t1 RENAME TO t1x; |
| 362 |
} |
| 363 |
|
| 364 |
#------------------------------------------------------------------------- |
| 365 |
reset_db |
| 366 |
do_execsql_test 17.1 { |
| 367 |
CREATE TABLE t1(a,b,c); |
| 368 |
CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN |
| 369 |
SELECT a () FILTER (WHERE a>0) FROM t1; |
| 370 |
END; |
| 371 |
} |
| 372 |
|
| 373 |
do_execsql_test 17.2 { |
| 374 |
ALTER TABLE t1 RENAME TO t1x; |
| 375 |
ALTER TABLE t1x RENAME a TO aaa; |
| 376 |
SELECT sql FROM sqlite_master WHERE type='trigger'; |
| 377 |
} { |
| 378 |
{CREATE TRIGGER AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN |
| 379 |
SELECT a () FILTER (WHERE aaa>0) FROM "t1x"; |
| 380 |
END} |
| 381 |
} |
| 382 |
|
| 383 |
#------------------------------------------------------------------------- |
| 384 |
reset_db |
| 385 |
do_execsql_test 18.1 { |
| 386 |
CREATE TABLE t1(a,b); |
| 387 |
CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN |
| 388 |
SELECT a, b FROM t1 |
| 389 |
INTERSECT SELECT b,a FROM t1 |
| 390 |
ORDER BY b IN ( |
| 391 |
SELECT a UNION SELECT b |
| 392 |
FROM t1 |
| 393 |
ORDER BY b COLLATE nocase |
| 394 |
) |
| 395 |
; |
| 396 |
END; |
| 397 |
} |
| 398 |
|
| 399 |
do_catchsql_test 18.2 { |
| 400 |
SELECT a, b FROM t1 |
| 401 |
INTERSECT |
| 402 |
SELECT b,a FROM t1 |
| 403 |
ORDER BY b IN ( |
| 404 |
SELECT a UNION SELECT b |
| 405 |
FROM t1 |
| 406 |
ORDER BY b COLLATE nocase |
| 407 |
); |
| 408 |
} {1 {1st ORDER BY term does not match any column in the result set}} |
| 409 |
|
| 410 |
do_catchsql_test 18.3 { |
| 411 |
ALTER TABLE t1 RENAME TO t1x; |
| 412 |
} {1 {error in trigger r1: 1st ORDER BY term does not match any column in the result set}} |
| 413 |
|
| 414 |
#------------------------------------------------------------------------- |
| 415 |
reset_db |
| 416 |
do_execsql_test 19.0 { |
| 417 |
CREATE TABLE a(a,h CONSTRAINT a UNIQUE ON CONFLICT FAIL,CONSTRAINT a); |
| 418 |
} |
| 419 |
|
| 420 |
foreach {tn v res} { |
| 421 |
1 { |
| 422 |
CREATE VIEW q AS SELECT 123 |
| 423 |
|
| 424 |
WINDOW x AS ( |
| 425 |
RANGE BETWEEN UNBOUNDED PRECEDING AND INDEXED() OVER( |
| 426 |
PARTITION BY ( WITH x AS(VALUES(col1)) VALUES(453) ) |
| 427 |
) |
| 428 |
FOLLOWING |
| 429 |
) |
| 430 |
} {1 {error in view q: no such column: col1}} |
| 431 |
|
| 432 |
2 { |
| 433 |
CREATE VIEW q AS SELECT |
| 434 |
CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(RIGHT |
| 435 |
AS)AS)AS)AS)AS)AS)AS)AS)AS)AS)AS)WINDOW x AS(RANGE BETWEEN UNBOUNDED |
| 436 |
PRECEDING AND INDEXED(*)OVER(PARTITION BY |
| 437 |
CROSS,CROSS,NATURAL,sqlite_master(*)OVER a,(WITH a AS(VALUES(LEFT)UNION |
| 438 |
VALUES(LEFT)UNION VALUES(LEFT)UNION VALUES(LEFT)UNION VALUES(LEFT)UNION |
| 439 |
VALUES(LEFT)UNION VALUES(LEFT))VALUES(LEFT))IN |
| 440 |
STORED,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT)*LEFT FOLLOWING)ORDER BY |
| 441 |
LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT LIMIT |
| 442 |
LEFT,INDEXED(*)OVER(PARTITION BY |
| 443 |
CROSS,CROSS,CROSS,LEFT,INDEXED(*)OVER(PARTITION BY |
| 444 |
CROSS,CROSS,CROSS),INDEXED(*)OVER(PARTITION BY |
| 445 |
LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT), |
| 446 |
LEFT,LEFT,INNER,CROSS,CROSS,CROSS,INNER,NATURAL ORDER BY |
| 447 |
OUTER,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,INNER, |
| 448 |
INNER,INNER NULLS LAST GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED |
| 449 |
FOLLOWING); |
| 450 |
} {1 {error in view q: no such column: LEFT}} |
| 451 |
|
| 452 |
3 { |
| 453 |
CREATE VIEW q AS SELECT 99 WINDOW x AS (RANGE BETWEEN UNBOUNDED PRECEDING |
| 454 |
AND count(*)OVER(PARTITION BY (WITH a AS(VALUES(2),(x3))VALUES(0))) |
| 455 |
FOLLOWING)ORDER BY x2,sum(1)OVER(PARTITION BY avg(5)OVER(PARTITION BY x1)); |
| 456 |
} {1 {error in view q: no such column: x3}} |
| 457 |
} { |
| 458 |
do_execsql_test 19.$tn.1 " |
| 459 |
DROP VIEW IF EXISTS q; |
| 460 |
$v |
| 461 |
" {} |
| 462 |
|
| 463 |
do_catchsql_test 19.$tn.2 { |
| 464 |
ALTER TABLE a RENAME TO g; |
| 465 |
} $res |
| 466 |
} |
| 467 |
|
| 468 |
# Verify that the "if( pParse->nErr ) return WRC_Abort" at the top of the |
| 469 |
# renameUnmapSelectCb() routine in alter.c (2019-12-04) is really required. |
| 470 |
# |
| 471 |
sqlite3 db :memory: |
| 472 |
do_catchsql_test 20.10 { |
| 473 |
CREATE TABLE s(a, b, c); |
| 474 |
CREATE INDEX k ON s( (WITH s AS( SELECT * ) VALUES(2) ) IN () ); |
| 475 |
ALTER TABLE s RENAME a TO a2; |
| 476 |
} {1 {error in index k: no tables specified}} |
| 477 |
|
| 478 |
#------------------------------------------------------------------------ |
| 479 |
# |
| 480 |
reset_db |
| 481 |
do_execsql_test 21.1 { |
| 482 |
CREATE TABLE s(col); |
| 483 |
CREATE VIEW v AS SELECT ( |
| 484 |
WITH x(a) AS(SELECT * FROM s) VALUES(RIGHT) |
| 485 |
) IN() ; |
| 486 |
CREATE TABLE a(a); |
| 487 |
ALTER TABLE a RENAME a TO b; |
| 488 |
} |
| 489 |
|
| 490 |
#------------------------------------------------------------------------ |
| 491 |
# |
| 492 |
reset_db |
| 493 |
do_execsql_test 22.1 { |
| 494 |
CREATE TABLE t1(a); |
| 495 |
CREATE VIEW v2(b) AS SELECT * FROM v2; |
| 496 |
} |
| 497 |
|
| 498 |
do_catchsql_test 22.2 { |
| 499 |
ALTER TABLE t1 RENAME TO t4; |
| 500 |
} {1 {error in view v2: view v2 is circularly defined}} |
| 501 |
|
| 502 |
do_execsql_test 22.3 { |
| 503 |
DROP VIEW v2; |
| 504 |
CREATE VIEW v2(b) AS WITH t3 AS (SELECT b FROM v2) SELECT * FROM t3; |
| 505 |
} |
| 506 |
|
| 507 |
do_catchsql_test 22.4 { |
| 508 |
ALTER TABLE t1 RENAME TO t4; |
| 509 |
} {1 {error in view v2: view v2 is circularly defined}} |
| 510 |
|
| 511 |
do_execsql_test 22.5 { |
| 512 |
DROP VIEW v2; |
| 513 |
CREATE VIEW v2(b) AS WITH t3 AS (SELECT b FROM v2) VALUES(1); |
| 514 |
} |
| 515 |
|
| 516 |
do_catchsql_test 22.6 { |
| 517 |
ALTER TABLE t1 RENAME TO t4; |
| 518 |
} {0 {}} |
| 519 |
|
| 520 |
#------------------------------------------------------------------------ |
| 521 |
# |
| 522 |
reset_db |
| 523 |
do_execsql_test 23.1 { |
| 524 |
CREATE TABLE t1(x); |
| 525 |
CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN |
| 526 |
UPDATE t1 SET (c,d)=((SELECT 1 FROM t1 JOIN t2 ON b=x),1); |
| 527 |
END; |
| 528 |
} |
| 529 |
|
| 530 |
do_catchsql_test 23.2 { |
| 531 |
ALTER TABLE t1 RENAME TO t1x; |
| 532 |
} {1 {error in trigger r1: no such table: main.t2}} |
| 533 |
|
| 534 |
#------------------------------------------------------------------------ |
| 535 |
# |
| 536 |
reset_db |
| 537 |
do_execsql_test 23.1 { |
| 538 |
CREATE TABLE v0 (a); |
| 539 |
CREATE VIEW v2 (v3) AS |
| 540 |
WITH x1 AS (SELECT * FROM v2) |
| 541 |
SELECT v3 AS x, v3 AS y FROM v2; |
| 542 |
} |
| 543 |
|
| 544 |
do_catchsql_test 23.2 { |
| 545 |
SELECT * FROM v2 |
| 546 |
} {1 {view v2 is circularly defined}} |
| 547 |
|
| 548 |
db close |
| 549 |
sqlite3 db test.db |
| 550 |
|
| 551 |
do_catchsql_test 23.3 { |
| 552 |
ALTER TABLE v0 RENAME TO t3 ; |
| 553 |
} {1 {error in view v2: view v2 is circularly defined}} |
| 554 |
|
| 555 |
#------------------------------------------------------------------------ |
| 556 |
# |
| 557 |
reset_db |
| 558 |
do_execsql_test 24.1 { |
| 559 |
CREATE TABLE v0 (v1); |
| 560 |
CREATE TABLE v2 (v3 INTEGER UNIQUE ON CONFLICT ABORT); |
| 561 |
CREATE TRIGGER x AFTER INSERT ON v2 WHEN ( |
| 562 |
( SELECT v1 AS PROMO_REVENUE FROM v2 JOIN v0 USING ( VALUE ) ) AND 0 ) |
| 563 |
BEGIN |
| 564 |
DELETE FROM v2; |
| 565 |
END; |
| 566 |
} |
| 567 |
do_catchsql_test 24.2 { |
| 568 |
ALTER TABLE v0 RENAME TO x ; |
| 569 |
} {1 {error in trigger x: cannot join using column VALUE - column not present in both tables}} |
| 570 |
|
| 571 |
do_execsql_test 24.3 { |
| 572 |
DROP TRIGGER x; |
| 573 |
CREATE TRIGGER x AFTER INSERT ON v2 WHEN ( |
| 574 |
0 AND (SELECT rowid FROM v0) |
| 575 |
) BEGIN |
| 576 |
DELETE FROM v2; |
| 577 |
END; |
| 578 |
} |
| 579 |
|
| 580 |
do_execsql_test 24.4 { |
| 581 |
ALTER TABLE v0 RENAME TO xyz; |
| 582 |
SELECT sql FROM sqlite_master WHERE type='trigger' |
| 583 |
} {{CREATE TRIGGER x AFTER INSERT ON v2 WHEN ( |
| 584 |
0 AND (SELECT rowid FROM "xyz") |
| 585 |
) BEGIN |
| 586 |
DELETE FROM v2; |
| 587 |
END}} |
| 588 |
|
| 589 |
#------------------------------------------------------------------------ |
| 590 |
# |
| 591 |
reset_db |
| 592 |
do_execsql_test 25.1 { |
| 593 |
CREATE TABLE t1(a, b, c); |
| 594 |
CREATE TABLE t2(a, b, c); |
| 595 |
CREATE TRIGGER ttt AFTER INSERT ON t1 BEGIN |
| 596 |
UPDATE t1 SET a=t2.a FROM t2 WHERE t1.a=t2.a; |
| 597 |
END; |
| 598 |
} |
| 599 |
#do_execsql_test 25.2 { |
| 600 |
# ALTER TABLE t2 RENAME COLUMN a TO aaa; |
| 601 |
#} |
| 602 |
|
| 603 |
#------------------------------------------------------------------------ |
| 604 |
# |
| 605 |
reset_db |
| 606 |
do_execsql_test 26.1 { |
| 607 |
CREATE TABLE t1(x); |
| 608 |
|
| 609 |
CREATE TABLE t3(y); |
| 610 |
CREATE TABLE t4(z); |
| 611 |
|
| 612 |
CREATE TRIGGER tr1 INSERT ON t3 BEGIN |
| 613 |
UPDATE t3 SET y=z FROM (SELECT z FROM t4); |
| 614 |
END; |
| 615 |
|
| 616 |
CREATE TRIGGER tr2 INSERT ON t3 BEGIN |
| 617 |
UPDATE t3 SET y=abc FROM (SELECT x AS abc FROM t1); |
| 618 |
END; |
| 619 |
} |
| 620 |
|
| 621 |
do_execsql_test 26.2 { |
| 622 |
ALTER TABLE t1 RENAME TO t2; |
| 623 |
} |
| 624 |
|
| 625 |
do_execsql_test 26.3 { |
| 626 |
ALTER TABLE t2 RENAME x TO xx; |
| 627 |
} |
| 628 |
|
| 629 |
do_execsql_test 26.4 { |
| 630 |
SELECT sql FROM sqlite_schema WHERE name='tr2' |
| 631 |
} { |
| 632 |
{CREATE TRIGGER tr2 INSERT ON t3 BEGIN |
| 633 |
UPDATE t3 SET y=abc FROM (SELECT xx AS abc FROM "t2"); |
| 634 |
END} |
| 635 |
} |
| 636 |
|
| 637 |
# 2020-11-02 OSSFuzz |
| 638 |
# |
| 639 |
reset_db |
| 640 |
do_execsql_test 26.5 { |
| 641 |
CREATE TABLE t1(xx); |
| 642 |
CREATE TRIGGER xx INSERT ON t1 BEGIN |
| 643 |
UPDATE t1 SET xx=xx FROM(SELECT xx); |
| 644 |
END; |
| 645 |
} {} |
| 646 |
do_catchsql_test 26.6 { |
| 647 |
ALTER TABLE t1 RENAME TO t2; |
| 648 |
} {1 {error in trigger xx: no such column: xx}} |
| 649 |
|
| 650 |
|
| 651 |
#------------------------------------------------------------------------- |
| 652 |
reset_db |
| 653 |
|
| 654 |
do_execsql_test 27.1 { |
| 655 |
CREATE TABLE t1(a, b AS ((WITH w1 (xyz) AS ( SELECT t1.b FROM t1 ) SELECT 123) IN ()), c); |
| 656 |
} |
| 657 |
|
| 658 |
do_execsql_test 27.2 { |
| 659 |
ALTER TABLE t1 DROP COLUMN c; |
| 660 |
SELECT sql FROM sqlite_schema WHERE name = 't1'; |
| 661 |
} { |
| 662 |
{CREATE TABLE t1(a, b AS ((WITH w1 (xyz) AS ( SELECT t1.b FROM t1 ) SELECT 123) IN ()))} |
| 663 |
} |
| 664 |
|
| 665 |
#------------------------------------------------------------------------- |
| 666 |
reset_db |
| 667 |
do_execsql_test 28.1 { |
| 668 |
CREATE TABLE t1(a,b,c,d); |
| 669 |
CREATE TRIGGER AFTER INSERT ON t1 BEGIN |
| 670 |
UPDATE t1 SET (c,d)=(a,b); |
| 671 |
END; |
| 672 |
ALTER TABLE t1 RENAME TO t2; |
| 673 |
} |
| 674 |
|
| 675 |
do_execsql_test 28.2 { |
| 676 |
SELECT sql FROM sqlite_schema WHERE type='trigger' |
| 677 |
} {{CREATE TRIGGER AFTER INSERT ON "t2" BEGIN |
| 678 |
UPDATE "t2" SET (c,d)=(a,b); |
| 679 |
END}} |
| 680 |
|
| 681 |
|
| 682 |
#------------------------------------------------------------------------- |
| 683 |
reset_db |
| 684 |
do_execsql_test 29.1 { |
| 685 |
CREATE TABLE t1(x, y); |
| 686 |
CREATE TRIGGER Trigger1 DELETE ON t1 |
| 687 |
BEGIN |
| 688 |
SELECT t1.*, t1.x FROM t1 ORDER BY t1.x; |
| 689 |
END; |
| 690 |
} |
| 691 |
|
| 692 |
|
| 693 |
do_execsql_test 29.2 { |
| 694 |
ALTER TABLE t1 RENAME x TO z; |
| 695 |
} |
| 696 |
|
| 697 |
do_execsql_test 29.3 { |
| 698 |
ALTER TABLE t1 RENAME TO t2; |
| 699 |
} |
| 700 |
|
| 701 |
do_execsql_test 29.4 { |
| 702 |
CREATE TRIGGER tr2 AFTER DELETE ON t2 BEGIN |
| 703 |
SELECT z, y FROM ( |
| 704 |
SELECT t2.* FROM t2 |
| 705 |
); |
| 706 |
END; |
| 707 |
} |
| 708 |
|
| 709 |
do_execsql_test 29.5 { |
| 710 |
DELETE FROM t2 |
| 711 |
} |
| 712 |
|
| 713 |
do_execsql_test 29.6 { |
| 714 |
ALTER TABLE t2 RENAME TO t3; |
| 715 |
} |
| 716 |
|
| 717 |
do_execsql_test 29.7 { |
| 718 |
SELECT sql FROM sqlite_schema WHERE type='trigger' |
| 719 |
} { |
| 720 |
{CREATE TRIGGER Trigger1 DELETE ON "t3" |
| 721 |
BEGIN |
| 722 |
SELECT "t3".*, "t3".z FROM "t3" ORDER BY "t3".z; |
| 723 |
END} |
| 724 |
{CREATE TRIGGER tr2 AFTER DELETE ON "t3" BEGIN |
| 725 |
SELECT z, y FROM ( |
| 726 |
SELECT "t3".* FROM "t3" |
| 727 |
); |
| 728 |
END} |
| 729 |
} |
| 730 |
|
| 731 |
#------------------------------------------------------------------------- |
| 732 |
reset_db |
| 733 |
do_execsql_test 30.0 { |
| 734 |
CREATE TABLE t1(a, b); |
| 735 |
CREATE VIEW v1 AS |
| 736 |
SELECT ( VALUES(a), (b) ) FROM ( |
| 737 |
SELECT a, b FROM t1 |
| 738 |
) |
| 739 |
; |
| 740 |
} |
| 741 |
|
| 742 |
do_execsql_test 30.1 { |
| 743 |
SELECT * FROM v1 |
| 744 |
} |
| 745 |
|
| 746 |
do_execsql_test 30.1 { |
| 747 |
ALTER TABLE t1 RENAME TO t2; |
| 748 |
} |
| 749 |
do_execsql_test 30.2 { |
| 750 |
SELECT sql FROM sqlite_schema WHERE type='view' |
| 751 |
} { |
| 752 |
{CREATE VIEW v1 AS |
| 753 |
SELECT ( VALUES(a), (b) ) FROM ( |
| 754 |
SELECT a, b FROM "t2" |
| 755 |
)} |
| 756 |
} |
| 757 |
|
| 758 |
#------------------------------------------------------------------------- |
| 759 |
reset_db |
| 760 |
do_execsql_test 31.0 { |
| 761 |
CREATE TABLE t1(ii INTEGER PRIMARY KEY, tt INTEGER, rr REAL); |
| 762 |
WITH s(i) AS ( |
| 763 |
SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000 |
| 764 |
) |
| 765 |
INSERT INTO t1 SELECT NULL, i, 5.0 FROM s; |
| 766 |
} |
| 767 |
|
| 768 |
do_test 31.1 { |
| 769 |
set pg [db one {PRAGMA page_count}] |
| 770 |
execsql { |
| 771 |
ALTER TABLE t1 DROP COLUMN tt; |
| 772 |
} |
| 773 |
set pg2 [db one {PRAGMA page_count}] |
| 774 |
expr $pg==$pg2 |
| 775 |
} {1} |
| 776 |
|
| 777 |
do_execsql_test 31.2 { |
| 778 |
SELECT rr FROM t1 LIMIT 1 |
| 779 |
} {5.0} |
| 780 |
|
| 781 |
#------------------------------------------------------------------------- |
| 782 |
reset_db |
| 783 |
do_execsql_test 32.1.0 { |
| 784 |
CREATE TABLE t1( |
| 785 |
a INT, |
| 786 |
b INT, |
| 787 |
-- comment with comma |
| 788 |
c INT |
| 789 |
); |
| 790 |
} |
| 791 |
do_execsql_test 32.1.1 { |
| 792 |
ALTER TABLE t1 DROP COLUMN c; |
| 793 |
} |
| 794 |
do_execsql_test 32.1.2 { |
| 795 |
SELECT sql FROM sqlite_schema |
| 796 |
} {{CREATE TABLE t1( |
| 797 |
a INT, |
| 798 |
b INT)}} |
| 799 |
|
| 800 |
reset_db |
| 801 |
do_execsql_test 32.2.0 { |
| 802 |
CREATE TABLE t1( |
| 803 |
a INT, |
| 804 |
b INT, |
| 805 |
-- comment with, comma |
| 806 |
c INT |
| 807 |
); |
| 808 |
} |
| 809 |
do_execsql_test 32.2.1 { |
| 810 |
ALTER TABLE t1 DROP COLUMN c; |
| 811 |
} |
| 812 |
do_execsql_test 32.2.2 { |
| 813 |
SELECT sql FROM sqlite_schema |
| 814 |
} {{CREATE TABLE t1( |
| 815 |
a INT, |
| 816 |
b INT)}} |
| 817 |
|
| 818 |
#------------------------------------------------------------------------- |
| 819 |
reset_db |
| 820 |
do_execsql_test 33.1 { |
| 821 |
CREATE TABLE x1(a TEXT, b INTEGER, c CHECK(c!=0)); |
| 822 |
} |
| 823 |
|
| 824 |
do_execsql_test 33.2 { |
| 825 |
ALTER TABLE x1 DROP COLUMN b; |
| 826 |
SELECT sql FROM sqlite_schema; |
| 827 |
} {{CREATE TABLE x1(a TEXT, c CHECK(c!=0))}} |
| 828 |
|
| 829 |
finish_test |