SQLite Forum

Bug ? Same query that works in shell produces constraint violation when run by a C program with sqlite3_{prepare,bind,step}
Login

Bug ? Same query that works in shell produces constraint violation when run by a C program with sqlite3_{prepare,bind,step}

(1) By JVD66 (jason.vas.dias) on 2021-03-19 14:25:44 [link] [source]

Good day - this problem is driving me crazy and I'd much appreciate any
advice you SQLite Gurus could offer.

I am using sqlite3 -version:
3.35.2 2021-03-17 19:07:21 ea80f3002f4120f5dcee76e8779dfdc88e1e096c5cdd06904c20fd26d50c3827
for Windows x86 , and I installed the VSIX package for that version.

I am trying to insert a timestamp with microsecond precision into
a column declared with DDL:

  'timestamp	NUMERIC		NOT NULL UNIQUE PRIMARY KEY ASC'

and I have a UNIQUE index defined for it.

I want to ensure records with identical timestamps cannot be inserted,
yet strftime makes my timestamps identical, so I do (in shell), eg:
  INSERT INTO $my_table, (timestamp, ...) VALUES 
         ( strftime('%s', '2021-03-19T11:00:00.123456+00:00', 'utc') + .123456,
           ...
         ) ;

When I insert a row into the empty table with the 'sqlite3' shell, the
insert succeeds .

With exactly the same values, used in a C program that does
sqlite3_prepare(), sqlite3_bind() (which succeeds using SAME
values I used in the shell!), and sqlite3_step(), with the relevant
clause of the query being:

   strftime('%s', ?, 'utc') + ?, ...

and EXACTLY the same valid values as above being used,
the sqlite3_step() call fails with error :
  return code: 19 (SQLITE_CONSTRAINT)
and the error message:
  'NOT NULL constraint failed: ${DBNAME}.timestamp'
  
I print out the values I bind to the first (index 1) and second (index 2)
placeholders , and they are identical to the values I use in the shell query
which gets no error.

Please could anyone suggest where I might be going wrong ?

I have tried to set '.explain on' and '.echo on' in the schema I run
from my code , which creates the table, but no queries / commands
are echoed to stdout / stderr.  Is there any way I can get the sqlite 
library to print out the actual query it has prepared / is stepping through ?

I can share the C code & build command line with a human email, 
but can't post it here due to NDA.

Thanks in advance for any helpful replies !

(2) By Larry Brasfield (larrybr) on 2021-03-19 14:36:35 in reply to 1 [link] [source]

As far as you have described the problem, I would agree it ought to work. I also know that parameter binding is very fundamental and well tested. So I think we need to see your bind calls. Unbound parameters default to NULL, so that is where I would focus attention.

You can test binding to a degree with the shell. See the .param meta-command and its help. But I suspect your C code is not doing the binding quite right.

(3) By JVD66 (jason.vas.dias) on 2021-03-19 15:09:17 in reply to 2 [link] [source]

Thanks for the tip. Here is a snippet of my code doing the bind:

  // I have snprintf-ed the string parameters to a buffer,
  // and converted the fractional seconds into a double 'fract_sec' value:

  fprintf(stderr, "TIMESTAMP: %*.*s + %s %g\n", len, len, &_buf_.b[0],  
          (char*)(&_buf_.b[0] + len + 1), fract_sec);
  if ( (sql_err = sqlite3_bind_text( _insert_location_stmt_,  1, 
                                     &_buf_.b[0], len, SQLITE_STATIC )
                                   )
     != SQLITE_OK
     )
    _ERR_(ERROR_LOG | ERROR_SYSTEM, ERR_ACT_RETURN, 0, false, 
          "Failed to bind timestamp to location insert statement: '%s'",
          sqlite3_errmsg(_db_)
         );
  if ( (sql_err = sqlite3_bind_double(_insert_location_stmt_, 2,
                                         fract_sec))
        != SQLITE_OK
     )
    _ERR_(ERROR_LOG | ERROR_SYSTEM, ERR_ACT_RETURN, 0, false, 
     "Failed to bind fractional seconds to location insert statement: '%s'",
     sqlite3_errmsg(_db_)
         );
  // bind more parameters ...
  switch (sql_err = sqlite3_step(_insert_location_stmt_) )
  {case SQLITE_DONE:
    break;
   default:
    _ERR_(ERROR_LOG | ERROR_SYSTEM, ERR_ACT_RETURN, 0, false, 
         "sqlite_step failed: "
         "%u:'%s'", sql_err, sqlite3_errmsg(_db_)
         );
  }

