FAQ
Repository: hive
Updated Branches:
   refs/heads/branch-1 10ba196f0 -> 908e600ef


HIVE-13302 : direct SQL: cast to date doesn't work on Oracle (Sergey Shelukhin, reviewed by Sushanth Sowmyan)

Conflicts:
  metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java


Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/908e600e
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/908e600e
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/908e600e

Branch: refs/heads/branch-1
Commit: 908e600efa24d2d0094aaf63fafc4fbd54a20da1
Parents: 10ba196
Author: Sergey Shelukhin <sershe@apache.org>
Authored: Tue Mar 22 15:20:02 2016 -0700
Committer: Sergey Shelukhin <sershe@apache.org>
Committed: Tue Mar 22 15:22:21 2016 -0700

----------------------------------------------------------------------
  .../hive/metastore/MetaStoreDirectSql.java | 21 +++++++++++++-------
  1 file changed, 14 insertions(+), 7 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/hive/blob/908e600e/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java
----------------------------------------------------------------------
diff --git a/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java b/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java
index 3ba7d64..d345e9a 100644
--- a/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java
+++ b/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java
@@ -359,7 +359,7 @@ class MetaStoreDirectSql {
      // Derby and Oracle do not interpret filters ANSI-properly in some cases and need a workaround.
      boolean dbHasJoinCastBug = (dbType == DB.DERBY || dbType == DB.ORACLE);
      String sqlFilter = PartitionFilterGenerator.generateSqlFilter(
- table, tree, params, joins, dbHasJoinCastBug);
+ table, tree, params, joins, dbHasJoinCastBug, dbType);
      if (sqlFilter == null) {
        return null; // Cannot make SQL filter to push down.
      }
