1. The identical function in a different column of the same SELECT has a
separate meta-data cache.

2. Functions with identical names and different arg count, yet sharing
common identical meta data argument at the specified argument index, use a
separate meta-data cache.

Ideally, these problems should be fixed instead of simply being documented
away. And, if they are fixed, SELECT columns can have an efficient,
thread-safe, and powerful new expressiveness for the idioms of running
sums, running products, running concatenations, synthetic id columns, and
so on.
_______________________________________________
sqlite-users mailing list
[hidden email]http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: sqlite3_get_auxdata() defect

petern wrote:
> So, at the very least, the documentation at
> https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete because:
>
> 1. The identical function in a different column of the same SELECT has a
> separate meta-data cache.

> Ideally, these problems should be fixed instead of simply being documented
> away. And, if they are fixed, SELECT columns can have an efficient,
> thread-safe, and powerful new expressiveness for the idioms of running
> sums, running products, running concatenations, synthetic id columns, and
> so on.

The auxdata is intended as a cache, so it must depend only on the
argument value, not on any other state.

If you want to do something different, store the data elsewhere. And
there is not much of a guarantee when or how often user-defined functions
are called, so even with your desired changes to the auxdata mechanism,
you would not get the desired result.

> petern wrote:
> > So, at the very least, the documentation at
> > https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete
> because:
> >
> > 1. The identical function in a different column of the same SELECT has a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > 2. Functions with identical names and different arg count, yet sharing
> > common identical meta data argument at the specified argument index, use
> a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > Ideally, these problems should be fixed instead of simply being
> documented
> > away. And, if they are fixed, SELECT columns can have an efficient,
> > thread-safe, and powerful new expressiveness for the idioms of running
> > sums, running products, running concatenations, synthetic id columns, and
> > so on.
>
> The auxdata is intended as a cache, so it must depend only on the
> argument value, not on any other state.
>
> If you want to do something different, store the data elsewhere. And
> there is not much of a guarantee when or how often user-defined functions
> are called, so even with your desired changes to the auxdata mechanism,
> you would not get the desired result.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>

Re: sqlite3_get_auxdata() defect

Here it that example again, but with the argument factored out so there is
no question about value or memory location of the cache argument target.
Remarkably, caching doesn't work at all when one does this!

> petern wrote:
> > So, at the very least, the documentation at
> > https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete
> because:
> >
> > 1. The identical function in a different column of the same SELECT has a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > 2. Functions with identical names and different arg count, yet sharing
> > common identical meta data argument at the specified argument index, use
> a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > Ideally, these problems should be fixed instead of simply being
> documented
> > away. And, if they are fixed, SELECT columns can have an efficient,
> > thread-safe, and powerful new expressiveness for the idioms of running
> > sums, running products, running concatenations, synthetic id columns, and
> > so on.
>
> The auxdata is intended as a cache, so it must depend only on the
> argument value, not on any other state.
>
> If you want to do something different, store the data elsewhere. And
> there is not much of a guarantee when or how often user-defined functions
> are called, so even with your desired changes to the auxdata mechanism,
> you would not get the desired result.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>

Re: sqlite3_get_auxdata() defect

Clemens,

Indeed. It doesn't work as a basis for reliable performance improvement of
user code. Basically, that API is practically useless except for the
narrow in-house use case for which it was created. And yes, I already make
heavy use of the natural aggregate cache functionality but for the
limitation of only producing only one final output value. A statement
level function cache obviously has more general use.

So, as I pointed out to Ben, the other poster who also discovered the
limitations of sqlite3_get_auxdata(), it would be trivial to get the
desired statement level cache speedup if only there were a per-connection
version of the API, say sqlite3_set_data(sqlite3*,void*) and void*
sqlite3_get_data(sqlite3*).

A connection level data area is definitely missing from the API. To
implement one's own thread aware global connection to cache map wrapper
seems a lot to ask when it could so easily be added to the SQLite API.
Such an SQLite API implementation has a huge advantage in already having an
internal hash map implementation and total awareness of current operational
threading mode of SQLite.

That being said, there is still a documentation bug that is wasting
people's time. A face value reading of the documentation at
https://www.sqlite.org/c3ref/get_auxdata.html gives the impression that
caching is solely dependent on the index and value of the function argument
when in fact there are 3 more very important and unstated limitations.
Documenting those limitations would only add one or two sentences but would
save people a lot of time.

> petern wrote:
> > Evidently the sqlite3_get_auxdata() API is very buggy.
>
> The documentation says that
> | under some circumstances the associated metadata may be preserved.
>
> You have found circumstances where the metadata is not preserved.
>
> This is not a bug.
>
> I can see that you are unhappy, but that is just because you are trying
> to make it do something that it was never intended to do.
>
> If you want a function with mutable state over multiple invocations,
> do not use sqlite3_set_auxdata() but an aggregation function, or store
> the data elsewhere.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>

Re: sqlite3_get_auxdata() defect

Even though the documentation makes no guaranty about the lifetime, it is was also for me rather useless.

I need to do some spatial calculations in my custom SQL function, and use the proj4 projection library within it. This SQL function is called very often, and having to recreate the proj4 object with projection params each time was a huge performance issue for me. I wasn’t able to figure out when the data was actually cached – during my tests I simply got the free callback fired immediately after each step.

I also had a second use case which actually requires a guaranty for the aux data to exist (providing the current position of a 3D camera. Somewhat comparable to a date function – you get current data whenever you call it).

I eventually hacked the VDBE struct, added a pointer to it, and altered some places in order to be able to pass a pointer to the sqlite3_step which I then retrieve from the custom sql function from the context via something like sqlite3_get_custom_ptr.

I would call my implementation a hack, though I simply required the performance improvement and the guaranty for the instance be available whenever the function gets called. It would be great if there was a (probably new) API which does that in a proper way.