|
1 # 2008 August 12 |
|
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 # This file tests changes to the name resolution logic that occurred |
|
13 # in august of 2008 and where associated with tickets #3298 and #3301 |
|
14 # |
|
15 # $Id: tkt3298.test,v 1.2 2008/08/22 12:46:46 drh Exp $ |
|
16 |
|
17 set testdir [file dirname $argv0] |
|
18 source $testdir/tester.tcl |
|
19 |
|
20 do_test tkt3298-1.1 { |
|
21 execsql { |
|
22 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT); |
|
23 INSERT INTO t1 VALUES(0, 1); |
|
24 INSERT INTO t1 VALUES(1, 1); |
|
25 INSERT INTO t1 VALUES(2, 1); |
|
26 CREATE VIEW v1 AS SELECT a AS x, b+1 AS y FROM t1; |
|
27 CREATE TRIGGER r1 INSTEAD OF UPDATE ON v1 |
|
28 BEGIN |
|
29 UPDATE t1 SET b=new.y-1 WHERE a=new.x; |
|
30 END; |
|
31 CREATE TRIGGER r2 INSTEAD OF DELETE ON v1 |
|
32 BEGIN |
|
33 DELETE FROM t1 WHERE a=old.x; |
|
34 END; |
|
35 SELECT * FROM v1 ORDER BY x; |
|
36 } |
|
37 } {0 2 1 2 2 2} |
|
38 do_test tkt3298-1.2 { |
|
39 execsql { |
|
40 UPDATE v1 SET y=3 WHERE x=0; |
|
41 SELECT * FROM v1 ORDER by x; |
|
42 } |
|
43 } {0 3 1 2 2 2} |
|
44 do_test tkt3298-1.3 { |
|
45 execsql { |
|
46 UPDATE v1 SET y=4 WHERE v1.x=2; |
|
47 SELECT * FROM v1 ORDER by x; |
|
48 } |
|
49 } {0 3 1 2 2 4} |
|
50 do_test tkt3298-1.4 { |
|
51 execsql { |
|
52 DELETE FROM v1 WHERE x=1; |
|
53 SELECT * FROM v1 ORDER BY x; |
|
54 } |
|
55 } {0 3 2 4} |
|
56 do_test tkt3298-1.5 { |
|
57 execsql { |
|
58 DELETE FROM v1 WHERE v1.x=2; |
|
59 SELECT * FROM v1 ORDER BY x; |
|
60 } |
|
61 } {0 3} |
|
62 |
|
63 # Ticket #3301 |
|
64 # |
|
65 do_test tkt3298-2.1 { |
|
66 execsql { |
|
67 CREATE TABLE t2(p,q); |
|
68 INSERT INTO t2 VALUES(1,11); |
|
69 INSERT INTO t2 VALUES(2,22); |
|
70 CREATE TABLE t3(x,y); |
|
71 INSERT INTO t3 VALUES(1,'one'); |
|
72 |
|
73 SELECT *, (SELECT z FROM (SELECT y AS z FROM t3 WHERE x=t1.a+1) ) FROM t1; |
|
74 } |
|
75 } {0 2 one} |
|
76 |
|
77 |
|
78 finish_test |