Hi, So far I never used the command-line shell. It is clearly compiled with different optimization settings and that might be the cause of apparent correct functionality. Anyway, I decided to give it a try and attack the problem as a whole (I suspected, from previous experience, that the bug is just randomly appearing!). So I have fired up the shell, opened the DB file, pasted the CREATE TEMP VIEW... statements and executed "select sum(Quantity*PriceMed) from Stocks_View;". With 3.8.11 and newer I get a results around 20528.817 and with 3.8.10.2 and older I get a results around 11383.248. The value with the older SQLite versions is the correct one!
Is this considered a satisfactory proof that there is a problem starting with version 3.8.11? We are using SQLite in other projects where we deal with simply storing data and we have not seen any problems with newer versions. With this strange test case we might have just hit a combination of factors that triggers the problem! Catalin On 24.10.2015 16:22, Richard Hipp wrote: > 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 >>>> >> >
