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