SQLite Forum

Bug ? Same query that works in shell produces constraint violation when run by a C program with sqlite3_{prepare,bind,step}
Login
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 .