sqlite3/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.25 2005/06/06 15:32:08 drh 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 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.2 {
  42   catchsql {
  43     ROLLBACK;
  44     SELECT * FROM v1 ORDER BY a;
  45   }
  46 } {1 {no such table: v1}}
  47 do_test view-1.3 {
  48   execsql {
  49     CREATE VIEW v1 AS SELECT a,b FROM t1;
  50     SELECT * FROM v1 ORDER BY a;
  51   }
  52 } {1 2 4 5 7 8}
  53 do_test view-1.3.1 {
  54   db close
  55   sqlite3 db test.db
  56   execsql {
  57     SELECT * FROM v1 ORDER BY a;
  58   }
  59 } {1 2 4 5 7 8}
  60 do_test view-1.4 {
  61   catchsql {
  62     DROP VIEW v1;
  63     SELECT * FROM v1 ORDER BY a;
  64   }
  65 } {1 {no such table: v1}}
  66 do_test view-1.5 {
  67   execsql {
  68     CREATE VIEW v1 AS SELECT a,b FROM t1;
  69     SELECT * FROM v1 ORDER BY a;
  70   }
  71 } {1 2 4 5 7 8}
  72 do_test view-1.6 {
  73   catchsql {
  74     DROP TABLE t1;
  75     SELECT * FROM v1 ORDER BY a;
  76   }
  77 } {1 {no such table: main.t1}}
  78 do_test view-1.7 {
  79   execsql {
  80     CREATE TABLE t1(x,a,b,c);
  81     INSERT INTO t1 VALUES(1,2,3,4);
  82     INSERT INTO t1 VALUES(4,5,6,7);
  83     INSERT INTO t1 VALUES(7,8,9,10);
  84     SELECT * FROM v1 ORDER BY a;
  85   }
  86 } {2 3 5 6 8 9}
  87 do_test view-1.8 {
  88   db close
  89   sqlite3 db test.db
  90   execsql {
  91     SELECT * FROM v1 ORDER BY a;
  92   }
  93 } {2 3 5 6 8 9}
  94
  95 do_test view-2.1 {
  96   execsql {
  97     CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5
  98   };  # No semicolon
  99   execsql2 {
 100     SELECT * FROM v2;
 101   }
 102 } {x 7 a 8 b 9 c 10}
 103 do_test view-2.2 {
 104   catchsql {
 105     INSERT INTO v2 VALUES(1,2,3,4);
 106   }
 107 } {1 {cannot modify v2 because it is a view}}
 108 do_test view-2.3 {
 109   catchsql {
 110     UPDATE v2 SET a=10 WHERE a=5;
 111   }
 112 } {1 {cannot modify v2 because it is a view}}
 113 do_test view-2.4 {
 114   catchsql {
 115     DELETE FROM v2;
 116   }
 117 } {1 {cannot modify v2 because it is a view}}
 118 do_test view-2.5 {
 119   execsql {
 120     INSERT INTO t1 VALUES(11,12,13,14);
 121     SELECT * FROM v2 ORDER BY x;
 122   }
 123 } {7 8 9 10 11 12 13 14}
 124 do_test view-2.6 {
 125   execsql {
 126     SELECT x FROM v2 WHERE a>10
 127   }
 128 } {11}
 129
 130 # Test that column name of views are generated correctly.
 131 #
 132 do_test view-3.1 {
 133   execsql2 {
 134     SELECT * FROM v1 LIMIT 1
 135   }
 136 } {a 2 b 3}
 137 do_test view-3.2 {
 138   execsql2 {
 139     SELECT * FROM v2 LIMIT 1
 140   }
 141 } {x 7 a 8 b 9 c 10}
 142 do_test view-3.3 {
 143   execsql2 {
 144     DROP VIEW v1;
 145     CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1;
 146     SELECT * FROM v1 LIMIT 1
 147   }
 148 } {xyz 2 pqr 7 c-b 1}
 149
 150 ifcapable compound {
 151 do_test  view-3.4 {
 152   execsql2 {
 153     CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b;
 154     SELECT * FROM v3 LIMIT 4;
 155   }
 156 } {b 2 b 3 b 5 b 6}
 157 do_test view-3.5 {
 158   execsql2 {
 159     CREATE VIEW v4 AS
 160       SELECT a, b FROM t1
 161       UNION
 162       SELECT b AS 'x', a AS 'y' FROM t1
 163       ORDER BY x, y;
 164     SELECT y FROM v4 ORDER BY y LIMIT 4;
 165   }
 166 } {y 2 y 3 y 5 y 6}
 167 } ;# ifcapable compound
 168
 169
 170 do_test view-4.1 {
 171   catchsql {
 172     DROP VIEW t1;
 173   }
 174 } {1 {use DROP TABLE to delete table t1}}
 175 do_test view-4.2 {
 176   execsql {
 177     SELECT 1 FROM t1 LIMIT 1;
 178   }
 179 } 1
 180 do_test view-4.3 {
 181   catchsql {
 182     DROP TABLE v1;
 183   }
 184 } {1 {use DROP VIEW to delete view v1}}
 185 do_test view-4.4 {
 186   execsql {
 187      SELECT 1 FROM v1 LIMIT 1;
 188   }
 189 } {1}
 190 do_test view-4.5 {
 191   catchsql {
 192     CREATE INDEX i1v1 ON v1(xyz);
 193   }
 194 } {1 {views may not be indexed}}
 195
 196 do_test view-5.1 {
 197   execsql {
 198     CREATE TABLE t2(y,a);
 199     INSERT INTO t2 VALUES(22,2);
 200     INSERT INTO t2 VALUES(33,3);
 201     INSERT INTO t2 VALUES(44,4);
 202     INSERT INTO t2 VALUES(55,5);
 203     SELECT * FROM t2;
 204   }
 205 } {22 2 33 3 44 4 55 5}
 206 do_test view-5.2 {
 207   execsql {
 208     CREATE VIEW v5 AS
 209       SELECT t1.x AS v, t2.y AS w FROM t1 JOIN t2 USING(a);
 210     SELECT * FROM v5;
 211   }
 212 } {1 22 4 55}
 213
 214 # Verify that the view v5 gets flattened.  see sqliteFlattenSubquery().
 215 # This will only work if EXPLAIN is enabled.
 216 # Ticket #272
 217 #
 218 ifcapable {explain} {
 219 do_test view-5.3 {
 220   lsearch [execsql {
 221     EXPLAIN SELECT * FROM v5;
 222   }] OpenTemp
 223 } {-1}
 224 do_test view-5.4 {
 225   execsql {
 226     SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
 227   }
 228 } {1 22 22 2 4 55 55 5}
 229 do_test view-5.5 {
 230   lsearch [execsql {
 231     EXPLAIN SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
 232   }] OpenTemp
 233 } {-1}
 234 do_test view-5.6 {
 235   execsql {
 236     SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
 237   }
 238 } {22 2 1 22 55 5 4 55}
 239 do_test view-5.7 {
 240   lsearch [execsql {
 241     EXPLAIN SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
 242   }] OpenTemp
 243 } {-1}
 244 do_test view-5.8 {
 245   execsql {
 246     SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
 247   }
 248 } {1 2 3 4 1 22 22 2 4 5 6 7 4 55 55 5}
 249 do_test view-5.9 {
 250   lsearch [execsql {
 251     EXPLAIN SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
 252   }] OpenTemp
 253 } {-1}
 254 } ;# endif explain
 255
 256 do_test view-6.1 {
 257   execsql {
 258     SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2;
 259   }
 260 } {7 8 9 10 27}
 261 do_test view-6.2 {
 262   execsql {
 263     SELECT max(x), max(a), max(b), max(c), max(a+b+c) FROM v2;
 264   }
 265 } {11 12 13 14 39}
 266
 267 do_test view-7.1 {
 268   execsql {
 269     CREATE TABLE test1(id integer primary key, a);
 270     CREATE TABLE test2(id integer, b);
 271     INSERT INTO test1 VALUES(1,2);
 272     INSERT INTO test2 VALUES(1,3);
 273     CREATE VIEW test AS
 274       SELECT test1.id, a, b
 275       FROM test1 JOIN test2 ON test2.id=test1.id;
 276     SELECT * FROM test;
 277   }
 278 } {1 2 3}
 279 do_test view-7.2 {
 280   db close
 281   sqlite3 db test.db
 282   execsql {
 283     SELECT * FROM test;
 284   }
 285 } {1 2 3}
 286 do_test view-7.3 {
 287   execsql {
 288     DROP VIEW test;
 289     CREATE VIEW test AS
 290       SELECT test1.id, a, b
 291       FROM test1 JOIN test2 USING(id);
 292     SELECT * FROM test;
 293   }
 294 } {1 2 3}
 295 do_test view-7.4 {
 296   db close
 297   sqlite3 db test.db
 298   execsql {
 299     SELECT * FROM test;
 300   }
 301 } {1 2 3}
 302 do_test view-7.5 {
 303   execsql {
 304     DROP VIEW test;
 305     CREATE VIEW test AS
 306       SELECT test1.id, a, b
 307       FROM test1 NATURAL JOIN test2;
 308     SELECT * FROM test;
 309   }
 310 } {1 2 3}
 311 do_test view-7.6 {
 312   db close
 313   sqlite3 db test.db
 314   execsql {
 315     SELECT * FROM test;
 316   }
 317 } {1 2 3}
 318
 319 do_test view-8.1 {
 320   execsql {
 321     CREATE VIEW v6 AS SELECT pqr, xyz FROM v1;
 322     SELECT * FROM v6 ORDER BY xyz;
 323   }
 324 } {7 2 13 5 19 8 27 12}
 325 do_test view-8.2 {
 326   db close
 327   sqlite3 db test.db
 328   execsql {
 329     SELECT * FROM v6 ORDER BY xyz;
 330   }
 331 } {7 2 13 5 19 8 27 12}
 332 do_test view-8.3 {
 333   execsql {
 334     CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6;
 335     SELECT * FROM v7 ORDER BY a;
 336   }
 337 } {9 18 27 39}
 338
 339 ifcapable subquery {
 340   do_test view-8.4 {
 341     execsql {
 342       CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM
 343         (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo);
 344       SELECT * FROM v8;
 345     }
 346   } 3
 347   do_test view-8.5 {
 348     execsql {
 349       SELECT mx+10, mx*2 FROM v8;
 350     }
 351   } {13 6}
 352   do_test view-8.6 {
 353     execsql {
 354       SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2;
 355     }
 356   } {13 7}
 357   do_test view-8.7 {
 358     execsql {
 359       SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2;
 360     }
 361   } {13 13 13 19 13 27}
 362 } ;# ifcapable subquery
 363
 364 # Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW.
 365 #
 366 do_test view-9.1 {
 367   execsql {
 368     INSERT INTO t2 SELECT * FROM t2 WHERE a<5;
 369     INSERT INTO t2 SELECT * FROM t2 WHERE a<4;
 370     INSERT INTO t2 SELECT * FROM t2 WHERE a<3;
 371     SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1;
 372   }
 373 } {1 2 4 8}
 374 do_test view-9.2 {
 375   execsql {
 376     SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
 377   }
 378 } {1 2 4}
 379 do_test view-9.3 {
 380   execsql {
 381     CREATE VIEW v9 AS
 382        SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
 383     SELECT * FROM v9;
 384   }
 385 } {1 2 4}
 386 do_test view-9.4 {
 387   execsql {
 388     SELECT * FROM v9 ORDER BY 1 DESC;
 389   }
 390 } {4 2 1}
 391 do_test view-9.5 {
 392   execsql {
 393     CREATE VIEW v10 AS
 394        SELECT DISTINCT a, count(*) FROM t2 GROUP BY a ORDER BY 2 LIMIT 3;
 395     SELECT * FROM v10;
 396   }
 397 } {5 1 4 2 3 4}
 398 do_test view-9.6 {
 399   execsql {
 400     SELECT * FROM v10 ORDER BY 1;
 401   }
 402 } {3 4 4 2 5 1}
 403
 404 # Tables with columns having peculiar quoted names used in views
 405 # Ticket #756.
 406 #
 407 do_test view-10.1 {
 408   execsql {
 409     CREATE TABLE t3("9" integer, [4] text);
 410     INSERT INTO t3 VALUES(1,2);
 411     CREATE VIEW v_t3_a AS SELECT a.[9] FROM t3 AS a;
 412     CREATE VIEW v_t3_b AS SELECT "4" FROM t3;
 413     SELECT * FROM v_t3_a;
 414   }
 415 } {1}
 416 do_test view-10.2 {
 417   execsql {
 418     SELECT * FROM v_t3_b;
 419   }
 420 } {2}
 421
 422 do_test view-11.1 {
 423   execsql {
 424     CREATE TABLE t4(a COLLATE NOCASE);
 425     INSERT INTO t4 VALUES('This');
 426     INSERT INTO t4 VALUES('this');
 427     INSERT INTO t4 VALUES('THIS');
 428     SELECT * FROM t4 WHERE a = 'THIS';
 429   }
 430 } {This this THIS}
 431 do_test view-11.2 {
 432   execsql {
 433     SELECT * FROM (SELECT * FROM t4) WHERE a = 'THIS';
 434   }
 435 } {This this THIS}
 436 do_test view-11.3 {
 437   execsql {
 438     CREATE VIEW v11 AS SELECT * FROM t4;
 439     SELECT * FROM v11 WHERE a = 'THIS';
 440   }
 441 } {This this THIS}
 442
 443 # Ticket #1270:  Do not allow parameters in view definitions.
 444 #
 445 do_test view-12.1 {
 446   catchsql {
 447     CREATE VIEW v12 AS SELECT a FROM t1 WHERE b=?
 448   }
 449 } {1 {parameters are not allowed in views}}
 450
 451 finish_test