Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Richard Hipp]

On Fri, Mar 9, 2012 at 1:51 AM, Alan Chandler wrote:

>
>  Can you scrub the data (replace numbers with values from random(), and all
>> strings with random text?)  If not, email the database directly to me.
>>
>>
>>  I have done some randomisation and sent it to you privately
>

Based on your data, I have devised a small self-contained test case here:
http://www.sqlite.org/src/tktview/b7c8682cc17f3290


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Alan Chandler]

On 09/03/12 00:29, Richard Hipp wrote:

On Thu, Mar 8, 2012 at 6:47 PM, Alan Chandlerwrote:


The complete database schema is here

https://github.com/akc42/**AKCMoney/blob/master/app/inc/**database.sql

My database is full of private financial data so I would rather not just
post it publically.  If you really need the data I could mail it to you
privately (its only 366kb big)


Can you scrub the data (replace numbers with values from random(), and all
strings with random text?)  If not, email the database directly to me.



I have done some randomisation and sent it to you privately

--
Alan Chandler
http://www.chandlerfamily.org.uk

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Richard Hipp]

On Thu, Mar 8, 2012 at 6:47 PM, Alan Chandler wrote:

> The complete database schema is here
>
> https://github.com/akc42/**AKCMoney/blob/master/app/inc/**database.sql
>
> My database is full of private financial data so I would rather not just
> post it publically.  If you really need the data I could mail it to you
> privately (its only 366kb big)
>

Can you scrub the data (replace numbers with values from random(), and all
strings with random text?)  If not, email the database directly to me.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Alan Chandler]

On 08/03/12 23:32, Richard Hipp wrote:

On Thu, Mar 8, 2012 at 5:44 PM, Alan Chandlerwrote:


I have been running a financial management application application I wrote
for a number of years.  My "production" version runs on Debian stable
system as a result is running sqlite v3.7.3.  My personal development
machine is running Debian unstable and as a result has sqlite 3.7.10.

Earlier this week I discovered a bug in a rarely used part of the
application, so took a copy of the production database and ran it on my
development machine. I quickly found that and fixed it, but another major
element of the application appeared to give some strange results.

I have spend some down tracking down what caused the problem, and it seems
to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes the sql.
  It seems to me that the later release gets things wrong - but it might be
that the newer version has some sort of PRAGMA that I am not using right.
  So I would like to ask here where I am going wrong.

The basic issue is around a view on a table called "xaction" - the
transactions processed.  It has optional "source" and "destination"
accounts (must be at least one or the other but can also have both) and
optional "codes" that relate to classes of transaction as they appear in
the account.  I put a view on top of this which normalises the currency for
use in my accounts. The schema for the view is ...

CREATE VIEW dfxaction AS
SELECT t.id,t.date,t.version, src, srccode, dst,
dstcode,t.description, rno, repeat,
CASE
WHEN t.currency = 'GBP' THEN t.amount
WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN
t.srcamount
WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN
t.dstamount
ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS INTEGER)
END AS dfamount
FROM
xaction AS t
LEFT JOIN account AS sa ON t.src = sa.name
LEFT JOIN account AS da ON t.dst = da.name
LEFT JOIN currency ON
t.currency != 'GBP' AND
(t.srcamount IS NULL OR sa.currency != 'GBP') AND
(t.dstamount IS NULL OR da.currency != 'GBP') AND
t.currency = currency.name;


The query that results in differences between the two versions of sqlite
(I have cut this down to the minimum I could find showed the difference).

SELECT
c.id AS id, c.type AS type, c.description AS description, t.*
FROM
dfxaction AS t, code AS c
WHERE
t.date BETWEEN 129384 AND 1325375999
AND ((t.src IS NOT NULL AND t.srccode = c.id)  OR t.dstcode = c.id )
ORDER BY
c.description COLLATE NOCASE ASC;

The little part "t.src IS NOT NULL AND" seems to be the trigger for
cutting down the records to almost none from the full amount because when I
remove it I got more like the correct number of records.  Most of the
records excluded by putting the clause in DO NOT have t.src of NULL.

AND OF COURSE ON sqlite 3.7.3 I get all the records I expect.

The other important aspect.  If I don't use a view, but instead replace
"FROM dfxaction" with "FROM xaction" using the raw table then 3.7.10
delivers all the records I would expect.

So I am completely perplexed as to why there are changes between
behaviour.  Can anyone help me understand.


Maybe the change at http://www.sqlite.org/src/info/b23ae13187 broke
something.  Can you get us a complete schema with enough data to actually
run a test case that shows the problem?

The complete database schema is here

https://github.com/akc42/AKCMoney/blob/master/app/inc/database.sql

My database is full of private financial data so I would rather not just 
post it publically.  If you really need the data I could mail it to you 
privately (its only 366kb big)



--
Alan Chandler
http://www.chandlerfamily.org.uk

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Richard Hipp]

On Thu, Mar 8, 2012 at 6:27 PM, Alan Chandler wrote:

