Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6984

Cannot use UNION in correlated subquery or derived table when first UNION subquery is parenthesised

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.14.1.0
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None

      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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              lukas.eder Lukas Eder
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: