[jira] [Created] (DERBY-6984) Cannot use UNION in correlated subquery or derived table when first UNION subquery is parenthesised

["Lukas Eder (JIRA)" <ji...@apache.org>]

Lukas Eder created DERBY-6984:
---------------------------------

             Summary: Cannot use UNION in correlated subquery or derived table when first UNION subquery is parenthesised
                 Key: DERBY-6984
                 URL: https://issues.apache.org/jira/browse/DERBY-6984
             Project: Derby
          Issue Type: Improvement
          Components: SQL
    Affects Versions: 10.14.1.0
            Reporter: Lukas Eder


This is a correct SQL query in Derby:

 
{code:java}
(
  SELECT tablename
  FROM sys.systables
  ORDER BY tablename
  FETCH FIRST ROW ONLY
)
UNION ALL
(
  SELECT tablename
  FROM sys.systables
  ORDER BY tablename DESC
  FETCH FIRST ROW ONLY
){code}
Now, if I want to put this query in a derived table or correlated subquery, it doesn't work. The expectedly correct syntax should be:

 

 
{code:java}
SELECT *
FROM (
  (
    SELECT tablename
    FROM sys.systables
    ORDER BY tablename
    FETCH FIRST ROW ONLY
  )
  UNION ALL
  (
    SELECT tablename
    FROM sys.systables
    ORDER BY tablename DESC
    FETCH FIRST ROW ONLY
  )
) t{code}
The error I'm getting is

 
{code:java}
Syntax error: Encountered "UNION" at line 10, column 3.{code}
 

 

In this case, I cannot remove the parentheses around the first union subquery because of the ORDER BY / FETCH clauses. These are workarounds:

*Wrapping the first subquery in a derived table:*
{code:java}
SELECT *
FROM (
  SELECT tablename
  FROM (
    SELECT tablename
    FROM sys.systables
    ORDER BY tablename
    FETCH FIRST ROW ONLY
  ) t
  UNION ALL
  (
    SELECT tablename
    FROM sys.systables
    ORDER BY tablename DESC
    FETCH FIRST ROW ONLY
  )
) t{code}
*Prepending a dummy union subquery:*
{code:java}
SELECT *
FROM (
  SELECT '' tablename
  FROM sysibm.sysdummy1
  WHERE 1 = 0
  UNION ALL
  (
    SELECT tablename
    FROM sys.systables
    ORDER BY tablename
    FETCH FIRST ROW ONLY
  )
  UNION ALL
  (
    SELECT tablename
    FROM sys.systables
    ORDER BY tablename DESC
    FETCH FIRST ROW ONLY
  )
) t{code}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)