SQLite – TEXT<=>INTEGER<=>FLOAT conversion

Minor version (two lines)

CAST('...' AS INTEGER) is designed to delimit a result to the range <SMALLEST_INT64;LARGEST_INT64>. However, it calculates u64 value while scanning an input text. This results in (mod 2^64) operation applied to the result before it is checked against the above–mentioned range. For example, SELECT CAST('295147905179352827090' AS INTEGER); returns 1234. The following patch repairs that problem.

The second role of this patch is to suppress a creation of FLOAT value, where INTEGER value would be sufficient/adequate. double type, which is a base for SQLite's FLOAT value, has 53 bit mantissa, what, in turn, causes a loss of an accuracy for INTEGER numbers whose 1 bits spread over 53 bits. This occurs in two places: (1) when inserting TEXT value into INTEGER/NUMERIC affinity column and that text contains trailing spaces (for example, INSERT INTO sometable VALUES('9000000000000000001'),('9000000000000000001 '); makes the first value to be INTEGER 9000000000000000001, and the second value to be FLOAT 9.0e18 turned into INTEGER 9000000000000000000); (2) when casting TEXT value as NUMERIC and that text contains trailing non–numeric characters (spaces or others) (for example, SELECT CAST('9000000000000000001' AS NUMERIC); returns INTEGER 9000000000000000001, while SELECT CAST('9000000000000000001 ' AS NUMERIC); and SELECT CAST('9000000000000000001X' AS NUMERIC); return FLOAT 9.0e18). The following patch partially repairs that problem, i.e. it does not handle a casting where trailing characters are not spaces — such handling requires a bit greater effort then changing of two lines of code.

This minor version is discontinued as the SQLite team improved sqlite3Atoi64() in check–in [ace0644a].

Main version

This patch is an seriously expanded version of the previous one. It handles the above–mentioned problem with casting TEXT value as NUMERIC when that text contains trailing characters, which are not numeric, nor spaces. The patch contains other, not necessarily requested features, thus it uses flags, which control parts of a code to be compiled. A flag will be unset (false/0) when it is undefined or defined to 0. A flag will be set (true/!0) when it is defined to an empty replacement list (-DCHN_SQLITE_ENABLE_NUMERIFY) or to something other then 0 (-DCHN_SQLITE_ENABLE_NUMERIFY=1). If all flags are unset then the patched files will behave like original ones. If you want to have a minor version + handling non–space chars at the end of INTEGER strings, then you will have to compile a patched files with the following options: -DCHN_SQLITE_ENABLE_NUMERIFY -DCHN_SQLITE_DISABLE_FLTCAREFUL.

Additional features of the main varsion

At the beginning, I would like to inform you, that same features could seem to be strange, giving no advantages, or even without sense. They could be for fun or my personal necessities.

Flags are organized in a tree–like structure: defining of a flag of a deeper level does not cause that all parent flags are defined — it causes an error if one of the parent flags is unset:


Mail me