test/values.test

   1 # 2024 March 3
   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.
  12 #
  13
  14 set testdir [file dirname $argv0]
  15 source $testdir/tester.tcl
  16 set testprefix values
  17
  18
  19 do_execsql_test 1.0 {
  20   CREATE TABLE x1(a, b, c);
  21 }
  22
  23
  24 explain_i {
  25   INSERT INTO x1(a, b, c) VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
  26 }
  27 do_execsql_test 1.1.1 {
  28   INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
  29 }
  30 do_execsql_test 1.1.2 {
  31   SELECT * FROM x1;
  32 } {
  33   1 1 1
  34   2 2 2
  35   3 3 3
  36   4 4 4
  37 }
  38
  39 do_execsql_test 1.2.0 {
  40   DELETE FROM x1
  41 }
  42 do_execsql_test 1.2.1 {
  43   INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3) UNION ALL SELECT 4, 4, 4;
  44   SELECT * FROM x1;
  45 } {1 1 1  2 2 2   3 3 3  4 4 4}
  46
  47 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 4
  48
  49 do_execsql_test 1.2.2 {
  50   DELETE FROM x1;
  51   INSERT INTO x1
  52   VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5)
  53   UNION ALL SELECT 6, 6, 6;
  54   SELECT * FROM x1;
  55 } {1 1 1  2 2 2   3 3 3  4 4 4  5 5 5  6 6 6}
  56
  57 do_execsql_test 1.2.3 {
  58   DELETE FROM x1;
  59   INSERT INTO x1
  60   VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
  61   UNION ALL SELECT 6, 6, 6;
  62   SELECT * FROM x1;
  63 } {1 1 1  2 2 2   3 3 3  4 4 4  6 6 6}
  64
  65 do_execsql_test 1.2.4 {
  66   DELETE FROM x1;
  67   INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3) UNION ALL SELECT 6, 6, 6;
  68   SELECT * FROM x1;
  69 } {
  70  1 1 1
  71  2 2 2
  72  3 3 3
  73  6 6 6
  74 }
  75
  76 set a 4
  77 set b 5
  78 set c 6
  79 do_execsql_test 1.2.5 {
  80   DELETE FROM x1;
  81   INSERT INTO x1
  82   VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3),
  83         (4, 4, $a), (5, 5, $b), (6, 6, $c)
  84 }
  85
  86 do_execsql_test 1.2.6 {
  87   SELECT * FROM x1;
  88 } {
  89   1 1 1
  90   2 2 2
  91   3 3 3
  92   4 4 4
  93   5 5 5
  94   6 6 6
  95 }
  96
  97 #-------------------------------------------------------------------------
  98 # SQLITE_LIMIT_COMPOUND_SELECT set to 0.
  99 #
 100 reset_db
 101
 102 do_execsql_test 2.0 {
 103   CREATE TABLE x1(a, b, c);
 104 }
 105
 106 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 3
 107
 108 do_catchsql_test 2.1.1 {
 109   INSERT INTO x1 VALUES
 110       (1, 1, 1),
 111       (2, 2, 2),
 112       (3, 3, 3),
 113       (4, 4, 4),
 114       (5, 5, 5),
 115       (6, 6, 6),
 116       (7, 7, 7),
 117       (8, 8, 8),
 118       (9, 9, 9),
 119       (10, 10, 10, 10)
 120 } {1 {all VALUES must have the same number of terms}}
 121
 122 do_catchsql_test 2.1.2 {
 123   INSERT INTO x1 VALUES
 124       (1, 1, 1),
 125       (2, 2, 2, 2),
 126       (3, 3, 3),
 127       (4, 4, 4),
 128       (5, 5, 5),
 129       (6, 6, 6),
 130       (7, 7, 7),
 131       (8, 8, 8),
 132       (9, 9, 9),
 133       (10, 10, 10)
 134 } {1 {all VALUES must have the same number of terms}}
 135
 136 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 0
 137
 138 do_execsql_test 2.2 {
 139   INSERT INTO x1 VALUES
 140       (1, 1, 1),
 141       (2, 2, 2),
 142       (3, 3, 3),
 143       (4, 4, 4),
 144       (5, 5, 5),
 145       (6, 6, 6),
 146       (7, 7, 7),
 147       (8, 8, 8),
 148       (9, 9, 9),
 149       (10, 10, 10)
 150 } {}
 151 do_execsql_test 2.3 {
 152   INSERT INTO x1 VALUES
 153       (1, 1, 1),
 154       (2, 2, 2),
 155       (3, 3, 3),
 156       (4, 4, 4),
 157       (5, 5, 5),
 158       (6, 6, 6),
 159       (7, 7, 7),
 160       (8, 8, 8),
 161       (9, 9, 9),
 162       (10, 10, 10)
 163       UNION ALL
 164       SELECT 5, 12, 12
 165       ORDER BY 1
 166 } {}
 167
 168 #-------------------------------------------------------------------------
 169 reset_db
 170
 171 do_execsql_test 3.0 {
 172   CREATE TABLE y1(x, y);
 173 }
 174
 175 do_execsql_test 3.1.1 {
 176   DELETE FROM y1;
 177   INSERT INTO y1 VALUES(1, 2), (3, 4), (row_number() OVER (), 5);
 178 }
 179 do_execsql_test 3.1.2 {
 180   SELECT * FROM y1;
 181 } {1 2  3 4  1 5}
 182 do_execsql_test 3.2.1 {
 183   DELETE FROM y1;
 184   INSERT INTO y1 VALUES(1, 2), (3, 4), (row_number() OVER (), 6)
 185     , (row_number() OVER (), 7)
 186 }
 187 do_execsql_test 3.1.2 {
 188   SELECT * FROM y1;
 189 } {1 2  3 4  1 6  1 7}
 190
 191 #-------------------------------------------------------------------------
 192 reset_db
 193
 194 do_execsql_test 4.0 {
 195   CREATE TABLE x1(a PRIMARY KEY, b) WITHOUT ROWID;
 196 }
 197
 198 foreach {tn iLimit} {1 0    2 3} {
 199   sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT $iLimit
 200
 201   do_execsql_test 4.1.1 {
 202     DELETE FROM x1;
 203     INSERT INTO x1 VALUES
 204         (1, 1),
 205         (2, (SELECT * FROM  (VALUES('a'), ('b'), ('c'), ('d')) ))
 206   }
 207   do_execsql_test 4.1.2 {
 208     SELECT * FROM x1
 209   } {1 1 2 a}
 210
 211   do_execsql_test 4.2.1 {
 212     DELETE FROM x1;
 213     INSERT INTO x1 VALUES
 214         (1, 1),
 215         (2, 2),
 216         (3, 3),
 217         (4, 4),
 218         (5, (SELECT * FROM  (VALUES('a'), ('b'), ('c'), ('d')) ))
 219   }
 220   do_execsql_test 4.2.2 {
 221     SELECT * FROM x1
 222   } {1 1 2 2 3 3 4 4 5 a}
 223
 224   do_execsql_test 4.3.1 {
 225     DELETE FROM x1;
 226     INSERT INTO x1 VALUES
 227         (1, (SELECT * FROM  (VALUES('a'), ('b'), ('c'), ('d'), ('e')) ))
 228   }
 229   do_execsql_test 4.3.2 {
 230     SELECT * FROM x1
 231   } {1 a}
 232 }
 233
 234 #------------------------------------------------------------------------
 235 reset_db
 236
 237 do_execsql_test 5.0 {
 238   CREATE VIEW v1 AS VALUES(1, 2, 3), (4, 5, 6), (7, 8, 9);
 239 }
 240 do_execsql_test 5.1 {
 241   SELECT * FROM v1
 242 } {1 2 3 4 5 6 7 8 9}
 243
 244 #-------------------------------------------------------------------------
 245 reset_db
 246 do_execsql_test 6.0 {
 247   CREATE TABLE t1(x);
 248   INSERT INTO t1 VALUES(1), (2);
 249 }
 250
 251 do_execsql_test 6.1 {
 252   SELECT ( VALUES( x ), ( x ) ) FROM t1;
 253 } {1 2}
 254
 255 #-------------------------------------------------------------------------
 256 reset_db
 257 do_execsql_test 6.0 {
 258   CREATE TABLE t1(x);
 259   INSERT INTO t1 VALUES('x'), ('y');
 260 }
 261
 262 do_execsql_test 6.1 {
 263   SELECT * FROM t1, (VALUES(1), (2))
 264 } {x 1 x 2 y 1 y 2}
 265
 266 do_execsql_test 6.2 {
 267   VALUES(CAST(44 AS REAL)),(55);
 268 } {44.0 55}
 269
 270 #------------------------------------------------------------------------
 271 do_execsql_test 7.1 {
 272   WITH x1(a, b) AS (
 273     VALUES(1, 2), ('a', 'b')
 274   )
 275   SELECT * FROM x1 one, x1 two
 276 } {
 277   1 2  1 2
 278   1 2  a b
 279   a b  1 2
 280   a b  a b
 281 }
 282
 283 #-------------------------------------------------------------------------
 284 reset_db
 285
 286 set VVV {
 287   ( VALUES('a', 'b'), ('c', 'd'), (123, NULL) )
 288 }
 289 set VVV2 {
 290   (
 291   SELECT 'a' AS column1, 'b' AS column2
 292   UNION ALL SELECT 'c', 'd' UNION ALL SELECT 123, NULL
 293   )
 294 }
 295
 296 do_execsql_test 8.0 {
 297   CREATE TABLE t1(x);
 298   INSERT INTO t1 VALUES('d'), (NULL), (123)
 299 }
 300 foreach {tn q res} {
 301   1 "SELECT * FROM t1 LEFT JOIN VVV" {
 302     d a b   d c d   d 123 {}
 303     {} a b   {} c d   {} 123 {}
 304     123 a b   123 c d   123 123 {}
 305   }
 306
 307   2 "SELECT * FROM t1 LEFT JOIN VVV ON (column1=x)" {
 308     d {} {}
 309     {} {} {}
 310     123 123 {}
 311   }
 312
 313   3 "SELECT * FROM t1 RIGHT JOIN VVV" {
 314     d a b   d c d   d 123 {}
 315     {} a b   {} c d   {} 123 {}
 316     123 a b   123 c d   123 123 {}
 317   }
 318
 319   4 "SELECT * FROM t1 RIGHT JOIN VVV ON (column1=x)" {
 320     123 123 {}
 321     {} a b
 322     {} c d
 323   }
 324
 325   5 "SELECT * FROM t1 FULL OUTER JOIN VVV ON (column1=x)" {
 326     d {} {}
 327     {} {} {}
 328     123 123 {}
 329     {} a b
 330     {} c d
 331   }
 332
 333   6 "SELECT count(*) FROM VVV" { 3 }
 334
 335   7 "SELECT (SELECT column1 FROM VVV)" { a }
 336
 337   8 "SELECT * FROM VVV UNION ALL SELECT * FROM VVV" {
 338     a b c d 123 {}
 339     a b c d 123 {}
 340   }
 341
 342   9 "SELECT * FROM VVV INTERSECT SELECT * FROM VVV" {
 343     123 {} a b c d
 344   }
 345
 346   10 "SELECT * FROM VVV eXCEPT SELECT * FROM VVV" { }
 347
 348   11 "SELECT * FROM VVV eXCEPT SELECT 'a', 'b'" { 123 {} c d }
 349
 350 } {
 351   set q1 [string map [list VVV $VVV] $q]
 352   set q2 [string map [list VVV $VVV2] $q]
 353   set q3 "WITH VVV AS $VVV $q"
 354
 355   do_execsql_test 8.1.$tn.1 $q1 $res
 356   do_execsql_test 8.1.$tn.2 $q2 $res
 357   do_execsql_test 8.1.$tn.3 $q3 $res
 358 }
 359
 360 #-------------------------------------------------------------------------
 361 reset_db
 362
 363 do_execsql_test 9.1 {
 364   VALUES(456), (123), (NULL) UNION ALL SELECT 122 ORDER BY 1
 365 } { {} 122 123 456 }
 366
 367 do_execsql_test 9.2 {
 368   VALUES (1, 2), (3, 4), (
 369     ( SELECT column1 FROM ( VALUES (5, 6), (7, 8) ) ),
 370     ( SELECT max(column2) FROM ( VALUES (5, 1), (7, 6) ) )
 371   )
 372 } { 1 2 3 4 5 6 }
 373
 374 do_execsql_test 10.1 {
 375   CREATE TABLE a2(a, b, c DEFAULT 'xyz');
 376 }
 377 do_execsql_test 10.2 {
 378   INSERT INTO a2(a) VALUES(3),(4);
 379 }
 380
 381 #-------------------------------------------------------------------------
 382 reset_db
 383 ifcapable fts5 {
 384   do_execsql_test 11.0 {
 385     CREATE VIRTUAL TABLE ft USING fts3(x);
 386   }
 387   do_execsql_test 11.1 {
 388     INSERT INTO ft VALUES('one'), ('two');
 389   }
 390 }
 391
 392 #-------------------------------------------------------------------------
 393 reset_db
 394 do_execsql_test 12.0 {
 395   CREATE TABLE t1(a, b);
 396 }
 397 do_execsql_test 12.1 {
 398   INSERT INTO t1 SELECT 1, 2 UNION ALL VALUES(3, 4), (5, 6);
 399 }
 400 do_execsql_test 12.2 {
 401   SELECT * FROM t1
 402 } {1 2 3 4 5 6}
 403
 404 #-------------------------------------------------------------------------
 405 reset_db
 406 do_execsql_test 13.0 {
 407   CREATE TABLE t1(x);
 408   INSERT INTO t1 VALUES('xyz');
 409
 410   SELECT (
 411       VALUES( (max(substr('abc', 1, 1), x)) ),
 412       (123),
 413       (456)
 414       )
 415   FROM t1;
 416 } {xyz}
 417
 418 do_catchsql_test 13.1 {
 419   VALUES(300), (zeroblob(300) OVER win);
 420 } {1 {zeroblob() may not be used as a window function}}
 421
 422 #--------------------------------------------------------------------------
 423 reset_db
 424 do_execsql_test 14.1 {
 425   PRAGMA encoding = utf16;
 426   CREATE TABLE t1(a, b);
 427 } {}
 428
 429 db close
 430 sqlite3 db test.db
 431
 432 do_execsql_test 14.2 {
 433   INSERT INTO t1 VALUES
 434     (17, 'craft'),
 435     (16, 'urtlek' IN(1,2,3));
 436 }
 437
 438 #--------------------------------------------------------------------------
 439 #
 440 reset_db
 441 do_eqp_test 15.1 {
 442   VALUES(1),(2),(3),(4),(5);
 443 } {
 444   QUERY PLAN
 445   `--SCAN 5-ROW VALUES CLAUSE
 446 }
 447 do_execsql_test 15.2 {
 448   CREATE TABLE t1(a,b);
 449 }
 450 do_eqp_test 15.3 {
 451   INSERT INTO t1 VALUES
 452     (1,2),(3,4),(7,8);
 453 } {
 454   QUERY PLAN
 455   `--SCAN 3-ROW VALUES CLAUSE
 456 }
 457 do_eqp_test 15.4 {
 458   INSERT INTO t1 VALUES
 459     (1,2),(3,4),(7,8),
 460     (5,row_number()OVER());
 461 } {
 462   QUERY PLAN
 463   `--COMPOUND QUERY
 464      |--LEFT-MOST SUBQUERY
 465      |  `--SCAN 3-ROW VALUES CLAUSE
 466      `--UNION ALL
 467         |--CO-ROUTINE (subquery-xxxxxx)
 468         |  `--SCAN CONSTANT ROW
 469         `--SCAN (subquery-xxxxxx)
 470 }
 471 do_eqp_test 15.5 {
 472   SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6)), (VALUES('a'),('b'),('c'));
 473 } {
 474   QUERY PLAN
 475   |--SCAN 6-ROW VALUES CLAUSE
 476   `--SCAN 3-ROW VALUES CLAUSE
 477 }
 478 do_execsql_test 15.6 {
 479   CREATE TABLE t2(x,y);
 480 }
 481 do_eqp_test 15.7 {
 482   SELECT * FROM t2 UNION ALL VALUES(1,2),(3,4),(5,6),(7,8);
 483 } {
 484   QUERY PLAN
 485   `--COMPOUND QUERY
 486      |--LEFT-MOST SUBQUERY
 487      |  `--SCAN t2
 488      `--UNION ALL
 489         `--SCAN 4-ROW VALUES CLAUSE
 490 }
 491
 492 #--------------------------------------------------------------------------
 493 # The VALUES-as-coroutine optimization can be applied to later rows of
 494 # a VALUES clause even if earlier rows do not qualify.
 495 #
 496 reset_db
 497 do_execsql_test 16.1 {
 498   CREATE TABLE t1(a,b);
 499 }
 500 do_execsql_test 16.2 {
 501   BEGIN;
 502   INSERT INTO t1 VALUES(1,2),(3,4),(5,6),
 503      (7,row_number()OVER()),
 504      (9,10), (11,12), (13,14), (15,16);
 505   SELECT * FROM t1 ORDER BY a, b;
 506   ROLLBACK;
 507 } {1 2 3 4 5 6 7 1 9 10 11 12 13 14 15 16}
 508 do_eqp_test 16.3 {
 509   INSERT INTO t1 VALUES(1,2),(3,4),(5,6),
 510      (7,row_number()OVER()),
 511      (9,10), (11,12), (13,14), (15,16);
 512 } {
 513   QUERY PLAN
 514   `--COMPOUND QUERY
 515      |--LEFT-MOST SUBQUERY
 516      |  `--SCAN 3-ROW VALUES CLAUSE
 517      |--UNION ALL
 518      |  |--CO-ROUTINE (subquery-xxxxxx)
 519      |  |  `--SCAN CONSTANT ROW
 520      |  `--SCAN (subquery-xxxxxx)
 521      `--UNION ALL
 522         `--SCAN 4-ROW VALUES CLAUSE
 523 }
 524 do_execsql_test 16.4 {
 525   BEGIN;
 526   INSERT INTO t1 VALUES
 527      (1,row_number()OVER()),
 528      (2,3), (4,5), (6,7);
 529   SELECT * FROM t1 ORDER BY a, b;
 530   ROLLBACK;
 531 } {1 1 2 3 4 5 6 7}
 532 do_eqp_test 16.5 {
 533   INSERT INTO t1 VALUES
 534      (1,row_number()OVER()),
 535      (2,3), (4,5), (6,7);
 536 } {
 537   QUERY PLAN
 538   `--COMPOUND QUERY
 539      |--LEFT-MOST SUBQUERY
 540      |  |--CO-ROUTINE (subquery-xxxxxx)
 541      |  |  `--SCAN CONSTANT ROW
 542      |  `--SCAN (subquery-xxxxxx)
 543      `--UNION ALL
 544         `--SCAN 3-ROW VALUES CLAUSE
 545 }
 546 do_execsql_test 16.6 {
 547   BEGIN;
 548   INSERT INTO t1 VALUES
 549      (1,2),(3,4),
 550      (5,row_number()OVER()),
 551      (7,8),(9,10),(11,12),
 552      (13,row_number()OVER()),
 553      (15,16),(17,18),(19,20),(21,22);
 554   SELECT * FROM t1 ORDER BY a, b;
 555   ROLLBACK;
 556 } { 1 2 3 4 5 1 7 8 9 10 11 12 13 1 15 16 17 18 19 20 21 22}
 557 do_eqp_test 16.7 {
 558   INSERT INTO t1 VALUES
 559      (1,2),(3,4),
 560      (5,row_number()OVER()),
 561      (7,8),(9,10),(11,12),
 562      (13,row_number()OVER()),
 563      (15,16),(17,18),(19,20),(21,22);
 564 } {
 565   QUERY PLAN
 566   `--COMPOUND QUERY
 567      |--LEFT-MOST SUBQUERY
 568      |  `--SCAN 2-ROW VALUES CLAUSE
 569      |--UNION ALL
 570      |  |--CO-ROUTINE (subquery-xxxxxx)
 571      |  |  `--SCAN CONSTANT ROW
 572      |  `--SCAN (subquery-xxxxxx)
 573      |--UNION ALL
 574      |  `--SCAN 3-ROW VALUES CLAUSE
 575      |--UNION ALL
 576      |  |--CO-ROUTINE (subquery-xxxxxx)
 577      |  |  `--SCAN CONSTANT ROW
 578      |  `--SCAN (subquery-xxxxxx)
 579      `--UNION ALL
 580         `--SCAN 4-ROW VALUES CLAUSE
 581 }
 582
 583 #--------------------------------------------------------------------------
 584 # 2024-03-23 dbsqlfuzz crash-c2c5e7e08b7e489d270a26d895077a03f678c33b
 585 #
 586 do_execsql_test 17.1 {
 587   DROP TABLE IF EXISTS t1;
 588   CREATE TABLE t1 AS SELECT * FROM (VALUES(1,2), (3,4 IN (1,2,3)));
 589 }
 590
 591 do_execsql_test 17.2 {
 592   SELECT * FROM t1
 593 } {1 2 3 0}
 594
 595 finish_test