Consider the simplest possible pair of argument metadata test functions as
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;
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
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 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

Reply via email to