GNATCOLL SQLite3 vs Ada.Calendar.Time

[Stephen Leake <stephen_leake@stephe-leake.org>]

I've run into some very odd behavior using GNATCOLL to interface to an SQLite3 database, and querying on a DATETIME field. Here's a very simple program that shows the problem:

-- create_db.sql
-- Create a table, fill it with some test data
-- SQLite3 syntax

CREATE TABLE Title
(ID       INTEGER PRIMARY KEY,
 Modified DATETIME,
 Title    TEXT);

INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "2001"); 
INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundation");
INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundations Edge");
INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundation and Earth");
INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundation and Empire");

-- end of file


-- debug_db_times.adb
--  Abstract :
--
--  demonstrate a bug in GNATCOLL/sql related to time field handling

pragma License (GPL);

with Ada.Text_IO; use Ada.Text_IO;
with Ada.Calendar;
with GNATCOLL.SQL.Exec; use GNATCOLL.SQL.Exec;
with GNATCOLL.SQL.Sqlite;
with GNATCOLL.Traces;
procedure Debug_DB_Times
is
   DB     : Database_Connection := Build_Connection (GNATCOLL.SQL.Sqlite.Setup ("debug_db_times.db"));
   Cursor : Forward_Cursor;

   Before_Created : constant Ada.Calendar.Time := Ada.Calendar.Time_Of (1999, 1, 1);
   Jan_1_2000 : constant Ada.Calendar.Time := Ada.Calendar.Time_Of (2000, 1, 1);

   procedure Put_Current
   is begin
      for I  in Field_Index'(0) .. 2 loop
         Put (Cursor.Value (I));
         if I /= 2 then
            Put (", ");
         end if;
      end loop;
      New_Line;
   end Put_Current;
begin
   GNATCOLL.Traces.Parse_Config_File
     (Filename         => "debug_db_times.trace",
      Force_Activation => False);

   Put_Line ("find all:");
   Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title ORDER BY ID");
   loop
      exit when not Cursor.Has_Row;
      Put_Current;
      Cursor.Next;
   end loop;

   Put_Line ("find modified time (long before created):");
   Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID", (1 => +Before_Created));
   loop
      exit when not Cursor.Has_Row;
      Put_Current;
      Cursor.Next;
   end loop;

   Put_Line ("find modified time (1 day before created):");
   Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID", (1 => +Jan_1_2000));
   loop
      exit when not Cursor.Has_Row;
      Put_Current;
      Cursor.Next;
   end loop;

   Put_Line ("find modified string:");
   Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID",
          (1 => +"2000-01-01 00:00:00"));
   loop
      exit when not Cursor.Has_Row;
      Put_Current;
      Cursor.Next;
   end loop;
end Debug_DB_Times;
-- end of file

-- debug_db_times.trace
SQL=TRUE
SQL.SELECT=TRUE
-- end of file

Running on Windows 8.1 with GNAT GPL 2016:

sqlite3 -echo -init create_db.sql debug_db_times.db ".quit"
gprbuild -p -P debug_db_times.gpr debug_db_times.adb
./debug_db_times.exe
find all:
[SQL] Connecting to sqlite database debug_db_times.db ()
[SQL] PRAGMA foreign_keys=ON 
[SQL.SELECT] SELECT ID, Modified, Title FROM Title ORDER BY ID 
1, 2000-01-02 00:00:00, 2001
2, 2000-01-02 00:00:00, Foundation
3, 2000-01-02 00:00:00, Foundations Edge
4, 2000-01-02 00:00:00, Foundation and Earth
5, 2000-01-02 00:00:00, Foundation and Empire
find modified time (long before created):
[SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID, 1999-01-01 06:00:00 
1, 2000-01-02 00:00:00, 2001
find modified time (1 day before created):
[SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID, 2000-01-01 06:00:00 
1, 2000-01-02 00:00:00, 2001
find modified string:
[SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID, 2000-01-01 00:00:00 
1, 2000-01-02 00:00:00, 2001
2, 2000-01-02 00:00:00, Foundation
3, 2000-01-02 00:00:00, Foundations Edge
4, 2000-01-02 00:00:00, Foundation and Earth
5, 2000-01-02 00:00:00, Foundation and Empire


When I pass an Ada.Calendar.Time as a parameter, I only get one record back. When I pass a string as a parameter, I get all the records back.

Note that Ada.Calendar.Time messes with time zones in an undocumented way (I happen to be in GMT+6); that's not a problem, since I figured out how to deal with it in my full program.

In my full program, I have similar but different behavior, and always passing a string doesn't fix it.

One solution would be to replace the "DATETIME" field type in the .sql file with "TEXT", always pass strings for times, and rely on lexicographic sorting for time comparison. But that seems extreme.

Any ideas what's going on here?