This posted in response to a bug notification on the dev list, but I
moved it here for a question:
On 2015-10-11 12:45 PM, Richard Hipp wrote:
> On 10/11/15, Gilles Vollant <vollant.g at gmail.com> wrote:
>> I made a lot of very nice thing using json1,
>>
>> But I've an exception in the DLL just trying:
>>
>> CREATE TABLE mini_mini_npjson ( id_row text primary key,json_info text );
>> insert into mini_mini_npjson values ('1','[22,33]');
>> SELECT * FROM (select json_info from mini_mini_npjson ) as npjs ,
>> json_each(npjs.json_info);
>>
>> The error is "Access violation, read of address ."
>>
> Good catch, Gilles.
>
> An equivalent problem is this:
>
> CREATE TABLE t1(a);
> INSERT INTO t1(a) VALUES('[22,33]');
> SELECT * FROM (SELECT a FROM t1) AS x, json_each(x.a);
>
> What happens is that SQLite tries to "flatten" the subquery up into
> the outer join. The transformed query that it should be generating
> code for is:
>
> SELECT * FROM t1, json_each(t1.a);
>
> And that query works. However, the query flattener has a bug. It is
> not correctly translating the values for the arguments to a
> table-valued function (since that is new syntax that the query
> flattener did not previously know about). So it tries to generate
> code for:
>
> SELECT * FROM t1, json_each(x.a);
>
> And since no such table as "x" exists anymore, bad things happen.
>
I originally thought the aliasing as used in the demonstration above was
wrong, but of course it works in 3.9.0 as per the JSON docs.
Two questions -
1 - would referencing a table alias formed from a table in the same FROM
clause be valid in future? Or is this just for JSON purposes?
2 - would this be allowed to reference ANY aliased table, or only one
that can be simplified/factored to an actual table - What rules would
govern such things?
More explicitly - would these be valid queries:
SELECT props.*
FROM (
SELECT 1 as id, JSON_OBJECT('i',5,'n','James') AS obj UNION ALL
SELECT 2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
SELECT 3, JSON_OBJECT('i',7,'n','John')
) AS T1, JSON_EACH(T1.obj) AS props
WHERE 1
and would that be semantically equivalent to this:
SELECT props.*
FROM (
SELECT 1 as id, JSON_OBJECT('i',5,'n','James') AS obj UNION ALL
SELECT 2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
SELECT 3, JSON_OBJECT('i',7,'n','John')
) AS T1
INNER JOIN JSON_EACH(T1.obj) AS props
WHERE 1
and in future (without JSON):
SELECT B.* FROM
(SELECT (C1 || C2 || C3) AS CX FROM T) AS A,
(SELECT CX FROM A) AS B
WHERE 1
or perhaps these will be allowed with table valued functions only?
Thanks,
Ryan
