sqlite3_get_auxdata() defect

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

sqlite3_get_auxdata() defect

petern
Consider the simplest possible pair of argument metadata test functions as
follows.
----------
/*
Further to https://sqlite.org/c3ref/get_auxdata.html
UDF auxint(X,I) - stores I plus the function argument X's current metadata
INT and returns the new value.
UDF auxint(X) - returns the current value of the function meta-data INT
associated with X.
*/
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
static void reclaim(void*p) {if (p) sqlite3_free(p);}
static void auxint(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  int* pInt = sqlite3_get_auxdata(context, 0);
  if (argc>1 && sqlite3_value_type(argv[1]) != SQLITE_NULL){
        if (!pInt) {pInt = sqlite3_malloc(sizeof(int)); *pInt=0;
sqlite3_set_auxdata(context, 0, pInt, reclaim);}
    *pInt += sqlite3_value_int(argv[1]);
  }
  if (pInt) sqlite3_result_int(context,*pInt);
}
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_auxint_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  int rc = sqlite3_create_function(db, "auxint", 1, SQLITE_UTF8, 0, auxint,
0, 0);
  if (SQLITE_OK == rc) sqlite3_create_function(db, "auxint", 2,
SQLITE_UTF8, 0, auxint, 0, 0);
  return rc;
}
----------
Below is a simple query of two rows which both set and get the INT
meta-data for arg0 named 'x' in various ways:

sqlite> .load auxint.so
sqlite> WITH t(id) AS (VALUES(1),(2)) SELECT
id,auxint('x',id),auxint('x',1),auxint('x',null),auxint('x') FROM t;
id          auxint('x',id)  auxint('x',1)  auxint('x',null)  auxint('x')
----------  --------------  -------------  ----------------  -----------
1           1               1
2           3               2

Expected output:

id          auxint('x',id)  auxint('x',1)  auxint('x',null)  auxint('x')
----------  --------------  -------------  ----------------  -----------
1           1               2               2               2
2           4               5               5               5

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.

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
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_get_auxdata() defect

Clemens Ladisch
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
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_get_auxdata() defect

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

I'm not following.  Where is the argument different in my example.  The
zeroth argument is always 'x'.  Are you saying the argument has to be the
same memory location as well as the same value?

Take another look at the example.



On Mon, Nov 27, 2017 at 1:15 AM, Clemens Ladisch <[hidden email]> wrote:

> 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
>
_______________________________________________
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_get_auxdata() defect

petern
In reply to this post by Clemens Ladisch
Clemens.

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!

sqlite> WITH arg(x) AS (SELECT 'x'), t(id) AS (VALUES(1),(2)) SELECT
id,auxint(x,id),auxint(x,1),auxint(x,null),auxint(x) FROM arg,t;
id          auxint(x,id)  auxint(x,1)  auxint(x,null)  auxint(x)
----------  ------------  -----------  --------------  ----------
1           1             1
2           2             1

Compare with the original example using literal value for the cached
argument:

sqlite> WITH t(id) AS (VALUES(1),(2)) SELECT
id,auxint('x',id),auxint('x',1),auxint('x',null),auxint('x') FROM t;
id          auxint('x',id)  auxint('x',1)  auxint('x',null)  auxint('x')
----------  --------------  -------------  ----------------  -----------
1           1               1
2           3               2

I think the poster Ben, from last week, bumped into the above bug and was
seeking advice about why his function argument cache was being flushed for
every row.

Evidently the sqlite3_get_auxdata() API is very buggy.

Clemens, I'm a bit surprised you can't see or don't want to see the defects
here.  Very curious to read your reply.

For completeness, here is the testing extension auxint.c once again:
----------
/*
Further to https://sqlite.org/c3ref/get_auxdata.html
UDF auxint(X,I) - stores I plus the function argument X's current metadata
INT and returns the new value.
UDF auxint(X) - returns the current value of the function meta-data INT
associated with X.
*/
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
static void reclaim(void*p) {if (p) sqlite3_free(p);}
static void auxint(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  int* pInt = sqlite3_get_auxdata(context, 0);
  if (argc>1 && sqlite3_value_type(argv[1]) != SQLITE_NULL){
        if (!pInt) {pInt = sqlite3_malloc(sizeof(int)); *pInt=0;
sqlite3_set_auxdata(context, 0, pInt, reclaim);}
    *pInt += sqlite3_value_int(argv[1]);
  }
  if (pInt) sqlite3_result_int(context,*pInt);
}
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_auxint_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  int rc = sqlite3_create_function(db, "auxint", 1, SQLITE_UTF8, 0, auxint,
0, 0);
  if (SQLITE_OK == rc) sqlite3_create_function(db, "auxint", 2,
SQLITE_UTF8, 0, auxint, 0, 0);
  return rc;
}
----------

On Mon, Nov 27, 2017 at 1:15 AM, Clemens Ladisch <[hidden email]> wrote:

> 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
>
_______________________________________________
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_get_auxdata() defect

Clemens Ladisch
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
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_get_auxdata() defect

petern
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.

Peter





On Mon, Nov 27, 2017 at 1:27 PM, Clemens Ladisch <[hidden email]> wrote:

> 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
>
_______________________________________________
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_get_auxdata() defect

Stadin, Benjamin
In reply to this post by Clemens Ladisch
Hi Clemens,

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.

Regards
Ben

Am 27.11.17, 22:28 schrieb "sqlite-users im Auftrag von Clemens Ladisch" <[hidden email] im Auftrag von [hidden email]>:

    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
   

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