On 08/03/12 23:32, Richard Hipp wrote:
On Thu, Mar 8, 2012 at 5:44 PM, Alan Chandler<a...@chandlerfamily.org.uk>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 1293840000 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
