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

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
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"
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
int sqlite3_auxint_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *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 <> wrote:

> petern wrote:
> > So, at the very least, the documentation at
> > 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
sqlite-users mailing list

Reply via email to