Re: [sqlite] Parser bug on sub joins when on right side of the join operator
Hi,
According to the documentation (Sqlite + standard Ansi),
queries that should be supported like :
SELECT *
FROM A JOIN (B JOIN C ON B.ID = C.ID) ON A.ID = B.ID
are still not supported whereas badly formed queries like :
SELECT *
FROM A JOIN (B JOIN C ON B.ID = C.ID) AS D ON A.ID = D.ID
work !
anyone to fix this ?
Mathieu
-Message d'origine-
De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
De la part de Mathieu TAUZIN Envoyé : lundi 26 mars 2012 15:53 À :
sqlite-users@sqlite.org Objet : [sqlite] Parser bug on sub joins when on right
side of the join operator
Hi,
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)
...
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);
}
}
...
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.
I wish I could propose a fix but I have no skills in C/yacc.
Hope this will help anyway.
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Missing SQL_CONSTRAINTS resource in System.Data.SQLite.Linq since v1.0.79.0
Hi, After a double check it appears that I gave you a wrong information (never trust your co-workers). The correct version having an issue is v1.0.74.0 (loaded from GAC or file). It seems most likely like a localization issue. Anyway here is the exception (French): Test method Cegid.Framework.Data.SQLite.Tests.SyncManagerTest.SyncConfiguredScopeDescriptionIsEqual2Test threw exception: System.Data.EntityCommandExecutionException: Une erreur s'est produite lors de l'exécution de la définition de la commande. Pour plus de détails, consultez l'exception interne. ---> System.Resources.MissingManifestResourceException: Impossible de trouver des ressources appropriées pour la culture spécifiée ou la culture neutre. Assurez-vous que "System.Data.SQLite.Properties.Resources.resources" a été correctement incorporé ou lié dans l'assembly "System.Data.SQLite.Linq" au moment de la compilation ou que tous les assemblys satellites requis sont chargeables et complètement signés. StackTrace: System.Resources.ManifestBasedResourceGroveler.HandleResourceStreamMissing(String fileName) System.Resources.ManifestBasedResourceGroveler.GrovelForResourceSet(CultureInfo culture, Dictionary`2 localResourceSets, Boolean tryParents, Boolean createIfNotExists, StackCrawlMark& stackMark) System.Resources.ResourceManager.InternalGetResourceSet(CultureInfo requestedCulture, Boolean createIfNotExists, Boolean tryParents, StackCrawlMark& stackMark) System.Resources.ResourceManager.InternalGetResourceSet(CultureInfo culture, Boolean createIfNotExists, Boolean tryParents) System.Resources.ResourceManager.GetString(String name, CultureInfo culture) System.Data.SQLite.Properties.Resources.get_SQL_CONSTRAINTS() System.Data.SQLite.SQLiteProviderServices.System.Data.SQLite.ISQLiteSchemaExtensions.BuildTempSchema(SQLiteConnection cnn) System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLite3.cs: line 309 System.Data.SQLite.SQLiteCommand.BuildNextCommand() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs: line 254 System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs: line 261 System.Data.SQLite.SQLiteDataReader.NextResult() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteDataReader.cs: line 885 System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteDataReader.cs: line 85 System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs: line 536 System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs: line 524 System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues) System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable.GetEnumerator() System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) Cegid.Framework.Data.SQLite.Sync.SQLiteSyncScopeManager.GetCachedConfiguredScopeDescription() in G:\CBP9\trunk\SDK\Framework\Data.SQLite\Sync\SQLiteSyncScopeManager.cs: line 281 Cegid.Framework.Data.SQLite.Sync.SQLiteSyncScopeManager.GetConfiguredScopeDescription() in G:\CBP9\trunk\SDK\Framework\Data.SQLite\Sync\SQLiteSyncScopeManager.cs: line 294 Cegid.Framework.Data.SQLite.Tests.SyncManagerTest.SyncConfiguredScopeDescriptionIsEqual2Test() in G:\CBP9\trunk\SDK\Framework\tests\Data.SQLite.Tests\SyncManagerTest.cs: line 358 -Message d'origine- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Joe Mistachkin Envoyé : mercredi 2 mai 2012 16:58 À : 'General Discussion of SQLite Database' Objet : Re: [sqlite] Missing SQL_CONSTRAINTS resource in System.Data.SQLite.Linq since v1.0.79.0 Using Reflector, I can see the resources: https://system.data.sqlite.org/temporary/linq_resources.png What is the exception you are seeing when the BuildTempSchema method is called? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sq
Re: [sqlite] Missing SQL_CONSTRAINTS resource in System.Data.SQLite.Linq since v1.0.79.0
Hi, I'm using the assembly in the GAC, the resource named "System.Data.SQLite.Linq.Properties.Resources.resources" is present but I could'nt see the SQL_CONSTRAINTS string resource in it browsing the assemblies 1.0.79.0 and 1.0.80.0 with ILSpy. I checked the resource string is not missing in v 1.0.74.0 (from gac also). Mathieu -Message d'origine- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Joe Mistachkin Envoyé : mercredi 2 mai 2012 16:28 À : 'General Discussion of SQLite Database' Objet : Re: [sqlite] Missing SQL_CONSTRAINTS resource in System.Data.SQLite.Linq since v1.0.79.0 TAUZIN Mathieu wrote: > > I've just noticed that a resource is missing in System.Data.SQLite.Linq.dll > since version 1.0.79.0. > I'm not able to reproduce this problem. When viewed with ILDASM, the resource named "System.Data.SQLite.Linq.Properties.Resources.resources" is present. Also, the test cases for these resources passes with version 1.0.79.0 (as well as 1.0.80.0 and trunk). I've added an additional test case for the ISQLiteSchemaExtensions.BuildTempSchema method and that passes too: https://system.data.sqlite.org/index.html/ci/0a25b20f9c?sbs=0 Any additional information you could provide on this would be useful? For example, which build of System.Data.SQLite are you using? Is the assembly in the GAC? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Missing SQL_CONSTRAINTS resource in System.Data.SQLite.Linq since v1.0.79.0
Hi, I've just noticed that a resource is missing in System.Data.SQLite.Linq.dll since version 1.0.79.0. The missing resource causes an exception when calling the SQLiteProviderServices' ISQLiteSchemaExtensions.BuildTempSchema implementation. Hope this will help Mathieu TAUZIN ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parser bug on sub joins when on right side of the join operator
Hi,
I expected someone would reply to this one
Mathieu
-Message d'origine-
De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
De la part de Mathieu TAUZIN
Envoyé : lundi 26 mars 2012 15:53
À : sqlite-users@sqlite.org
Objet : [sqlite] Parser bug on sub joins when on right side of the join operator
Hi,
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)
...
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);
}
}
...
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.
I wish I could propose a fix but I have no skills in C/yacc.
Hope this will help anyway.
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Join-source issue when using sub '(' join-source ')'
Hi,
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)
...
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);
}
}
...
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-users-boun...@sqlite.org [mailto:sqlite-users-boun...@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 wrote:
> 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 as
a sort of sub-select.
Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Join-source issue when using sub '(' join-source ')'
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.
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-users-boun...@sqlite.org [mailto:sqlite-users-boun...@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 +, TAUZIN Mathieu scratched on the wall:
> 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 ___
sqlite-users mailing list
sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Join-source issue when using sub '(' join-source ')'
Thanks,
This syntax works but it is not documented... it looks like a short hand for a
subquery, interesting !.
Mathieu
-Message d'origine-
De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@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 wrote:
>> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Join-source issue when using sub '(' join-source ')'
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.
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-users-boun...@sqlite.org [mailto:sqlite-users-boun...@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 ')'
On 19 Mar 2012, at 12:03pm, TAUZIN Mathieu 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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>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
>>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Join-source issue when using sub '(' join-source ')'
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
