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? > > Thanks > > -- > Alan Chandler > http://www.chandlerfamily.org.**uk <http://www.chandlerfamily.org.uk> > > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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
