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

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.14.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Lukas Eder
• Created: 26/Feb/18 11:54
• Updated: 26/Feb/18 11:54

Description

This is a correct SQL query in Derby:

 

(
  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
)

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:

 

 

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

The error I'm getting is

 

Syntax error: Encountered "UNION" at line 10, column 3.

 

 

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:

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

Prepending a dummy union subquery:

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