[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] crash
From:       "E.Pasma" <pasma10 () concepts ! nl>
Date:       2015-10-23 16:34:05
Message-ID: 775E787C-8D37-409A-8D89-69A315B97CD5 () concepts ! nl
[Download RAW message or body]

12 okt 2015, om 20:22, R.Smith:

>
> On 2015-10-12 07:40 PM, Richard Hipp wrote:
>> On 10/12/15, R.Smith <rsmith@rsweb.co.za> wrote:
>>> 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
>>>
>> Yes.
>>
>>> 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
>>>
>> This is not valid, and has never been valid.  I'm sorry, but I don't
>> understand the connection to the previous query.  They are completely
>> different, as far as I can see.
>
> Yes, I guess what I am after is quantifying the difference. In The  
> first query, an alias to a completely made-up table is passed to a  
> table-valued function (the T1 alias) and it knows to reference/read  
> from that made up table designated as T1.
>
> In the second query, a completely made up table A is used in a  
> second table definition (but this time not a table-valued function -  
> which is the only real difference) to produce B.
>
> Of course they are not the same, and the first works but the second  
> doesn't - so is it ONLY because the first is a Table-valued function  
> (and thus will this be common workings for Table-valued functions  
> henceforth), or perhaps it has to be an eponymous virtual table, or  
> is it specifically because it's a JSON function which allows this  
> uniquely?
>
> I think/hope this will be common workings for all table-valued  
> functions - this is essentially what I'm asking.
>
> (The other question about whether this would work with normal table  
> functions is already answered and understood to be: Never).
>
> Thank you kindly,
> Ryan
Hello, I still wish to add something and would like to hear if I'm  
wrong.
The question if table aliasses are visible within json functions  
becomes easier when using an alternative notation. It looks as if  
table-like functions do have "column-like" parameters that can be  
passed in regular WHERE clauses.
The common notation:
     json_each(t1.obj) as props
is then:
    json_each as props where props.json=t1.obj
Note that I'm uninvolved and only found this while experimenting with  
SQLite 3.9.0.
Ryan Smith's example query:

SELECT t1.*, 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

can be written as

SELECT t1.id, props.*
  FROM (
    SELECT  1 as id, JSON_OBJECT('i',5,'n','James') AS json UNION ALL
    SELECT  2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
    SELECT  3, JSON_OBJECT('i',7,'n','John')
) AS t1
JOIN (SELECT '$.n' AS root) t2
JOIN JSON_EACH AS props
WHERE props.json=t1.json

and that should clear the initial question marks.
I assume it remains so in future versions but actually don't care.
Regards, Edzard.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[prev in list] [next in list] [prev in thread] [next in thread]