[gnome-db] possible bug when deleting a row after selecting a blob

[Timo Kluck <tkluck infty nl>]

Dear Gnome-DB developers,

I may have stumbled upon a bug in the SQLite engine having to do with
deleting rows after having selected fields containing BLOB values.

It appears that libgda-4.0 does not delete rows if a blob has been
selected previously. In fact, it will seem to have been deleted to the
program itself, but in subsequent runs of the program, the row turns
out to have persisted.

I have attached a testcase. It is in vala, but there's a shebang so
you should be able to execute it like a script.

Is this familiar behaviour to any of you? Could you think of a
workaround? Should I put this on bugzilla?

Thanks and best regards,

Timo Kluck

#!/usr/bin/vala --pkg=libgda-4.0
/*
It appears that libgda-4.0 does not delete rows if a blob has been
selected previously. In fact, it will seem to have been deleted to
the program itself, but in subsequent runs of the program, the row
turns out to have persisted.

This file is a test case showing the problem.

   This works as expected:
     $ ./test.vala
     deleted 1 rows
     second time, 0 remained to be deleted
     $ ./test.vala
     deleted 1 rows
     second time, 0 remained to be deleted
     $ ./test.vala
     deleted 1 rows
     second time, 0 remained to be deleted
   This does not:
     $ ./test.vala select-blob
     deleted 1 rows
     second time, 0 remained to be deleted
     $ ./test.vala select-blob
     deleted 2 rows
     second time, 0 remained to be deleted
     $ ./test.vala select-blob
     deleted 3 rows
     second time, 0 remained to be deleted

 */

public int main(string[] args) {

  Gda.init ();

  var conn= Gda.Connection.open_from_string ("SQLite", 
               @"SQLite://DB_DIR=.;DB_NAME=test", null, 0);

  conn.execute_non_select_command (@"
      CREATE TABLE IF NOT EXISTS tab (
          ident integer,
          blobby blob)");
  
  conn.execute_non_select_command (@"
      INSERT INTO tab (ident,blobby) VALUES (1,X'BEEF')");

  if(args.length > 1 && args[1] == "select-blob") { 
    // OPTION 1 gives problems
    var result = conn.execute_select_command(@"
              SELECT ident, blobby FROM tab");
  } else {
    // OPTION 2 works as expected
    var result = conn.execute_select_command(@"
              SELECT ident FROM tab");
  }

  var affected_rows = conn.execute_non_select_command(@"
    DELETE FROM tab WHERE ident=1"
    );
  print(@"deleted $affected_rows rows\n");
  affected_rows = conn.execute_non_select_command(@"
    DELETE FROM tab WHERE ident=1"
    );
  // show that we actually deleted something
  print(@"second time, $affected_rows remained to be deleted\n");

  return 0;

  // now, if one opens the file in sqlite, one can see that the row is still there
  // alternatively, one can run this program several times in a row. Each invocation will
  // add a row, report it deleted, but it will still be there when the next invocation
  // deletes all rows.
}