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).