[jira] Commented: (DERBY-2374) UNION PROBLEM WITH PARENTHESIS

["Bryan Pendleton (JIRA)" <ji...@apache.org>]

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

Bryan Pendleton commented on DERBY-2374:
----------------------------------------

Interestingly, the parentheses are OK if placed on the right side
of the union sub-select, but not if placed on the left. That is, this 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;

I wonder if the issue is related to this comment in sqlgrammar.jj:

/*
 * <A NAME="queryExpression">queryExpression</A>
 *
 * We have to be carefull to get the associativity correct. According to the SQL
 spec
 *   <non-join query expression> ::=
 *     <non-join query term>
 *    | <query expression body> UNION [ ALL ] <query term>
 *    | <query expression body> EXCEPT [ ALL ] <query term>
 * Meaning that
 *   t1 UNION ALL t2 UNION t3
 * is equivalent to
 *   (t1 UNION ALL t2) UNION t3
 * However recursive descent parsers want recursion to be on the right, so this
kind of associativity is unnatural
 * for our parser. The queryExpression method must know whether it is being call
ed as the right hand side of a
 * set operator to produce a query tree with the correct associativity.
 */

Here's the relevant snip from the SQL 92 BNF grammar.

         7.10  <query expression>

         Function

         Specify a table.

         Format

         <query expression> ::=
                <non-join query expression>
              | <joined table>

         <non-join query expression> ::=
                <non-join query term>
              | <query expression> UNION  [ ALL ] [ <corresponding spec> ] <query term>

              | <query expression> EXCEPT [ ALL ] [ <corresponding spec> ] <query term>


         <query term> ::=
                <non-join query term>
              | <joined table>

         <non-join query term> ::=
                <non-join query primary>
              | <query term> INTERSECT [ ALL ] [ <corresponding spec> ] <query primary>


         <query primary> ::=
                <non-join query primary>
              | <joined table>

         <non-join query primary> ::=
                <simple table>
              | <left paren> <non-join query expression> <right paren>

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

That's as far as I went with this, as my skills with diagnosing
generated parsers kind of hit a roadblock. :)


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