Hello!
Just like the others in this conversation, I also believe that you
must not change the rules how strings are converted to integers by
type affinity, or by type conversions of arithmetic operators. Thus,
you must not add hexadecimal representation to conversions (nor hex
floats or 'inf' or any other new numeric representation).
Let me explain why. I create the following table in a database:
CREATE TABLE tb(cl NUMERIC PRIMARY KEY);
INSERT INTO tb VALUES('3.50');
INSERT INTO tb VALUES('0x20');
This inserts the real 3.5 and the string '0x20' into the table. Then,
any of the following queries finds a row from that table.
SELECT cl FROM tb WHERE cl = 3.5;
SELECT cl FROM tb WHERE cl = '3.5';
SELECT cl FROM tb WHERE cl = '3.50';
SELECT cl FROM tb WHERE cl = '3.500';
SELECT cl FROM tb WHERE cl = '0x20';
Now suppose hypothetically that you changed a future version of sqlite
to convert the string '0x20' to the number 32. Load the existing
database into this new version. This table will then have a strange
state: it will have a row with the string ('0x20') which is something
you couldn't insert with the new version of sqlite, because if you
tried to insert it, the new version would convert it to a number.
Further, the queries
SELECT cl FROM tb WHERE cl = '0x20';
SELECT cl FROM tb WHERE cl = 32;
would now either not find that row, or would have to search in
multiple places in the index, because '0x20' is still indexed as a
string, not as a number.
Similarly, a table could have a check constraint that the previous
version of sqlite had validated at insertion, but is no longer true in
the hypothetical new version; or a partial index that used to be
correct in a previous version of sqlite but is now suddenly missing
rows that should be in there in the hypothetical new version. (I
don't know whether there could be a problem with foreign keys.)
Now of course adding hexadecimal literals in the parser doesn't cause
any of these problems, as long as you make sure any statement or
schema with such a hexadecimal literal definitely gives an error in
previous versions of sqlite. Similarly, if you believe that parsing
numbers from a hexadecimal representation is useful at runtime, you
could add an ordinary SQL function that does this.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
