SQLite User Forum

update-from on without rowid virtual tables
Login
Chronological

update-from on without rowid virtual tables

(1) By Kevin Martin (kev82) on 2020-10-10 20:15:21 [source]

Hi,

I am trying out the new update-from syntax in the latest release, 3.33, and am struggling a bit with a virtual table that is declared as 'without rowid'. Normal updates on the table seem to work ok like they did previously, but using the new syntax triggers an error in my code, which I think is coming from sqlite calling xRowid on the table.

Reading the virtual table document, I thought that I would be ok to supply an xRowid for a without rowid virtual table (I use the same module for tables that do and don't have a rowid), but that it wouldn't be called if the vtab defined the table instance as without rowid.

I tried running an 'update from' on a normal without rowid table, and it seemed to work, so I think I might have just screwed something up. If anyone has any ideas, would be great.

Thanks, Kev

(2) By Gunter Hick (gunter_hick) on 2020-10-12 06:09:59 in reply to 1 [link] [source] 

I don't think SQLite will call your xRowid method without a good reason (like mentioning the rowid within your SQL), but you would need to show your schema, your exact statement and the explain output to be sure.

(3) By Dan Kennedy (dan) on 2020-10-12 14:33:40 in reply to 1 [link] [source]

A bug. Thanks for reporting it. Should now be fixed here:

https://sqlite.org/src/info/bcb0bc6a7b7006f0

Dan

(4.1) By Kevin Martin (kev82) on 2020-10-14 07:07:14 edited from 4.0 in reply to 3 [link] [source] 

Thanks for the patch, it gets further, but am still struggling a bit as it now looks like the xUpdate may not be being passed the arguments I am expecting.

This could be a problem on our end, and I will debug it tomorrow to see if I can find out more details.

If it's helpful for now, please see below for the results of the explain before and after applying the patch, as well as on a simple update that works. 

In both cases model_options_vt is an eponymous virtual table with a "name", "old_name" and "value" column where the primary key is "name".

=== Before patch ===

[
  {
    "sql":"create table motable as select 'XMLFilePrecision' as name, '8' as value;",
    "time":0.001,
    "cols":[],
    "results":[
    ]
  },
  {
    "sql":"\n\nexplain update model_options_vt as mvt set value = t.value from motable as t where mvt.name = t.name;",
    "time":0,
    "cols":["addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment"],
    "results":[
      [0, "Init", 0, 39, 0, "<Null>", 0, "Start at 39"],
      [1, "OpenEphemeral", 2, 5, 0, "<Null>", 0, "nColumn=5"],
      [2, "VOpen", 3, 0, 0, "vtab:1AD1236EFE0", 0, "<Null>"],
      [3, "OpenRead", 4, 4, 0, "2", 0, "root=4 iDb=0; motable"],
      [4, "Integer", 0, 11, 0, "<Null>", 0, "r[11]=0"],
      [5, "Integer", 0, 12, 0, "<Null>", 0, "r[12]=0"],
      [6, "VFilter", 3, 29, 11, "<Null>", 0, "iplan=r[11] zplan=''"],
      [7, "Once", 0, 16, 0, "<Null>", 0, "<Null>"],
      [8, "OpenAutoindex", 5, 3, 0, "k(3,B,,)", 0, "nColumn=3; for motable"],
      [9, "Rewind", 4, 16, 0, "<Null>", 0, "<Null>"],
      [10, "Column", 4, 0, 14, "<Null>", 0, "r[14]=motable.name"],
      [11, "Column", 4, 1, 15, "<Null>", 0, "r[15]=motable.value"],
      [12, "Rowid", 4, 16, 0, "<Null>", 0, "r[16]=rowid"],
      [13, "MakeRecord", 14, 3, 13, "<Null>", 0, "r[13]=mkrec(r[14..16])"],
      [14, "IdxInsert", 5, 13, 0, "<Null>", 16, "key=r[13]"],
      [15, "Next", 4, 10, 0, "<Null>", 3, "<Null>"],
      [16, "VColumn", 3, 0, 17, "<Null>", 0, "r[17]=vcolumn(0); model_options_vt.name"],
      [17, "SeekGE", 5, 28, 17, "1", 0, "key=r[17]"],
      [18, "IdxGT", 5, 28, 17, "1", 0, "key=r[17]"],
      [19, "Rowid", 3, 18, 0, "<Null>", 0, "r[18]=rowid"],
      [20, "Rowid", 3, 19, 0, "<Null>", 0, "r[19]=rowid"],
      [21, "VColumn", 3, 0, 20, "<Null>", 0, "r[20]=vcolumn(0); model_options_vt.name"],
      [22, "VColumn", 3, 1, 21, "<Null>", 0, "r[21]=vcolumn(1); model_options_vt.old_name"],
      [23, "Column", 5, 1, 22, "<Null>", 0, "r[22]=motable.value"],
      [24, "MakeRecord", 18, 5, 13, "<Null>", 0, "r[13]=mkrec(r[18..22])"],
      [25, "NewRowid", 2, 23, 0, "<Null>", 0, "r[23]=rowid"],
      [26, "Insert", 2, 13, 23, "<Null>", 8, "intkey=r[23] data=r[13]"],
      [27, "Next", 5, 18, 0, "<Null>", 0, "<Null>"],
      [28, "VNext", 3, 7, 0, "<Null>", 0, "<Null>"],
      [29, "Rewind", 2, 37, 0, "<Null>", 0, "<Null>"],
      [30, "Column", 2, 0, 6, "<Null>", 0, "r[6]="],
      [31, "Column", 2, 1, 7, "<Null>", 0, "r[7]="],
      [32, "Column", 2, 2, 8, "<Null>", 0, "r[8]="],
      [33, "Column", 2, 3, 9, "<Null>", 0, "r[9]="],
      [34, "Column", 2, 4, 10, "<Null>", 0, "r[10]="],
      [35, "VUpdate", 0, 5, 6, "vtab:1AD1236EFE0", 2, "data=r[6..10]"],
      [36, "Next", 2, 30, 0, "<Null>", 0, "<Null>"],
      [37, "Close", 2, 0, 0, "<Null>", 0, "<Null>"],
      [38, "Halt", 0, 0, 0, "<Null>", 0, "<Null>"],
      [39, "Transaction", 0, 1, 2, "0", 1, "usesStmtJournal=0"],
      [40, "VBegin", 0, 0, 0, "vtab:1AD1236EFE0", 0, "<Null>"],
      [41, "Goto", 0, 1, 0, "<Null>", 0, "<Null>"]
    ]
  },
  {
    "sql":"\n\nupdate model_options_vt as mvt set value = t.value from motable as t where mvt.name = t.name ;",
    "time":0,
    "error":"Problem executing statement 'Failed to get rowid: Tried to access rowid on a table that doesn't have one'"
  }
]

=== After Patch ===

[
  {
    "sql":"create table motable as select 'XMLFilePrecision' as name, '8' as value;",
    "time":0,
    "cols":[],
    "results":[
    ]
  },
  {
    "sql":"\n\nexplain update model_options_vt as mvt set value = t.value from motable as t where mvt.name = t.name;",
    "time":0,
    "cols":["addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment"],
    "results":[
      [0, "Init", 0, 39, 0, "<Null>", 0, "Start at 39"],
      [1, "OpenEphemeral", 2, 5, 0, "<Null>", 0, "nColumn=5"],
      [2, "VOpen", 3, 0, 0, "vtab:26FF01314D0", 0, "<Null>"],
      [3, "OpenRead", 4, 4, 0, "2", 0, "root=4 iDb=0; motable"],
      [4, "Integer", 0, 11, 0, "<Null>", 0, "r[11]=0"],
      [5, "Integer", 0, 12, 0, "<Null>", 0, "r[12]=0"],
      [6, "VFilter", 3, 29, 11, "<Null>", 0, "iplan=r[11] zplan=''"],
      [7, "Once", 0, 16, 0, "<Null>", 0, "<Null>"],
      [8, "OpenAutoindex", 5, 3, 0, "k(3,B,,)", 0, "nColumn=3; for motable"],
      [9, "Rewind", 4, 16, 0, "<Null>", 0, "<Null>"],
      [10, "Column", 4, 0, 14, "<Null>", 0, "r[14]=motable.name"],
      [11, "Column", 4, 1, 15, "<Null>", 0, "r[15]=motable.value"],
      [12, "Rowid", 4, 16, 0, "<Null>", 0, "r[16]=rowid"],
      [13, "MakeRecord", 14, 3, 13, "<Null>", 0, "r[13]=mkrec(r[14..16])"],
      [14, "IdxInsert", 5, 13, 0, "<Null>", 16, "key=r[13]"],
      [15, "Next", 4, 10, 0, "<Null>", 3, "<Null>"],
      [16, "VColumn", 3, 0, 17, "<Null>", 0, "r[17]=vcolumn(0); model_options_vt.name"],
      [17, "SeekGE", 5, 28, 17, "1", 0, "key=r[17]"],
      [18, "IdxGT", 5, 28, 17, "1", 0, "key=r[17]"],
      [19, "VColumn", 3, 0, 18, "<Null>", 0, "r[18]=vcolumn(0); model_options_vt.name"],
      [20, "VColumn", 3, 0, 19, "<Null>", 0, "r[19]=vcolumn(0); model_options_vt.name"],
      [21, "VColumn", 3, 0, 20, "<Null>", 0, "r[20]=vcolumn(0); model_options_vt.name"],
      [22, "VColumn", 3, 1, 21, "<Null>", 0, "r[21]=vcolumn(1); model_options_vt.old_name"],
      [23, "Column", 5, 1, 22, "<Null>", 0, "r[22]=motable.value"],
      [24, "MakeRecord", 18, 5, 13, "<Null>", 0, "r[13]=mkrec(r[18..22])"],
      [25, "NewRowid", 2, 23, 0, "<Null>", 0, "r[23]=rowid"],
      [26, "Insert", 2, 13, 23, "<Null>", 8, "intkey=r[23] data=r[13]"],
      [27, "Next", 5, 18, 0, "<Null>", 0, "<Null>"],
      [28, "VNext", 3, 7, 0, "<Null>", 0, "<Null>"],
      [29, "Rewind", 2, 37, 0, "<Null>", 0, "<Null>"],
      [30, "Column", 2, 0, 6, "<Null>", 0, "r[6]="],
      [31, "Column", 2, 1, 7, "<Null>", 0, "r[7]="],
      [32, "Column", 2, 2, 8, "<Null>", 0, "r[8]="],
      [33, "Column", 2, 3, 9, "<Null>", 0, "r[9]="],
      [34, "Column", 2, 4, 10, "<Null>", 0, "r[10]="],
      [35, "VUpdate", 0, 5, 6, "vtab:26FF01314D0", 2, "data=r[6..10]"],
      [36, "Next", 2, 30, 0, "<Null>", 0, "<Null>"],
      [37, "Close", 2, 0, 0, "<Null>", 0, "<Null>"],
      [38, "Halt", 0, 0, 0, "<Null>", 0, "<Null>"],
      [39, "Transaction", 0, 1, 2, "0", 1, "usesStmtJournal=0"],
      [40, "VBegin", 0, 0, 0, "vtab:26FF01314D0", 0, "<Null>"],
      [41, "Goto", 0, 1, 0, "<Null>", 0, "<Null>"]
    ]
  },
  {
    "sql":"\n\nupdate model_options_vt as mvt set value = t.value from motable as t where mvt.name = t.name ;",
    "time":0.002,
    "error":"Problem executing statement 'Caught exception updating table: Unable to convert 'XMLFilePrecision' to type 'int' for config. value 'XMLFilePrecision''"
  }
]

=== A simple update ===

[
  {
    "sql":"\nexplain update model_options_vt set value='8' where name='XMLFilePrecision';",
    "time":0,
    "cols":["addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment"],
    "results":[
      [0, "Init", 0, 15, 0, "<Null>", 0, "Start at 15"],
      [1, "Noop", 2, 5, 0, "<Null>", 0, "<Null>"],
      [2, "VOpen", 1, 0, 0, "vtab:26FA5A9C720", 0, "<Null>"],
      [3, "String8", 0, 15, 0, "XMLFilePrecision", 0, "r[15]='XMLFilePrecision'"],
      [4, "Integer", 1, 13, 0, "<Null>", 0, "r[13]=1"],
      [5, "Integer", 1, 14, 0, "<Null>", 0, "r[14]=1"],
      [6, "VFilter", 1, 14, 13, "<Null>", 0, "iplan=r[13] zplan=''"],
      [7, "VColumn", 1, 0, 8, "<Null>", 1, "r[8]=vcolumn(0)"],
      [8, "VColumn", 1, 1, 9, "<Null>", 1, "r[9]=vcolumn(1)"],
      [9, "String8", 0, 10, 0, "8", 0, "r[10]='8'"],
      [10, "VColumn", 1, 0, 6, "<Null>", 0, "r[6]=vcolumn(0)"],
      [11, "SCopy", 8, 7, 0, "<Null>", 0, "r[7]=r[8]"],
      [12, "Close", 1, 0, 0, "<Null>", 0, "<Null>"],
      [13, "VUpdate", 0, 5, 6, "vtab:26FA5A9C720", 2, "data=r[6..10]"],
      [14, "Halt", 0, 0, 0, "<Null>", 0, "<Null>"],
      [15, "Transaction", 0, 1, 1, "0", 1, "usesStmtJournal=0"],
      [16, "VBegin", 0, 0, 0, "vtab:26FA5A9C720", 0, "<Null>"],
      [17, "Goto", 0, 1, 0, "<Null>", 0, "<Null>"]
    ]
  },
  {
    "sql":"\nupdate model_options_vt set value='8' where name='XMLFilePrecision';",
    "time":0,
    "cols":[],
    "results":[
    ]
  }
]

(5) By Kevin Martin (kev82) on 2020-10-14 08:12:29 in reply to 3 [link] [source]

Thanks for the quick patch, I have incorporated and still can't get it to work (see other post).

I've done some debugging now, and think the problem is that sqlite3_value_nochange does not appear to be working as I am expecting in the case of the update from.

The vtab is declared as

create table t (name text, old_name text, value text, primary key(name)) without rowid

Specifically, when I do the normal update which works, I get the following args to xUpdate

Update arg 0 (nochange: 0): 'XMLFilePrecision'
Update arg 1 (nochange: 1): NULL
Update arg 2 (nochange: 1): NULL
Update arg 3 (nochange: 1): NULL
Update arg 4 (nochange: 0): '8'

(Note, this is an update with arg1 as null, see https://marc.info/?l=sqlite-users&m=156570172529056&w=2)

When I do the update from, I get the following args to xUpdate

Update arg 0 (nochange: 0): 'XMLFilePrecision'
Update arg 1 (nochange: 0): 'XMLFilePrecision'
Update arg 2 (nochange: 0): 'XMLFilePrecision'
Update arg 3 (nochange: 0): ''
Update arg 4 (nochange: 0): '8'

The args themselves are correct in value - as in the new value of the row would be correct, but I am expecting sqlite3_value_nochange to return true for both args 2 and 3, because those columns haven't changed by the update.

(6.1) By Gunter Hick (gunter_hick) on 2020-10-14 09:14:19 edited from 6.0 in reply to 5 [link] [source] 

Reading https://sqlite.org/c3ref/vtab_nochange.html and https://sqlite.org/vtab.html#the_xupdate_method would suggest that you might need to query sqlite3_vtab_nochange() in your xColumn function and, if it returns true, refrain from setting a value, which will, in turn, force sqlite3_value_nochange() to return true for that column.

Remember that the arguments to xUpdate are:
- old rowid/primary key
- new rowid/primary key
- list of field values

If you are already handling sqlite3_vtab_nochange() correctly, then the "not changed" NULL value seems to be getting lost between generating the xUpdate arguments (Instructions 19-26 in the second code listing) and actually calling xUpdate (Instructions 30-35), i.e. in the internal "work to do" table.

BTW: Note that Instructions 19-20 attempt to call xRowid in the first listing, but are replaced by retrieving the primary key in the second listing.

(7) By Kevin Martin (kev82) on 2020-10-14 10:47:19 in reply to 6.1 [link] [source]

I had forgotten how it worked (not looked at this in over a year) and now you've said about the sqlite_vtab_nochange, I've gone and checked the return value from it inside the xColumn method.

In the case of the normal update, the query appears to be using the index on the primary key and I just get this as the full output:

sqlite3_vtab_nochange for column 0 returned true
sqlite3_vtab_nochange for column 1 returned true
sqlite3_vtab_nochange for column 0 returned false

In the case of the update from, it looks like it's not using the index, but instead iterating over the table as I get loads of output like:

sqlite3_vtab_nochange for column 0 returned false

But the in the middle I critically get

sqlite3_vtab_nochange for column 0 returned false
sqlite3_vtab_nochange for column 1 returned false

So rather than the nochange value being lost in between the instructions, it looks like it may never be correct in the first place.

(8) By Gunter Hick (gunter_hick) on 2020-10-14 12:37:40 in reply to 7 [link] [source]

So judging from the bytecode examples, the VColumn opcodes in 21-22 should be generated with a P5 of 1 instead of 0, just like the corresponding calls in the direct update.

The update from is using a full table scan and checking the primary key against the motable, instead of scanning the motable and doing a lookup in the virtual table.

What are the constraints the xBestIndex function is being called with? And what are you returning as cost and estimated number of rows?

IMHO you should be returning an estimatedCost of 1, an estimatedRows of 1 and setting the SQLITE_INDEX_SCAN_UNIQUE in idxFlags whenever there is a usable constraint on the primary key, and the number of rows in the table if not.

(9) By Kevin Martin (kev82) on 2020-10-14 13:06:05 in reply to 8 [link] [source]

So on the query plan for the update from, I think you're correct, in the case of an equality constraint on "name" the code was:

info->estimatedRows = 1;
info->idxFlags = SQLITE_INDEX_SCAN_UNIQUE;

Adding

info->estimatedCost = 1;

Causes it to use an alternate query plan as I now only get this output from xColumn, so it's not doing a full scan any more.

sqlite3_vtab_nochange for column 0 returned false
sqlite3_vtab_nochange for column 0 returned false
sqlite3_vtab_nochange for column 0 returned false
sqlite3_vtab_nochange for column 1 returned false

I think when we wrote this, we may have mistakenly thought we didn't have to set the cost as saying there was one row would be enough - clearly not! I have raised an issue to review our other table implementations, it's really useful we've caught that, thanks!

Hopefully we'll hear from one of the devs soon on the sqlite_vtab_nochange issue.

(10) By Gunter Hick (gunter_hick) on 2020-10-14 13:39:55 in reply to 9 [link] [source] 

The estimatedCost field is found in the original virtual table interface, so it probably takes precedence in finding a total cost estimate.

The output fields are initialized to (SQLite Release 3.24):

pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2; // 4.99999e98
pIdxInfo->estimatedRows = 25;

So not changing estimatedCost will tend to discourage using this particular plan.

(11) By Richard Hipp (drh) on 2020-10-14 15:38:23 in reply to 5 [link] [source]

The sqlite3_vtab_nochange() function is an optimization. Virtual tables should continue to work even if sqlite3_vtab_nochange() always returns false. (If they do not, then that is a bug in the virtual table implementation.) As it happens, sqlite3_vtab_nochange() does always return false for an UPDATE-FROM statement.

(12) By Kevin Martin (kev82) on 2020-10-14 16:38:05 in reply to 11 [link] [source]

Thanks for that confirmation, we'll have a think about how to change the logic of our code to not rely on it, as I really like the 'update from' syntax.

The virtual table page https://sqlite.org/vtab.html may be a bit misleading here though and I think it's why I misunderstood:

If the xUpdate method is performing an UPDATE, then sqlite3_value_nochange(X) can be used to discover which columns of the virtual table were actually modified by the UPDATE statement. The sqlite3_value_nochange(X) interface returns true for columns that do not change.

Thanks everyone for all the help.