Return-Path: 
 <derby-dev-return-73716-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: (qmail 21912 invoked from network); 28 Oct 2009 22:08:22 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3)
  by minotaur.apache.org with SMTP; 28 Oct 2009 22:08:22 -0000
Received: (qmail 80185 invoked by uid 500); 28 Oct 2009 22:08:22 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 80136 invoked by uid 500); 28 Oct 2009 22:08:22 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 80128 invoked by uid 99); 28 Oct 2009 22:08:22 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 28 Oct 2009 22:08:22 +0000
X-ASF-Spam-Status: No, hits=-10.5 required=5.0
	tests=AWL,BAYES_00,RCVD_IN_DNSWL_HI
X-Spam-Check-By: apache.org
Received: from [140.211.11.140] (HELO brutus.apache.org) (140.211.11.140)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 28 Oct 2009 22:08:19 +0000
Received: from brutus (localhost [127.0.0.1])
	by brutus.apache.org (Postfix) with ESMTP id A4056234C495
	for <derby-dev@db.apache.org>; Wed, 28 Oct 2009 15:07:59 -0700 (PDT)
Message-ID: <2051176845.1256767679670.JavaMail.jira@brutus>
Date: Wed, 28 Oct 2009 22:07:59 +0000 (UTC)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-2374) UNION PROBLEM WITH PARENTHESIS
In-Reply-To: <15209127.1172275745510.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394


    [ https://issues.apache.org/jira/browse/DERBY-2374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12771145#action_12771145 ] 

Mamta A. Satoor commented on DERBY-2374:
----------------------------------------

I have included selective part of the grammar from Derby and SQL standard to try to show how the implementation is different than the spec and probably the cause behind the behavior. The example query is as below 
select c1 from ((select c1 from t1) union (select c1 from t2)) t1t2;

I will work with the following part of the query above since the handling of first 2 tokens "select c1" seems same for Derby and SQL standard 
from ((select c1 from t1) union (select c1 from t2)) t1t2;

Derby implementation
1)Start with
from ((select c1 from t1) union (select c1 from t2)) t1t2;
2)Following rule consumes token from and the remaining string is handled by <table reference>
<from clause> ::= FROM <table reference>
Before <table reference> takes over, the string looks as follows
((select c1 from t1) union (select c1 from t2)) t1t2;
3)Following rules will be used next
<table reference> ::= <table factor>
<table factor> ::= <left paren> <table reference> <right paren>
Before going into the rule about <table reference> and after <left paren> consumption, our string will look as follows
(select c1 from t1) union (select c1 from t2)) t1t2;
The processing done by <table reference> <right paren> is covered in 4)
4)Following rules will be used next
<table reference> ::= <table factor>
<table factor> ::= <derived table>
<derived table> ::= <left paren> <table subquery> <right paren>
Before going into the rule about <table subquery> and after <left paren> consumption, our string will look as follows
select c1 from t1) union (select c1 from t2)) t1t2;
5)Next comes rules decided by <table subquery> <right paren>
<table subquery> ::= <queryExpression>
<queryExpression> ::= SELECT <column list> FROM <table reference> [UNION]******
******Very important to note that optional UNION token is checked by <queryExpression> and I think this is what is wrong. We either need some other place also to look for UNION or this [UNION] check should be moved out from here somewhere up. UNION or this [UNION] check should be moved out from here somewhere up. When <queryExpression> gets to optional UNION token check, our string will look as follows
) union (select c1 from t2)) t1t2;
5)Since the next token is not UNION, rather it is ), we get out of <queryExpression>, <table sunqery> and come to the <right paren> part of <derived table> as shown below
<derived table> ::= <left paren> <table subquery> <right paren>
After the <right paren> processing, our string looks as follows
union (select c1 from t2)) t1t2;
Notice now that the next token is union and we are going up the stack of rules, out from <derived table>, <table factor>, <table reference> and onto <table factor> again
<table factor> ::= <left paren> <table reference> <right paren>
Here, we are looking for <right paren> but what we have in the string is UNION and this is when the parser throws exception for unexpected UNION as per it's rules


SQL spec
For the same query above, this is how SQL spec rules kick in
1)Start with
from ((select c1 from t1) union (select c1 from t2)) t1t2;
<from clause> ::= <FROM> <table reference>
After the <FROM> consumption, our string will look as follows
((select c1 from t1) union (select c1 from t2)) t1t2;
2)Following 4 rules kick in
<table reference> ::= <table factor>
<table factor> ::= <derived table>
<derived table> ::= <table subquery>
<table subquery> ::= <left paren> <query expression> <right paren>
At the end of <left paren> consumption, our original sql will now look like following
(select c1 from t1) union (select c1 from t2)) t1t2;
3)First the <query expression body> of the rule below will kick in
<query expression> ::= <query expression body> UNION <query term>
<query expression body> ::= <query term>
<query term> ::= <left paren> <query expression body> <right paren>
After <left paren> from <query term>, now we have
select c1 from t1) union (select c1 from t2)) t1t2;
4)Once the <query term> below is finished, we will have
union (select c1 from t2)) t1t2;
5)Now we move up the stack of the rules fired so far and when we get back to <query expression> ::= <query expression body> UNION <query term>, we are done with <query expression body> and we will consume UNION from original sql as follows
(select c1 from t2)) t1t2;
So, as we see from the SQL spec rules, it has proper rules in place to handle parentheses around the left term of UNION which Derby rules do not support at this point.
 

> UNION PROBLEM WITH PARENTHESIS
> ------------------------------
>
>                 Key: DERBY-2374
>                 URL: https://issues.apache.org/jira/browse/DERBY-2374
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.1.3.3, 10.2.2.0, 10.3.3.1, 10.4.2.1, 10.5.3.1, 10.6.0.0
>            Reporter: Kenneth Gee
>            Priority: Minor
>
> The following query shows the error using the Derby demo toursDB:
> SELECT FLIGHT_ID , SEGMENT_NUMBER , ARRIVE_TIME
>   FROM (
>          (SELECT HW.FLIGHT_ID , HW.SEGMENT_NUMBER , HW.ARRIVE_TIME FROM FLIGHTS HW)
>            UNION
>          (SELECT SW.FLIGHT_ID , SW.SEGMENT_NUMBER , SW.ARRIVE_TIME FROM FLIGHTS SW)
>        ) SRC;
> ERROR 42X01: Syntax error: Encountered "UNION" at line 4, column 12.
> The following query works:
> SELECT FLIGHT_ID , SEGMENT_NUMBER , ARRIVE_TIME
>   FROM (
>          SELECT HW.FLIGHT_ID , HW.SEGMENT_NUMBER , HW.ARRIVE_TIME FROM FLIGHTS HW
>           UNION
>          SELECT SW.FLIGHT_ID , SW.SEGMENT_NUMBER , SW.ARRIVE_TIME FROM FLIGHTS SW
>        )  SRC;

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.