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