Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

Kevin Martin
Hi,

I have a without rowid virtual table with an implementation of xColumn that begins with

if(sqlite3_vtab_nochange(ctx)) return SQLITE_OK;

If I try to perform an update on this table that doesn't involve a primary key change, then my understanding from the documentation is that xUpdate will be called and the value of argv[0] and argv[1] will be the same. What I am seeing is that argv[1] is set an sql null value, although when I call sqlite3_value_nochange(argv[1]) I do get true returned.

Am I therefore right in thinking that the correct detection of whether there is an update without a primary key change when using sqlite3_vtab_nochange is actually

sqlite3_value_nochange(argv[1]) || values_are_equal(argv[0], argv[1])

Thanks,
Kev
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

Hick Gunter
To correctly determine what SQLite is asking of your xUpdate routine requires looking at argc, argv[0] and possibly argv[1] (if argc > 1).

You did not state your argc and argv[0] values, so looking at the documentation would suggest that SQLite is actually asking for an INSERT into a WITHOUT ROWID virtual table.

https://sqlite.org/vtab.html#xupdate

"INSERT: ... The argv[1] will be NULL for a WITHOUT ROWID virtual table, in which case the implementation should take the PRIMARY KEY value from the appropriate column in argv[2] and following."


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Kevin Martin
Gesendet: Samstag, 10. August 2019 16:29
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

Hi,

I have a without rowid virtual table with an implementation of xColumn that begins with

if(sqlite3_vtab_nochange(ctx)) return SQLITE_OK;

If I try to perform an update on this table that doesn't involve a primary key change, then my understanding from the documentation is that xUpdate will be called and the value of argv[0] and argv[1] will be the same. What I am seeing is that argv[1] is set an sql null value, although when I call sqlite3_value_nochange(argv[1]) I do get true returned.

Am I therefore right in thinking that the correct detection of whether there is an update without a primary key change when using sqlite3_vtab_nochange is actually

sqlite3_value_nochange(argv[1]) || values_are_equal(argv[0], argv[1])

Thanks,
Kev
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

Kevin Martin

> On 12 Aug 2019, at 07:53, Hick Gunter <[hidden email]> wrote:
>
> You did not state your argc and argv[0] values,

Apologies, it is a 2 column table. Full details are:

- argc is 4
- argv[0] is the value of the primary key for the row I want to update.
- argv[1] is SQLITE_NULL, but as described, sqlite3_value_nochange(argv[1]) returns true
- argv[2] is the same as argv[1] (null, but sqlite3_value_nochange returns true)
- argv[3] is the new value of the non-primary key column.

> so looking at the documentation would suggest that SQLite is actually asking for an INSERT into a WITHOUT ROWID virtual table.

I see that is what the documentation leads you to believe, but I can assure you I am exciting an update of the form

update t set notprimarykey='some value' where primarykey='other value'

Removing the sqlite3_vtab_nochange from the xColumn call gets the documented behaviour. If I keep the sqlite3_vtab_nochange and change my code in xUpdate as described in the last email, everything seems to work. I just want to check it is correct.

Thanks,
Kev
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

Hick Gunter
Very strange and AFAICT not documented. I would not have though that calling sqlite3_value_nochange on argv[1] was even legal, given that it would correspond to field number -1. Could you provide an "explain" (SQlite bytecode program dump) of your statement?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Kevin Martin
Gesendet: Dienstag, 13. August 2019 13:23
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange


> On 12 Aug 2019, at 07:53, Hick Gunter <[hidden email]> wrote:
>
> You did not state your argc and argv[0] values,

Apologies, it is a 2 column table. Full details are:

- argc is 4
- argv[0] is the value of the primary key for the row I want to update.
- argv[1] is SQLITE_NULL, but as described, sqlite3_value_nochange(argv[1]) returns true
- argv[2] is the same as argv[1] (null, but sqlite3_value_nochange returns true)
- argv[3] is the new value of the non-primary key column.

> so looking at the documentation would suggest that SQLite is actually asking for an INSERT into a WITHOUT ROWID virtual table.

I see that is what the documentation leads you to believe, but I can assure you I am exciting an update of the form

update t set notprimarykey='some value' where primarykey='other value'

Removing the sqlite3_vtab_nochange from the xColumn call gets the documented behaviour. If I keep the sqlite3_vtab_nochange and change my code in xUpdate as described in the last email, everything seems to work. I just want to check it is correct.

Thanks,
Kev
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

Richard Hipp-3
In reply to this post by Kevin Martin
On 8/10/19, Kevin Martin <[hidden email]> wrote:

> Hi,
>
> I have a without rowid virtual table with an implementation of xColumn that
> begins with
>
> if(sqlite3_vtab_nochange(ctx)) return SQLITE_OK;
>
> If I try to perform an update on this table that doesn't involve a primary
> key change, then my understanding from the documentation is that xUpdate
> will be called and the value of argv[0] and argv[1] will be the same. What I
> am seeing is that argv[1] is set an sql null value, although when I call
> sqlite3_value_nochange(argv[1]) I do get true returned.
>
> Am I therefore right in thinking that the correct detection of whether there
> is an update without a primary key change when using sqlite3_vtab_nochange
> is actually
>
> sqlite3_value_nochange(argv[1]) || values_are_equal(argv[0], argv[1])
>

I think that is correct.

Honestly, when I invented the sqlite3_vtab_nochange() mechanism, I
intended it to be used for auxiliary columns that were expensive to
compute.  Example:  In the zipvfile virtual table (used to read/write
ZIP archives), if you want to update the access permissions on a file,
you shouldn't have to load and decompess the complete content of that
file as part of the UPDATE operation.  sqlite3_vtab_nochange()
facilitates such optimizations.

But it never occurred to me that somebody might do this on the PRIMARY
KEY.  I don't see any reason why it wouldn't work, though.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

Kevin Martin

> On 13 Aug 2019, at 14:08, Richard Hipp <[hidden email]> wrote:
>
> I think that is correct.
>

Great, thanks.

> But it never occurred to me that somebody might do this on the PRIMARY
> KEY.  I don't see any reason why it wouldn't work, though.

I have a c++ interface built on top of the virtual table api which multiple modules are then built on top of, it doesn't know which columns are large/expensive, so the idea was just to use call sqlite3_vtab_nochange for all of them, which includes the primary key.


> On 13 Aug 2019, at 13:00, Hick Gunter <[hidden email]> wrote:
>
> Very strange and AFAICT not documented. I would not have though that calling sqlite3_value_nochange on argv[1] was even legal, given that it would correspond to field number -1. Could you provide an "explain" (SQlite bytecode program dump) of your statement?

See below JSON for the explain as the table is not accessible in the command line tool.

Thanks,
Kev

---

[
  {
    "sql":"pragma table_info(modeloption_vt_writable);",
    "cols":["cid", "name", "type", "notnull", "dflt_value", "pk"],
    "time":0.016,
    "results":[
      ["0", "option", "text", "1", "<Null>", "1"],
      ["1", "value", "text", "0", "<Null>", "0"]
    ]
  },
  {
    "sql":"\n\nexplain update modeloption_vt_writable set value = 'v' where option='o';",
    "cols":["addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment"],
    "time":0,
    "results":[
      ["0", "Init", "0", "25", "0", "", "00", "Start at 25"],
      ["1", "OpenEphemeral", "2", "4", "0", "", "00", "nColumn=4"],
      ["2", "VOpen", "1", "0", "0", "vtab:C3289DFC0", "00", ""],
      ["3", "Integer", "0", "10", "0", "", "00", "r[10]=0"],
      ["4", "Integer", "0", "11", "0", "", "00", "r[11]=0"],
      ["5", "VFilter", "1", "16", "10", "", "00", "iplan=r[10] zplan=''"],
      ["6", "VColumn", "1", "0", "12", "", "00", "r[12]=vcolumn(0); modeloption_vt_writable.option"],
      ["7", "Ne", "13", "15", "12", "(BINARY)", "52", "if r[12]!=r[13] goto 15"],
      ["8", "VColumn", "1", "0", "6", "", "01", "r[6]=vcolumn(0)"],
      ["9", "String8", "0", "7", "0", "v", "00", "r[7]='v'"],
      ["10", "VColumn", "1", "0", "4", "", "00", "r[4]=vcolumn(0)"],
      ["11", "SCopy", "6", "5", "0", "", "00", "r[5]=r[6]"],
      ["12", "MakeRecord", "4", "4", "8", "", "00", "r[8]=mkrec(r[4..7])"],
      ["13", "NewRowid", "2", "9", "0", "", "00", "r[9]=rowid"],
      ["14", "Insert", "2", "8", "9", "", "00", "intkey=r[9] data=r[8]"],
      ["15", "VNext", "1", "6", "0", "", "00", ""],
      ["16", "Rewind", "2", "23", "0", "", "00", ""],
      ["17", "Column", "2", "0", "4", "", "00", "r[4]="],
      ["18", "Column", "2", "1", "5", "", "00", "r[5]="],
      ["19", "Column", "2", "2", "6", "", "00", "r[6]="],
      ["20", "Column", "2", "3", "7", "", "00", "r[7]="],
      ["21", "VUpdate", "0", "4", "4", "vtab:C3289DFC0", "02", "data=r[4..7]"],
      ["22", "Next", "2", "17", "0", "", "00", ""],
      ["23", "Close", "2", "0", "0", "", "00", ""],
      ["24", "Halt", "0", "0", "0", "", "00", ""],
      ["25", "Transaction", "0", "1", "1", "0", "01", "usesStmtJournal=1"],
      ["26", "VBegin", "0", "0", "0", "vtab:C3289DFC0", "00", ""],
      ["27", "String8", "0", "13", "0", "o", "00", "r[13]='o'"],
      ["28", "Goto", "0", "1", "0", "", "00", ""]
    ]
  }
]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

Hick Gunter
OK now I see. I think this is a major bug in the code generator because it breaks documented behaviour.

In lines 8 to 12, SQlite is building records for an ephemeral "to do" table.

Line 8: retrieve the value of the primary key with OPFLAG_NOCHNG set and store result in R6
Line 9: store the replacement string into R7
Line 10: retrieve hte value of the primary key without OPFLAG_NOCHNG and store result in R4
Line 11: Copy R6 to R5 <-- THIS IS WRONG, it should be copy R4 to R5 as per documentation
Line 12: Make a record of R4 through R7

This sets up a later (lines 17-21) call to VUpdate with

Argc=4
Argv[0] = R4 "primary key"
Argv[1] = R5 "copy of unchanged NULL" instead of "copy of primary key"
Argv[2] = R6 "unchanged NULL"
Argv[2] = R7 "new value"

And that explains the strange behaviour you noticed.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Kevin Martin
Gesendet: Dienstag, 13. August 2019 17:47
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange


> On 13 Aug 2019, at 14:08, Richard Hipp <[hidden email]> wrote:
>
> I think that is correct.
>

Great, thanks.

> But it never occurred to me that somebody might do this on the PRIMARY
> KEY.  I don't see any reason why it wouldn't work, though.

I have a c++ interface built on top of the virtual table api which multiple modules are then built on top of, it doesn't know which columns are large/expensive, so the idea was just to use call sqlite3_vtab_nochange for all of them, which includes the primary key.


> On 13 Aug 2019, at 13:00, Hick Gunter <[hidden email]> wrote:
>
> Very strange and AFAICT not documented. I would not have though that calling sqlite3_value_nochange on argv[1] was even legal, given that it would correspond to field number -1. Could you provide an "explain" (SQlite bytecode program dump) of your statement?

See below JSON for the explain as the table is not accessible in the command line tool.

Thanks,
Kev

---

[
  {
    "sql":"pragma table_info(modeloption_vt_writable);",
    "cols":["cid", "name", "type", "notnull", "dflt_value", "pk"],
    "time":0.016,
    "results":[
      ["0", "option", "text", "1", "<Null>", "1"],
      ["1", "value", "text", "0", "<Null>", "0"]
    ]
  },
  {
    "sql":"\n\nexplain update modeloption_vt_writable set value = 'v' where option='o';",
    "cols":["addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment"],
    "time":0,
    "results":[
      ["0", "Init", "0", "25", "0", "", "00", "Start at 25"],
      ["1", "OpenEphemeral", "2", "4", "0", "", "00", "nColumn=4"],
      ["2", "VOpen", "1", "0", "0", "vtab:C3289DFC0", "00", ""],
      ["3", "Integer", "0", "10", "0", "", "00", "r[10]=0"],
      ["4", "Integer", "0", "11", "0", "", "00", "r[11]=0"],
      ["5", "VFilter", "1", "16", "10", "", "00", "iplan=r[10] zplan=''"],
      ["6", "VColumn", "1", "0", "12", "", "00", "r[12]=vcolumn(0); modeloption_vt_writable.option"],
      ["7", "Ne", "13", "15", "12", "(BINARY)", "52", "if r[12]!=r[13] goto 15"],
      ["8", "VColumn", "1", "0", "6", "", "01", "r[6]=vcolumn(0)"],
      ["9", "String8", "0", "7", "0", "v", "00", "r[7]='v'"],
      ["10", "VColumn", "1", "0", "4", "", "00", "r[4]=vcolumn(0)"],
      ["11", "SCopy", "6", "5", "0", "", "00", "r[5]=r[6]"],
      ["12", "MakeRecord", "4", "4", "8", "", "00", "r[8]=mkrec(r[4..7])"],
      ["13", "NewRowid", "2", "9", "0", "", "00", "r[9]=rowid"],
      ["14", "Insert", "2", "8", "9", "", "00", "intkey=r[9] data=r[8]"],
      ["15", "VNext", "1", "6", "0", "", "00", ""],
      ["16", "Rewind", "2", "23", "0", "", "00", ""],
      ["17", "Column", "2", "0", "4", "", "00", "r[4]="],
      ["18", "Column", "2", "1", "5", "", "00", "r[5]="],
      ["19", "Column", "2", "2", "6", "", "00", "r[6]="],
      ["20", "Column", "2", "3", "7", "", "00", "r[7]="],
      ["21", "VUpdate", "0", "4", "4", "vtab:C3289DFC0", "02", "data=r[4..7]"],
      ["22", "Next", "2", "17", "0", "", "00", ""],
      ["23", "Close", "2", "0", "0", "", "00", ""],
      ["24", "Halt", "0", "0", "0", "", "00", ""],
      ["25", "Transaction", "0", "1", "1", "0", "01", "usesStmtJournal=1"],
      ["26", "VBegin", "0", "0", "0", "vtab:C3289DFC0", "00", ""],
      ["27", "String8", "0", "13", "0", "o", "00", "r[13]='o'"],
      ["28", "Goto", "0", "1", "0", "", "00", ""]
    ]
  }
]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users