This code fragment prints:

  TIMESTAMP: 2021-03-19T11:00:00.123456+00:00 + .123456 0.123456
  src\${my_prog}.exe[8616] : sql\${my_src}.c:375($the_function_name) :
  Error: - sqlite_step failed: 19:'NOT NULL constraint failed: 
                                   $TABLENAME.timestamp'.

Those are exactly the same values I use in the shell version of the query, which works .

Is there no way to get the VSIX winsqlite3.dll code to print out more information about the query it has prepared ?

Why don't '.echo on' or '.explain on' seem to work when run with sqlite3_exec ?

Is there any other way of getting the libraries to print more info ?

It would be nice if the VSIX package supplied debugging versions of the libraries & their *.pdb files . I am now trying to build them from source with debugging enabled.

(4) By Richard Hipp (drh) on 2021-03-19 15:12:45 in reply to 1 [link] [source]

SQLite stores floating point numbers using IEEE-754 64-bit floats. These have a precision limit of about 15.95 digits, of which SQLite promises to preserve 15 digits. But to store microseconds since 1970, you need 16 digits of accuracy.

Two approaches jump to mind:

  1. Store your timestamps as an integer number of microseconds since 1970. This is probably the easiest approach.

  2. Use the decimal extension to store unlimited precision floating point numbers as text.

See further discussion in the Floating Point Numbers documentation.

(5) By Larry Brasfield (larrybr) on 2021-03-19 15:42:20 in reply to 3 [link] [source]

Those are exactly the same values I use in the shell version of the query, which works .

(Not the problem you report but:) Your bind_text call appears to include the "+00:00" part.

I notice in your original post:

