On 10/24/15, Catalin Ionescu <catalin.ionescu at radioconsult.ro> wrote: > 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.
When I run "SELECT * FROM Stocks_View WHERE CompID=231" on the attached database, I always get a single line of output: 231|13|Faulty|0.0|1163.872|1320.44558088179|1216.59944761022 This is true regardless of which version of SQLite I use. I'm sorry you are having difficulties. We'd like to help. But you really do need to give us a specific example of what is going wrong in order for us to fix it. Please avoid vague statements like "Just check Stocks_View for CompID 231". It is more precise to say "Run the statement 'SELECT * FROM Stocks_View WHERE CompID=231;'". Better still, send us a single script that can be run using the sqlite3.exe command-line shell that demonstrates the problem, and does not require us to use a specific database file and to create a bunch of TEMP views first. Perhaps you can build that script by running the ".dump" command on your sample database file, concatenating the CREATE TEMP VIEW statements that are required, and appending the SELECT statement that is causing problems for you. > > 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 >>> >> > > -- D. Richard Hipp drh at sqlite.org