@@ -901,14 +901,16 @@ class MetaStoreDirectSql {
      private final List<Object> params;
      private final List<String> joins;
      private final boolean dbHasJoinCastBug;
+ private final DB dbType;

- private PartitionFilterGenerator(
- Table table, List<Object> params, List<String> joins, boolean dbHasJoinCastBug) {
+ private PartitionFilterGenerator(Table table, List<Object> params, List<String> joins,
+ boolean dbHasJoinCastBug, DB dbType) {
        this.table = table;
        this.params = params;
        this.joins = joins;
        this.dbHasJoinCastBug = dbHasJoinCastBug;
        this.filterBuffer = new FilterBuilder(false);
+ this.dbType = dbType;
      }

      /**
@@ -918,14 +920,14 @@ class MetaStoreDirectSql {
       * @param joins the joins necessary for the resulting expression
       * @return the string representation of the expression tree
       */
- private static String generateSqlFilter(Table table, ExpressionTree tree,
- List<Object> params, List<String> joins, boolean dbHasJoinCastBug) throws MetaException {
+ private static String generateSqlFilter(Table table, ExpressionTree tree, List<Object> params,
+ List<String> joins, boolean dbHasJoinCastBug, DB dbType) throws MetaException {
        assert table != null;
        if (tree.getRoot() == null) {
          return "";
        }
        PartitionFilterGenerator visitor = new PartitionFilterGenerator(
- table, params, joins, dbHasJoinCastBug);
+ table, params, joins, dbHasJoinCastBug, dbType);
        tree.accept(visitor);
        if (visitor.filterBuffer.hasError()) {
          LOG.info("Unable to push down SQL filter: " + visitor.filterBuffer.getErrorMessage());
@@ -1061,7 +1063,12 @@ class MetaStoreDirectSql {
          if (colType == FilterType.Integral) {
            tableValue = "cast(" + tableValue + " as decimal(21,0))";
          } else if (colType == FilterType.Date) {
- tableValue = "cast(" + tableValue + " as date)";
+ if (dbType == DB.ORACLE) {
+ // Oracle requires special treatment... as usual.
+ tableValue = "TO_DATE(" + tableValue + ", 'YYYY-MM-DD')";
+ } else {
+ tableValue = "cast(" + tableValue + " as date)";
+ }
          }

          if (dbHasJoinCastBug) {

Search Discussions

  • Sershe at Mar 22, 2016 at 10:23 pm
    Repository: hive
    Updated Branches:
       refs/heads/master 8f27cfe3c -> e3ca81ea7


    HIVE-13302 : direct SQL: cast to date doesn't work on Oracle (Sergey Shelukhin, reviewed by Sushanth Sowmyan)


    Project: http://git-wip-us.apache.org/repos/asf/hive/repo
    Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/e3ca81ea
    Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/e3ca81ea
    Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/e3ca81ea

    Branch: refs/heads/master
    Commit: e3ca81ea7552193f9e4ec7c83cd220856f64d7ac
    Parents: 8f27cfe
    Author: Sergey Shelukhin <sershe@apache.org>
    Authored: Tue Mar 22 15:20:02 2016 -0700
    Committer: Sergey Shelukhin <sershe@apache.org>
    Committed: Tue Mar 22 15:23:25 2016 -0700

    ----------------------------------------------------------------------
      .../hive/metastore/MetaStoreDirectSql.java | 21 +++++++++++++-------
      1 file changed, 14 insertions(+), 7 deletions(-)
    ----------------------------------------------------------------------


    http://git-wip-us.apache.org/repos/asf/hive/blob/e3ca81ea/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java
    ----------------------------------------------------------------------
    diff --git a/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java b/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java
    index 26fbd20..d51f58d 100644
    --- a/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java
    +++ b/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java
    @@ -380,7 +380,7 @@ class MetaStoreDirectSql {
          // Derby and Oracle do not interpret filters ANSI-properly in some cases and need a workaround.
          boolean dbHasJoinCastBug = (dbType == DB.DERBY || dbType == DB.ORACLE);
          String sqlFilter = PartitionFilterGenerator.generateSqlFilter(
    - table, tree, params, joins, dbHasJoinCastBug, defaultPartName);
    + table, tree, params, joins, dbHasJoinCastBug, defaultPartName, dbType);
          if (sqlFilter == null) {
            return null; // Cannot make SQL filter to push down.
          }
    @@ -395,7 +395,7 @@ class MetaStoreDirectSql {
          // Derby and Oracle do not interpret filters ANSI-properly in some cases and need a workaround.
          boolean dbHasJoinCastBug = (dbType == DB.DERBY || dbType == DB.ORACLE);
          String sqlFilter = PartitionFilterGenerator.generateSqlFilter(
    - table, tree, params, joins, dbHasJoinCastBug, defaultPartName);
    + table, tree, params, joins, dbHasJoinCastBug, defaultPartName, dbType);
          if (sqlFilter == null) {
            return 0; // Cannot make SQL filter to push down.
          }
    @@ -962,15 +962,17 @@ class MetaStoreDirectSql {
          private final List<String> joins;
          private final boolean dbHasJoinCastBug;
          private final String defaultPartName;
    + private final DB dbType;

          private PartitionFilterGenerator(Table table, List<Object> params, List<String> joins,
    - boolean dbHasJoinCastBug, String defaultPartName) {
    + boolean dbHasJoinCastBug, String defaultPartName, DB dbType) {
            this.table = table;
            this.params = params;
            this.joins = joins;
            this.dbHasJoinCastBug = dbHasJoinCastBug;
            this.filterBuffer = new FilterBuilder(false);
            this.defaultPartName = defaultPartName;
    + this.dbType = dbType;
          }

          /**
    @@ -980,15 +982,15 @@ class MetaStoreDirectSql {
           * @param joins the joins necessary for the resulting expression
           * @return the string representation of the expression tree
           */
    - private static String generateSqlFilter(Table table, ExpressionTree tree,
    - List<Object> params, List<String> joins, boolean dbHasJoinCastBug, String defaultPartName)
    + private static String generateSqlFilter(Table table, ExpressionTree tree, List<Object> params,
    + List<String> joins, boolean dbHasJoinCastBug, String defaultPartName, DB dbType)
                  throws MetaException {
            assert table != null;
            if (tree.getRoot() == null) {
              return "";
            }
            PartitionFilterGenerator visitor = new PartitionFilterGenerator(
    - table, params, joins, dbHasJoinCastBug, defaultPartName);
    + table, params, joins, dbHasJoinCastBug, defaultPartName, dbType);
            tree.accept(visitor);
            if (visitor.filterBuffer.hasError()) {
              LOG.info("Unable to push down SQL filter: " + visitor.filterBuffer.getErrorMessage());
    @@ -1124,7 +1126,12 @@ class MetaStoreDirectSql {
              if (colType == FilterType.Integral) {
                tableValue = "cast(" + tableValue + " as decimal(21,0))";
              } else if (colType == FilterType.Date) {
    - tableValue = "cast(" + tableValue + " as date)";
    + if (dbType == DB.ORACLE) {
    + // Oracle requires special treatment... as usual.
    + tableValue = "TO_DATE(" + tableValue + ", 'YYYY-MM-DD')";
    + } else {
    + tableValue = "cast(" + tableValue + " as date)";
    + }
              }

              // Workaround for HIVE_DEFAULT_PARTITION - ignore it like JDO does, for now.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupcommits @
categorieshive, hadoop
postedMar 22, '16 at 10:23p
activeMar 22, '16 at 10:23p
posts2
users1
websitehive.apache.org

1 user in discussion

Sershe: 2 posts

People

Translate

site design / logo © 2021 Grokbase