... so I do (in shell), eg: INSERT INTO $my_table, (timestamp, ...

If that $my_table is in your SQL, it may be confounding the parameter index value. Schema elements cannot be parameterized; only values can.

Is there no way to get the VSIX winsqlite3.dll code to print out more information about the query it has prepared ?

I don't think so. However, you can use a CLI shell compiled with the same options, (other than those designating a VSIX build, of course), to see what the query plan is for any given SQL.

Why don't '.echo on' or '.explain on' seem to work when run with sqlite3_exec ?

Those are CLI shell features rather than SQLite library features.

Is there any other way of getting the libraries to print more info ?

There are, but you would have to rebuild. Then you would get a lot of information. I think it is premature to bring in such heavy artillery for this issue.

It would be nice if the VSIX package supplied debugging versions of the libraries & their *.pdb files . I am now trying to build them from source with debugging enabled.

I doubt this is a bug. Have you tried the exact same SQL in the shell after using .param to set symbolic parameter values? My bet is that will work, and focus can then concentrate on how your binding differs.

(6) By JVD66 (jason.vas.dias) on 2021-03-21 14:40:05 in reply to 1 [link] [source]

Thanks to all who replied!

I have confirmed this is definitely a bug with the Window VSIX version.

This program compiles and runs without errors under Linux ,
but always produces an error when run under windows:


    /* sqlite3_windows_strftime_bind_bug.c:
     *
     * Program to demonstrate sqlite3 bug with binding parameters to strftime()
     * on the windows platform. This compiles and runs fine under Linux, with
     * no errors, but under Windows, when compiled with eg.:
     *
     *    $ CL sqlite3_windows_strftime_bind_bug.c /link winsqlite3.lib ucrt.lib shell32.lib kernel32.lib
     *
     * (when the $PATH, $LIB, and $INCLUDE environment variables are set, eg. by a run
     *  of 'call "C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\VC\Auxiliary\Build\vcvarsall.bat" x86'
     * ).
     */
    #ifdef __linux__
    # include <sqlite3.h>
    #else
    # include <winsqlite3.h>
    #endif
    #include <stdio.h>
    #include <stdlib.h>

    static
    const char
      _db_schema_stmt_[] =
      "CREATE TABLE ts_log "
      "( ts NUMERIC	NOT NULL UNIQUE PRIMARY KEY ASC\n"
      // If one replaces the above declaration with:
      // "ts INTEGER NOT NULL UNIQUE PRIMARY KEY " ,
      // then no error is reported, (the insert succeeds),
      // but the ts column ends up with the wholly incorrect value '1' !
      ", st TEXT"
      ");"
      ;

    static
    const char
      _example_timestamp_[] =
      "2021-03-19T11:00:00.123456+00:00"
      ;

    static
    const char
      _example_text_[] =
      "Some text associated with timestamp"
      ;

    static
    const char
      _db_name_[]=
      "sqlite3_windows_strftime_bind_bug.db"
      ;

    static
    const char
      _ts_log_insert_stmt_[] =
      "INSERT INTO ts_log (ts, st) VALUES "
      "( ( (1000000 * strftime('%s', ?, 'utc')) + ? ), ? );"
      ;

    static
    sqlite3
      *_db_ = NULL;

    static const char
      *_program_name_=NULL;

    #define _ERR_( _RV_, _FMT_, ... )                         \
      do { fprintf(stderr, "%s: " _FMT_ ".\n",_program_name_,##__VA_ARGS__); \
           return ( _RV_ );                                   \
         } while(0)

    static char
      _buf_[4096] ={0};

    int main( int argc, const char *const *argv, const char *const *envp )
    { char
       *emsg_w=NULL;
      register const char
       *emsg = NULL;
      register unsigned int
        sql_err;

      _program_name_ = argv[0];

      if ( ((sql_err = sqlite3_open_v2( _db_name_, &_db_, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, NULL))
           != SQLITE_OK
           )
         ||(_db_ == NULL)
         )
        _ERR_(1, "Failed to open SQLite Database: %u:'%s'"
             , sql_err, sqlite3_errmsg(_db_)
             );

      if ( (sql_err = sqlite3_exec(_db_, _db_schema_stmt_, NULL, NULL, &emsg_w)) != SQLITE_OK)
      { if( emsg_w == NULL)
          emsg = sqlite3_errmsg(_db_);
        else
          emsg = emsg_w;
        _ERR_(1, "Schema creation query failed: %u:'%s'"
             , sql_err, emsg ? emsg : ""
             );
      }

      sqlite3_stmt
        *insert_stmt = NULL;

      if ((sql_err =
             sqlite3_prepare_v2
             ( _db_
             , &_ts_log_insert_stmt_[0]
             , sizeof(_ts_log_insert_stmt_)-1
             , &insert_stmt
             , NULL
             )
          ) != SQLITE_OK
         )
        _ERR_(1, "Failed to prepare ts_log insert statement: %u:'%s'"
             , sql_err
             , sqlite3_errmsg(_db_)
             );

      register const char
       *ps = _example_timestamp_
      ,*p;
      for(p=ps; *p && (*p != '.'); ++p);
      if (*p != '.')
        _ERR_(1, "Oops, _example_timestamp_ does not contain '.' ?!?");
      ps = p+1;
      for(p=ps; *p && (*p != '+') && (*p != '-') && (*p != 'Z'); ++p)
        _buf_[p-ps] = *p;
      _buf_[p-ps] = '\0';

      unsigned int
        fractional_second = atoi(&_buf_[0]);

      fprintf
      ( stderr, "TIMESTAMP: %s\n" "FRACTIONAL SECOND: %u\n"
      , _example_timestamp_, fractional_second
      );

      if ( (sql_err = sqlite3_bind_text( insert_stmt, 1,  _example_timestamp_, sizeof(_example_timestamp_)-1, SQLITE_STATIC ))
          != SQLITE_OK
         )
        _ERR_(1, "Failed to bind example timestamp to insert statement: %u:'%s'"
             , sql_err, sqlite3_errmsg(_db_)
             );
      if ( (sql_err = sqlite3_bind_int ( insert_stmt, 2, fractional_second))
          != SQLITE_OK
         )
        _ERR_(1, "Failed to bind fractional seconds to location insert statement: '%s'",
             sqlite3_errmsg(_db_)
             );
      if ( (sql_err = sqlite3_bind_text( insert_stmt, 3, _example_text_, sizeof(_example_text_)-1, SQLITE_STATIC ))
          != SQLITE_OK
         )
        _ERR_(1, "Failed to bind example text to insert statement: %u:'%s'"
             , sql_err, sqlite3_errmsg(_db_)
             );

      switch( sql_err = sqlite3_step( insert_stmt ) )
      {case SQLITE_DONE:
        fprintf(stderr, "Insert Succeeded!\n");
        return 0;
       default:
        _ERR_(1, "sqlite3_step failed on insert statement: %u:'%s'"
             , sql_err
             , sqlite3_errmsg(_db_)
             );
        break;
      }
      return 1;
    }
    #ifndef __linux__
    void
      mainCRTStartup ( void )
    // this is a startup routine suitable for this program only.
    { const char
       *argv[2] ={ "sqlite3_windows_strftime_bind_bug.exe", NULL };
      _exit( main( 1, argv, (const char *const*)environ) );
    }
    #endif




Compile & Run under Windows:

    $  CL sqlite3_windows_strftime_bind_bug.c \
       /link winsqlite3.lib ucrt.lib shell32.lib kernel32.lib
    
(when the $LIB and $INCLUDE environment variables are set by a run of 
 Visual Studio 2019 Community Editions's 'vcvarsall.bat x86' .
)

    $ ./sqlite3_windows_strftime_bind_bug.exe
    TIMESTAMP: 2021-03-19T11:00:00.123456+00:00
    FRACTIONAL SECOND: 123456
    sqlite3_windows_strftime_bind_bug.exe: sqlite3_step failed on insert \ 
    statement: 19:'NOT NULL constraint failed: ts_log.ts'.

Compile & Run under Linux :

    $ gcc -o sqlite_windows_strftime_bug sqlite_windows_strftime_bug.c -lsqlite3
    $ ./sqlite_windows_strftime_bug 
    TIMESTAMP: 2021-03-19T11:00:00.123456+00:00
    FRACTIONAL SECOND: 123456
    Insert Succeeded!

As noted in the code, if the declaration of the timestamp column is changed
from:
    'ts NUMERIC	NOT NULL UNIQUE PRIMARY KEY ASC'
to  'ts INTEGER NOT NULL UNIQUE PRIMARY KEY'
(making it into the ROWID column), then the value '1' gets inserted into
 it under Windows, because it appears that under Windows, the value
 returned by strfime('%s', $value, 'utc') IS ALWAYS NULL .

This is a bug, IMHO - if strftime() is provided under Windows, then it should
work as documented and not return NULL .

(7) By JVD66 (jason.vas.dias) on 2021-03-21 14:52:24 in reply to 4 [link] [source]

Thanks, Richard - good point!
My code now treats the timestamp as a 64-bit integer, multiplies it by 
1,000,000, and adds the microseconds.

(8) By JVD66 (jason.vas.dias) on 2021-03-21 14:59:44 in reply to 5 [link] [source]

Thanks, Larry - RE:> (Not the problem you report but:) > Your bind_text call appears to include the "+00:00" part

Yes, the '+HH:MM' Timezone offset is part of the ISO 8601 standard format that strftime claims to support, and which the server my code receives the the timestamps from produces them in.

No, I am not using any $my_table parameters in the actual query.

I did try using .param in the shell, and that does work OK .

But see the my last comment - it includes a complete program that compiles and runs fine under Linux, but under Windows, the strftime('%s', ?, 'utc') + ? call is ALWAYS returning NULL .

(9) By JVD66 (jason.vas.dias) on 2021-03-21 15:12:30 in reply to 1 [link] [source]

I've had to produce a workaround under Windows, to avoid calling strftime() at all, which I copy here, in case anyone else is affected by this bug before it
gets fixed (which I hope one day it will be!) - this function parses ISO-8601
timestamps into 64-bit numbers of microseconds :


/* timestamp_parse.h:
 *
 *  To workaround bug in SQLite's 'strftime', this provides a function to parse timestamps in the same way,
 *  so that we can avoid using strftime.
 *
 *  The function expects timestamps to be of the form:
 *
 *    YYYY('-'or'/')MM('-'or'/')DD('T'or' ')HH':'MM':'SS'.'FFFFFF('+'or'-')HH:MM
 *
 *  The largest timestamp that does not return an error on Windows is:
 *    "3001/01/19@07:59:59.999999+00:00"
 *  ( "3001/01/19@08:00:00+00:00" returns an error ).
 *
 * Jason Vas Dias <jason.vas.dias@gmail.com> March 2021
 */

#ifndef _TIMESTAMP_PARSE_H_
#define _TIMESTAMP_PARSE_H_

#ifndef _INC_TIME
# include <time.h>
#endif

typedef unsigned long long U64_t;

typedef unsigned int U32_t;

extern int atoi( const char *);

static
char _ts_buf_[64] = {0};

static inline
U64_t timestamp_to_utc_ms_since_1970 ( register const char *ts )
{ if ( ts == NULL )
    return 0;
  register const char
   *p = ts;
  register char
   *np = &_ts_buf_[0];
  register U64_t
    epoch_second = 0;
  struct tm
    tm = {0};
  U32_t n_ms=0;
  int *flds[7] =
    { &tm.tm_year, &tm.tm_mon, &tm.tm_mday
    , &tm.tm_hour, &tm.tm_min, &tm.tm_sec
    , &n_ms
    }
  , tz_hour=0, tz_min=0;
  register int
   **fld = &flds[0];
  for(; *p && (np < &_ts_buf_[64]) && (fld < &flds[7]); ++p )
  { if(!((*p >= '0') && (*p <= '9')))
    { *np = '\0';
      if ( (fld == &flds[6])
         &&(np > &_ts_buf_[6])
         )
      { np = &_ts_buf_[6];
        *np = '\0';
      }
      if( np > &_ts_buf_[0])
        **fld = atoi(_ts_buf_);
      np = &_ts_buf_[0];
      if ( (fld == &flds[6])                // we need to support case where there is NO fractional second field
         ||(*p == '+')                      // & detect beginning of timezone offset field
         ||((fld > &flds[2]) &&(*p == '-')) // '-' not valid separator for time fields!
         ||(*p == 'Z')
         )
        break;
      fld += 1;
    }else
    { *np = *p;
      np += 1;
    }
  }
  if ( tm.tm_year < 1900 )
    return 0;
  tm.tm_year -= 1900;
  if ( tm.tm_mon < 1)
    return 0;
  tm.tm_mon -= 1;
  if ( (tm.tm_mon  > 11)
     ||(tm.tm_mday > 31)
     ||(tm.tm_hour > 23)
     ||(tm.tm_min  > 59)
     ||(tm.tm_sec  > 59)
     )
    return 0;
  epoch_second = _mktime64( &tm );
  if ( (epoch_second >= 0xfffffffffff0be3b) || (GetLastError() != 0 ))
    return 0;
  register bool
   is_negative = false;
  flds[0] = &tz_hour;
  flds[1] = &tz_min;
  fld = &flds[0];
  switch (*p)
  {case '\0':
   case 'Z':
     return (epoch_second * 1000000) + n_ms;
   case '-':
     is_negative = true;
     // fall through:
   case '+':
     for(++p, np=&_ts_buf_[0]; (fld < &flds[2]); ++p)
     { if(!((*p >= '0') && (*p <= '9')))
       { *np = '\0';
         if( np > &_ts_buf_[0] )
           **fld = atoi(_ts_buf_);
         fld += 1;
         np = &_ts_buf_[0];
       }else
       { *np = *p;
         np += 1;
       }
       if( !*p )
         break;
     }
     if( is_negative )
       epoch_second += (tz_hour * 3600) + (tz_min * 60);
     else
       epoch_second -= (tz_hour * 3600) + (tz_min * 60);
     break;
    default:
     break;
  }
  return ((epoch_second * 1000000) + n_ms);
}

#endif

(10) By Keith Medcalf (kmedcalf) on 2021-03-21 17:48:38 in reply to 1 [link] [source]

Although this has no effect on your problem, why are you adjusting a localized timestring using the 'utc' modifier? (unless, of course, the bug is in the bit of code that determines the localtime offset)

Localized timestrings are already expressed in utc so the application of the 'utc' modifier will result in an incorrect time.

(11) By JVD66 (jason.vas.dias) on 2021-03-21 18:18:51 in reply to 10 [link] [source]

Thanks Keith -

I am getting the ISO-8601 timestamp string from an external networked server, which is relaying them from many hosts which may be in different timezones - so I want to store the timestamps in UTC, and so am applying the offset to express the time in UTC.

When it works, this is what strftime( '%s', $X, 'utc') does - it converts the timestamp to a UNIX epoch second, and then, IFF the string specifies a UTC offset, and not 'Z', it adds / subtracts the offset converted to seconds to the epoch seconds value.

In the shell:

sqlite> select strftime('%s', '2021-03-21T18:00:00+00:00', 'utc'); 1616349600 

sqlite> select strftime('%s', '2021-03-21T18:00:00+01:00', 'utc'); 1616346000

(12) By Keith Medcalf (kmedcalf) on 2021-03-21 18:58:40 in reply to 11 [source]

Yes, you are correct -- the UTC modified does nothing for a localized timestring. When a localized timestring is parsed a flag is set so that the 'utc' operator does nothing.