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 > -- D. Richard Hipp drh at sqlite.org
