Hi! The attached database shows the problem. Just check Stocks_View for CompID 231. Normally it should be 0, but it gives 5. There are correctly computed 8 inputs but wrongly computed 3 outputs.
Catalin On 22.10.2015 15:19, Richard Hipp wrote: > Thanks for the report. > > Can you also provide us with a test case that demonstrates the malfunction? > > On 10/22/15, Catalin Ionescu <catalin.ionescu at radioconsult.ro> wrote: >> 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). >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >
