sqlite3_value_subtype setting not preserved with GROUP BY or ORDER BY

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3_value_subtype setting not preserved with GROUP BY or ORDER BY

scasaletto
Hi,

I've implemented a virtual table and in my xColumn() method I'm using
sqlite3_value_subtype() to indicate when my SQLITE_TEXT columns need some
different processing when I'm retrieving the columns of my query results.
I've found that the subtype setting is getting preserved for some reason
when my query includes GROUP BY or ORDER BY.

Any ideas why this could be happening? Is this the expected behavior? I'm
running SQLite version 3.19.3.

Thanks
Sharon Casaletto




_______________________________________________
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: sqlite3_value_subtype setting not preserved with GROUP BY or ORDER BY

scasaletto
Sorry, in my previous email, I meant to say that my subtype setting is *not*
getting preserved when my query includes GROUP BY or ORDER BY.

From:  Sharon Casaletto <[hidden email]>
Date:  Thursday, August 31, 2017 at 6:23 AM
To:  <[hidden email]>
Cc:  Sharon Casaletto <[hidden email]>
Subject:  sqlite3_value_subtype setting not preserved with GROUP BY or ORDER
BY

Hi,

I've implemented a virtual table and in my xColumn() method I'm using
sqlite3_value_subtype() to indicate when my SQLITE_TEXT columns need some
different processing when I'm retrieving the columns of my query results.
I've found that the subtype setting is getting preserved for some reason
when my query includes GROUP BY or ORDER BY.

Any ideas why this could be happening? Is this the expected behavior? I'm
running SQLite version 3.19.3.

Thanks
Sharon Casaletto




_______________________________________________
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: sqlite3_value_subtype setting not preserved with GROUP BY or ORDER BY

Hick Gunter
See description of JSON1 extension:

"For functions that accept "value" arguments (also shown as "value1" and "value2"), those arguments is usually understood to be a literal strings that are quoted and becomes JSON string values in the result. Even if the input value strings look like well-formed JSON, they are still interpreted as literal strings in the result.

However, if a value argument come directly from the result of another json1 function, then the argument is understood to be actual JSON and the complete JSON is inserted rather than a quoted string."

And

"The json1 extension uses the sqlite3_value_subtype() and sqlite3_result_subtype() interfaces that were introduced with SQLite version 3.9.0 (2015-10-14) The json1 extension will not work in earlier versions of SQLite."

And in the Pointer Passing Interface:

"The first attempt at closing security gaps in pointer passing was to prevent pointer values from being forged. This was accomplished by having the sender attach a subtype to each pointer using sqlite3_result_subtype() and having the receiver verify that subtype using sqlite3_value_subtype() and reject pointers that had an incorrect subtype. "

And

"The pointers that piggy-back on SQL NULL values using the sqlite3_bind_pointer(), sqlite3_result_pointer(), and sqlite3_value_pointer() interface are transient and ephemeral. The pointers are never written into the database. The pointers will not survive sorting. "

I would expect that the subtype information is discarded during sorting, maybe unintentionally.



-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Sharon Casaletto
Gesendet: Donnerstag, 31. August 2017 15:27
An: [hidden email]
Cc: Sharon Casaletto <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] sqlite3_value_subtype setting not preserved with GROUP BY or ORDER BY

Sorry, in my previous email, I meant to say that my subtype setting is *not* getting preserved when my query includes GROUP BY or ORDER BY.

From:  Sharon Casaletto <[hidden email]>
Date:  Thursday, August 31, 2017 at 6:23 AM
To:  <[hidden email]>
Cc:  Sharon Casaletto <[hidden email]>
Subject:  sqlite3_value_subtype setting not preserved with GROUP BY or ORDER BY

Hi,

I've implemented a virtual table and in my xColumn() method I'm using
sqlite3_value_subtype() to indicate when my SQLITE_TEXT columns need some different processing when I'm retrieving the columns of my query results.
I've found that the subtype setting is getting preserved for some reason when my query includes GROUP BY or ORDER BY.

Any ideas why this could be happening? Is this the expected behavior? I'm running SQLite version 3.19.3.

Thanks
Sharon Casaletto




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


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
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: sqlite3_value_subtype setting not preserved with GROUP BY or ORDER BY

scasaletto
So is the usage of sqlite3_value_subtype() and sqlite3_result_subtype() not
intended for general use and only intended for the usage scenarios mentioned
by Hick Gunter?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: sqlite3_value_subtype setting not preserved with GROUP BY or ORDER BY

Richard Hipp-3
In reply to this post by scasaletto
On 8/31/17, Sharon Casaletto <[hidden email]> wrote:
>
> I've implemented a virtual table and in my xColumn() method I'm using
> sqlite3_value_subtype() to indicate when my SQLITE_TEXT columns need some
> different processing when I'm retrieving the columns of my query results.
> I've found that the subtype setting is [not] getting preserved for some reason
> when my query includes GROUP BY or ORDER BY.
>
> Any ideas why this could be happening? Is this the expected behavior? I'm
> running SQLite version 3.19.3.

That is documented behavior.

The subtype is an *ephemeral* in-memory tag that exists only while a
value is held in registers of the virtual machine that runs the SQL
statement.  The subtype is not an attribute of the value that is
written to secondary storage.  Nor is the subtype preserved by the
sorter.

The subtype was originally created to facilitate nesting of JSON
primitive functions in the JSON1 extension.  The subtype has
subsequently been used to help the full-text search engine pass
information into auxiliary functions such as snippet().

If you need an long-lived tag that can be persisted to secondary
storage and/or run through a sorter, then the subtype will not work
for you.  You'll need to come up with a different mechanism.

All of the above also applies to sqlite3_result_pointer().
--
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: sqlite3_value_subtype setting not preserved with GROUP BY or ORDER BY

scasaletto
Thanks for the clarification.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users