investigate sqlite3 vs pysqlite3-binary difference

[cldellow]

import sqlite3
import pysqlite3

sql = '''WITH xs AS (SELECT json(1) x) SELECT json_group_array(x) FROM xs'''

def go(module):
    c = module.connect(':memory:')
    rv = c.execute(sql).fetchall()
    print('{}: {}'.format(module.sqlite_version, rv))

go(sqlite3)
go(pysqlite3)

Gives this output:

3.31.1: [('[1]',)]
3.40.0: [('["1"]',)]

This was the root cause of #97

[cldellow]

I first repro this with 3.39.0 (pysqlite3-binary 0.4.7post6)

[cldellow]

Hm, I think the new behaviour is by design :(

See https://sqlite.org/forum/forumpost/c96206d45c6122e1c81fc18d220fd3d5e183aac9f08294fb242671398090a44d

[cldellow]

Also see https://sqlite.org/forum/info/a37d3531d0d1665013007e61d

[cldellow]

The gist seems to be: the JSON functions can introduce a new JSON type (as opposed to the standard REAL, TEXT, BLOB, etc).

This type was previously allowed to escape subqueries, which introduced the opportunity for inconsistency.

It no longer is permitted to do that, and is serialized as a string when it escapes.

Can we get the same behaviour by explicitly casting to text?