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)
