db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2374) UNION PROBLEM WITH PARENTHESIS
Date Fri, 23 Oct 2009 17:16:59 GMT

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

Dag H. Wanvik commented on DERBY-2374:
--------------------------------------

Just to make sure it is allowed, I did the derivation as an exercise ;-) 

Quote from std (2003):

<query expression body> ::=
    <query term>
    | <query expression body> UNION [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query term>
    | <query expression body> EXCEPT [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query term>

<query term> ::=
    <query primary>
    | <query term> INTERSECT [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query primary>

<query primary> ::=
    <simple table>
    | <left paren> <query expression body> <right paren>

<simple table> ::=
    <query specification>
    | <table value constructor>
    | <explicit table>

<query specification> ::=
    SELECT [ <set quantifier> ] <select list> <table expression>


Example:
  SELECT c1 FROM ((SELECT c1 FROM t1) UNION (SELECT c1 FROM t2)) t1t2;


Derivation:

               <query expression body>
               <query expression body> UNION ...
               <query term> UNION ...
               <query primary> UNION ...
               <left paren> <query expression body> <right paren> UNION
...
               ( <query expression body> ) UNION ...
               ( <query term> ) UNION ...
               ( <query primary> ) UNION ...
               ( <simple table> ) UNION ...
               ( <query specification> ) UNION ...
               ( SELECT c1 FROM t1 ) UNION ...
               
Similarly for the right hand side of the union.

> 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.2.2.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.


Mime
View raw message