Here it goes. A more simplified version:
--
-- Table updated by the trigger TGR_InventoryControl_AfterInsert
--
CREATE TABLE InventoryControl (
InventoryControlId INTEGER PRIMARY KEY AUTOINCREMENT,
SKU INTEGER NOT NULL,
Variant INTEGER NOT NULL DEFAULT 0,
ControlDate DATE NOT NULL,
ControlState INTEGER NOT NULL DEFAULT -1,
DeliveredQty VARCHAR(30)
);
--
-- Trigger fired by the test case
-- (It was simplified from the original data model)
--
CREATE TRIGGER TGR_InventoryControl_AfterInsert
AFTER INSERT ON InventoryControl
FOR EACH ROW WHEN NEW.ControlState=-1
BEGIN
INSERT OR REPLACE INTO
InventoryControl
(InventoryControlId,SKU,Variant,ControlDate,ControlState,DeliveredQty)
SELECT
T1.InventoryControlId AS InventoryControlId,
T1.SKU AS SKU,
T1.Variant AS Variant,
T1.ControlDate AS ControlDate,
1 AS ControlState,
COALESCE(T2.DeliveredQty,0) AS DeliveredQty
FROM (
SELECT
NEW.InventoryControlId AS InventoryControlId,
II.SKU AS SKU,
II.Variant AS Variant,
COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS
ControlDate
FROM
InventoryItem II
--
-- We noticed that this LEFT JOIN is one of causes:
-- If we modify the "IN (SELECT 99999)" to "IN (99999)" the
problem is solved.
--
LEFT JOIN
InventoryControl LastClosedIC
ON LastClosedIC.InventoryControlId IN ( SELECT 99999 )
WHERE
II.SKU=NEW.SKU AND
II.Variant=NEW.Variant
) T1
LEFT JOIN (
SELECT
TD.SKU AS SKU,
TD.Variant AS Variant,
10 AS DeliveredQty
FROM
TransactionDetail TD
WHERE
TD.SKU=NEW.SKU AND
TD.Variant=NEW.Variant
) T2
--
-- This is the main problem: The LEFT JOIN never matches the T1
record.
-- NOTE: if we change the ON clause to the following (just add
a '+' sign):
-- ON +T2.SKU=T1.SKU AND
-- +T2.Variant=T1.Variant;
-- the problem is solved.
--
ON T2.SKU=T1.SKU AND
T2.Variant=T1.Variant;
END;
--
-- Addtional table took from the original model necessary for the test
case.
--
CREATE TABLE InventoryItem (
SKU INTEGER NOT NULL,
Variant INTEGER NOT NULL DEFAULT 0,
DeptCode INTEGER NOT NULL,
GroupCode INTEGER NOT NULL,
ItemDescription VARCHAR(120) NOT NULL,
PRIMARY KEY(SKU, Variant)
);
--
-- Additional note: The test case works only with the record with SKU
== 31. But, if we leave only the records
-- related to it, the problem does not happen. It's
needed to have records before and after it
-- (SKUs 220 and 72 in this case).
--
INSERT INTO InventoryItem VALUES(220,0,1,170,'Scoth Tampon Recurer');
INSERT INTO InventoryItem VALUES(31,0,1,110,'Fromage');
--
-- Addtional table took from the original model necessary for the test
case.
--
CREATE TABLE TransactionDetail (
TransactionId INTEGER NOT NULL,
SKU INTEGER NOT NULL,
Variant INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(TransactionId, SKU, Variant)
);
INSERT INTO TransactionDetail(TransactionId, SKU, Variant) VALUES(44,
31, 0);
-- **********************************************************
-- THE TEST CASE
-- **********************************************************
-- This is the INSERT statement which will fire the
-- TGR_InventoryControl_AfterInsert trigger.
-- NOTE: The INSERT statement must have a SELECT to reproduce
-- the problem.
INSERT INTO InventoryControl (SKU, Variant, ControlDate)
SELECT
II.SKU AS SKU,
II.Variant AS Variant,
'2011-08-30' AS ControlDate
FROM
InventoryItem II;
--
-- Expected RESULT: 31|10
-- Problematic RESULT: 31|0
--
SELECT 'Expected result: 31|10';
SELECT 'Problematic result: 31|0';
SELECT SKU, DeliveredQty FROM InventoryControl WHERE SKU=31;
SELECT CASE WHEN DeliveredQty=10 THEN "TEST PASSED!" ELSE "TEST
FAILED!" END FROM InventoryControl WHERE SKU=31;
On 08/12/2011, at 15:40, Black, Michael (IS) wrote:
Your test_case.sql didn't come thru.
Can you report it in-ilne with an email?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-
boun...@sqlite.org] on behalf of Alessandro Merolli [amero...@mwneo.com
]
Sent: Thursday, December 08, 2011 11:25 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Strange behavior on SQLite 3.7x compared with
3.6.22
Hi,
We've being working with SQLite version 3.6.22 in our project
and we
wish to upgrade it to the latest one. During the tests with the new
library version, we noticed a strange behavior related to a trigger
which updates the last inserted row. We where able to simplify the
data model and produce a test case which can be executed using the
sqlite3 shell. Here are the outputs:
Execution with latest SQLite 3.7.9:
c:\>sqlite3shell.exe -init .\test_case.sql ":memory:"
-- Loading resources from .\test_case.sql
Expected result: 31|10
Problematic result: 31|0
31|0
SQLite version 3.7.9
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit
c:\>
Execution with latest SQLite 3.6.22:
c:\>sqlite3shell.exe -init .\test_case.sql ":memory:"
-- Loading resources from .\test_case.sql
Expected result: 31|10
Problematic result: 31|0
31|10
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit
We are using the amalgamation version of the source for both libraries
and here are the compilation defines:
TEMP_STORE=3
SQLITE_THREADSAFE=2
SQLITE_DEFAULT_CACHE_SIZE=65568
SQLITE_DEFAULT_TEMP_CACHE_SIZE=65568
SQLITE_MAX_ATTACHED=30
SQLITE_ENABLE_COLUMN_METADATA
SQLITE_ENABLE_UNLOCK_NOTIFY
The error was also reproduced on the original library available for
Ubuntu 10.10.
Additional information is available in the test_case.sql script
comments.
Thanks for the assistance.
Regards,
Alessandro Merolli.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
