Rajkumar Singh created HIVE-21601:
-------------------------------------

             Summary: Hive JDBC Storage Handler query fail because projected 
timestamp max precision is not valid for mysql
                 Key: HIVE-21601
                 URL: https://issues.apache.org/jira/browse/HIVE-21601
             Project: Hive
          Issue Type: Bug
          Components: Hive, JDBC
    Affects Versions: 3.1.1
         Environment: Hive-3.1
            Reporter: Rajkumar Singh


Steps to reproduce:
{code}
--mysql table
mysql> show create table dd_timestamp_error;
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table              | Create Table                                             
                                                                                
                                            |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dd_timestamp_error | CREATE TABLE `dd_timestamp_error` (
  `col1` text,
  `col2` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE 
CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- hive table 

+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE EXTERNAL TABLE `dd_timestamp_error`(        |
|   `col1` string COMMENT 'from deserializer',       |
|   `col2` timestamp COMMENT 'from deserializer')    |
| ROW FORMAT SERDE                                   |
|   'org.apache.hive.storage.jdbc.JdbcSerDe'         |
| STORED BY                                          |
|   'org.apache.hive.storage.jdbc.JdbcStorageHandler'  |
| WITH SERDEPROPERTIES (                             |
|   'serialization.format'='1')                      |
| TBLPROPERTIES (                                    |
|   'bucketing_version'='2',                         |
|   'hive.sql.database.type'='MYSQL',                |
|   'hive.sql.dbcp.maxActive'='1',                   |
|   'hive.sql.dbcp.password'='testuser',             |
|   'hive.sql.dbcp.username'='testuser',             |
|   'hive.sql.jdbc.driver'='com.mysql.jdbc.Driver',  |
|   'hive.sql.jdbc.url'='jdbc:mysql://c46-node3.squadron-labs.com/test',  |
|   'hive.sql.table'='dd_timestamp_error',           |
|   'transient_lastDdlTime'='1554910389')            |
+----------------------------------------------------+

--query failure

0: jdbc:hive2://c46-node2.squadron-labs.com:2>  select * from 
dd_timestamp_error where col2 = '2019-04-03 15:54:21.543654';

Error: java.io.IOException: java.io.IOException: 
org.apache.hive.storage.jdbc.exception.HiveJdbcDatabaseAccessException: Caught 
exception while trying to execute query:You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right 
syntax to use near 'TIMESTAMP(9)) AS `col2`


--
explain select * from dd_timestamp_error where col2 = '2019-04-03 
15:54:21.543654';

TableScan [TS_0]                             |
|         Output:["col1","col2"],properties:{"hive.sql.query":"SELECT `col1`, 
CAST(TIMESTAMP '2019-04-03 15:54:21.543654000' AS TIMESTAMP(9)) AS `col2`\nFROM 
`dd_timestamp_error`\nWHERE `col2` = TIMESTAMP '2019-04-03 
15:54:21.543654000'","hive.sql.query.fieldNames":"col1,col2","hive.sql.query.fieldTypes":"string,timestamp","hive.sql.query.split":"true"}
 |
|                   
{code}

the problem seems to be with convertedFilterExpr ( -- where col2 = '2019-04-03 
15:54:21.543654';) while comparing timestamp with constant:- 

https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/RexNodeConverter.java#L856
https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HiveTypeSystemImpl.java#L38

hive timestamp MAX_TIMESTAMP_PRECISION seems to be 9 and it appears that hive 
pushes the same in query projection(JDBC project) for MySQL and fail the query.







--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Reply via email to