Hive
  1. Hive
  2. HIVE-5802

Hive UDF pow limits the second input to integer when the first input is a decimal

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 0.12.0
    • Fix Version/s: 0.13.0
    • Component/s: Types, UDF
    • Labels:
      None

      Description

      For example,

      hive> desc test;
      OK
      i                   	int                 	None                
      b                   	boolean             	None                
      d                   	double              	None                
      s                   	string              	None                
      dec                 	decimal(5,2)        	None                
      hive> explain select pow(dec, 2.5) from test;
      FAILED: SemanticException [Error 10014]: Line 1:15 Wrong arguments '2.5': No matching method for class org.apache.hadoop.hive.ql.udf.UDFPower with (decimal(5,2), double). Possible choices: _FUNC_(decimal(65,30), int)  _FUNC_(double, double)  _FUNC_(double, int)  
      

      This seems too restrictive. MySQL, on the other hand, supports fractional power as the second input.

      mysql> select pow(d, 2.5) from test;
      mysql> desc test;
      +-------+--------------+------+-----+---------+-------+
      | Field | Type         | Null | Key | Default | Extra |
      +-------+--------------+------+-----+---------+-------+
      | i     | int(11)      | YES  |     | NULL    |       |
      | b     | tinyint(1)   | YES  |     | NULL    |       |
      | d     | double       | YES  |     | NULL    |       |
      | s     | varchar(5)   | YES  |     | NULL    |       |
      | dd    | decimal(5,2) | YES  |     | NULL    |       |
      +-------+--------------+------+-----+---------+-------+
      5 rows in set (0.00 sec)
      
      mysql> select pow(dd, 2.5) from test;
      +-------------------+
      | pow(dd, 2.5)      |
      +-------------------+
      | 2570.215713318881 |
      +-------------------+
      1 row in set (0.00 sec)
      

      Hive should also support this.

        Activity

        Xuefu Zhang created issue -
        Hide
        Xuefu Zhang added a comment -

        This is fixed in HIVE-5706. Here is the new console output:

        hive> explain select pow(dd, 2) from test limit 1;  
        OK
        ABSTRACT SYNTAX TREE:
          (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME test1))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION pow (TOK_TABLE_OR_COL dd) 2))) (TOK_LIMIT 1)))
        
        STAGE DEPENDENCIES:
          Stage-1 is a root stage
          Stage-0 is a root stage
        
        STAGE PLANS:
          Stage: Stage-1
            Map Reduce
              Alias -> Map Operator Tree:
                test1 
                  TableScan
                    alias: test1
                    Select Operator
                      expressions:
                            expr: power(dd, 2)
                            type: double
                      outputColumnNames: _col0
                      Limit
                        File Output Operator
                          compressed: false
                          GlobalTableId: 0
                          table:
                              input format: org.apache.hadoop.mapred.TextInputFormat
                              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
        
          Stage: Stage-0
            Fetch Operator
              limit: 1
        
        Show
        Xuefu Zhang added a comment - This is fixed in HIVE-5706 . Here is the new console output: hive> explain select pow(dd, 2) from test limit 1; OK ABSTRACT SYNTAX TREE: (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME test1))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION pow (TOK_TABLE_OR_COL dd) 2))) (TOK_LIMIT 1))) STAGE DEPENDENCIES: Stage-1 is a root stage Stage-0 is a root stage STAGE PLANS: Stage: Stage-1 Map Reduce Alias -> Map Operator Tree: test1 TableScan alias: test1 Select Operator expressions: expr: power(dd, 2) type: double outputColumnNames: _col0 Limit File Output Operator compressed: false GlobalTableId: 0 table: input format: org.apache.hadoop.mapred.TextInputFormat output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe Stage: Stage-0 Fetch Operator limit: 1
        Xuefu Zhang made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Ashutosh Chauhan made changes -
        Fix Version/s 0.13.0 [ 12324986 ]
        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open Resolved Resolved
        20d 1h 47m 1 Xuefu Zhang 02/Dec/13 23:37

          People

          • Assignee:
            Xuefu Zhang
            Reporter:
            Xuefu Zhang
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development