Due to hardware problems with my Debian Stable server, I have just
upgraded to Ubuntu-Server 12.04.
I have installed sqlite3 and when I ask it the version (with .version)
it replies
SQLite 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e
Which exactly complies with the sqlite web site for this release.
Sometime ago I reported a problem with 3.7.10 referenced here
http://www.sqlite.org/src/info/b7c8682cc1
Using the same database (which I would prefer not to publish because its
full of personal financial information - I already gave Richard Hipp a
randomised copy in respect of the last bug) I have experienced a problem
shown below. I am not sure this is the same problem as before (its very
similar) but that was reported as being introduced with changes
introduced in 3.7.10
select c.id,c.type,c.description,sum(t.dfamount) AS tamount FROM
dfxaction as t, account as a,code AS c WHERE c.type = 'C' and a.domain =
'Hartley' AND ((t.src = a.name and t.srccode = c.id) ) GROUP BY c.id;
Produces output, where as
select c.id,c.type,c.description,sum(t.dfamount) AS tamount FROM
dfxaction as t, account as a,code AS c WHERE c.type = 'C' and a.domain =
'Hartley' AND ((t.src = a.name and t.srccode = c.id) OR (t.dst = a.name
and t.dstcode = c.id )) GROUP BY c.id;
does not even though ONLY added an OR clause within a bracketed AND clause
Just to confuse the issue dfxaction (but not the other tables) is a view
- defined as below (and I think this is where the similarity to the
other bug comes in). If I replace that with xaction (the real table its
based on) then the second select above does produce expected output.
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;
ALSO just to confirm - I repeated the same experiment on sqlite3 version
3.7.13 (Debian unstable version) and the second query performs perfectly
I am mentioning this here because the earlier bug was supposed to have
been caused by a change made by 3.7.10, whereas this is 3.7.9 and given
its the version of choice in ubuntu it might be better to clarify
whether there is a problem there or not.
--
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
