FLOATING-POINT-OVERFLOW with declared type of 'string'

[thomp]

According to the sqlite spec (http://www.sqlite.org/datatype3.html), a column with declared type of 'string' should have affinity NUMERIC. However, the last query triggers a FLOATING-POINT-OVERFLOW error. Is this what we expect with the last query? Rather, as with the earlier query (8351E70), shouldn't the last query also return NIL?

(defun test1 ()
  (let ((our-db (connect ":memory:")))
    (execute-non-query our-db "CREATE TABLE \"sub\" (`tr` string);")
    (execute-non-query our-db "INSERT INTO `sub` VALUES('0');")
    (sqlite::execute-single our-db "SELECT * FROM sub WHERE tr=? ;" "8351E70")
    (sqlite::execute-single our-db "SELECT * FROM sub WHERE tr=? ;" "8351E700")))

[TeMPOraL]

Hi, new maintainer here.

Could you tell me on which implementation(s) and system(s) did you run into this problem? I currently can't reproduce it on my end.

[thomp]

I believe I ran into this initially with cl-sqlite-20130615-git (quicklisp). The same issue arises with the current version on github. This is with SBCL 1.4.0 on Linux (4.9.0-9-amd64 #1 SMP Debian 4.9.168-1+deb9u4 (2019-07-19) x86_64 GNU/Linux).

arithmetic error FLOATING-POINT-OVERFLOW signalled
   [Condition of type FLOATING-POINT-OVERFLOW]

Restarts:
 0: [RETRY] Retry SLIME REPL evaluation request.
 1: [*ABORT] Return to SLIME's top level.
 2: [ABORT] abort thread (#<THREAD "new-repl-thread" RUNNING {100407FDB3}>)

Backtrace:
  0: ((FLET SB-UNIX::RUN-HANDLER :IN SB-SYS:ENABLE-INTERRUPT) 8 #.(SB-SYS:INT-SAP #X7FFFF4BB5430) #.(SB-SYS:INT-SAP #X7FFFF4BB5300))

[TeMPOraL]

Ok, thanks. I still can't reproduce it, neither on SBCL 1.5.0 nor on SBCL 1.4.0 (both on Linux, 5.0.0-23-generic #24~18.04.1-Ubuntu SMP Mon Jul 29 16:12:28 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux).

I'll keep digging.

[weatherman2095]

I managed to replicate it using different strings/numbers.

"1e328" and "17e328" work for me (I ran into this bug while working with checksums).

It seems to be that they're interpreted as the 1e10 float notation, which runs into implementation & platform-dependent limits.

Of course it shouldn't ever be interpreted as a float in the first place, but that seems to be what happens.

To add to the weirdness, this only occurs if the table has its affinities set.

Here is the exact code I used for this:

(defun test1 ()
  (let ((our-db (sqlite:connect ":memory:")))
    (sqlite:execute-non-query our-db "CREATE TABLE sub (tr STRING);")
    (sqlite:execute-non-query our-db "INSERT INTO sub VALUES ('0');")
    (sqlite:execute-single our-db "SELECT * FROM sub WHERE tr = ? ;" "17e328")
    (sqlite:execute-single our-db "SELECT * FROM sub WHERE tr = ? ;" "1")
    (sqlite:execute-non-query our-db "INSERT INTO sub (tr) VALUES (?);" "1")))

If you remove even just that STRING the error goes away.

[weatherman2095]

I have confirmed that ECL throws the same overflow error.

CCL doesn't, but using just a single insert of the buggy value and reading it back shows that it instead silently corrupted the data.

So that's 3 implementations with the same bug being replicable with the same data. This suggests either something within either cl-sqlite or cffi itself is responsible for it.

[weatherman2095]

CFFI seems to have no particular issues with such values from my testing.

However, it turns out that STRING is not a TEXT column affinity for SQLite, it is a NUMERIC column affinity.

So, with that in mind, some surprising conversions happen (flexible typing gotcha) and the language used for the queries doesn't matter.

#!/usr/bin/python3

import sqlite3
con = sqlite3.connect(":memory:")
with con:
    con.execute("CREATE TABLE sub (tr string);")
    con.execute("INSERT INTO sub VALUES ('0');")
    con.execute("INSERT INTO sub VALUES (?);", ["test"])
    # Yes, it works with original value, irrespective of
    # capitalization.
    con.execute("INSERT INTO sub (tr) VALUES (?);", ["8351E70"]) 
    con.execute("INSERT INTO sub (tr) VALUES (?);", ["1e256"])
    con.execute("INSERT INTO sub (tr) VALUES (?);", ["17e328"])
    con.execute("INSERT INTO sub (tr) VALUES (?);", ["1e512"])
    cur = con.execute("SELECT typeof(tr), tr FROM sub;")
    print(cur.fetchall())
con.close()

Result: [('integer', 0), ('text', 'test'), ('real', 8.351e+73), ('real', 1e+256), ('real', inf), ('real', inf)]

Pointer invalidations happening when SQLite converts data types is mentioned in sqlite.h which might be part of what's causing float overflows to leak back into cl-sqlite's C interface.

The same code as earlier with the TEXT type results in the following: [('text', '0'), ('text', 'test'), ('text', '8351E70'), ('text', '1e256'), ('text', '17e328'), ('text', '1e512')]

Strict Tables are also a tangentially-related thing that would've prevented this issue by simply not recognizing STRING at all.

The same test can also be done in Lisp of course:

(defun test1 (table-type)
  (let ((our-db (sqlite:connect ":memory:")))
    (sqlite:execute-non-query
     our-db 
     (format nil "CREATE TABLE sub (tr ~a);" table-type))
    (sqlite:execute-non-query our-db "INSERT INTO sub (tr) VALUES (?);" "8351E70")
    (sqlite:execute-non-query our-db "INSERT INTO sub (tr) VALUES (?);" "1e256")
    (sqlite:execute-non-query our-db "INSERT INTO sub (tr) VALUES (?);" "17e328")
    (sqlite:execute-non-query our-db "INSERT INTO sub (tr) VALUES (?);" "1e328")
    (sqlite:execute-non-query our-db "INSERT INTO sub (tr) VALUES (?);" "1e512")
    (sqlite:execute-to-list our-db "SELECT typeof(tr), tr FROM sub")))

Called as (test1 "string") errors out.

Called as (test1 "text"): (("text" "8351E70") ("text" "1e256") ("text" "17e328") ("text" "1e328") ("text" "1e512"))