Re: [EXTERNAL] Either a bug or I don't understand SQL update

You have fallen into the double quote trap.

SQLite uses double quotes to denote COLUMN NAMES, and single quotes to delimit STRING CONSTANTS.

When asking for "M" or "G", you get the contents of the column named m and g respectively (column names are case insensitive).

When asking for "P" or "R", there is no column with that name, so SQLite pretends that you meant to provide a string constant.

BTW: You have columns with NUMERIC affinity obviously representing amounts, which will get stored as REAL numbers. Don't do that if you expect to do anything financially relevant. You will incur rounding errors. Instead, store integer values of cents.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[email protected]] Im Auftrag von Paul Ausbeck
Gesendet: Donnerstag, 30. Januar 2020 00:11
An: [email protected]
Betreff: [EXTERNAL] [sqlite] Either a bug or I don't understand SQL update

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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users