Major Description
Steps to reproduce:
--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"} | |
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 since max timestamp precision in MySQL is 6.