Hive
  1. Hive
  2. HIVE-6013

Supporting Quoted Identifiers in Column Names

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.13.0
    • Component/s: Query Processor
    • Labels:
      None
    • Hadoop Flags:
      Incompatible change
    • Release Note:
      Hide
      The default behavior for quoted identifiers has been changed. Now at the language level any Column Name that is specified within back-ticks(`) is treated literally. This is inline with standard sql behavior for quoted identifiers. Within back-tick strings use double back-ticks to escape.

      To revert to old behavior(that of interpreting back-ticked names as regular expressions) use the new parameter: hive.support.quoted.identifiers; set to the value 'none'.
      Show
      The default behavior for quoted identifiers has been changed. Now at the language level any Column Name that is specified within back-ticks(`) is treated literally. This is inline with standard sql behavior for quoted identifiers. Within back-tick strings use double back-ticks to escape. To revert to old behavior(that of interpreting back-ticked names as regular expressions) use the new parameter: hive.support.quoted.identifiers; set to the value 'none'.

      Description

      Hive's current behavior on Quoted Identifiers is different from the normal interpretation. Quoted Identifier (using backticks) has a special interpretation for Select expressions(as Regular Expressions). Have documented current behavior and proposed a solution in attached doc.

      Summary of solution is:

      • Introduce 'standard' quoted identifiers for columns only.
      • At the langauage level this is turned on by a flag.
      • At the metadata level we relax the constraint on column names.
      1. HIVE-6013.1.patch
        99 kB
        Harish Butani
      2. HIVE-6013.2.patch
        102 kB
        Harish Butani
      3. HIVE-6013.3.patch
        101 kB
        Harish Butani
      4. HIVE-6013.4.patch
        102 kB
        Harish Butani
      5. HIVE-6013.5.patch
        102 kB
        Harish Butani
      6. HIVE-6013.6.patch
        105 kB
        Harish Butani
      7. HIVE-6013.7.patch
        123 kB
        Harish Butani
      8. QuotedIdentifier.html
        20 kB
        Harish Butani

        Issue Links

          Activity

          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/12618322/HIVE-6013.1.patch

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

          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_quotedId_alter
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_quotedId_skew
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_quotedId_smb
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_invalid_columns
          

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/616/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/616/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: 4 tests failed
          

          This message is automatically generated.

          ATTACHMENT ID: 12618322

          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/12618322/HIVE-6013.1.patch ERROR: -1 due to 4 failed/errored test(s), 4768 tests executed Failed tests: org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_quotedId_alter org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_quotedId_skew org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_quotedId_smb org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_invalid_columns Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/616/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/616/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: 4 tests failed This message is automatically generated. ATTACHMENT ID: 12618322
          Hide
          Eric Hanson added a comment -

          The spec sounds reasonable. I'm fine with it. I'm curious though, did you consider allowing square brackets to delimit an identifier, like SQL Server allows? E.g. create table sale([Shipment Date] date, ....); That seems a tad more readable than the back-quotes.

          Show
          Eric Hanson added a comment - The spec sounds reasonable. I'm fine with it. I'm curious though, did you consider allowing square brackets to delimit an identifier, like SQL Server allows? E.g. create table sale( [Shipment Date] date, ....); That seems a tad more readable than the back-quotes.
          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/12618945/HIVE-6013.2.patch

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

          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_quotedid_skew
          

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/653/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/653/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: 1 tests failed
          

          This message is automatically generated.

          ATTACHMENT ID: 12618945

          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/12618945/HIVE-6013.2.patch ERROR: -1 due to 1 failed/errored test(s), 4790 tests executed Failed tests: org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_quotedid_skew Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/653/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/653/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: 1 tests failed This message is automatically generated. ATTACHMENT ID: 12618945
          Hide
          Harish Butani added a comment -

          Back-ticks are already there (sort of). For e.g: today view definitions are unparsed(and persisted) to wrap all identifiers with back-ticks.
          Moving to square brackets would either require a migration step, or supporting both back-ticks and square brackets. Don't want to take on either option in this first step.
          Besides not clear which is the preferred style for the hive community: back-ticks seems to have come from the mysql way of specifying quoted identifiers.

          Show
          Harish Butani added a comment - Back-ticks are already there (sort of). For e.g: today view definitions are unparsed(and persisted) to wrap all identifiers with back-ticks. Moving to square brackets would either require a migration step, or supporting both back-ticks and square brackets. Don't want to take on either option in this first step. Besides not clear which is the preferred style for the hive community: back-ticks seems to have come from the mysql way of specifying quoted identifiers.
          Hide
          Eric Hanson added a comment -

          Okay, sounds fine then.

          Show
          Eric Hanson added a comment - Okay, sounds fine then.
          Hide
          Sergey Shelukhin added a comment -

          The fun fact is that double quotes are ANSI way of specifying quoted identifiers

          Show
          Sergey Shelukhin added a comment - The fun fact is that double quotes are ANSI way of specifying quoted identifiers
          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/12618984/HIVE-6013.3.patch

          SUCCESS: +1 4793 tests passed

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/659/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/659/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: 12618984

          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/12618984/HIVE-6013.3.patch SUCCESS: +1 4793 tests passed Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/659/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/659/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: 12618984
          Hide
          Ashutosh Chauhan added a comment -

          Approach looks ok to me. Some implementation level comments on RB.

          One test scenario. If this is already covered in your test, feel free to ignore. Otherwise, can you add following test.
          set hive.support.quoted.identifiers=column;
          create table t1 (aa int, ab string);
          select a.* from t1; – this should select both columns.

          Also, you mentioned in html doc, some of jdbc api methods need to change, but I don't see any changes in jdbc package.

          Show
          Ashutosh Chauhan added a comment - Approach looks ok to me. Some implementation level comments on RB. One test scenario. If this is already covered in your test, feel free to ignore. Otherwise, can you add following test. set hive.support.quoted.identifiers=column; create table t1 (aa int, ab string); select a.* from t1; – this should select both columns. Also, you mentioned in html doc, some of jdbc api methods need to change, but I don't see any changes in jdbc package.
          Hide
          Harish Butani added a comment -

          1. there is a .q that covers this: regex_col.q
          2. Oh, yes. I forgot about the jdbc metadata apis. Just looked at the jdbc project. Currently a lot of the methods in the DBMetadata class just throw SQLException("Method not supported"). Who should I talk to about this? Can this be done in a followup jira.

          Show
          Harish Butani added a comment - 1. there is a .q that covers this: regex_col.q 2. Oh, yes. I forgot about the jdbc metadata apis. Just looked at the jdbc project. Currently a lot of the methods in the DBMetadata class just throw SQLException("Method not supported"). Who should I talk to about this? Can this be done in a followup jira.
          Hide
          Ashutosh Chauhan added a comment -

          1. But that test case doesn't have set hive.support.quoted.identifiers=column;
          2. Doing jdbc changes in follow-up is ok.

          Show
          Ashutosh Chauhan added a comment - 1. But that test case doesn't have set hive.support.quoted.identifiers=column; 2. Doing jdbc changes in follow-up is ok.
          Hide
          Harish Butani added a comment -

          You mean set hive.support.quoted.identifiers=none; right?
          With it is set to 'column' it will treat it as a literal.
          And the query would be: select `a.*` from t1;
          You need the back-ticks. Otherwise this will not get past the lexer.

          Since 'none' is default setting, i thought the exiting test was enough.

          Show
          Harish Butani added a comment - You mean set hive.support.quoted.identifiers=none; right? With it is set to 'column' it will treat it as a literal. And the query would be: select `a.*` from t1; You need the back-ticks. Otherwise this will not get past the lexer. Since 'none' is default setting, i thought the exiting test was enough.
          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/12619370/HIVE-6013.4.patch

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

          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_create_or_replace_view
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_create_view_partitioned
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_database_drop
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_describe_formatted_view_partitioned
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_describe_formatted_view_partitioned_json
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auth
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_empty
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_file_format
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_mult_tables
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_mult_tables_compact
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_multiple
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_partitioned
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_self_join
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_unused
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_update
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap1
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap2
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap3
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap_auto
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap_auto_partitioned
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap_compression
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap_rc
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compact
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compact_1
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compact_2
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compact_3
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compact_binary_search
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compression
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_creation
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_serde
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_stale
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_stale_partitioned
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_infer_bucket_sort_convert_join
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_input3
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_ql_rewrite_gbtoidx
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_show_indexes_edge_cases
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_show_indexes_syntax
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_union_view
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_alter_view_as_select_with_partition
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_alter_view_failure4
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_alter_view_failure6
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_create_or_replace_view1
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_create_or_replace_view2
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_index_bitmap_no_map_aggr
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_index_compact_entry_limit
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_index_compact_size_limit
          

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/689/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/689/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: 48 tests failed
          

          This message is automatically generated.

          ATTACHMENT ID: 12619370

          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/12619370/HIVE-6013.4.patch ERROR: -1 due to 48 failed/errored test(s), 4795 tests executed Failed tests: org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_create_or_replace_view org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_create_view_partitioned org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_database_drop org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_describe_formatted_view_partitioned org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_describe_formatted_view_partitioned_json org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auth org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_empty org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_file_format org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_mult_tables org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_mult_tables_compact org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_multiple org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_partitioned org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_self_join org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_unused org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_auto_update org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap1 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap2 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap3 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap_auto org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap_auto_partitioned org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap_compression org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap_rc org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compact org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compact_1 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compact_2 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compact_3 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compact_binary_search org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_compression org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_creation org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_serde org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_stale org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_stale_partitioned org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_infer_bucket_sort_convert_join org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_input3 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_ql_rewrite_gbtoidx org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_show_indexes_edge_cases org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_show_indexes_syntax org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_union_view org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_alter_view_as_select_with_partition org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_alter_view_failure4 org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_alter_view_failure6 org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_create_or_replace_view1 org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_create_or_replace_view2 org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_index_bitmap_no_map_aggr org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_index_compact_entry_limit org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_index_compact_size_limit Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/689/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/689/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: 48 tests failed This message is automatically generated. ATTACHMENT ID: 12619370
          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/12619403/HIVE-6013.5.patch

          SUCCESS: +1 4796 tests passed

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/690/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/690/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: 12619403

          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/12619403/HIVE-6013.5.patch SUCCESS: +1 4796 tests passed Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/690/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/690/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: 12619403
          Hide
          Harish Butani added a comment -

          had a discussion with Ashutosh Chauhan
          Leaning towards setting 'hive.support.quoted.identifiers' to support quoted identifiers by default.
          This is a backward incompatible change.
          Assumption is that the regex feature with backticks is a obscure feature; it makes more sense to have this feature on by default.
          Will document the incompatible change.
          If anybody strongly disagrees with this, please voice your concerns.

          Show
          Harish Butani added a comment - had a discussion with Ashutosh Chauhan Leaning towards setting 'hive.support.quoted.identifiers' to support quoted identifiers by default. This is a backward incompatible change. Assumption is that the regex feature with backticks is a obscure feature; it makes more sense to have this feature on by default. Will document the incompatible change. If anybody strongly disagrees with this, please voice your concerns.
          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/12619449/HIVE-6013.6.patch

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

          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_ambiguous_col
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_auto_join_reordering_values
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_escape_clusterby1
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_escape_distributeby1
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_escape_orderby1
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_escape_sortby1
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap3
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap_auto
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_quote1
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_show_tablestatus
          org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_udf_index
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_ambiguous_col1
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_ambiguous_col2
          org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_invalidate_view1
          

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/693/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/693/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: 14 tests failed
          

          This message is automatically generated.

          ATTACHMENT ID: 12619449

          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/12619449/HIVE-6013.6.patch ERROR: -1 due to 14 failed/errored test(s), 4796 tests executed Failed tests: org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_ambiguous_col org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_auto_join_reordering_values org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_escape_clusterby1 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_escape_distributeby1 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_escape_orderby1 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_escape_sortby1 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap3 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_index_bitmap_auto org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_quote1 org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_show_tablestatus org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_udf_index org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_ambiguous_col1 org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_ambiguous_col2 org.apache.hadoop.hive.cli.TestNegativeCliDriver.testNegativeCliDriver_invalidate_view1 Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/693/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/693/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: 14 tests failed This message is automatically generated. ATTACHMENT ID: 12619449
          Hide
          Ashutosh Chauhan added a comment -

          +1

          Show
          Ashutosh Chauhan 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/12619598/HIVE-6013.7.patch

          SUCCESS: +1 4799 tests passed

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/703/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/703/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: 12619598

          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/12619598/HIVE-6013.7.patch SUCCESS: +1 4799 tests passed Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/703/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/703/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: 12619598
          Hide
          Ashutosh Chauhan added a comment -

          Committed to trunk. Thanks, Harish!

          Show
          Ashutosh Chauhan added a comment - Committed to trunk. Thanks, Harish!
          Hide
          Lefty Leverenz added a comment -

          Do you want the attached html doc to go in the wiki design docs, or should I just document the new behavior and hive.support.quoted.identifiers in the user docs?

          Show
          Lefty Leverenz added a comment - Do you want the attached html doc to go in the wiki design docs, or should I just document the new behavior and hive.support.quoted.identifiers in the user docs?
          Hide
          Harish Butani added a comment -

          No need to add as a design doc.
          Thanks for doing this.

          Show
          Harish Butani added a comment - No need to add as a design doc. Thanks for doing this.
          Hide
          Thejas M Nair added a comment -

          Harish,
          The doc seems to suggest that quoted identfiers are supported only for column names. But it seems to work when I try it with user name in grant statement. Is that not expected to work ? - eg -

           grant all on x to user `user-qa`; 
          show grant user `user-qa` on table x; 
          
          Show
          Thejas M Nair added a comment - Harish, The doc seems to suggest that quoted identfiers are supported only for column names. But it seems to work when I try it with user name in grant statement. Is that not expected to work ? - eg - grant all on x to user `user-qa`; show grant user `user-qa` on table x;
          Hide
          Harish Butani added a comment -

          At the language level any identifier can be quoted. The change made was at the Lexer level.
          Special characters is probably ok in Usernames. I didn't want to make this assertion because there maybe code in the metadata layer that doesn't like special characters. For e.g we know for tableNames this is an issue.
          If you don't anticipate an issue, we can say that special characters are supported for Usernames. Hopefully this can be extended to role/privilege names also.

          Show
          Harish Butani added a comment - At the language level any identifier can be quoted. The change made was at the Lexer level. Special characters is probably ok in Usernames. I didn't want to make this assertion because there maybe code in the metadata layer that doesn't like special characters. For e.g we know for tableNames this is an issue. If you don't anticipate an issue, we can say that special characters are supported for Usernames. Hopefully this can be extended to role/privilege names also.
          Hide
          Thejas M Nair added a comment -

          I have created a jira to test it out with users and role names - HIVE-6305 . I think it should work fine.
          Lefty Leverenz, is the documentation of this jira already part of any wiki page ? I had trouble finding it.

          Show
          Thejas M Nair added a comment - I have created a jira to test it out with users and role names - HIVE-6305 . I think it should work fine. Lefty Leverenz , is the documentation of this jira already part of any wiki page ? I had trouble finding it.
          Hide
          Lefty Leverenz added a comment -

          Not in the wiki yet. I'll bump its priority to the top.

          Show
          Lefty Leverenz added a comment - Not in the wiki yet. I'll bump its priority to the top.
          Hide
          Lefty Leverenz added a comment -

          Are backticks required for column names? If not, then do column names specified without backticks have the old limitations (alphanumeric plus underscore) even when hive.support.quoted.identifiers is set to 'column'?

          Show
          Lefty Leverenz added a comment - Are backticks required for column names? If not, then do column names specified without backticks have the old limitations (alphanumeric plus underscore) even when hive.support.quoted.identifiers is set to 'column'?
          Hide
          Lefty Leverenz added a comment -

          Please review the naming information in these two sections of the DDL wikidoc:

          Still to come: adding hive.support.quoted.identifiers to the Configuration Properties wikidoc.

          Show
          Lefty Leverenz added a comment - Please review the naming information in these two sections of the DDL wikidoc: Create Table (right after the syntax) Alter Column Still to come: adding hive.support.quoted.identifiers to the Configuration Properties wikidoc.
          Hide
          Harish Butani added a comment -

          looks good. thanks for doing this

          Show
          Harish Butani added a comment - looks good. thanks for doing this
          Hide
          Lefty Leverenz added a comment -

          This needs to be documented in various SELECT sections too. If backticks are required when hive.support.quoted.identifiers is set to 'column' then a lot of examples in Getting Started, the Tutorial, Language Manual's Select and other Language Manual docs will need to be changed (or rather, duplicated with a version note), for example:

          Can wildcards/regex be used when hive.support.quoted.identifiers is 'column'? Presumably 'SELECT * FROM table' is still valid. Here's the current documentation:

          Show
          Lefty Leverenz added a comment - This needs to be documented in various SELECT sections too. If backticks are required when hive.support.quoted.identifiers is set to 'column' then a lot of examples in Getting Started, the Tutorial, Language Manual's Select and other Language Manual docs will need to be changed (or rather, duplicated with a version note), for example: Example Queries Querying and Inserting Data Select Can wildcards/regex be used when hive.support.quoted.identifiers is 'column'? Presumably 'SELECT * FROM table' is still valid. Here's the current documentation: REGEX Column Specification
          Hide
          Harish Butani added a comment -

          No with hive.support.quoted.identifiers=column names in backticks are treated literally.
          Select * is supported in either case.

          Had a cursory look at the examples, don't see why they need to be changed. Are there particular ones you are referring to.

          Show
          Harish Butani added a comment - No with hive.support.quoted.identifiers=column names in backticks are treated literally. Select * is supported in either case. Had a cursory look at the examples, don't see why they need to be changed. Are there particular ones you are referring to.
          Hide
          Lefty Leverenz added a comment -

          I guess I misunderstood. So with hive.support.quoted.identifiers=column, you can use backticks to allow Unicode characters in column names but backticks are not required if the names are restricted to alphanumeric plus underscore. Is that right, for both DDL and queries?

          And if you omit the backticks, you can use * as a wildcard for "all columns" in queries. But you can't have regex queries unless hive.support.quoted.identifiers=none, in which case backticks surround the regular expression.

          What happens if hive.support.quoted.identifiers=none (in Hive 0.13) and you use backticks for a regex, then escape some Unicode characters? That's probably not a realistic use case, but I'm wondering if it's a way to select Unicode column names when you've got the wrong configuration setting.

          Show
          Lefty Leverenz added a comment - I guess I misunderstood. So with hive.support.quoted.identifiers=column, you can use backticks to allow Unicode characters in column names but backticks are not required if the names are restricted to alphanumeric plus underscore. Is that right, for both DDL and queries? And if you omit the backticks, you can use * as a wildcard for "all columns" in queries. But you can't have regex queries unless hive.support.quoted.identifiers=none, in which case backticks surround the regular expression. What happens if hive.support.quoted.identifiers=none (in Hive 0.13) and you use backticks for a regex, then escape some Unicode characters? That's probably not a realistic use case, but I'm wondering if it's a way to select Unicode column names when you've got the wrong configuration setting.
          Hide
          Harish Butani added a comment -

          Answer to your first 2 questions is yes.

          For the scenario you layout in the 3rd paragraph, if hive.support.quoted.identifiers=none you will not be able to create columns with special characters; so regexes will not help. The regex style is only allowed in the select clause.

          Show
          Harish Butani added a comment - Answer to your first 2 questions is yes. For the scenario you layout in the 3rd paragraph, if hive.support.quoted.identifiers=none you will not be able to create columns with special characters; so regexes will not help. The regex style is only allowed in the select clause.
          Hide
          Lefty Leverenz added a comment -

          This is documented in a bullet list after the CREATE TABLE syntax and in a version box in the Alter Column section. It's also documented after the SELECT syntax and in the section "REGEX Column Specification":

          Show
          Lefty Leverenz added a comment - This is documented in a bullet list after the CREATE TABLE syntax and in a version box in the Alter Column section. It's also documented after the SELECT syntax and in the section "REGEX Column Specification": Create Table Alter Column – Rules for Column Names Select Syntax REGEX Column Specification
          Hide
          Lefty Leverenz added a comment -

          The configuration parameter hive.support.quoted.identifiers is documented in the wiki:

          Show
          Lefty Leverenz added a comment - The configuration parameter hive.support.quoted.identifiers is documented in the wiki: Configuration Properties – hive.support.quoted.identifiers
          Hide
          ASF GitHub Bot added a comment -

          GitHub user Jeffrio opened a pull request:

          https://github.com/apache/hive/pull/31

          Update HiveDatabaseMetaData.java change the identifierQuoteString

          according to this jira https://issues.apache.org/jira/browse/HIVE-6013
          hive use the backstick as the quotestring
          so, I think the getIdentifierQuoteString() function should return the backstick rather than the space

          You can merge this pull request into a Git repository by running:

          $ git pull https://github.com/Jeffrio/hive patch-1

          Alternatively you can review and apply these changes as the patch at:

          https://github.com/apache/hive/pull/31.patch

          To close this pull request, make a commit to your master/trunk branch
          with (at least) the following in the commit message:

          This closes #31


          commit 5ac637c83615aa389db49ce169c0df0461619c63
          Author: Jeffrio <corejeff@163.com>
          Date: 2015-04-07T16:35:11Z

          Update HiveDatabaseMetaData.java change the identifierQuoteString

          according to this jira https://issues.apache.org/jira/browse/HIVE-6013
          hive use the backstick as the quotestring
          so, I think the getIdentifierQuoteString() function should return the backstick rather than the space


          Show
          ASF GitHub Bot added a comment - GitHub user Jeffrio opened a pull request: https://github.com/apache/hive/pull/31 Update HiveDatabaseMetaData.java change the identifierQuoteString according to this jira https://issues.apache.org/jira/browse/HIVE-6013 hive use the backstick as the quotestring so, I think the getIdentifierQuoteString() function should return the backstick rather than the space You can merge this pull request into a Git repository by running: $ git pull https://github.com/Jeffrio/hive patch-1 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/hive/pull/31.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #31 commit 5ac637c83615aa389db49ce169c0df0461619c63 Author: Jeffrio <corejeff@163.com> Date: 2015-04-07T16:35:11Z Update HiveDatabaseMetaData.java change the identifierQuoteString according to this jira https://issues.apache.org/jira/browse/HIVE-6013 hive use the backstick as the quotestring so, I think the getIdentifierQuoteString() function should return the backstick rather than the space

            People

            • Assignee:
              Harish Butani
              Reporter:
              Harish Butani
            • Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development