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)
