[sqlite] Join-source issue when using sub '(' join-source ')'
TAUZIN Mathieu
According to the documentation on SELECT statements http://www.sqlite.org/lang_select.html
It seems possible to write join chains as A join (B join C). (using a '(' join-source ')' single-source )
But on the well known NorthwindEF database this query ...
SELECT Orders.OrderID
FROM Customers
INNER JOIN
(Orders
LEFT OUTER JOIN InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID
)
ON Customers.CustomerID = Orders.CustomerID
WHERE 'ALFKI' = Customers.CustomerID
... raises an error :
no such column: Orders.OrderID
It seems that parsing is ok (no syntax error) but sources in the sub join can't be used outside the parenthesis.
Could you confirm this is a bug ? or did I miss something ?
I tried this query on other DB engines (SqlServer and Oracle) and it works fine (producing the same execution plan than the equivalent queries below).
I know I could rewrite my example with a sub query ...
SELECT Useless.OrderID
FROM Customers
INNER JOIN (
SELECT
Orders.OrderID as OrderID,
Orders.CustomerID as CustomerID
FROM Orders LEFT OUTER JOIN InternationalOrders ON Orders.OrderID = InternationalOrders.OrderID
) AS Useless
ON Customers.CustomerID = Useless.CustomerID
WHERE 'ALFKI' = Customers.CustomerID
Or without subjoin...
SELECT Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
LEFT OUTER JOIN InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID
WHERE 'ALFKI' = Customers.CustomerID
But it illustrates the issue.
Thanks,
Mathieu TAUZIN
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Simon Slavin
On 19 Mar 2012, at 12:03pm, TAUZIN Mathieu <MTA...@cegid.fr> wrote:
> Or without subjoin...
> SELECT Orders.OrderID
> FROM Customers
> INNER JOIN Orders
> ON Customers.CustomerID = Orders.CustomerID
> LEFT OUTER JOIN InternationalOrders
> ON Orders.OrderID = InternationalOrders.OrderID
> WHERE 'ALFKI' = Customers.CustomerID
This form should work fine in SQLite and is the expected way to do JOINs to many files. It is just as efficient as subjoins in SQLite: the engine ends up doing no more work. And it has the advantage that it works in many different versions of SQL since it's part of the SQL definition. Unless you have a particular reason for wanting to avoid this form, I'd say go ahead and use it.
Simon.
TAUZIN Mathieu
The sample I gave is a simplified version of the real query which is built by a tool so I have not the choice on the form.
Mathieu.
-----Message d'origine-----
De : sqlite-use...@sqlite.org [mailto:sqlite-use...@sqlite.org] De la part de Simon Slavin
Envoyé : lundi 19 mars 2012 13:20
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')'
>>Original message :
>>
>>Hi,
>>
>>According to the documentation on SELECT statements http://www.sqlite.org/lang_select.html
>>It seems possible to write join chains as A join (B join C). (using a '(' join-source ')' single-source )
>>
>>But on the well known NorthwindEF database this query ...
>>
>>SELECT Orders.OrderID
>>FROM Customers
>>INNER JOIN
>>(Orders
>>LEFT OUTER JOIN InternationalOrders
>>ON Orders.OrderID = InternationalOrders.OrderID
>>)
>>ON Customers.CustomerID = Orders.CustomerID WHERE 'ALFKI' = Customers.CustomerID
>>
>>... raises an error :
>>no such column: Orders.OrderID
>>
>>It seems that parsing is ok (no syntax error) but sources in the sub join can't be used outside the parenthesis.
>>
>>Could you confirm this is a bug ? or did I miss something ?
>>
>>I tried this query on other DB engines (SqlServer and Oracle) and it works fine (producing the same execution plan than the equivalent queries below).
>>
>>I know I could rewrite my example with a sub query ...
>>SELECT Useless.OrderID
>>FROM Customers
>> INNER JOIN (
>> SELECT
>> Orders.OrderID as OrderID,
>> Orders.CustomerID as CustomerID
>> FROM Orders LEFT OUTER JOIN InternationalOrders ON Orders.OrderID = InternationalOrders.OrderID
>> ) AS Useless
>> ON Customers.CustomerID = Useless.CustomerID WHERE 'ALFKI' = Customers.CustomerID
>>
>>Or without subjoin...
>>SELECT Orders.OrderID
>>FROM Customers
>> INNER JOIN Orders
>> ON Customers.CustomerID = Orders.CustomerID
>> LEFT OUTER JOIN InternationalOrders
>> ON Orders.OrderID = InternationalOrders.OrderID WHERE 'ALFKI' = Customers.CustomerID
>>
>>But it illustrates the issue.
>>
>>Thanks,
>>
>>Mathieu TAUZIN
>>
Pavel Ivanov
> It seems possible to write join chains as A join (B join C). (using a '(' join-source ')' single-source )
> It seems that parsing is ok (no syntax error) but sources in the sub join can't be used outside the parenthesis.
>
> Could you confirm this is a bug ? or did I miss something ?
So there's no problem in documentation and you can indeed write join
chains using parenthesis. Also there's no bug in SQLite because
(according to documentation) join-source with parenthesis is
considered a single-source and as a consequence you can't link to some
details of this single-source outside of parenthesis. Maybe MS SQL
Server and Oracle provide an extension to do that, but SQLite doesn't.
So we can't confirm a bug and you didn't miss anything. You can file a
bug with the application generating your original query.
Pavel
Simon Slavin
On 19 Mar 2012, at 12:51pm, TAUZIN Mathieu <MTA...@cegid.fr> wrote:
> Thanks for your response but my intent was to give rise to either an bug on SQLite engine or an error (or maybe lack of precision) in the documentation.
SQLite is fine. The documentation is accurate about what SQLite does.
> The sample I gave is a simplified version of the real query which is built by a tool so I have not the choice on the form.
If your tool is intended for SQLite, and builds this form for SQLite, then there is a bug in your tool. You can submit this bug with the authors of that tool.
Simon Davies
>> According to the documentation on SELECT statements http://www.sqlite.org/lang_select.html
>> It seems possible to write join chains as A join (B join C). (using a '(' join-source ')' single-source )
> ...
>> It seems that parsing is ok (no syntax error) but sources in the sub join can't be used outside the parenthesis.
>>
>> Could you confirm this is a bug ? or did I miss something ?
>
> So there's no problem in documentation and you can indeed write join
> chains using parenthesis. Also there's no bug in SQLite because
> (according to documentation) join-source with parenthesis is
> considered a single-source and as a consequence you can't link to some
> details of this single-source outside of parenthesis. Maybe MS SQL
> Server and Oracle provide an extension to do that, but SQLite doesn't.
>
> So we can't confirm a bug and you didn't miss anything. You can file a
> bug with the application generating your original query.
>
>
> Pavel
Maybe what the application should be doing:
SELECT subjoin.OrderID
FROM Customers
INNER JOIN
(Orders
LEFT OUTER JOIN InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID
) AS subjoin
ON Customers.CustomerID = subjoin.CustomerID
WHERE 'ALFKI' = Customers.CustomerID
Regards,
Simon
TAUZIN Mathieu
This syntax works but it is not documented... it looks like a short hand for a subquery, interesting !.
Mathieu
-----Message d'origine-----
De : sqlite-use...@sqlite.org [mailto:sqlite-use...@sqlite.org] De la part de Simon Davies
Envoyé : lundi 19 mars 2012 14:24
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')'
On 19 March 2012 13:05, Pavel Ivanov <paiv...@gmail.com> wrote:
Nico Williams
> Thanks,
>
> This syntax works but it is not documented... it looks like a short hand for a subquery, interesting !.
Join sources are like sub-queries. Look at the syntax.
A sub-select specified in the join-source following the FROM clause in
a simple SELECT statement is handled as if it was a table containing
the data returned by executing the sub-select statement. The docs
could perhaps be clearer about this. This sentence:
"A sub-select specified in the join-source following the FROM clause
in a simple SELECT statement is handled as if it was a table
containing the data returned by executing the sub-select statement. "
does hint at this, but maybe that's just because I think of
<single-source> as a sort of sub-select.
Nico
--
Jay A. Kreibich
> Hi,
>
> According to the documentation on SELECT statements
> http://www.sqlite.org/lang_select.html
> It seems possible to write join chains as A join (B join C).
> (using a '(' join-source ')' single-source )
>
> But on the well known NorthwindEF database this query ...
>
> SELECT Orders.OrderID
> FROM Customers
> INNER JOIN
> (Orders
> LEFT OUTER JOIN InternationalOrders
> ON Orders.OrderID = InternationalOrders.OrderID
> )
> ON Customers.CustomerID = Orders.CustomerID
> WHERE 'ALFKI' = Customers.CustomerID
>
> ... raises an error :
> no such column: Orders.OrderID
This does appear to be a bug.
You can get around this using an AS clause to name the
sub-expression:
SELECT OrdInt.OrderID
FROM Customers
INNER JOIN (
Orders
LEFT OUTER JOIN
InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID
) AS OrdInt
ON Customers.CustomerID = OrdInt.CustomerID
WHERE 'ALFKI' = OrdInt.CustomerID
The thing is, you're not supposed to need to name a sub-expression.
In fact, according the the "single-source" syntax diagram, naming a
sub-expression (via AS) isn't even allowed.
To be clear, a sub-*select* that is used as a source can be (and, in
fact, must be) named to access it outside of the sub-select, but a
sub-*expression*-- where the parenthesis only serve to enforce order
of operations-- should expose the contained tables, just as if the
parenthesis were not there.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
TAUZIN Mathieu
SQL Ansi<http://savage.net.au/SQL/sql-99.bnf.html#qualified%20join> (and every major DB SqlServer<http://msdn.microsoft.com/en-US/library/ms177634(v=sql.90).aspx>, Oracle<http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_10002.htm>) supports this syntax as described in SQLite documentation.
The parenthesis are here to enforce the priority of the joins.
If the subjoined objects are no longer accessible outside the parenthesis what would be the interest of such a syntax ?
Therefore this query works fine :
SELECT Orders.OrderID
FROM (Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID )
INNER JOIN InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID
WHERE 'ALFKI' = Customers.CustomerID
Whereas that one doesn't :
SELECT Orders.OrderID
FROM Customers
INNER JOIN
(Orders
INNER JOIN InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID)
ON Customers.CustomerID = Orders.CustomerID
WHERE 'ALFKI' = Customers.CustomerID
So, sub-joins on the left of the join-type are OK
but sub-joins on the right of the joint-type are NOT OK.
It seems like a bug to me.
Mathieu
-----Message d'origine-----
De : sqlite-use...@sqlite.org [mailto:sqlite-use...@sqlite.org] De la part de Jay A. Kreibich
Envoyé : lundi 19 mars 2012 16:26
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')'
On Mon, Mar 19, 2012 at 12:03:44PM +0000, TAUZIN Mathieu scratched on the wall:
> Hi,
>
> http://www.sqlite.org/lang_select.html
>
>
> SELECT Orders.OrderID
> FROM Customers
> INNER JOIN
> (Orders
> LEFT OUTER JOIN InternationalOrders
> ON Orders.OrderID = InternationalOrders.OrderID
> )
> Customers.CustomerID
>
> ... raises an error :
> no such column: Orders.OrderID
sub-expression:
SELECT OrdInt.OrderID
FROM Customers
INNER JOIN (
Orders
LEFT OUTER JOIN
InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID
) AS OrdInt
ON Customers.CustomerID = OrdInt.CustomerID
WHERE 'ALFKI' = OrdInt.CustomerID
parenthesis were not there.
-j
--
sqlite-users mailing list
sqlite...@sqlite.org<mailto:sqlite...@sqlite.org>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Ryan Johnson
> Thanks for your support !
>
>
>
> SQL Ansi<http://savage.net.au/SQL/sql-99.bnf.html#qualified%20join> (and every major DB SqlServer<http://msdn.microsoft.com/en-US/library/ms177634(v=sql.90).aspx>, Oracle<http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_10002.htm>) supports this syntax as described in SQLite documentation.
>
>
>
> The parenthesis are here to enforce the priority of the joins.
orders. If you want to bypass the optimizer, the accepted way (as with
postgres, I believe) is to use a CROSS JOIN rather than the default
[INNER] JOIN. You may want to test whether the above-mentioned engines
actually respect parentheses for join ordering.
Whether the parenthesis as used should or should not introduce a scope,
I'm not prepared to make any claims about...
Ryan
TAUZIN Mathieu
You're wrong.
I think I've found the bug.
It is a parser issue.
According to their definition (http://sqlite.org/syntaxdiagrams.html#single-source) , Join sources (named single-source) are either :
* a table or view with an optional alias and/or with an optional index
* a sub query with an optional alias
* a sub join (with no alias)
In SQLite parser.y source code we can find on line 496 the grammar rule handling those three cases (in the same order)
<snippet line='496'>
...
seltablist(A) ::= stl_prefix(X) nm(Y) dbnm(D) as(Z) indexed_opt(I) on_opt(N) using_opt(U). {
A = sqlite3SrcListAppendFromTerm(pParse,X,&Y,&D,&Z,0,N,U);
sqlite3SrcListIndexedBy(pParse, A, &I);
}
seltablist(A) ::= stl_prefix(X) LP select(S) RP
as(Z) on_opt(N) using_opt(U). {
A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,S,N,U);
}
seltablist(A) ::= stl_prefix(X) LP seltablist(F) RP
as(Z) on_opt(N) using_opt(U). {
if( X==0 && Z.n==0 && N==0 && U==0 ){
A = F;
}else{
Select *pSubquery;
sqlite3SrcListShiftJoinType(F);
pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,0,0,0);
A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,pSubquery,N,U);
}
}
...
</snippet>
Case 1 and 2 are handled properly but as you can see the third definition (wich should deal with sub joins) contains mistakes :
#1 : It allows an as clause after the parenthesis
#2 : on the right of a join operator (else { ... }) it generates a subquery instead of merging F (which is a seltabList, not a sub query) with X into A.
Do you still think there is no issue here ?
I wish I could propose a fix but I have no skills in C/yacc.
Hope this will help anyway.
Thanks
-----Message d'origine-----
De : sqlite-use...@sqlite.org [mailto:sqlite-use...@sqlite.org] De la part de Nico Williams
Envoyé : lundi 19 mars 2012 16:10
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')'
On Mon, Mar 19, 2012 at 10:02 AM, TAUZIN Mathieu <MTA...@cegid.fr> wrote: