1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2002 February 26 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. The 137c478bd9Sstevel@tonic-gate# focus of this file is testing VIEW statements. 147c478bd9Sstevel@tonic-gate# 157c478bd9Sstevel@tonic-gate# $Id: view.test,v 1.16.2.1 2004/07/20 00:20:47 drh Exp $ 167c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 177c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 187c478bd9Sstevel@tonic-gate 197c478bd9Sstevel@tonic-gatedo_test view-1.0 { 207c478bd9Sstevel@tonic-gate execsql { 217c478bd9Sstevel@tonic-gate CREATE TABLE t1(a,b,c); 227c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3); 237c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(4,5,6); 247c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(7,8,9); 257c478bd9Sstevel@tonic-gate SELECT * FROM t1; 267c478bd9Sstevel@tonic-gate } 277c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8 9} 287c478bd9Sstevel@tonic-gate 297c478bd9Sstevel@tonic-gatedo_test view-1.1 { 307c478bd9Sstevel@tonic-gate execsql { 317c478bd9Sstevel@tonic-gate BEGIN; 327c478bd9Sstevel@tonic-gate CREATE VIEW v1 AS SELECT a,b FROM t1; 337c478bd9Sstevel@tonic-gate SELECT * FROM v1 ORDER BY a; 347c478bd9Sstevel@tonic-gate } 357c478bd9Sstevel@tonic-gate} {1 2 4 5 7 8} 367c478bd9Sstevel@tonic-gatedo_test view-1.2 { 377c478bd9Sstevel@tonic-gate catchsql { 387c478bd9Sstevel@tonic-gate ROLLBACK; 397c478bd9Sstevel@tonic-gate SELECT * FROM v1 ORDER BY a; 407c478bd9Sstevel@tonic-gate } 417c478bd9Sstevel@tonic-gate} {1 {no such table: v1}} 427c478bd9Sstevel@tonic-gatedo_test view-1.3 { 437c478bd9Sstevel@tonic-gate execsql { 447c478bd9Sstevel@tonic-gate CREATE VIEW v1 AS SELECT a,b FROM t1; 457c478bd9Sstevel@tonic-gate SELECT * FROM v1 ORDER BY a; 467c478bd9Sstevel@tonic-gate } 477c478bd9Sstevel@tonic-gate} {1 2 4 5 7 8} 487c478bd9Sstevel@tonic-gatedo_test view-1.3.1 { 497c478bd9Sstevel@tonic-gate db close 507c478bd9Sstevel@tonic-gate sqlite db test.db 517c478bd9Sstevel@tonic-gate execsql { 527c478bd9Sstevel@tonic-gate SELECT * FROM v1 ORDER BY a; 537c478bd9Sstevel@tonic-gate } 547c478bd9Sstevel@tonic-gate} {1 2 4 5 7 8} 557c478bd9Sstevel@tonic-gatedo_test view-1.4 { 567c478bd9Sstevel@tonic-gate catchsql { 577c478bd9Sstevel@tonic-gate DROP VIEW v1; 587c478bd9Sstevel@tonic-gate SELECT * FROM v1 ORDER BY a; 597c478bd9Sstevel@tonic-gate } 607c478bd9Sstevel@tonic-gate} {1 {no such table: v1}} 617c478bd9Sstevel@tonic-gatedo_test view-1.5 { 627c478bd9Sstevel@tonic-gate execsql { 637c478bd9Sstevel@tonic-gate CREATE VIEW v1 AS SELECT a,b FROM t1; 647c478bd9Sstevel@tonic-gate SELECT * FROM v1 ORDER BY a; 657c478bd9Sstevel@tonic-gate } 667c478bd9Sstevel@tonic-gate} {1 2 4 5 7 8} 677c478bd9Sstevel@tonic-gatedo_test view-1.6 { 687c478bd9Sstevel@tonic-gate catchsql { 697c478bd9Sstevel@tonic-gate DROP TABLE t1; 707c478bd9Sstevel@tonic-gate SELECT * FROM v1 ORDER BY a; 717c478bd9Sstevel@tonic-gate } 727c478bd9Sstevel@tonic-gate} {1 {no such table: main.t1}} 737c478bd9Sstevel@tonic-gatedo_test view-1.7 { 747c478bd9Sstevel@tonic-gate execsql { 757c478bd9Sstevel@tonic-gate CREATE TABLE t1(x,a,b,c); 767c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4); 777c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(4,5,6,7); 787c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(7,8,9,10); 797c478bd9Sstevel@tonic-gate SELECT * FROM v1 ORDER BY a; 807c478bd9Sstevel@tonic-gate } 817c478bd9Sstevel@tonic-gate} {2 3 5 6 8 9} 827c478bd9Sstevel@tonic-gatedo_test view-1.8 { 837c478bd9Sstevel@tonic-gate db close 847c478bd9Sstevel@tonic-gate sqlite db test.db 857c478bd9Sstevel@tonic-gate execsql { 867c478bd9Sstevel@tonic-gate SELECT * FROM v1 ORDER BY a; 877c478bd9Sstevel@tonic-gate } 887c478bd9Sstevel@tonic-gate} {2 3 5 6 8 9} 897c478bd9Sstevel@tonic-gate 907c478bd9Sstevel@tonic-gatedo_test view-2.1 { 917c478bd9Sstevel@tonic-gate execsql { 927c478bd9Sstevel@tonic-gate CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5 937c478bd9Sstevel@tonic-gate }; # No semicolon 947c478bd9Sstevel@tonic-gate execsql2 { 957c478bd9Sstevel@tonic-gate SELECT * FROM v2; 967c478bd9Sstevel@tonic-gate } 977c478bd9Sstevel@tonic-gate} {x 7 a 8 b 9 c 10} 987c478bd9Sstevel@tonic-gatedo_test view-2.2 { 997c478bd9Sstevel@tonic-gate catchsql { 1007c478bd9Sstevel@tonic-gate INSERT INTO v2 VALUES(1,2,3,4); 1017c478bd9Sstevel@tonic-gate } 1027c478bd9Sstevel@tonic-gate} {1 {cannot modify v2 because it is a view}} 1037c478bd9Sstevel@tonic-gatedo_test view-2.3 { 1047c478bd9Sstevel@tonic-gate catchsql { 1057c478bd9Sstevel@tonic-gate UPDATE v2 SET a=10 WHERE a=5; 1067c478bd9Sstevel@tonic-gate } 1077c478bd9Sstevel@tonic-gate} {1 {cannot modify v2 because it is a view}} 1087c478bd9Sstevel@tonic-gatedo_test view-2.4 { 1097c478bd9Sstevel@tonic-gate catchsql { 1107c478bd9Sstevel@tonic-gate DELETE FROM v2; 1117c478bd9Sstevel@tonic-gate } 1127c478bd9Sstevel@tonic-gate} {1 {cannot modify v2 because it is a view}} 1137c478bd9Sstevel@tonic-gatedo_test view-2.5 { 1147c478bd9Sstevel@tonic-gate execsql { 1157c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(11,12,13,14); 1167c478bd9Sstevel@tonic-gate SELECT * FROM v2 ORDER BY x; 1177c478bd9Sstevel@tonic-gate } 1187c478bd9Sstevel@tonic-gate} {7 8 9 10 11 12 13 14} 1197c478bd9Sstevel@tonic-gatedo_test view-2.6 { 1207c478bd9Sstevel@tonic-gate execsql { 1217c478bd9Sstevel@tonic-gate SELECT x FROM v2 WHERE a>10 1227c478bd9Sstevel@tonic-gate } 1237c478bd9Sstevel@tonic-gate} {11} 1247c478bd9Sstevel@tonic-gate 1257c478bd9Sstevel@tonic-gate# Test that column name of views are generated correctly. 1267c478bd9Sstevel@tonic-gate# 1277c478bd9Sstevel@tonic-gatedo_test view-3.1 { 1287c478bd9Sstevel@tonic-gate execsql2 { 1297c478bd9Sstevel@tonic-gate SELECT * FROM v1 LIMIT 1 1307c478bd9Sstevel@tonic-gate } 1317c478bd9Sstevel@tonic-gate} {a 2 b 3} 1327c478bd9Sstevel@tonic-gatedo_test view-3.2 { 1337c478bd9Sstevel@tonic-gate execsql2 { 1347c478bd9Sstevel@tonic-gate SELECT * FROM v2 LIMIT 1 1357c478bd9Sstevel@tonic-gate } 1367c478bd9Sstevel@tonic-gate} {x 7 a 8 b 9 c 10} 1377c478bd9Sstevel@tonic-gatedo_test view-3.3 { 1387c478bd9Sstevel@tonic-gate execsql2 { 1397c478bd9Sstevel@tonic-gate DROP VIEW v1; 1407c478bd9Sstevel@tonic-gate CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1; 1417c478bd9Sstevel@tonic-gate SELECT * FROM v1 LIMIT 1 1427c478bd9Sstevel@tonic-gate } 1437c478bd9Sstevel@tonic-gate} {xyz 2 pqr 7 c-b 1} 1447c478bd9Sstevel@tonic-gatedo_test view-3.4 { 1457c478bd9Sstevel@tonic-gate execsql2 { 1467c478bd9Sstevel@tonic-gate CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b; 1477c478bd9Sstevel@tonic-gate SELECT * FROM v3 LIMIT 4; 1487c478bd9Sstevel@tonic-gate } 1497c478bd9Sstevel@tonic-gate} {b 2 b 3 b 5 b 6} 1507c478bd9Sstevel@tonic-gatedo_test view-3.5 { 1517c478bd9Sstevel@tonic-gate execsql2 { 152*1da57d55SToomas Soome CREATE VIEW v4 AS 153*1da57d55SToomas Soome SELECT a, b FROM t1 1547c478bd9Sstevel@tonic-gate UNION 1557c478bd9Sstevel@tonic-gate SELECT b AS 'x', a AS 'y' FROM t1 1567c478bd9Sstevel@tonic-gate ORDER BY x, y; 1577c478bd9Sstevel@tonic-gate SELECT y FROM v4 ORDER BY y LIMIT 4; 1587c478bd9Sstevel@tonic-gate } 1597c478bd9Sstevel@tonic-gate} {y 2 y 3 y 5 y 6} 1607c478bd9Sstevel@tonic-gate 1617c478bd9Sstevel@tonic-gate 1627c478bd9Sstevel@tonic-gatedo_test view-4.1 { 1637c478bd9Sstevel@tonic-gate catchsql { 1647c478bd9Sstevel@tonic-gate DROP VIEW t1; 1657c478bd9Sstevel@tonic-gate } 1667c478bd9Sstevel@tonic-gate} {1 {use DROP TABLE to delete table t1}} 1677c478bd9Sstevel@tonic-gatedo_test view-4.2 { 1687c478bd9Sstevel@tonic-gate execsql { 1697c478bd9Sstevel@tonic-gate SELECT 1 FROM t1 LIMIT 1; 1707c478bd9Sstevel@tonic-gate } 1717c478bd9Sstevel@tonic-gate} 1 1727c478bd9Sstevel@tonic-gatedo_test view-4.3 { 1737c478bd9Sstevel@tonic-gate catchsql { 1747c478bd9Sstevel@tonic-gate DROP TABLE v1; 1757c478bd9Sstevel@tonic-gate } 1767c478bd9Sstevel@tonic-gate} {1 {use DROP VIEW to delete view v1}} 1777c478bd9Sstevel@tonic-gatedo_test view-4.4 { 1787c478bd9Sstevel@tonic-gate execsql { 1797c478bd9Sstevel@tonic-gate SELECT 1 FROM v1 LIMIT 1; 1807c478bd9Sstevel@tonic-gate } 1817c478bd9Sstevel@tonic-gate} {1} 1827c478bd9Sstevel@tonic-gatedo_test view-4.5 { 1837c478bd9Sstevel@tonic-gate catchsql { 1847c478bd9Sstevel@tonic-gate CREATE INDEX i1v1 ON v1(xyz); 1857c478bd9Sstevel@tonic-gate } 1867c478bd9Sstevel@tonic-gate} {1 {views may not be indexed}} 1877c478bd9Sstevel@tonic-gate 1887c478bd9Sstevel@tonic-gatedo_test view-5.1 { 1897c478bd9Sstevel@tonic-gate execsql { 1907c478bd9Sstevel@tonic-gate CREATE TABLE t2(y,a); 1917c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(22,2); 1927c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(33,3); 1937c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(44,4); 1947c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(55,5); 1957c478bd9Sstevel@tonic-gate SELECT * FROM t2; 1967c478bd9Sstevel@tonic-gate } 1977c478bd9Sstevel@tonic-gate} {22 2 33 3 44 4 55 5} 1987c478bd9Sstevel@tonic-gatedo_test view-5.2 { 1997c478bd9Sstevel@tonic-gate execsql { 2007c478bd9Sstevel@tonic-gate CREATE VIEW v5 AS 2017c478bd9Sstevel@tonic-gate SELECT t1.x AS v, t2.y AS w FROM t1 JOIN t2 USING(a); 2027c478bd9Sstevel@tonic-gate SELECT * FROM v5; 2037c478bd9Sstevel@tonic-gate } 2047c478bd9Sstevel@tonic-gate} {1 22 4 55} 2057c478bd9Sstevel@tonic-gate 2067c478bd9Sstevel@tonic-gate# Verify that the view v5 gets flattened. see sqliteFlattenSubquery(). 2077c478bd9Sstevel@tonic-gate# Ticket #272 2087c478bd9Sstevel@tonic-gatedo_test view-5.3 { 2097c478bd9Sstevel@tonic-gate lsearch [execsql { 2107c478bd9Sstevel@tonic-gate EXPLAIN SELECT * FROM v5; 2117c478bd9Sstevel@tonic-gate }] OpenTemp 2127c478bd9Sstevel@tonic-gate} {-1} 2137c478bd9Sstevel@tonic-gatedo_test view-5.4 { 2147c478bd9Sstevel@tonic-gate execsql { 2157c478bd9Sstevel@tonic-gate SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y; 2167c478bd9Sstevel@tonic-gate } 2177c478bd9Sstevel@tonic-gate} {1 22 22 2 4 55 55 5} 2187c478bd9Sstevel@tonic-gatedo_test view-5.5 { 2197c478bd9Sstevel@tonic-gate lsearch [execsql { 2207c478bd9Sstevel@tonic-gate EXPLAIN SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y; 2217c478bd9Sstevel@tonic-gate }] OpenTemp 2227c478bd9Sstevel@tonic-gate} {-1} 2237c478bd9Sstevel@tonic-gatedo_test view-5.6 { 2247c478bd9Sstevel@tonic-gate execsql { 2257c478bd9Sstevel@tonic-gate SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y; 2267c478bd9Sstevel@tonic-gate } 2277c478bd9Sstevel@tonic-gate} {22 2 1 22 55 5 4 55} 2287c478bd9Sstevel@tonic-gatedo_test view-5.7 { 2297c478bd9Sstevel@tonic-gate lsearch [execsql { 2307c478bd9Sstevel@tonic-gate EXPLAIN SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y; 2317c478bd9Sstevel@tonic-gate }] OpenTemp 2327c478bd9Sstevel@tonic-gate} {-1} 2337c478bd9Sstevel@tonic-gatedo_test view-5.8 { 2347c478bd9Sstevel@tonic-gate execsql { 2357c478bd9Sstevel@tonic-gate SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y; 2367c478bd9Sstevel@tonic-gate } 2377c478bd9Sstevel@tonic-gate} {1 2 3 4 1 22 22 2 4 5 6 7 4 55 55 5} 2387c478bd9Sstevel@tonic-gatedo_test view-5.9 { 2397c478bd9Sstevel@tonic-gate lsearch [execsql { 2407c478bd9Sstevel@tonic-gate EXPLAIN SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y; 2417c478bd9Sstevel@tonic-gate }] OpenTemp 2427c478bd9Sstevel@tonic-gate} {-1} 2437c478bd9Sstevel@tonic-gate 2447c478bd9Sstevel@tonic-gatedo_test view-6.1 { 2457c478bd9Sstevel@tonic-gate execsql { 2467c478bd9Sstevel@tonic-gate SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2; 2477c478bd9Sstevel@tonic-gate } 2487c478bd9Sstevel@tonic-gate} {7 8 9 10 27} 2497c478bd9Sstevel@tonic-gatedo_test view-6.2 { 2507c478bd9Sstevel@tonic-gate execsql { 2517c478bd9Sstevel@tonic-gate SELECT max(x), max(a), max(b), max(c), max(a+b+c) FROM v2; 2527c478bd9Sstevel@tonic-gate } 2537c478bd9Sstevel@tonic-gate} {11 12 13 14 39} 2547c478bd9Sstevel@tonic-gate 2557c478bd9Sstevel@tonic-gatedo_test view-7.1 { 2567c478bd9Sstevel@tonic-gate execsql { 2577c478bd9Sstevel@tonic-gate CREATE TABLE test1(id integer primary key, a); 2587c478bd9Sstevel@tonic-gate CREATE TABLE test2(id integer, b); 2597c478bd9Sstevel@tonic-gate INSERT INTO test1 VALUES(1,2); 2607c478bd9Sstevel@tonic-gate INSERT INTO test2 VALUES(1,3); 2617c478bd9Sstevel@tonic-gate CREATE VIEW test AS 2627c478bd9Sstevel@tonic-gate SELECT test1.id, a, b 2637c478bd9Sstevel@tonic-gate FROM test1 JOIN test2 ON test2.id=test1.id; 2647c478bd9Sstevel@tonic-gate SELECT * FROM test; 2657c478bd9Sstevel@tonic-gate } 2667c478bd9Sstevel@tonic-gate} {1 2 3} 2677c478bd9Sstevel@tonic-gatedo_test view-7.2 { 2687c478bd9Sstevel@tonic-gate db close 2697c478bd9Sstevel@tonic-gate sqlite db test.db 2707c478bd9Sstevel@tonic-gate execsql { 2717c478bd9Sstevel@tonic-gate SELECT * FROM test; 2727c478bd9Sstevel@tonic-gate } 2737c478bd9Sstevel@tonic-gate} {1 2 3} 2747c478bd9Sstevel@tonic-gatedo_test view-7.3 { 2757c478bd9Sstevel@tonic-gate execsql { 2767c478bd9Sstevel@tonic-gate DROP VIEW test; 2777c478bd9Sstevel@tonic-gate CREATE VIEW test AS 2787c478bd9Sstevel@tonic-gate SELECT test1.id, a, b 2797c478bd9Sstevel@tonic-gate FROM test1 JOIN test2 USING(id); 2807c478bd9Sstevel@tonic-gate SELECT * FROM test; 2817c478bd9Sstevel@tonic-gate } 2827c478bd9Sstevel@tonic-gate} {1 2 3} 2837c478bd9Sstevel@tonic-gatedo_test view-7.4 { 2847c478bd9Sstevel@tonic-gate db close 2857c478bd9Sstevel@tonic-gate sqlite db test.db 2867c478bd9Sstevel@tonic-gate execsql { 2877c478bd9Sstevel@tonic-gate SELECT * FROM test; 2887c478bd9Sstevel@tonic-gate } 2897c478bd9Sstevel@tonic-gate} {1 2 3} 2907c478bd9Sstevel@tonic-gatedo_test view-7.5 { 2917c478bd9Sstevel@tonic-gate execsql { 2927c478bd9Sstevel@tonic-gate DROP VIEW test; 2937c478bd9Sstevel@tonic-gate CREATE VIEW test AS 2947c478bd9Sstevel@tonic-gate SELECT test1.id, a, b 2957c478bd9Sstevel@tonic-gate FROM test1 NATURAL JOIN test2; 2967c478bd9Sstevel@tonic-gate SELECT * FROM test; 2977c478bd9Sstevel@tonic-gate } 2987c478bd9Sstevel@tonic-gate} {1 2 3} 2997c478bd9Sstevel@tonic-gatedo_test view-7.6 { 3007c478bd9Sstevel@tonic-gate db close 3017c478bd9Sstevel@tonic-gate sqlite db test.db 3027c478bd9Sstevel@tonic-gate execsql { 3037c478bd9Sstevel@tonic-gate SELECT * FROM test; 3047c478bd9Sstevel@tonic-gate } 3057c478bd9Sstevel@tonic-gate} {1 2 3} 3067c478bd9Sstevel@tonic-gate 3077c478bd9Sstevel@tonic-gatedo_test view-8.1 { 3087c478bd9Sstevel@tonic-gate execsql { 3097c478bd9Sstevel@tonic-gate CREATE VIEW v6 AS SELECT pqr, xyz FROM v1; 3107c478bd9Sstevel@tonic-gate SELECT * FROM v6 ORDER BY xyz; 3117c478bd9Sstevel@tonic-gate } 3127c478bd9Sstevel@tonic-gate} {7 2 13 5 19 8 27 12} 3137c478bd9Sstevel@tonic-gatedo_test view-8.2 { 3147c478bd9Sstevel@tonic-gate db close 3157c478bd9Sstevel@tonic-gate sqlite db test.db 3167c478bd9Sstevel@tonic-gate execsql { 3177c478bd9Sstevel@tonic-gate SELECT * FROM v6 ORDER BY xyz; 3187c478bd9Sstevel@tonic-gate } 3197c478bd9Sstevel@tonic-gate} {7 2 13 5 19 8 27 12} 3207c478bd9Sstevel@tonic-gatedo_test view-8.3 { 3217c478bd9Sstevel@tonic-gate execsql { 3227c478bd9Sstevel@tonic-gate CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6; 3237c478bd9Sstevel@tonic-gate SELECT * FROM v7 ORDER BY a; 3247c478bd9Sstevel@tonic-gate } 3257c478bd9Sstevel@tonic-gate} {9 18 27 39} 3267c478bd9Sstevel@tonic-gatedo_test view-8.4 { 3277c478bd9Sstevel@tonic-gate execsql { 3287c478bd9Sstevel@tonic-gate CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM 3297c478bd9Sstevel@tonic-gate (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo); 3307c478bd9Sstevel@tonic-gate SELECT * FROM v8; 3317c478bd9Sstevel@tonic-gate } 3327c478bd9Sstevel@tonic-gate} 3 3337c478bd9Sstevel@tonic-gatedo_test view-8.5 { 3347c478bd9Sstevel@tonic-gate execsql { 3357c478bd9Sstevel@tonic-gate SELECT mx+10, mx*2 FROM v8; 3367c478bd9Sstevel@tonic-gate } 3377c478bd9Sstevel@tonic-gate} {13 6} 3387c478bd9Sstevel@tonic-gatedo_test view-8.6 { 3397c478bd9Sstevel@tonic-gate execsql { 3407c478bd9Sstevel@tonic-gate SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2; 3417c478bd9Sstevel@tonic-gate } 3427c478bd9Sstevel@tonic-gate} {13 7} 3437c478bd9Sstevel@tonic-gatedo_test view-8.7 { 3447c478bd9Sstevel@tonic-gate execsql { 3457c478bd9Sstevel@tonic-gate SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2; 3467c478bd9Sstevel@tonic-gate } 3477c478bd9Sstevel@tonic-gate} {13 13 13 19 13 27} 3487c478bd9Sstevel@tonic-gate 3497c478bd9Sstevel@tonic-gate# Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW. 3507c478bd9Sstevel@tonic-gate# 3517c478bd9Sstevel@tonic-gatedo_test view-9.1 { 3527c478bd9Sstevel@tonic-gate execsql { 3537c478bd9Sstevel@tonic-gate INSERT INTO t2 SELECT * FROM t2 WHERE a<5; 3547c478bd9Sstevel@tonic-gate INSERT INTO t2 SELECT * FROM t2 WHERE a<4; 3557c478bd9Sstevel@tonic-gate INSERT INTO t2 SELECT * FROM t2 WHERE a<3; 3567c478bd9Sstevel@tonic-gate SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1; 3577c478bd9Sstevel@tonic-gate } 3587c478bd9Sstevel@tonic-gate} {1 2 4 8} 3597c478bd9Sstevel@tonic-gatedo_test view-9.2 { 3607c478bd9Sstevel@tonic-gate execsql { 3617c478bd9Sstevel@tonic-gate SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3; 3627c478bd9Sstevel@tonic-gate } 3637c478bd9Sstevel@tonic-gate} {1 2 4} 3647c478bd9Sstevel@tonic-gatedo_test view-9.3 { 3657c478bd9Sstevel@tonic-gate execsql { 366*1da57d55SToomas Soome CREATE VIEW v9 AS 3677c478bd9Sstevel@tonic-gate SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3; 3687c478bd9Sstevel@tonic-gate SELECT * FROM v9; 3697c478bd9Sstevel@tonic-gate } 3707c478bd9Sstevel@tonic-gate} {1 2 4} 3717c478bd9Sstevel@tonic-gatedo_test view-9.4 { 3727c478bd9Sstevel@tonic-gate execsql { 3737c478bd9Sstevel@tonic-gate SELECT * FROM v9 ORDER BY 1 DESC; 3747c478bd9Sstevel@tonic-gate } 3757c478bd9Sstevel@tonic-gate} {4 2 1} 3767c478bd9Sstevel@tonic-gatedo_test view-9.5 { 3777c478bd9Sstevel@tonic-gate execsql { 378*1da57d55SToomas Soome CREATE VIEW v10 AS 3797c478bd9Sstevel@tonic-gate SELECT DISTINCT a, count(*) FROM t2 GROUP BY a ORDER BY 2 LIMIT 3; 3807c478bd9Sstevel@tonic-gate SELECT * FROM v10; 3817c478bd9Sstevel@tonic-gate } 3827c478bd9Sstevel@tonic-gate} {5 1 4 2 3 4} 3837c478bd9Sstevel@tonic-gatedo_test view-9.6 { 3847c478bd9Sstevel@tonic-gate execsql { 3857c478bd9Sstevel@tonic-gate SELECT * FROM v10 ORDER BY 1; 3867c478bd9Sstevel@tonic-gate } 3877c478bd9Sstevel@tonic-gate} {3 4 4 2 5 1} 3887c478bd9Sstevel@tonic-gate 3897c478bd9Sstevel@tonic-gate# Tables with columns having peculiar quoted names used in views 3907c478bd9Sstevel@tonic-gate# Ticket #756. 3917c478bd9Sstevel@tonic-gate# 3927c478bd9Sstevel@tonic-gatedo_test view-10.1 { 3937c478bd9Sstevel@tonic-gate execsql { 3947c478bd9Sstevel@tonic-gate CREATE TABLE t3("9" integer, [4] text); 3957c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(1,2); 3967c478bd9Sstevel@tonic-gate CREATE VIEW v_t3_a AS SELECT a.[9] FROM t3 AS a; 3977c478bd9Sstevel@tonic-gate CREATE VIEW v_t3_b AS SELECT "4" FROM t3; 3987c478bd9Sstevel@tonic-gate SELECT * FROM v_t3_a; 3997c478bd9Sstevel@tonic-gate } 4007c478bd9Sstevel@tonic-gate} {1} 4017c478bd9Sstevel@tonic-gatedo_test view-10.2 { 4027c478bd9Sstevel@tonic-gate execsql { 4037c478bd9Sstevel@tonic-gate SELECT * FROM v_t3_b; 4047c478bd9Sstevel@tonic-gate } 4057c478bd9Sstevel@tonic-gate} {2} 4067c478bd9Sstevel@tonic-gate 4077c478bd9Sstevel@tonic-gate 4087c478bd9Sstevel@tonic-gatefinish_test 409