Hi, Indeed, this fixes the problem! Many thanks!
On 24.10.2015 23:47, Richard Hipp wrote: > Dan checked in a fix > (https://www.sqlite.org/src/info/bfea226d0d226a04) that may fix your > problem. It might also be that you can work around your problem by > setting: > > PRAGMA automatic_index=OFF; > > Please try these and let us know whether or not they help. > > On 10/24/15, Catalin Ionescu <catalin.ionescu at radioconsult.ro> wrote: >> 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 >>>>>> >> >
