xref: /sqlite-3.40.0/test/tkt-7bbfb7d442.test (revision 7d44b22d) 
1# 2011 December 9
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# This file implements tests to verify that ticket [7bbfb7d442] has been
14# fixed.
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19set testprefix tkt-7bbfb7d442
20
21do_execsql_test 1.1 {
22  CREATE TABLE t1(a, b);
23  INSERT INTO t1 VALUES(1, 'one');
24  INSERT INTO t1 VALUES(2, 'two');
25  INSERT INTO t1 VALUES(3, 'three');
26
27  CREATE TABLE t2(c, d);
28  INSERT INTO t2 VALUES('one', 'I');
29  INSERT INTO t2 VALUES('two', 'II');
30  INSERT INTO t2 VALUES('three', 'III');
31
32  CREATE TABLE t3(t3_a PRIMARY KEY, t3_d);
33  CREATE TRIGGER t3t AFTER INSERT ON t3 WHEN new.t3_d IS NULL BEGIN
34    UPDATE t3 SET t3_d = (
35      SELECT d FROM
36        (SELECT * FROM t2 WHERE (new.t3_a%2)=(rowid%2) LIMIT 10),
37        (SELECT * FROM t1 WHERE (new.t3_a%2)=(rowid%2) LIMIT 10)
38      WHERE a = new.t3_a AND b = c
39    ) WHERE t3_a = new.t3_a;
40  END;
41}
42
43do_execsql_test 1.2 {
44  INSERT INTO t3(t3_a) VALUES(1);
45  INSERT INTO t3(t3_a) VALUES(2);
46  INSERT INTO t3(t3_a) VALUES(3);
47  SELECT * FROM t3;
48} {1 I 2 II 3 III}
49
50do_execsql_test 1.3 { DELETE FROM t3 }
51
52ifcapable compound {
53  do_execsql_test 1.4 {
54    INSERT INTO t3(t3_a) SELECT 1 UNION SELECT 2 UNION SELECT 3;
55    SELECT * FROM t3;
56  } {1 I 2 II 3 III}
57}
58
59
60
61#-------------------------------------------------------------------------
62# The following test case - 2.* - is from the original bug report as
63# posted to the mailing list.
64#
65do_execsql_test 2.1 {
66  CREATE TABLE InventoryControl (
67    InventoryControlId INTEGER PRIMARY KEY AUTOINCREMENT,
68    SKU INTEGER NOT NULL,
69    Variant INTEGER NOT NULL DEFAULT 0,
70    ControlDate DATE NOT NULL,
71    ControlState INTEGER NOT NULL DEFAULT -1,
72    DeliveredQty VARCHAR(30)
73  );
74
75  CREATE TRIGGER TGR_InventoryControl_AfterInsert
76  AFTER INSERT ON InventoryControl
77  FOR EACH ROW WHEN NEW.ControlState=-1 BEGIN
78
79  INSERT OR REPLACE INTO InventoryControl(
80        InventoryControlId,SKU,Variant,ControlDate,ControlState,DeliveredQty
81  ) SELECT
82          T1.InventoryControlId AS InventoryControlId,
83          T1.SKU AS SKU,
84          T1.Variant AS Variant,
85          T1.ControlDate AS ControlDate,
86          1 AS ControlState,
87          COALESCE(T2.DeliveredQty,0) AS DeliveredQty
88      FROM (
89          SELECT
90              NEW.InventoryControlId AS InventoryControlId,
91              II.SKU AS SKU,
92              II.Variant AS Variant,
93              COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS ControlDate
94          FROM
95              InventoryItem II
96          LEFT JOIN
97              InventoryControl LastClosedIC
98              ON  LastClosedIC.InventoryControlId IN ( SELECT 99999 )
99          WHERE
100              II.SKU=NEW.SKU AND
101              II.Variant=NEW.Variant
102      )   T1
103      LEFT JOIN (
104          SELECT
105              TD.SKU AS SKU,
106              TD.Variant AS Variant,
107              10 AS DeliveredQty
108          FROM
109              TransactionDetail TD
110          WHERE
111              TD.SKU=NEW.SKU AND
112              TD.Variant=NEW.Variant
113      )   T2
114      ON  T2.SKU=T1.SKU AND
115          T2.Variant=T1.Variant;
116  END;
117
118  CREATE TABLE InventoryItem (
119    SKU INTEGER NOT NULL,
120    Variant INTEGER NOT NULL DEFAULT 0,
121    DeptCode INTEGER NOT NULL,
122    GroupCode INTEGER NOT NULL,
123    ItemDescription VARCHAR(120) NOT NULL,
124    PRIMARY KEY(SKU, Variant)
125  );
126
127  INSERT INTO InventoryItem VALUES(220,0,1,170,'Scoth Tampon Recurer');
128  INSERT INTO InventoryItem VALUES(31,0,1,110,'Fromage');
129
130  CREATE TABLE TransactionDetail (
131    TransactionId INTEGER NOT NULL,
132    SKU INTEGER NOT NULL,
133    Variant INTEGER NOT NULL DEFAULT 0,
134    PRIMARY KEY(TransactionId, SKU, Variant)
135  );
136  INSERT INTO TransactionDetail(TransactionId, SKU, Variant) VALUES(44, 31, 0);
137
138
139  INSERT INTO InventoryControl(SKU, Variant, ControlDate) SELECT
140      II.SKU AS SKU, II.Variant AS Variant, '2011-08-30' AS ControlDate
141      FROM InventoryItem II;
142}
143
144do_execsql_test 2.2 {
145  SELECT SKU, DeliveredQty FROM InventoryControl WHERE SKU=31
146} {31 10}
147
148do_execsql_test 2.3 {
149  SELECT CASE WHEN DeliveredQty=10 THEN 'TEST PASSED!' ELSE 'TEST FAILED!' END
150  FROM InventoryControl WHERE SKU=31;
151} {{TEST PASSED!}}
152
153
154finish_test
155