We are looking into adding hexadecimal integer literals to SQLite. In
other words, we are looking to enhance SQLite to understand 0x1234 as
another way of writing 4660. Hex literals are useful in conjunction with
the bit-wise AND and OR operators (& and |) and in applications that make
use of bit fields.
The question is what to do with hex literals that are larger than 64 bits.
In other words, what number is represented by (say):
0x123456789abcdef01
The current SQLite implementation (on the "hex-literal" branch) works by
converting hex literals of 64 bits or less into a signed 64-bit integer.
Hex literals of 65 bits or more are approximated by a nearby floating-point
number. So, for example, the 65-bit hex literal example above would become
2.09882954794206e+19. However, this approach creates a discontinuity at
the boundary between 64-bit and 65-bit literals. The value
0xffffffffffffffff is equal to -1 but 0x10000000000000000 is
1.84467440737096e+19. This approach means that all hexadecimal literals are
positive numbers, except literals 0x8000000000000000 through
0xffffffffffffffff which are negative. There is this range of negative
values smack in the middle of an otherwise uniformly increasing sequence of
positive numbers. That negative range seems discombobulating.
What Do Other SQL Database Engines Do?
Oracle and PostgreSQL do not support hexadecimal integer literals. MySQL
supports hex literals, but the support appears to be buggy. In MySQL
5.6.6m9 and 5.5.32 the statement "SELECT 0 + 0x7fffffffffffffff;" yields
9223372036854776000 when the correct answer is 9223372036854775807. If you
say just "SELECT 0x7fffffffffffffff", without the "0 +" part, then
sqlfiddle gives no answer at all, suggesting that MySQL is crashing. SQL
Server also supports hex literals, and seems to do so with fewer bugs than
MySQL. SQL Server appears to silently truncate hex integer to 32 or 64
bits (depending on context), ignoring more significant bits. Thus in SQL
Server, "SELECT 0x10000000000000001" yields just "1".
Possible Solutions:
(1) Keep the current prototype behavior. Hex literals of 64 bits or less
are converted into twos-complement 64-bit integers, meaning that some
values are negative. Hex literals of 65 bits or more are converted into a
floating-point approximation.
(2) Declare that the values of hex literals greater than 64 bits are
"undefined" and convert them into random 64-bit integers. Seriously: use
the random number generator to convert oversized hex literals into
integers, and thereby discourage programmers from using oversized hex
literals.
(3) Convert hex literals of 63-bits or less into integers and convert
64-bit or larger hex literals into a floating-point approximation. This
means that all hex literals will be non-negative and it eliminates the
discontinuities. But it also means that you can only use the lower 63 bits
of a 64-bit integer as a bitmask.
(4) Silently truncate all hexadecimal literals to 64-bits, like SQL Server
does.
(5) Throw an error when a hex literal larger than 64-bits is seen. This is
harder than it sounds, since literals can occur in so many different
contexts. There is the parser, of course. But also literals can be parsed
when doing string-to-numeric coercion via the CAST operator, or in order to
do arithmetic, or when inserting string values into columns with NUMERIC
affinity. There are many cases that would need to be coded and tested,
some of which have never before had the capability to throw an error.
(6) Do not support hexadecimal integer literals for casts and affinity
coercions. Only support hex literals in the SQL parser, and throw errors
for oversized hex literals in that context.
(7) Have the parser convert oversized hex literals into NULL, or throw
errors, and have attempts to coerce oversized hex literals strings into
numeric values fail, thus preventing affinity conversions.
(8) Work like PostgreSQL and Oracle and legacy SQLite and simply do not
support hexadecimal integer literals.
Other ideas? Comments? Criticism?
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
