Re: [sqlite] Partial indexes on JSON properties?

2016-10-04 Thread Richard Hipp
On 10/4/16, Keith Medcalf wrote: > This raises another question. Is there any way to mark a function > in-between volatile and deterministic? Currently if the deterministic flag > is not set the optimizer assumes that the function is truly volatile (it is > called every

Re: [sqlite] Partial indexes on JSON properties?

2016-10-04 Thread Keith Medcalf
On Monday, 3 October, 2016 12:30, Richard Hipp wrote: > On 10/1/16, Jens Alfke wrote: > > the WHERE clause in a CREATE INDEX statement > > explicitly disallows function calls Is this limitation something > > that might be lifted soon > Deterministic SQL functions are

Re: [sqlite] Partial indexes on JSON properties?

2016-10-03 Thread Jens Alfke
> On Oct 3, 2016, at 11:29 AM, Richard Hipp wrote: > > Deterministic SQL functions are now allowed in partial index WHERE > clauses, as of a few minutes ago. The current "Prerelease Snapshot" > (https://www.sqlite.org/download.html ) >

Re: [sqlite] Partial indexes on JSON properties?

2016-10-03 Thread Richard Hipp
On 10/1/16, Jens Alfke wrote: > the WHERE clause in a CREATE INDEX statement > explicitly disallows function calls Is this limitation something that > might be lifted soon Deterministic SQL functions are now allowed in partial index WHERE clauses, as of a few minutes ago.

Re: [sqlite] Partial indexes on JSON properties?

2016-10-02 Thread Clemens Ladisch
Jens Alfke wrote: > if the key is null and the table has a rowid, the node would be small, on the > order of 10 bytes or so … ? Typically less than 10 bytes. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Partial indexes on JSON properties?

2016-10-02 Thread Jens Alfke
> On Oct 2, 2016, at 6:20 AM, Clemens Ladisch wrote: > > Changing the function in any way (including not registering the > function) would essentially corrupt the index. Well, the same can be said of using a custom collation function, which has been supported since 3.0; or

Re: [sqlite] Partial indexes on JSON properties?

2016-10-02 Thread Clemens Ladisch
Jens Alfke wrote: > the WHERE clause in a CREATE INDEX statement explicitly disallows > function calls, so I can’t constrain the index to only the rows that > contain the JSON property. Is this limitation something that might be > lifted soon (now that functions can be identified as ‘pure’), or is

[sqlite] Partial indexes on JSON properties?

2016-10-01 Thread Jens Alfke
I’m experimenting with querying databases of JSON documents. These data-sets are schemaless and there’s no guarantee that they all have a common set of properties; in fact it’s common for them to have the equivalent of multiple ‘tables’ in the same data-set, i.e. groups of documents with