Hive
  1. Hive
  2. HIVE-6189

Support top level union all statements

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.13.0
    • Component/s: None
    • Labels:
      None

      Description

      I've always wondered why union all has to be in subqueries in hive.

      After looking at it, problems are:

      • Hive Parser:
      • Union happens at the wrong place (insert ... select ... union all select ...) is parsed as (insert select) union select.
      • There are many rewrite rules in the parser to force any query into the a from - insert -select form. No doubt for historical reasons.
      • Plan generation/semantic analysis assumes top level "TOK_QUERY" and not top level "TOK_UNION".

      The rewrite rules don't work when we move the "UNION ALL" recursion into the select statements. However, it's not hard to do that in code.

      1. HIVE-6189.3.patch
        71 kB
        Gunther Hagleitner
      2. HIVE-6189.2.patch
        89 kB
        Gunther Hagleitner
      3. HIVE-6189.1.patch
        83 kB
        Gunther Hagleitner

        Issue Links

          Activity

          Hide
          Gunther Hagleitner added a comment -

          Committed to trunk. Thanks for reviewing Harish Butani and Navis!

          Show
          Gunther Hagleitner added a comment - Committed to trunk. Thanks for reviewing Harish Butani and Navis !
          Hide
          Harish Butani added a comment -

          +1

          Show
          Harish Butani added a comment - +1
          Hide
          Gunther Hagleitner added a comment -

          Lefty Leverenz - the documentation in the link you sent looks good. We could specify that unions can be used in views, insert, and ctas statements, but this I'm thinking that's almost self explanatory. As for hive .12 and below - the restriction was that unions could only be used within a subquery. I.e.:

          "select_statement union all select_statement union all ..." had to be written as "select * from (select_statement union all select_statement union all ...) unionresult"

          Ditto for CTAS, insert, create/alter view as.

          Does that make sense?

          Show
          Gunther Hagleitner added a comment - Lefty Leverenz - the documentation in the link you sent looks good. We could specify that unions can be used in views, insert, and ctas statements, but this I'm thinking that's almost self explanatory. As for hive .12 and below - the restriction was that unions could only be used within a subquery. I.e.: "select_statement union all select_statement union all ..." had to be written as "select * from (select_statement union all select_statement union all ...) unionresult" Ditto for CTAS, insert, create/alter view as. Does that make sense?
          Hide
          Hive QA added a comment -

          Overall: +1 all checks pass

          Here are the results of testing the latest attachment:
          https://issues.apache.org/jira/secure/attachment/12623031/HIVE-6189.3.patch

          SUCCESS: +1 4925 tests passed

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/916/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/916/console

          Messages:

          Executing org.apache.hive.ptest.execution.PrepPhase
          Executing org.apache.hive.ptest.execution.ExecutionPhase
          Executing org.apache.hive.ptest.execution.ReportingPhase
          

          This message is automatically generated.

          ATTACHMENT ID: 12623031

          Show
          Hive QA added a comment - Overall : +1 all checks pass Here are the results of testing the latest attachment: https://issues.apache.org/jira/secure/attachment/12623031/HIVE-6189.3.patch SUCCESS: +1 4925 tests passed Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/916/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/916/console Messages: Executing org.apache.hive.ptest.execution.PrepPhase Executing org.apache.hive.ptest.execution.ExecutionPhase Executing org.apache.hive.ptest.execution.ReportingPhase This message is automatically generated. ATTACHMENT ID: 12623031
          Hide
          Navis added a comment -

          I'm pretty sure Harish is more proficient than me for this kind of works. Good to hear that hive supports top level union!

          Show
          Navis added a comment - I'm pretty sure Harish is more proficient than me for this kind of works. Good to hear that hive supports top level union!
          Hide
          Gunther Hagleitner added a comment -

          Thanks Navis for the review. I've added .3 because Harish Butani showed me a much better way to do this. He showed me how I can do all the necessary rewrites in the grammar without having to mess with the tree in semantic analysis. This is much cleaner.

          Show
          Gunther Hagleitner added a comment - Thanks Navis for the review. I've added .3 because Harish Butani showed me a much better way to do this. He showed me how I can do all the necessary rewrites in the grammar without having to mess with the tree in semantic analysis. This is much cleaner.
          Hide
          Lefty Leverenz added a comment -

          Apparently the nonsupport of top-level UNION ALL never got documented, so for starters that needs to be fixed in the wiki for Hive 0.12.0 and earlier. Then we'll need documentation for this ticket, which could go in a release note or directly into the wiki: Union Syntax.

          Show
          Lefty Leverenz added a comment - Apparently the nonsupport of top-level UNION ALL never got documented, so for starters that needs to be fixed in the wiki for Hive 0.12.0 and earlier. Then we'll need documentation for this ticket, which could go in a release note or directly into the wiki: Union Syntax .
          Hide
          Navis added a comment -

          +1

          Show
          Navis added a comment - +1
          Hide
          Hive QA added a comment -

          Overall: +1 all checks pass

          Here are the results of testing the latest attachment:
          https://issues.apache.org/jira/secure/attachment/12622691/HIVE-6189.2.patch

          SUCCESS: +1 4924 tests passed

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/891/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/891/console

          Messages:

          Executing org.apache.hive.ptest.execution.PrepPhase
          Executing org.apache.hive.ptest.execution.ExecutionPhase
          Executing org.apache.hive.ptest.execution.ReportingPhase
          

          This message is automatically generated.

          ATTACHMENT ID: 12622691

          Show
          Hive QA added a comment - Overall : +1 all checks pass Here are the results of testing the latest attachment: https://issues.apache.org/jira/secure/attachment/12622691/HIVE-6189.2.patch SUCCESS: +1 4924 tests passed Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/891/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/891/console Messages: Executing org.apache.hive.ptest.execution.PrepPhase Executing org.apache.hive.ptest.execution.ExecutionPhase Executing org.apache.hive.ptest.execution.ReportingPhase This message is automatically generated. ATTACHMENT ID: 12622691
          Show
          Gunther Hagleitner added a comment - RB: https://reviews.apache.org/r/16818/
          Hide
          Gunther Hagleitner added a comment -

          .2 addresses the failures.

          Show
          Gunther Hagleitner added a comment - .2 addresses the failures.
          Hide
          Hive QA added a comment -

          Overall: -1 at least one tests failed

          Here are the results of testing the latest attachment:
          https://issues.apache.org/jira/secure/attachment/12622596/HIVE-6189.1.patch

          ERROR: -1 due to 7 failed/errored test(s), 4917 tests executed
          Failed tests:

          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_subq_insert
          org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample2
          org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample3
          org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample4
          org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample5
          org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample6
          org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample7
          

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/881/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/881/console

          Messages:

          Executing org.apache.hive.ptest.execution.PrepPhase
          Executing org.apache.hive.ptest.execution.ExecutionPhase
          Executing org.apache.hive.ptest.execution.ReportingPhase
          Tests exited with: TestsFailedException: 7 tests failed
          

          This message is automatically generated.

          ATTACHMENT ID: 12622596

          Show
          Hive QA added a comment - Overall : -1 at least one tests failed Here are the results of testing the latest attachment: https://issues.apache.org/jira/secure/attachment/12622596/HIVE-6189.1.patch ERROR: -1 due to 7 failed/errored test(s), 4917 tests executed Failed tests: org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_subq_insert org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample2 org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample3 org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample4 org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample5 org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample6 org.apache.hadoop.hive.ql.parse.TestParse.testParse_sample7 Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/881/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/881/console Messages: Executing org.apache.hive.ptest.execution.PrepPhase Executing org.apache.hive.ptest.execution.ExecutionPhase Executing org.apache.hive.ptest.execution.ReportingPhase Tests exited with: TestsFailedException: 7 tests failed This message is automatically generated. ATTACHMENT ID: 12622596

            People

            • Assignee:
              Gunther Hagleitner
              Reporter:
              Gunther Hagleitner
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development