How to get the constraintargs list as set for a WHERE IN (list) clause?

[gfieremans]

I'm using Beto de Alemeida's shillelagh in order to proxy SQL statements from Superset to a REST API service. I'm not sure what's going on, but whenever I use a multi-select filter on Superset, the SQL statement correctly states to SELECT * from X WHERE Y IN ('s1','s2'). According to the documentation this list of strings should be passed as a set(['s1','s2') in the constraintargs of the VTCursor Filter, but I get these in as None. Is there a setting that I overlooked that triggers the use of the set() for the constraintargs. If I don't get this set() in then I cannot pass it along on the API and apsw does the filtering on a row by row basis.

[rogerbinns]

You do have to call IndexInfo.set_aConstraintUsage_in on the corresponding constraint in BestIndexObject to get the set.

[gfieremans]

Thanks for the quick reply. The 'in' constraint usage was set - as you can see from the index_info_dict which I printed before I return True:

index_info_dict: {'nConstraint': 1, 'aConstraint': [{'iColumn': 1, 'op': 2, 'op_str': 'SQLITE_INDEX_CONSTRAINT_EQ', 'usable': True, 'collation': 'BINARY', 'rhs': None}], 'nOrderBy': 0, 'aOrderBy': [], 'aConstraintUsage': [{'argvIndex': 0, 'omit': False, 'in': True}], 'idxNum': 42, 'idxStr': '[[1,2],[]]', 'orderByConsumed': False, 'estimatedCost': 5e+98, 'estimatedRows': 25, 'idxFlags': 0, 'idxFlags_set': set(), 'colUsed': {0, 1, 2, 3, 6}, 'distinct': 0}

Still the constraintargs in the Filter() method of the VTCursor is an empty (). I'm wondering if the 'rhs' is not already supposed to show the set?

[gfieremans]

Actually... I can see the set in Filter() when I set the argvIndex to 1 instead of 0. Is that correct behavior?

[rogerbinns]

Yes that is correct behaviour. You should read the page about sqlite3_index_info to understand what is going on. Note that for aConstraintUsage.argvIndex it says:

if >0, constraint is part of argv to xFilter

The argvIndex starts as zero, and is only presented to xFilter if set to non-zero.

[gfieremans]

Awesome thanks. Only started looking into this yesterday, but apsw is really a great product.