You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Rajkumar Singh (JIRA)" <ji...@apache.org> on 2019/04/11 05:02:00 UTC
[jira] [Updated] (HIVE-21601) Hive JDBC Storage Handler query fail
because projected timestamp max precision is not valid for mysql
[ https://issues.apache.org/jira/browse/HIVE-21601?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rajkumar Singh updated HIVE-21601:
----------------------------------
Description:
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 since max timestamp precision in MySQL is 6.
was:
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.
> 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
> Priority: Major
>
> 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 since max timestamp precision in MySQL is 6.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)