From 858c9b3a960ba1ba9a6b00339d7678dbdccfc9b9 Mon Sep 17 00:00:00 2001
From: Doug Coleman <doug.coleman@gmail.com>
Date: Fri, 22 Jul 2016 08:19:54 -0700
Subject: [PATCH] db.sqlite: Fix NULL returns for INTEGER columns in sqlite

The api for getting integer/double columns from sqlite3 returns a zero, but the value could still be NULL, so an extra api check for the sqlite type is required.

Fixes #1674.
---
 basis/db/sqlite/ffi/ffi.factor      |  2 +-
 basis/db/sqlite/lib/lib.factor      | 28 +++++++++++++++++++++-----
 basis/db/sqlite/sqlite-tests.factor | 31 +++++++++++++++++++++++++++++
 3 files changed, 55 insertions(+), 6 deletions(-)

diff --git a/basis/db/sqlite/ffi/ffi.factor b/basis/db/sqlite/ffi/ffi.factor
index f505add853..1c0defc317 100644
--- a/basis/db/sqlite/ffi/ffi.factor
+++ b/basis/db/sqlite/ffi/ffi.factor
@@ -132,7 +132,7 @@ FUNCTION: c-string sqlite3_column_decltype ( sqlite3_stmt* pStmt, int col )
 FUNCTION: int sqlite3_column_int ( sqlite3_stmt* pStmt, int col )
 FUNCTION: sqlite3_int64 sqlite3_column_int64 ( sqlite3_stmt* pStmt, int col )
 ! Bind the same function as above, but for unsigned 64bit integers
-FUNCTION-ALIAS: sqlite3-column-uint64
+FUNCTION-ALIAS: sqlite3_column_uint64
     sqlite3_uint64 sqlite3_column_int64 ( sqlite3_stmt* pStmt, int col )
 FUNCTION: double sqlite3_column_double ( sqlite3_stmt* pStmt, int col )
 FUNCTION: c-string sqlite3_column_name ( sqlite3_stmt* pStmt, int col )
diff --git a/basis/db/sqlite/lib/lib.factor b/basis/db/sqlite/lib/lib.factor
index b36edc2920..862df5b8ed 100644
--- a/basis/db/sqlite/lib/lib.factor
+++ b/basis/db/sqlite/lib/lib.factor
@@ -133,6 +133,24 @@ ERROR: sqlite-sql-error < sql-error n string ;
 : sqlite-column-name ( handle index -- string ) sqlite3_column_name ;
 : sqlite-column-type ( handle index -- string ) sqlite3_column_type ;
 
+
+: sqlite3-column-null ( sqlite n obj -- obj/f )
+    [ sqlite3_column_type SQLITE_NULL = f ] dip ? ; inline
+
+! sqlite_column_int returns 0 for both a ``0`` and for ``NULL``
+! so call sqlite3_column_type if it's 0
+: sqlite3-column-int ( handle index -- int/f )
+    2dup sqlite3_column_int dup 0 = [ sqlite3-column-null ] [ 2nip ] if ;
+
+: sqlite3-column-int64 ( handle index -- int/f )
+    2dup sqlite3_column_int64 dup 0 = [ sqlite3-column-null ] [ 2nip ] if ;
+
+: sqlite3-column-uint64 ( handle index -- int/f )
+    2dup sqlite3_column_uint64 dup 0 = [ sqlite3-column-null ] [ 2nip ] if ;
+
+: sqlite3-column-double ( handle index -- int/f )
+    2dup sqlite3_column_double dup 0.0 = [ sqlite3-column-null ] [ 2nip ] if ;
+
 : sqlite-column-blob ( handle index -- byte-array/f )
     [ sqlite3_column_bytes ] 2keep
     pick zero? [
@@ -146,12 +164,12 @@ ERROR: sqlite-sql-error < sql-error n string ;
     {
         { +db-assigned-id+ [ sqlite3_column_int64  ] }
         { +random-id+ [ sqlite3-column-uint64 ] }
-        { INTEGER [ sqlite3_column_int ] }
-        { BIG-INTEGER [ sqlite3_column_int64 ] }
-        { SIGNED-BIG-INTEGER [ sqlite3_column_int64 ] }
+        { INTEGER [ sqlite3-column-int ] }
+        { BIG-INTEGER [ sqlite3-column-int64 ] }
+        { SIGNED-BIG-INTEGER [ sqlite3-column-int64 ] }
         { UNSIGNED-BIG-INTEGER [ sqlite3-column-uint64 ] }
-        { BOOLEAN [ sqlite3_column_int 1 = ] }
-        { DOUBLE [ sqlite3_column_double ] }
+        { BOOLEAN [ sqlite3-column-int 1 = ] }
+        { DOUBLE [ sqlite3-column-double ] }
         { TEXT [ sqlite3_column_text ] }
         { VARCHAR [ sqlite3_column_text ] }
         { DATE [ sqlite3_column_text dup [ ymd>timestamp ] when ] }
diff --git a/basis/db/sqlite/sqlite-tests.factor b/basis/db/sqlite/sqlite-tests.factor
index b5866a71f3..cd804fc765 100644
--- a/basis/db/sqlite/sqlite-tests.factor
+++ b/basis/db/sqlite/sqlite-tests.factor
@@ -172,3 +172,34 @@ watch "WATCH" {
         ] with-transaction
     ] with-db
 ] unit-test
+
+! Reported by AlexIljin
+{ f } [
+    TUPLE: num-test1 num ;
+    num-test1 "NUM_TEST" { { "num" "NUM" INTEGER } } define-persistent
+    "resource:num-test-bad.db" <sqlite-db> [
+        num-test1 ensure-table
+        num-test1 new insert-tuple
+        num-test1 new select-tuple
+    ] with-db num>>
+] unit-test
+
+{ f } [
+    TUPLE: num-test2 num ;
+    num-test2 "NUM_TEST" { { "num" "NUM" DOUBLE } } define-persistent
+    "resource:num-test-bad.db" <sqlite-db> [
+        num-test2 ensure-table
+        num-test2 new insert-tuple
+        num-test2 new select-tuple
+    ] with-db num>>
+] unit-test
+
+{ f } [
+    TUPLE: num-test3 num ;
+    num-test3 "NUM_TEST" { { "num" "NUM" BOOLEAN } } define-persistent
+    "resource:num-test-bad.db" <sqlite-db> [
+        num-test3 ensure-table
+        num-test3 new insert-tuple
+        num-test3 new select-tuple
+    ] with-db num>>
+] unit-test
-- 
2.34.1