I've been using sqlite for some time but haven't used SQL update until
recently. On my first real use, I've encountered a problem that I can't
understand at all.
To pare this down, I have two tables, citytax and taxitems2, and I've attached
a database with just these two tables, total size ~12Kbytes.
I perform the following update using the sqlite3 command line interface:
update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum =
citytax.number and taxitems2.taxrate = "M");
The result is that the salesm column is set to NULL for all citytax rows. However, I
would expect that rows 9 and 22 would be non-NULL. I get the the same all-NULL result for
a taxrate of "G" as well, though I would expect that rows 14, 19, and 58 would
be non-NULL.
The strangest part is that if I specify taxrates of "P", "R", or "", I get the result that I expect. I've included a listing of
the taxitems2 table below. As one can see, there are rows where the second column, taxrate, is all of "", "P", "R",
"G", and "M". The last column is the amount column, non-null for all rows. I must admit that I don't understand what is going on here.
Perhaps even stranger is if I extract the select from the update and give it an
appropriate row number, I get the expected non-NULL result:
sqlite> select sum(amount) from taxitems2 where taxitems2.citynum = 9 and
taxitems2.taxrate = "M";
1176.72
I've included the contents of the taxitems2 table as well as the schema for
tables taxitems2 and citytax below. One the one hand, this seems almost
certainly to be a bug, but on the other, it is so basic, that I can't believe
that I'm the first to encounter it.
Any assistance that anyone can provide would of course be much appreciated. I'm
using sqlite 3.30 under Windows 7 cygwin and 3.27 under Debian linux, both
environments behave the same in this regard.
As I was writing I had yet another test idea:
update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum =
citytax.number and unicode(taxitems2.taxrate) = 77); /* taxrate = "M" */
and
update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum =
citytax.number and unicode(taxitems2.taxrate) = 71); /* taxrate = "G" */
Both of these updates perform as expected.
Regards,
Paul Ausbeck
sqlite> select * from taxitems2;
WAL,,68,10,2,1,4,0.75,2,1.5,4,142.5
SUM,,34,36,2,1,3.5,0.75,2,1.5,4,0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
DNT,,9,10,2,2,4,0.75,2,1.5,4,206.0
USX,P,58,14,1,0,0,0,0,0,0,7104.0
TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945
TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945
TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978
TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978
USX,P,58,14,1,0,0,0,0,0,0,5446.4
USX,P,58,14,1,0,0,0,0,0,0,1657.6
AAM,G,58,36,2,0,0,0.75,2,1.5,4,34.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,107.6
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,6.3
PEM,G,58,10,2,0,0,0.75,2,1.5,4,8
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
HVY,R,58,14,1,0,0,0,0,0,0,72.5
HVY,R,58,14,1,0,0,0,0,0,0,176.4
VEI,G,14,10,2,1,4,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,168
CSH,G,19,10,2,2,4,0.75,2,1.5,4,33.2
CSH,G,19,10,2,2,4,0.75,2,1.5,4,25.0
WAT,M,9,10,2,2,4,0.75,2,1.5,4,9.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,65
WAT,M,9,10,2,2,4,0.75,2,1.5,4,125.0
WAT,M,9,10,2,2,4,0.75,2,1.5,4,43.9
WAT,M,9,10,2,2,4,0.75,2,1.5,4,107.4
WAT,M,9,10,2,2,4,0.75,2,1.5,4,46.72
WAT,M,9,10,2,2,4,0.75,2,1.5,4,52.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,12.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,63.3
ICM,M,22,10,2,1,4,0.75,2,1.5,4,120.2000394
DNT,M,9,10,2,2,4,0.75,2,1.5,4,334.4
DNT,M,9,10,2,2,4,0.75,2,1.5,4,155.8
DNT,M,9,10,2,2,4,0.75,2,1.5,4,160.7
sqlite> .schema taxitems2
CREATE TABLE taxitems2(
code TEXT,
taxrate TEXT,
citynum INT,
countynum INT,
statenum INT,
citym NUM,
cityg NUM,
countym NUM,
countyg NUM,
statem NUM,
stateg NUM,
amount
);
sqlite> .schema citytax
CREATE TABLE citytax(
number INT,
code INT,
g NUM,
m NUM,
comment TEXT,
taxg NUM,
taxm NUM,
salesg NUM,
salesm NUM,
salesr NUM,
salesp NUM,
stamp INT
);
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
