I have an internal stocks management application that I rebuild from
time to time, usually against the latest SQLite version. After moving to
SQLite 3.9.1 it started reporting crazy stock values. Previously it was
compiled with SQLite 3.8.8.3. Further investigation showed that the last
usable SQLite version is 3.8.10.2. The application is compiled with
Visual Studio 2013 Express.
The SQL statement creating the database and the temporary views is:
*****
PRAGMA case_sensitive_like = ON;
PRAGMA foreign_keys = ON;
VACUUM;
CREATE TABLE IF NOT EXISTS Categories(CatID INTEGER PRIMARY KEY
AUTOINCREMENT,
Name TEXT);
CREATE TABLE IF NOT EXISTS Components(CompID INTEGER PRIMARY KEY
AUTOINCREMENT,
CatID INTEGER REFERENCES
Categories(CatID),
Name TEXT);
CREATE TABLE IF NOT EXISTS Suppliers(SupID INTEGER PRIMARY KEY
AUTOINCREMENT,
Name TEXT);
CREATE TABLE IF NOT EXISTS Codes(CodeID INTEGER PRIMARY KEY AUTOINCREMENT,
CompID INTEGER REFERENCES
Components(CompID),
SupID INTEGER REFERENCES Suppliers(SupID),
Name TEXT);
CREATE TABLE IF NOT EXISTS Inputs(InvID INTEGER PRIMARY KEY AUTOINCREMENT,
SupID INTEGER REFERENCES
Suppliers(SupID),
Date INTEGER,
Total REAL,
CompTotal REAL,
ExchgRate REAL,
Name TEXT);
CREATE TABLE IF NOT EXISTS InputsComp(InCompID INTEGER PRIMARY KEY
AUTOINCREMENT,
InvID INTEGER REFERENCES
Inputs(InvID),
CodeID INTEGER REFERENCES
Codes(CodeID),
Total REAL,
Quantity REAL);
CREATE TABLE IF NOT EXISTS Products(ProdID INTEGER PRIMARY KEY
AUTOINCREMENT,
Code TEXT,
Name TEXT);
CREATE TABLE IF NOT EXISTS ProductsComp(ProdCompID INTEGER PRIMARY KEY
AUTOINCREMENT,
ProdID INTEGER REFERENCES
Products(ProdID),
CompID INTEGER REFERENCES
Components(CompID),
Quantity REAL);
CREATE TABLE IF NOT EXISTS Outputs(OutID INTEGER PRIMARY KEY AUTOINCREMENT,
Date INTEGER,
Name TEXT);
CREATE TABLE IF NOT EXISTS OutputsProd(OutProdID INTEGER PRIMARY KEY
AUTOINCREMENT,
OutID INTEGER REFERENCES
Outputs(OutID),
ProdID INTEGER REFERENCES
Products(ProdID),
Quantity REAL);
CREATE TABLE IF NOT EXISTS OutputsComp(OutCompID INTEGER PRIMARY KEY
AUTOINCREMENT,
OutID INTEGER REFERENCES
Outputs(OutID),
CompID INTEGER REFERENCES
Components(CompID),
Quantity REAL);
CREATE TEMP VIEW Codes_View AS
SELECT CodeID,CompID,SupID,Name,
(SELECT Name FROM Suppliers WHERE (Suppliers.SupID =
Codes.SupID)) || ' - ' || Name AS SupplierCode,
(SELECT CatID FROM Components WHERE (Components.CompID =
Codes.CompID)) AS CatID,
(SELECT Name FROM Components WHERE (Components.CompID =
Codes.CompID)) AS Component
FROM Codes;
CREATE TEMP VIEW Inputs_View AS
SELECT InvID,SupID,Date,Total,CompTotal,ExchgRate,Name,
Total/ExchgRate/CompTotal AS PriceRatio,
(SELECT Name FROM Suppliers WHERE (Suppliers.SupID =
Inputs.SupID)) AS Supplier,
(SELECT Name FROM Suppliers WHERE (Suppliers.SupID =
Inputs.SupID)) || ' - ' || Name AS FullName
FROM Inputs;
CREATE TEMP VIEW InputsComp_View AS
SELECT InCompID,InvID,CodeID,Total,Quantity,
Total*(SELECT PriceRatio FROM Inputs_View WHERE
(Inputs_View.InvID = InputsComp.InvID)) AS EffPrice,
Total*(SELECT PriceRatio FROM Inputs_View WHERE
(Inputs_View.InvID = InputsComp.InvID))/Quantity AS UnitPrice,
(SELECT CompID FROM Codes WHERE (Codes.CodeID =
InputsComp.CodeID)) AS CompID,
(SELECT SupplierCode FROM Codes_View WHERE (Codes_View.CodeID
= InputsComp.CodeID)) AS SupplierCode,
(SELECT Component FROM Codes_View WHERE (Codes_View.CodeID =
InputsComp.CodeID)) AS Component,
(SELECT FullName FROM Inputs_View WHERE (Inputs_View.InvID =
InputsComp.InvID)) AS Invoice,
(SELECT Date FROM Inputs_View WHERE (Inputs_View.InvID =
InputsComp.InvID)) AS Date
FROM InputsComp;
CREATE TEMP VIEW ProductsComp_View AS
SELECT ProdCompID,ProdID,CompID,Quantity,
(SELECT Name FROM Components WHERE (Components.CompID =
ProductsComp.CompID)) AS Component,
(SELECT MIN(UnitPrice) FROM InputsComp_View WHERE
(InputsComp_View.CompID = ProductsComp.CompID)) *
Quantity AS PriceMin,
(SELECT MAX(UnitPrice) FROM InputsComp_View WHERE
(InputsComp_View.CompID = ProductsComp.CompID)) *
Quantity AS PriceMax,
(SELECT SUM(EffPrice) FROM InputsComp_View WHERE
(InputsComp_View.CompID = ProductsComp.CompID)) /
(SELECT SUM(Quantity) FROM InputsComp_View WHERE
(InputsComp_View.CompID = ProductsComp.CompID)) *
Quantity AS PriceMed
FROM ProductsComp;
CREATE TEMP VIEW Products_View AS
SELECT ProdID,Code,Name,
(SELECT SUM(PriceMin) FROM ProductsComp_View WHERE
(ProductsComp_View.ProdID = Products.ProdID)) AS PriceMin,
(SELECT SUM(PriceMax) FROM ProductsComp_View WHERE
(ProductsComp_View.ProdID = Products.ProdID)) AS PriceMax,
(SELECT SUM(PriceMed) FROM ProductsComp_View WHERE
(ProductsComp_View.ProdID = Products.ProdID)) AS PriceMed
FROM Products;
CREATE TEMP VIEW OutputsComp_View AS
SELECT ProductsComp.CompID AS CompID,OutputsProd.OutID AS
OutID,(OutputsProd.Quantity * ProductsComp.Quantity) AS Quantity,
(SELECT Date FROM Outputs WHERE (Outputs.OutID =
OutputsProd.OutID)) AS Date,
(SELECT Name FROM Outputs WHERE (Outputs.OutID =
OutputsProd.OutID)) || ' - ' ||
(SELECT Code FROM Products WHERE (Products.ProdID =
OutputsProd.ProdID)) AS Output,
(SELECT Name FROM Components WHERE (Components.CompID =
ProductsComp.CompID)) AS Component
FROM OutputsProd,ProductsComp
WHERE (OutputsProd.ProdID = ProductsComp.ProdID)
UNION
SELECT CompID,OutID,Quantity,
(SELECT Date FROM Outputs WHERE (Outputs.OutID =
OutputsComp.OutID)) AS Date,
(SELECT Name FROM Outputs WHERE (Outputs.OutID =
OutputsComp.OutID)) AS Output,
(SELECT Name FROM Components WHERE (Components.CompID =
OutputsComp.CompID)) AS Component
FROM OutputsComp;
CREATE TEMP VIEW Outputs_View AS
SELECT OutID,Date,Name,
(SELECT SUM(Quantity * (SELECT MIN(UnitPrice) FROM InputsComp_View
WHERE (InputsComp_View.CompID = OutputsComp_View.CompID)))
FROM OutputsComp_View WHERE (OutputsComp_View.OutID =
Outputs.OutID)) AS PriceMin,
(SELECT SUM(Quantity * (SELECT MAX(UnitPrice) FROM InputsComp_View
WHERE (InputsComp_View.CompID = OutputsComp_View.CompID)))
FROM OutputsComp_View WHERE (OutputsComp_View.OutID =
Outputs.OutID)) AS PriceMax,
(SELECT SUM(Quantity * (SELECT SUM(EffPrice) FROM InputsComp_View
WHERE (InputsComp_View.CompID = OutputsComp_View.CompID)) /
(SELECT SUM(Quantity) FROM InputsComp_View WHERE
(InputsComp_View.CompID = OutputsComp_View.CompID)))
FROM OutputsComp_View WHERE (OutputsComp_View.OutID =
Outputs.OutID)) AS PriceMed
FROM Outputs;
CREATE TEMP VIEW Stocks_View AS
SELECT CompID,CatID,Name,
ifnull((SELECT SUM(Quantity) FROM InputsComp_View WHERE
(InputsComp_View.CompID = Components.CompID)),0) -
ifnull((SELECT SUM(Quantity) FROM OutputsComp_View WHERE
(OutputsComp_View.CompID = Components.CompID)),0) AS Quantity,
(SELECT MIN(UnitPrice) FROM InputsComp_View WHERE
(InputsComp_View.CompID = Components.CompID)) AS PriceMin,
(SELECT MAX(UnitPrice) FROM InputsComp_View WHERE
(InputsComp_View.CompID = Components.CompID)) AS PriceMax,
(SELECT SUM(EffPrice) FROM InputsComp_View WHERE
(InputsComp_View.CompID = Components.CompID)) /
(SELECT SUM(Quantity) FROM InputsComp_View WHERE
(InputsComp_View.CompID = Components.CompID)) AS PriceMed
FROM Components;
*****
The problem is with the Quantity field of the Stocks_View temporary
view. While the Quantity field values are correct in InputsComp_View and
OutputsComp_View (the results for a specific CompID are shown in
separate grids when checking the stocks) and the result of
ifnull((SELECT SUM(Quantity) FROM InputsComp_View WHERE
(InputsComp_View.CompID = Components.CompID)),0)
is correct, the result of
ifnull((SELECT SUM(Quantity) FROM OutputsComp_View WHERE
(OutputsComp_View.CompID = Components.CompID)),0)
is not correctly considering all rows in OutputsComp_View.
One of the offending cases is for a CompID value that produces results
in OutputsComp_View only through the second part of the UNION (it is
found only in OutputsComp).
