Subtypes are never stored; they apply to in-memory values only.

I was going to reply to Russell in the same way. But if you read his bug report closely, he does have a point. His title is misleading, but the body of the text makes it clear that the current behavior is wrong.

In the SQL statement:

    SELECT id FROM my_table WHERE outer(inner(col)) > 1;

The subtype generated by inner() should be passed directly through to the parameter of outer(). But that pass-through gets disrupted if the "inner(col)" value is indexed. An optimization replaces the "inner(col)" with the value stored in the index, and as you observe, indexes don't store subtypes.

On my to-do list I now have a note to disable the optimization that replaces an expression with the value of an index on that expression (forcing the expression to be recomputed rather than just read out of the index) if:

1. The indexed expression is a function that has the SQLITE_FUNC_SUBTYPE property. (Russell: Make sure your custom functions set SQLITE_FUNC_SUBTYPE if they are setting subtypes!)

2. And, the indexed expression is used as a parameter of a function.

I also plan to update the documentation on subtypes to point out this problem so that future programmers as less likely to run into it.

SQLITE_FUNC_SUBTYPE is an internal constant. Did you mean SQLITE_SUBTYPE?

In the current documentation, Pointer Passing Interfaces has the most information about subtypes.

Did you mean SQLITE_SUBTYPE?

Yes, probably that is what I meant...

Thank you for the prompt reply. Reading the doucmentation, I wasn't sure what I should expect from the subtyping feature. Clarifying it there would certainly help.

The following SQL demonstrates the malfunction:

CREATE TABLE t1(x INT, y TEXT);
INSERT INTO t1(x,y) VALUES(1,'{b:5}');
SELECT json_insert('{}', '$.a', json(y)) FROM t1;
CREATE INDEX t1j ON t1(json(y));
SELECT json_insert('{}', '$.a', json(y)) FROM t1;

A different answer is computed after the index has been created. This should not be the case. Creating or dropping indexes might change the order of results, and hence might result in a different answer if there is a LIMIT clause or something like that, but in this case where there is only a single row of output and no LIMIT clause, dropping or creating an index should not make a difference in the output.

I believe this is now fixed at check-in ba789a7804ab96d8 and its descendants, including the tip of branch-3.44.

To get your application working you need to:

1. Update your source tree to check-in ba789a7804ab96d8 or one of its descendants.

2. When you register application-defined functions that might call sqlite3_result_subtype(), you must add the new SQLITE_RESULT_SUBTYPE flag to the text encoding parameter.

3. You can verify that you did step 2 correctly by adding the -DSQLITE_STRICT_SUBTYPE=1 compile-time option. If you missed any SQLITE_RESULT_SUBTYPE flags, then the call to sqlite3_result_subtype() will cause the function to raise an error.

Please try out this fix. Report back whether or not it fixes your issue.

I have tested this with both my repro and my application, and it appears to fix the issue.

Thank you!