[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

2015-10-25 Thread Simon Slavin 

On 24 Oct 2015, at 8:23pm, Catalin Ionescu  
wrote:

> 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?

Maybe.  It does at least give the development team something they can test 
without having to have a copy of your program.

There are some ambiguities in the specifications for SQL and SQLite, where the 
documentation says that something is undefined or perhaps doesn't mention it at 
all.  The thing you noticed may be one of them.  They usually involve NULLs or 
other troublesome thing like that.

Another possibility is that your database is corrupt.  Find this out using



There are two ways to proceed:

A) put a copy of your database somewhere where the dev team can download it
B) simplify your problem set by deleting tables and rows to the point where the 
commands to reproduce the problem are short.  (You can obtain the commands 
needed using the .dump command for the command-line shell.)  Then just post 
here listing the commands needed as text in your post.

Simon.

[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

2015-10-25 Thread Catalin Ionescu 
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  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  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  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,
>>

[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

2015-10-24 Thread Catalin Ionescu 
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  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  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,

[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

2015-10-24 Thread Richard Hipp 
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  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  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  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

[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

2015-10-24 Thread Richard Hipp 
On 10/24/15, Catalin Ionescu  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  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),
>>>

[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

2015-10-24 Thread Catalin Ionescu 
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.

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  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

[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

2015-10-22 Thread Catalin Ionescu 
I will prepare a version of my DB file with dummy customers and 
suppliers names (not too many) and I can pass it as it is.

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  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

[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

2015-10-22 Thread Catalin Ionescu 
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,

[sqlite] SELECT SUM(...) FROM ... WHERE regression in 3.8.11

2015-10-22 Thread Richard Hipp 
Thanks for the report.

Can you also provide us with a test case that demonstrates the malfunction?

On 10/22/15, Catalin Ionescu  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