> On 08/03/12 22:44, Alan Chandler wrote:
>
>> I have been running a financial management application application I
>> wrote for a number of years.  My "production" version runs on Debian stable
>> system as a result is running sqlite v3.7.3.  My personal development
>> machine is running Debian unstable and as a result has sqlite 3.7.10.
>>
>> Earlier this week I discovered a bug in a rarely used part of the
>> application, so took a copy of the production database and ran it on my
>> development machine. I quickly found that and fixed it, but another major
>> element of the application appeared to give some strange results.
>>
>> I have spend some down tracking down what caused the problem, and it
>> seems to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes
>> the sql.  It seems to me that the later release gets things wrong - but it
>> might be that the newer version has some sort of PRAGMA that I am not using
>> right.  So I would like to ask here where I am going wrong.
>>
>>
> Things have now got stranger.  I just saw the post on the e-mail list for
> sqlitestudio and thought that looks interesting, so I have now downloaded
> it.  It seems to be using sqlite 3.7.8
>
> It works correctly, and the very same sql using sqlite manager in Mozilla
> goes wrong (this is linked to sqlite 3.7.10)


The suspect change at http://www.sqlite.org/src/info/b23ae13187 first
appeared in version 3.7.10.


>
>
> --
> Alan Chandler
> http://www.chandlerfamily.org.**uk 
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Richard Hipp]

On Thu, Mar 8, 2012 at 5:44 PM, Alan Chandler wrote:

> I have been running a financial management application application I wrote
> for a number of years.  My "production" version runs on Debian stable
> system as a result is running sqlite v3.7.3.  My personal development
> machine is running Debian unstable and as a result has sqlite 3.7.10.
>
> Earlier this week I discovered a bug in a rarely used part of the
> application, so took a copy of the production database and ran it on my
> development machine. I quickly found that and fixed it, but another major
> element of the application appeared to give some strange results.
>
> I have spend some down tracking down what caused the problem, and it seems
> to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes the sql.
>  It seems to me that the later release gets things wrong - but it might be
> that the newer version has some sort of PRAGMA that I am not using right.
>  So I would like to ask here where I am going wrong.
>
> The basic issue is around a view on a table called "xaction" - the
> transactions processed.  It has optional "source" and "destination"
> accounts (must be at least one or the other but can also have both) and
> optional "codes" that relate to classes of transaction as they appear in
> the account.  I put a view on top of this which normalises the currency for
> use in my accounts. The schema for the view is ...
>
> CREATE VIEW dfxaction AS
>SELECT t.id,t.date,t.version, src, srccode, dst,
> dstcode,t.description, rno, repeat,
>CASE
>WHEN t.currency = 'GBP' THEN t.amount
>WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN
> t.srcamount
>WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN
> t.dstamount
>ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS INTEGER)
>END AS dfamount
>FROM
>xaction AS t
>LEFT JOIN account AS sa ON t.src = sa.name
>LEFT JOIN account AS da ON t.dst = da.name
>LEFT JOIN currency ON
>t.currency != 'GBP' AND
>(t.srcamount IS NULL OR sa.currency != 'GBP') AND
>(t.dstamount IS NULL OR da.currency != 'GBP') AND
>t.currency = currency.name;
>
>
> The query that results in differences between the two versions of sqlite
> (I have cut this down to the minimum I could find showed the difference).
>
> SELECT
>c.id AS id, c.type AS type, c.description AS description, t.*
> FROM
>dfxaction AS t, code AS c
> WHERE
>t.date BETWEEN 129384 AND 1325375999
>AND ((t.src IS NOT NULL AND t.srccode = c.id)  OR t.dstcode = c.id )
> ORDER BY
>c.description COLLATE NOCASE ASC;
>
> The little part "t.src IS NOT NULL AND" seems to be the trigger for
> cutting down the records to almost none from the full amount because when I
> remove it I got more like the correct number of records.  Most of the
> records excluded by putting the clause in DO NOT have t.src of NULL.
>
> AND OF COURSE ON sqlite 3.7.3 I get all the records I expect.
>
> The other important aspect.  If I don't use a view, but instead replace
> "FROM dfxaction" with "FROM xaction" using the raw table then 3.7.10
> delivers all the records I would expect.
>
> So I am completely perplexed as to why there are changes between
> behaviour.  Can anyone help me understand.
>

Maybe the change at http://www.sqlite.org/src/info/b23ae13187 broke
something.  Can you get us a complete schema with enough data to actually
run a test case that shows the problem?



>
> Thanks
>
> --
> Alan Chandler
> http://www.chandlerfamily.org.**uk 
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Alan Chandler]

On 08/03/12 22:44, Alan Chandler wrote:
I have been running a financial management application application I 
wrote for a number of years.  My "production" version runs on Debian 
stable system as a result is running sqlite v3.7.3.  My personal 
development machine is running Debian unstable and as a result has 
sqlite 3.7.10.


Earlier this week I discovered a bug in a rarely used part of the 
application, so took a copy of the production database and ran it on 
my development machine. I quickly found that and fixed it, but another 
major element of the application appeared to give some strange results.


I have spend some down tracking down what caused the problem, and it 
seems to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 
processes the sql.  It seems to me that the later release gets things 
wrong - but it might be that the newer version has some sort of PRAGMA 
that I am not using right.  So I would like to ask here where I am 
going wrong.




Things have now got stranger.  I just saw the post on the e-mail list 
for sqlitestudio and thought that looks interesting, so I have now 
downloaded it.  It seems to be using sqlite 3.7.8


It works correctly, and the very same sql using sqlite manager in 
Mozilla goes wrong (this is linked to sqlite 3.7.10)


--
Alan Chandler
http://www.chandlerfamily.org.uk

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users