test/view.test

   1 # 2002 February 26
   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 testing VIEW statements.
  13 #
  14 # $Id: view.test,v 1.39 2008/12/14 14:45:21 danielk1977 Exp $
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17
  18 # Omit this entire file if the library is not configured with views enabled.
  19 ifcapable !view {
  20   finish_test
  21   return
  22 }
  23
  24 do_test view-1.0 {
  25   execsql {
  26     CREATE TABLE t1(a,b,c);
  27     INSERT INTO t1 VALUES(1,2,3);
  28     INSERT INTO t1 VALUES(4,5,6);
  29     INSERT INTO t1 VALUES(7,8,9);
  30     SELECT * FROM t1;
  31   }
  32 } {1 2 3 4 5 6 7 8 9}
  33
  34 do_test view-1.1 {
  35   execsql {
  36     BEGIN;
  37     CREATE VIEW IF NOT EXISTS v1 AS SELECT a,b FROM t1;
  38     SELECT * FROM v1 ORDER BY a;
  39   }
  40 } {1 2 4 5 7 8}
  41 do_test view-1.1.100 {
  42   db config enable_view off
  43   catchsql {
  44     SELECT * FROM v1 ORDER BY a;
  45   }
  46 } {1 {access to view "v1" prohibited}}
  47 do_execsql_test view-1.1.101 {
  48   CREATE TEMP VIEW v1temp AS SELECT a, b FROM t1;
  49   SELECT * FROM v1temp ORDER BY a;
  50 } {1 2 4 5 7 8}
  51 do_test view-1.1.110 {
  52   db config enable_view on
  53   catchsql {
  54     SELECT * FROM v1 ORDER BY a;
  55     SELECT * FROM v1temp ORDER BY a;
  56   }
  57 } {0 {1 2 4 5 7 8 1 2 4 5 7 8}}
  58 ifcapable vtab {
  59   do_execsql_test view-1.1.120 {
  60     SELECT name, type FROM pragma_table_list('v1');
  61   } {v1 view}
  62 }
  63 do_test view-1.2 {
  64   catchsql {
  65     ROLLBACK;
  66     SELECT * FROM v1 ORDER BY a;
  67   }
  68 } {1 {no such table: v1}}
  69 do_test view-1.3 {
  70   execsql {
  71     CREATE VIEW v1 AS SELECT a,b FROM t1;
  72     SELECT * FROM v1 ORDER BY a;
  73   }
  74 } {1 2 4 5 7 8}
  75 do_test view-1.3.1 {
  76   db close
  77   sqlite3 db test.db
  78   execsql {
  79     SELECT * FROM v1 ORDER BY a;
  80   }
  81 } {1 2 4 5 7 8}
  82 do_test view-1.4 {
  83   catchsql {
  84     DROP VIEW IF EXISTS v1;
  85     SELECT * FROM v1 ORDER BY a;
  86   }
  87 } {1 {no such table: v1}}
  88 do_test view-1.5 {
  89   execsql {
  90     CREATE VIEW v1 AS SELECT a,b FROM t1;
  91     SELECT * FROM v1 ORDER BY a;
  92   }
  93 } {1 2 4 5 7 8}
  94 do_test view-1.6 {
  95   catchsql {
  96     DROP TABLE t1;
  97     SELECT * FROM v1 ORDER BY a;
  98   }
  99 } {1 {no such table: main.t1}}
 100 do_test view-1.7 {
 101   execsql {
 102     CREATE TABLE t1(x,a,b,c);
 103     INSERT INTO t1 VALUES(1,2,3,4);
 104     INSERT INTO t1 VALUES(4,5,6,7);
 105     INSERT INTO t1 VALUES(7,8,9,10);
 106     SELECT * FROM v1 ORDER BY a;
 107   }
 108 } {2 3 5 6 8 9}
 109 do_test view-1.8 {
 110   db close
 111   sqlite3 db test.db
 112   execsql {
 113     SELECT * FROM v1 ORDER BY a;
 114   }
 115 } {2 3 5 6 8 9}
 116
 117 do_execsql_test view-1.10 {
 118   CREATE TABLE t9(x INTEGER);
 119   CREATE VIEW v9a AS SELECT x FROM t9;
 120   CREATE VIEW v9b AS SELECT * FROM t9;
 121   CREATE VIEW v9c(x) AS SELECT x FROM t9;
 122   CREATE VIEW v9d(x) AS SELECT * FROM t9;
 123 } {}
 124 do_execsql_test view-1.11 {
 125   PRAGMA table_info(v9a);
 126 } {0 x INTEGER 0 {} 0}
 127 do_execsql_test view-1.12 {
 128   PRAGMA table_info(v9b);
 129 } {0 x INTEGER 0 {} 0}
 130 do_execsql_test view-1.13 {
 131   PRAGMA table_info(v9c);
 132 } {0 x INTEGER 0 {} 0}
 133 do_execsql_test view-1.14 {
 134   PRAGMA table_info(v9d);
 135 } {0 x INTEGER 0 {} 0}
 136
 137 do_test view-2.1 {
 138   execsql {
 139     CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5
 140   };  # No semicolon
 141   execsql2 {
 142     SELECT * FROM v2;
 143   }
 144 } {x 7 a 8 b 9 c 10}
 145 do_test view-2.2 {
 146   catchsql {
 147     INSERT INTO v2 VALUES(1,2,3,4);
 148   }
 149 } {1 {cannot modify v2 because it is a view}}
 150 do_test view-2.3 {
 151   catchsql {
 152     UPDATE v2 SET a=10 WHERE a=5;
 153   }
 154 } {1 {cannot modify v2 because it is a view}}
 155 do_test view-2.4 {
 156   catchsql {
 157     DELETE FROM v2;
 158   }
 159 } {1 {cannot modify v2 because it is a view}}
 160 do_test view-2.5 {
 161   execsql {
 162     INSERT INTO t1 VALUES(11,12,13,14);
 163     SELECT * FROM v2 ORDER BY x;
 164   }
 165 } {7 8 9 10 11 12 13 14}
 166 do_test view-2.6 {
 167   execsql {
 168     SELECT x FROM v2 WHERE a>10
 169   }
 170 } {11}
 171
 172 # Test that column name of views are generated correctly.
 173 #
 174 do_test view-3.1 {
 175   execsql2 {
 176     SELECT * FROM v1 LIMIT 1
 177   }
 178 } {a 2 b 3}
 179 do_test view-3.2 {
 180   execsql2 {
 181     SELECT * FROM v2 LIMIT 1
 182   }
 183 } {x 7 a 8 b 9 c 10}
 184 do_test view-3.3.1 {
 185   execsql2 {
 186     DROP VIEW v1;
 187     CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1;
 188     SELECT * FROM v1 LIMIT 1
 189   }
 190 } {xyz 2 pqr 7 c-b 1}
 191 do_test view-3.3.2 {
 192   execsql2 {
 193     CREATE VIEW v1b AS SELECT t1.a, b+c, t1.c FROM t1;
 194     SELECT * FROM v1b LIMIT 1
 195   }
 196 } {a 2 b+c 7 c 4}
 197 do_test view-3.3.3 {
 198   execsql2 {
 199     CREATE VIEW v1c(x,y,z) AS SELECT a, b+c, c-b FROM t1;
 200     SELECT * FROM v1c LIMIT 1;
 201   }
 202 } {x 2 y 7 z 1}
 203 do_catchsql_test view-3.3.4 {
 204   CREATE VIEW v1err(x,y DESC,z) AS SELECT a, b+c, c-b FROM t1;
 205 } {1 {syntax error after column name "y"}}
 206 do_catchsql_test view-3.3.5 {
 207   DROP VIEW IF EXISTS v1err;
 208   CREATE VIEW v1err(x,y) AS SELECT a, b+c, c-b FROM t1;
 209   SELECT * FROM v1err;
 210 } {1 {expected 2 columns for 'v1err' but got 3}}
 211 do_catchsql_test view-3.3.6 {
 212   DROP VIEW IF EXISTS v1err;
 213   CREATE VIEW v1err(w,x,y,z) AS SELECT a, b+c, c-b FROM t1;
 214   SELECT * FROM v1err;
 215 } {1 {expected 4 columns for 'v1err' but got 3}}
 216
 217 ifcapable compound {
 218 do_test  view-3.4 {
 219   execsql2 {
 220     CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b;
 221     SELECT * FROM v3 LIMIT 4;
 222   }
 223 } {a 2 a 3 a 5 a 6}
 224 do_test view-3.5 {
 225   execsql2 {
 226     CREATE VIEW v4 AS
 227       SELECT a, b FROM t1
 228       UNION
 229       SELECT b AS 'x', a AS 'y' FROM t1
 230       ORDER BY x, y;
 231     SELECT b FROM v4 ORDER BY b LIMIT 4;
 232   }
 233 } {b 2 b 3 b 5 b 6}
 234 } ;# ifcapable compound
 235
 236
 237 do_test view-4.1 {
 238   catchsql {
 239     DROP VIEW t1;
 240   }
 241 } {1 {use DROP TABLE to delete table t1}}
 242 do_test view-4.2 {
 243   execsql {
 244     SELECT 1 FROM t1 LIMIT 1;
 245   }
 246 } 1
 247 do_test view-4.3 {
 248   catchsql {
 249     DROP TABLE v1;
 250   }
 251 } {1 {use DROP VIEW to delete view v1}}
 252 do_test view-4.4 {
 253   execsql {
 254      SELECT 1 FROM v1 LIMIT 1;
 255   }
 256 } {1}
 257 do_test view-4.5 {
 258   catchsql {
 259     CREATE INDEX i1v1 ON v1(xyz);
 260   }
 261 } {1 {views may not be indexed}}
 262
 263 do_test view-5.1 {
 264   execsql {
 265     CREATE TABLE t2(y,a);
 266     INSERT INTO t2 VALUES(22,2);
 267     INSERT INTO t2 VALUES(33,3);
 268     INSERT INTO t2 VALUES(44,4);
 269     INSERT INTO t2 VALUES(55,5);
 270     SELECT * FROM t2;
 271   }
 272 } {22 2 33 3 44 4 55 5}
 273 do_test view-5.2 {
 274   execsql {
 275     CREATE VIEW v5 AS
 276       SELECT t1.x AS v, t2.y AS w FROM t1 JOIN t2 USING(a);
 277     SELECT * FROM v5;
 278   }
 279 } {1 22 4 55}
 280
 281 # Verify that the view v5 gets flattened.  see sqliteFlattenSubquery().
 282 # This will only work if EXPLAIN is enabled.
 283 # Ticket #272
 284 #
 285 ifcapable {explain} {
 286 do_test view-5.3 {
 287   lsearch [execsql {
 288     EXPLAIN SELECT * FROM v5;
 289   }] OpenEphemeral
 290 } {-1}
 291 do_test view-5.4 {
 292   execsql {
 293     SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
 294   }
 295 } {1 22 22 2 4 55 55 5}
 296 do_test view-5.5 {
 297   lsearch [execsql {
 298     EXPLAIN SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
 299   }] OpenEphemeral
 300 } {-1}
 301 do_test view-5.6 {
 302   execsql {
 303     SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
 304   }
 305 } {22 2 1 22 55 5 4 55}
 306 do_test view-5.7 {
 307   lsearch [execsql {
 308     EXPLAIN SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
 309   }] OpenEphemeral
 310 } {-1}
 311 do_test view-5.8 {
 312   execsql {
 313     SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
 314   }
 315 } {1 2 3 4 1 22 22 2 4 5 6 7 4 55 55 5}
 316 do_test view-5.9 {
 317   lsearch [execsql {
 318     EXPLAIN SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
 319   }] OpenEphemeral
 320 } {-1}
 321 } ;# endif explain
 322
 323 do_test view-6.1 {
 324   execsql {
 325     SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2;
 326   }
 327 } {7 8 9 10 27}
 328 do_test view-6.2 {
 329   execsql {
 330     SELECT max(x), max(a), max(b), max(c), max(a+b+c) FROM v2;
 331   }
 332 } {11 12 13 14 39}
 333
 334 do_test view-7.1 {
 335   execsql {
 336     CREATE TABLE test1(id integer primary key, a);
 337     CREATE TABLE test2(id integer, b);
 338     INSERT INTO test1 VALUES(1,2);
 339     INSERT INTO test2 VALUES(1,3);
 340     CREATE VIEW test AS
 341       SELECT test1.id, a, b
 342       FROM test1 JOIN test2 ON test2.id=test1.id;
 343     SELECT * FROM test;
 344   }
 345 } {1 2 3}
 346 do_test view-7.2 {
 347   db close
 348   sqlite3 db test.db
 349   execsql {
 350     SELECT * FROM test;
 351   }
 352 } {1 2 3}
 353 do_test view-7.3 {
 354   execsql {
 355     DROP VIEW test;
 356     CREATE VIEW test AS
 357       SELECT test1.id, a, b
 358       FROM test1 JOIN test2 USING(id);
 359     SELECT * FROM test;
 360   }
 361 } {1 2 3}
 362 do_test view-7.4 {
 363   db close
 364   sqlite3 db test.db
 365   execsql {
 366     SELECT * FROM test;
 367   }
 368 } {1 2 3}
 369 do_test view-7.5 {
 370   execsql {
 371     DROP VIEW test;
 372     CREATE VIEW test AS
 373       SELECT test1.id, a, b
 374       FROM test1 NATURAL JOIN test2;
 375     SELECT * FROM test;
 376   }
 377 } {1 2 3}
 378 do_test view-7.6 {
 379   db close
 380   sqlite3 db test.db
 381   execsql {
 382     SELECT * FROM test;
 383   }
 384 } {1 2 3}
 385
 386 do_test view-8.1 {
 387   execsql {
 388     CREATE VIEW v6 AS SELECT pqr, xyz FROM v1;
 389     SELECT * FROM v6 ORDER BY xyz;
 390   }
 391 } {7 2 13 5 19 8 27 12}
 392 do_test view-8.2 {
 393   db close
 394   sqlite3 db test.db
 395   execsql {
 396     SELECT * FROM v6 ORDER BY xyz;
 397   }
 398 } {7 2 13 5 19 8 27 12}
 399 do_test view-8.3 {
 400   execsql {
 401     CREATE VIEW v7(a) AS SELECT pqr+xyz FROM v6;
 402     SELECT * FROM v7 ORDER BY a;
 403   }
 404 } {9 18 27 39}
 405
 406 ifcapable subquery {
 407   do_test view-8.4 {
 408     execsql {
 409       CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM
 410         (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo);
 411       SELECT * FROM v8;
 412     }
 413   } 3
 414   do_test view-8.5 {
 415     execsql {
 416       SELECT mx+10, mx*2 FROM v8;
 417     }
 418   } {13 6}
 419   do_test view-8.6 {
 420     execsql {
 421       SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2;
 422     }
 423   } {13 7}
 424   do_test view-8.7 {
 425     execsql {
 426       SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2;
 427     }
 428   } {13 13 13 19 13 27}
 429 } ;# ifcapable subquery
 430
 431 # Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW.
 432 #
 433 do_test view-9.1 {
 434   execsql {
 435     INSERT INTO t2 SELECT * FROM t2 WHERE a<5;
 436     INSERT INTO t2 SELECT * FROM t2 WHERE a<4;
 437     INSERT INTO t2 SELECT * FROM t2 WHERE a<3;
 438     SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1;
 439   }
 440 } {1 2 4 8}
 441 do_test view-9.2 {
 442   execsql {
 443     SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
 444   }
 445 } {1 2 4}
 446 do_test view-9.3 {
 447   execsql {
 448     CREATE VIEW v9 AS
 449        SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
 450     SELECT * FROM v9;
 451   }
 452 } {1 2 4}
 453 do_test view-9.4 {
 454   execsql {
 455     SELECT * FROM v9 ORDER BY 1 DESC;
 456   }
 457 } {4 2 1}
 458 do_test view-9.5 {
 459   execsql {
 460     CREATE VIEW v10 AS
 461        SELECT DISTINCT a, count(*) FROM t2 GROUP BY a ORDER BY 2 LIMIT 3;
 462     SELECT * FROM v10;
 463   }
 464 } {5 1 4 2 3 4}
 465 do_test view-9.6 {
 466   execsql {
 467     SELECT * FROM v10 ORDER BY 1;
 468   }
 469 } {3 4 4 2 5 1}
 470
 471 # Tables with columns having peculiar quoted names used in views
 472 # Ticket #756.
 473 #
 474 do_test view-10.1 {
 475   execsql {
 476     CREATE TABLE t3("9" integer, [4] text);
 477     INSERT INTO t3 VALUES(1,2);
 478     CREATE VIEW v_t3_a AS SELECT a.[9] FROM t3 AS a;
 479     CREATE VIEW v_t3_b AS SELECT "4" FROM t3;
 480     SELECT * FROM v_t3_a;
 481   }
 482 } {1}
 483 do_test view-10.2 {
 484   execsql {
 485     SELECT * FROM v_t3_b;
 486   }
 487 } {2}
 488
 489 do_test view-11.1 {
 490   execsql {
 491     CREATE TABLE t4(a COLLATE NOCASE);
 492     INSERT INTO t4 VALUES('This');
 493     INSERT INTO t4 VALUES('this');
 494     INSERT INTO t4 VALUES('THIS');
 495     SELECT * FROM t4 WHERE a = 'THIS';
 496   }
 497 } {This this THIS}
 498 ifcapable subquery {
 499   do_test view-11.2 {
 500     execsql {
 501       SELECT * FROM (SELECT * FROM t4) WHERE a = 'THIS';
 502     }
 503   } {This this THIS}
 504 }
 505 do_test view-11.3 {
 506   execsql {
 507     CREATE VIEW v11 AS SELECT * FROM t4;
 508     SELECT * FROM v11 WHERE a = 'THIS';
 509   }
 510 } {This this THIS}
 511
 512 # Ticket #1270:  Do not allow parameters in view definitions.
 513 #
 514 do_test view-12.1 {
 515   catchsql {
 516     CREATE VIEW v12 AS SELECT a FROM t1 WHERE b=?
 517   }
 518 } {1 {parameters are not allowed in views}}
 519 do_test view-12.2 {
 520   catchsql {
 521     CREATE VIEW v12(x) AS SELECT a FROM t1 WHERE b=?
 522   }
 523 } {1 {parameters are not allowed in views}}
 524
 525 ifcapable attach {
 526   do_test view-13.1 {
 527     forcedelete test2.db
 528     catchsql {
 529       ATTACH 'test2.db' AS two;
 530       CREATE TABLE two.t2(x,y);
 531       CREATE VIEW v13 AS SELECT y FROM two.t2;
 532     }
 533   } {1 {view v13 cannot reference objects in database two}}
 534 }
 535
 536 # Ticket #1658
 537 #
 538 do_test view-14.1 {
 539   catchsql {
 540     CREATE TEMP VIEW t1 AS SELECT a,b FROM t1;
 541     SELECT * FROM temp.t1;
 542   }
 543 } {1 {view t1 is circularly defined}}
 544 do_test view-14.2 {
 545   catchsql {
 546     DROP VIEW IF EXISTS temp.t1;
 547     CREATE TEMP VIEW t1(a,b) AS SELECT a,b FROM t1;
 548     SELECT * FROM temp.t1;
 549   }
 550 } {1 {view t1 is circularly defined}}
 551
 552 # Tickets #1688, #1709
 553 #
 554 do_test view-15.1 {
 555   execsql2 {
 556     CREATE VIEW v15 AS SELECT a AS x, b AS y FROM t1;
 557     SELECT * FROM v15 LIMIT 1;
 558   }
 559 } {x 2 y 3}
 560 do_test view-15.2 {
 561   execsql2 {
 562     SELECT x, y FROM v15 LIMIT 1
 563   }
 564 } {x 2 y 3}
 565
 566 do_test view-16.1 {
 567   catchsql {
 568     CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1;
 569   }
 570 } {0 {}}
 571 do_test view-16.2 {
 572   execsql {
 573     SELECT sql FROM sqlite_master WHERE name='v1'
 574   }
 575 } {{CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1}}
 576 do_test view-16.3 {
 577   catchsql {
 578     DROP VIEW IF EXISTS nosuchview
 579   }
 580 } {0 {}}
 581
 582 # correct error message when attempting to drop a view that does not
 583 # exist.
 584 #
 585 do_test view-17.1 {
 586   catchsql {
 587     DROP VIEW nosuchview
 588   }
 589 } {1 {no such view: nosuchview}}
 590 do_test view-17.2 {
 591   catchsql {
 592     DROP VIEW main.nosuchview
 593   }
 594 } {1 {no such view: main.nosuchview}}
 595
 596 do_test view-18.1 {
 597   execsql {
 598     DROP VIEW t1;
 599     DROP TABLE t1;
 600     CREATE TABLE t1(a, b, c);
 601     INSERT INTO t1 VALUES(1, 2, 3);
 602     INSERT INTO t1 VALUES(4, 5, 6);
 603
 604     CREATE VIEW vv1 AS SELECT * FROM t1;
 605     CREATE VIEW vv2 AS SELECT * FROM vv1;
 606     CREATE VIEW vv3 AS SELECT * FROM vv2;
 607     CREATE VIEW vv4 AS SELECT * FROM vv3;
 608     CREATE VIEW vv5 AS SELECT * FROM vv4;
 609
 610     SELECT * FROM vv5;
 611   }
 612 } {1 2 3 4 5 6}
 613
 614 # Ticket #3308
 615 # Make sure "rowid" columns in a view are named correctly.
 616 #
 617 do_test view-19.1 {
 618   execsql {
 619     CREATE VIEW v3308a AS SELECT rowid, * FROM t1;
 620   }
 621   execsql2 {
 622     SELECT * FROM v3308a
 623   }
 624 } {rowid 1 a 1 b 2 c 3 rowid 2 a 4 b 5 c 6}
 625 do_test view-19.2 {
 626   execsql {
 627     CREATE VIEW v3308b AS SELECT t1.rowid, t1.a, t1.b+t1.c FROM t1;
 628   }
 629   execsql2 {
 630     SELECT * FROM v3308b
 631   }
 632 } {rowid 1 a 1 t1.b+t1.c 5 rowid 2 a 4 t1.b+t1.c 11}
 633 do_test view-19.3 {
 634   execsql {
 635     CREATE VIEW v3308c AS SELECT t1.oid, A, t1.b+t1.c AS x FROM t1;
 636   }
 637   execsql2 {
 638     SELECT * FROM v3308c
 639   }
 640 } {rowid 1 a 1 x 5 rowid 2 a 4 x 11}
 641
 642 # Ticket #3539 had this crashing (see commit [5940]).
 643 do_test view-20.1 {
 644   execsql {
 645     DROP TABLE IF EXISTS t1;
 646     DROP VIEW IF EXISTS v1;
 647     CREATE TABLE t1(c1);
 648     CREATE VIEW v1 AS SELECT c1 FROM (SELECT t1.c1 FROM t1);
 649   }
 650 } {}
 651
 652 db close
 653 sqlite3 db :memory:
 654 do_execsql_test view-22.1 {
 655   CREATE VIEW x1 AS SELECT 123 AS '', 234 AS '', 345 AS '';
 656   SELECT * FROM x1;
 657 } {123 234 345}
 658 do_test view-22.2 {
 659   unset -nocomplain x
 660   db eval {SELECT * FROM x1} x break
 661   lsort [array names x]
 662 } {{} * :1 :2}
 663
 664 do_test view-25.1 {
 665   db eval {
 666     CREATE TABLE t25 (x);
 667     INSERT INTO t25 (x) VALUES (1);
 668     ANALYZE;
 669   }
 670   proc authLogDelete {code arg1 arg2 arg3 arg4 args} {
 671     if {$code=="SQLITE_DELETE" && [string match sqlite_stat* $arg1]} {
 672       # lappend ::log [list $code $arg1 $arg2 $arg3 $arg4 $args]
 673       lappend ::log [list $code $arg1 $arg2 $arg3 $arg4]
 674     }
 675     return SQLITE_OK
 676   }
 677   set log ""
 678   db authorizer ::authLogDelete
 679   db eval {DROP VIEW x1;}
 680   set log
 681 } {}
 682
 683 set res [list {SQLITE_DELETE sqlite_stat1 {} main {}}]
 684 ifcapable stat4 { lappend res {SQLITE_DELETE sqlite_stat4 {} main {}} }
 685 do_test view-25.2 {
 686   set log ""
 687   db eval {DROP TABLE t25;}
 688   set log
 689 } $res
 690
 691 #-------------------------------------------------------------------------
 692 do_execsql_test view-26.0 {
 693   CREATE TABLE t16(a, b, c UNIQUE);
 694   INSERT INTO t16 VALUES(1, 1, 1);
 695   INSERT INTO t16 VALUES(2, 2, 2);
 696   INSERT INTO t16 VALUES(3, 3, 3);
 697   CREATE VIEW v16 AS SELECT max(a) AS mx, min(b) AS mn FROM t16 GROUP BY c;
 698
 699   SELECT * FROM v16 AS one, v16 AS two WHERE one.mx=1;
 700 } {
 701   1 1 1 1
 702   1 1 2 2
 703   1 1 3 3
 704 }
 705 do_execsql_test view-26.1 {
 706   WITH v17(x,y) AS (SELECT max(a), min(b) FROM t16 GROUP BY c)
 707   SELECT * FROM v17 AS one, v17 AS two WHERE one.x=1;
 708 } {
 709   1 1 1 1
 710   1 1 2 2
 711   1 1 3 3
 712 }
 713
 714 #-------------------------------------------------------------------------
 715 reset_db
 716 do_execsql_test view-27.0 {
 717   CREATE TABLE t0(c0 TEXT, c1);
 718   INSERT INTO t0(c0, c1) VALUES (-1, 0);
 719   CREATE VIEW v0(c0, c1) AS SELECT t0.c0, AVG(t0.c1) FROM t0;
 720 }
 721
 722 do_execsql_test view-27.1 {
 723   SELECT c0, typeof(c0), c1, typeof(c1) FROM v0;
 724 } {
 725   -1   text
 726    0.0 real
 727 }
 728
 729 do_execsql_test view-27.2 { SELECT c0<c1 FROM v0 } 1
 730 do_execsql_test view-27.3 { SELECT c1<c0 FROM v0 } 0
 731 do_execsql_test view-27.4 {
 732   SELECT 1 FROM v0 WHERE c1<c0
 733 } {}
 734 do_execsql_test view-27.5 {
 735   SELECT 1 FROM v0 WHERE c0<c1
 736 } {1}
 737
 738 do_execsql_test view-27.6 {
 739   SELECT c0<c1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0)
 740 } 1
 741 do_execsql_test view-27.7 {
 742   SELECT c1<c0 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0)
 743 } 0
 744 do_execsql_test view-27.8 {
 745   SELECT 1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) WHERE c1<c0
 746 } {}
 747 do_execsql_test view-27.9 {
 748   SELECT 1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) WHERE c0<c1
 749 } {1}
 750
 751 #-------------------------------------------------------------------------
 752 reset_db
 753 do_execsql_test view-28.0 {
 754   CREATE TABLE t0(c0 TEXT);
 755   CREATE VIEW v0(c0) AS SELECT t0.c0 FROM t0;
 756   INSERT INTO t0(c0) VALUES ('0');
 757 }
 758 do_execsql_test view-28.1 {
 759   SELECT 0 IN (c0) FROM t0;
 760 } {0}
 761 do_execsql_test view-28.2 {
 762   SELECT 0 IN (c0) FROM (SELECT c0 FROM t0);
 763 } {0}
 764
 765 #-------------------------------------------------------------------------
 766 # 2020-10-26.  https://sqlite.org/forum/forumpost/daa2c728cc
 767 #
 768 reset_db
 769 do_catchsql_test view-29.0 {
 770   CREATE TABLE t1(a,b,c);
 771   CREATE VIEW IF NOT EXISTS IF AS SELECT null;
 772 } {1 {malformed database schema (IF) - near "AS": syntax error}}
 773 do_catchsql_test view-29.1 {
 774   CREATE TABLE t2(c,d,e);
 775   SELECT name FROM sqlite_schema ORDER BY name;
 776 } {0 {t1 t2}}
 777
 778 #-------------------------------------------------------------------------
 779 # 2022-12-11.  https://sqlite.org/src/info/679ed6a2
 780 #
 781 # 2022-12-14 change:  If the AS SELECT of a VIEW is a compound where
 782 # the datatypes on each arm of the compound are different, then the
 783 # datatype of the overall column is BLOB (ANY).
 784 #
 785 reset_db
 786 do_execsql_test view-30.0 {
 787   CREATE TABLE t0(a INT, b TEXT);
 788
 789   INSERT INTO t0 VALUES(1,'one');
 790
 791   CREATE VIEW t1      AS SELECT a, b FROM t0 UNION ALL SELECT 2, 2;
 792   CREATE VIEW t2(a,b) AS SELECT a, b FROM t0 UNION ALL SELECT 2, 2;
 793 }
 794
 795 ifcapable schema_pragmas {
 796   do_execsql_test view-30.1 {
 797     PRAGMA table_info = t1;
 798   } { 0 a INT 0 {} 0 1 b BLOB 0 {} 0 }
 799   do_execsql_test view-30.2 {
 800     PRAGMA table_info = t2;
 801   } { 0 a INT 0 {} 0 1 b BLOB 0 {} 0 }
 802 }
 803
 804 #-----------------------------------------------------------------------
 805 # 2024-04-25   Trying to make type information on compound subqueries
 806 # more predictable and rational.
 807 #
 808 reset_db
 809 do_execsql_test view-31.1 {
 810   CREATE TABLE x2(b TEXT);
 811   CREATE TABLE x1(a TEXT);
 812   INSERT INTO x1 VALUES('123');
 813   -- Two queries get the same result even though the order of terms
 814   -- in the CTE is reversed
 815   WITH c(x) AS ( SELECT b FROM x2 UNION SELECT 123 )
 816     SELECT count(*) FROM x1 WHERE a IN c;
 817   WITH c(x) AS ( SELECT 123 UNION SELECT b FROM x2 )
 818     SELECT count(*) FROM x1 WHERE a IN c;
 819 } {0 0}
 820 do_execsql_test view-31.2 {
 821   CREATE TABLE t3(a INTEGER, b TEXT);
 822   INSERT INTO t3 VALUES(123, 123);
 823   WITH s AS ( VALUES(123), (456) ) SELECT * FROM t3 WHERE b IN s;
 824 } {123 123}
 825
 826
 827 finish_test