This is an automated email from the ASF dual-hosted git repository. kgyrtkirk pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/hive.git
commit dc7d146bb9fcee08a6e06a6bb25d0f9d13a6dbdf Author: Zoltan Haindrich <[email protected]> AuthorDate: Fri Jan 24 15:24:23 2020 +0000 HIVE-22706: Jdbc storage handler incorrectly interprets boolean column value in derby (Zoltan Haindrich reviewed by Syed Shameerur Rahman, Miklos Gergely) Signed-off-by: Zoltan Haindrich <[email protected]> --- .../apache/hive/storage/jdbc/DBRecordWritable.java | 9 +++- .../hive/storage/jdbc/dao/JdbcRecordIterator.java | 8 ++- ql/src/test/queries/clientpositive/jdbc_handler.q | 20 ++++---- .../results/clientpositive/llap/jdbc_handler.q.out | 59 ++++++++++++---------- .../results/clientpositive/llap/sysdb_schq.q.out | 2 +- 5 files changed, 59 insertions(+), 39 deletions(-) diff --git a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/DBRecordWritable.java b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/DBRecordWritable.java index b062aa3..77abae9 100644 --- a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/DBRecordWritable.java +++ b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/DBRecordWritable.java @@ -20,9 +20,11 @@ package org.apache.hive.storage.jdbc; import java.io.DataInput; import java.io.DataOutput; import java.io.IOException; +import java.sql.ParameterMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.Types; import java.util.Arrays; import org.apache.hadoop.io.Writable; @@ -59,8 +61,13 @@ public class DBRecordWritable implements Writable, if (columnValues == null) { throw new SQLException("No data available to be written"); } + ParameterMetaData parameterMetaData = statement.getParameterMetaData(); for (int i = 0; i < columnValues.length; i++) { - statement.setObject(i + 1, columnValues[i]); + Object value = columnValues[i]; + if ((parameterMetaData.getParameterType(i + 1) == Types.CHAR) && value != null && value instanceof Boolean) { + value = ((Boolean) value).booleanValue() ? "1" : "0"; + } + statement.setObject(i + 1, value); } } diff --git a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/JdbcRecordIterator.java b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/JdbcRecordIterator.java index dbc8453..cd7cd4f 100644 --- a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/JdbcRecordIterator.java +++ b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/JdbcRecordIterator.java @@ -30,6 +30,7 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLDataException; import java.sql.SQLException; +import java.sql.Types; import java.util.HashMap; import java.util.Iterator; import java.util.List; @@ -109,7 +110,12 @@ public class JdbcRecordIterator implements Iterator<Map<String, Object>> { value = rs.getBigDecimal(i + 1); break; case BOOLEAN: - value = rs.getBoolean(i + 1); + boolean b = rs.getBoolean(i + 1); + if (b && rs.getMetaData().getColumnType(i + 1) == Types.CHAR) { + // also accept Y/N in case of CHAR(1) - datanucleus stores booleans in CHAR(1) fields for derby + b = !"N".equals(rs.getString(i + 1)); + } + value = b; break; case CHAR: case VARCHAR: diff --git a/ql/src/test/queries/clientpositive/jdbc_handler.q b/ql/src/test/queries/clientpositive/jdbc_handler.q index 2c7e3fd..f2eba04 100644 --- a/ql/src/test/queries/clientpositive/jdbc_handler.q +++ b/ql/src/test/queries/clientpositive/jdbc_handler.q @@ -98,7 +98,7 @@ FROM src SELECT dboutput ( 'jdbc:derby:;databaseName=${system:test.tmp.dir}/test_insert_derby_as_external_table_db;create=true','','', 'CREATE TABLE INSERT_TO_DERBY_TABLE (a BOOLEAN, b INTEGER, c BIGINT, d FLOAT, e DOUBLE, f DATE, g VARCHAR(27), - h VARCHAR(27), i CHAR(2), j TIMESTAMP, k DECIMAL(5,4), l SMALLINT, m SMALLINT)' ) + h VARCHAR(27), i CHAR(2), j TIMESTAMP, k DECIMAL(5,4), l SMALLINT, m SMALLINT, b1 CHAR(10))' ) limit 1; @@ -116,7 +116,8 @@ CREATE EXTERNAL TABLE insert_to_ext_derby_table j TIMESTAMP, k DECIMAL(5,4), l TINYINT, - m SMALLINT + m SMALLINT, + b1 BOOLEAN ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -143,24 +144,25 @@ CREATE TABLE test_insert_tbl j TIMESTAMP, k DECIMAL(5,4), l TINYINT, - m SMALLINT + m SMALLINT, + b1 BOOLEAN ); -INSERT INTO test_insert_tbl VALUES(true, 342, 8900, 9.63, 1099.9999, '2019-04-11', 'abcd', 'efgh', 'k', '2019-05-01 00:00:00', 1.8899, 1, 2); +INSERT INTO test_insert_tbl VALUES(true, 342, 8900, 9.63, 1099.9999, '2019-04-11', 'abcd', 'efgh', 'k', '2019-05-01 00:00:00', 1.8899, 1, 2, true); -- Inserting single row of data -INSERT INTO insert_to_ext_derby_table VALUES(true, 10, 100, 2.63, 999.9999, '2019-01-11', 'test', 'test1', 'z', '2019-01-01 00:00:00', 1.7899, 1, 2); +INSERT INTO insert_to_ext_derby_table VALUES(true, 10, 100, 2.63, 999.9999, '2019-01-11', 'test', 'test1', 'z', '2019-01-01 00:00:00', 1.7899, 1, 2, true); INSERT INTO insert_to_ext_derby_table select * from test_insert_tbl; select * from insert_to_ext_derby_table; -INSERT INTO insert_to_ext_derby_table VALUES(false, 324, 53465, 2.6453, 599.9999, '2019-04-11', 'fgeg', 'asda', 'k', '2019-03-01 10:00:00', 1.7899, 1, 2); +INSERT INTO insert_to_ext_derby_table VALUES(false, 324, 53465, 2.6453, 599.9999, '2019-04-11', 'fgeg', 'asda', 'k', '2019-03-01 10:00:00', 1.7899, 1, 2, false); -- Inserting multiple row of data -INSERT INTO insert_to_ext_derby_table VALUES(false, 10, 100, 2.63, 999.9999, '2019-11-11', 'test', 'test1', 'a', '2019-01-01 00:00:00', 1.7899, 1, 2), - (true, 100, 1000, 2.632, 9999.99999, '2019-12-11', 'test_1', 'test1_1', 'b', '2019-02-01 01:00:01', 5.7899, 3, 4), - (false, 10, 999, 23.632, 99999.99999, '2019-09-11', 'test_2', 'test1_2', 'c', '2019-03-01 11:00:01', 9.7899, 5, 6); +INSERT INTO insert_to_ext_derby_table VALUES(false, 10, 100, 2.63, 999.9999, '2019-11-11', 'test', 'test1', 'a', '2019-01-01 00:00:00', 1.7899, 1, 2, false), + (true, 100, 1000, 2.632, 9999.99999, '2019-12-11', 'test_1', 'test1_1', 'b', '2019-02-01 01:00:01', 5.7899, 3, 4, false), + (false, 10, 999, 23.632, 99999.99999, '2019-09-11', 'test_2', 'test1_2', 'c', '2019-03-01 11:00:01', 9.7899, 5, 6, false); INSERT INTO insert_to_ext_derby_table select * from test_insert_tbl; select * from insert_to_ext_derby_table; diff --git a/ql/src/test/results/clientpositive/llap/jdbc_handler.q.out b/ql/src/test/results/clientpositive/llap/jdbc_handler.q.out index 33e4eb4..75b884b 100644 --- a/ql/src/test/results/clientpositive/llap/jdbc_handler.q.out +++ b/ql/src/test/results/clientpositive/llap/jdbc_handler.q.out @@ -368,7 +368,7 @@ PREHOOK: query: FROM src #### A masked pattern was here #### 'CREATE TABLE INSERT_TO_DERBY_TABLE (a BOOLEAN, b INTEGER, c BIGINT, d FLOAT, e DOUBLE, f DATE, g VARCHAR(27), - h VARCHAR(27), i CHAR(2), j TIMESTAMP, k DECIMAL(5,4), l SMALLINT, m SMALLINT)' ) + h VARCHAR(27), i CHAR(2), j TIMESTAMP, k DECIMAL(5,4), l SMALLINT, m SMALLINT, b1 CHAR(10))' ) limit 1 PREHOOK: type: QUERY @@ -378,7 +378,7 @@ POSTHOOK: query: FROM src #### A masked pattern was here #### 'CREATE TABLE INSERT_TO_DERBY_TABLE (a BOOLEAN, b INTEGER, c BIGINT, d FLOAT, e DOUBLE, f DATE, g VARCHAR(27), - h VARCHAR(27), i CHAR(2), j TIMESTAMP, k DECIMAL(5,4), l SMALLINT, m SMALLINT)' ) + h VARCHAR(27), i CHAR(2), j TIMESTAMP, k DECIMAL(5,4), l SMALLINT, m SMALLINT, b1 CHAR(10))' ) limit 1 POSTHOOK: type: QUERY @@ -399,7 +399,8 @@ PREHOOK: query: CREATE EXTERNAL TABLE insert_to_ext_derby_table j TIMESTAMP, k DECIMAL(5,4), l TINYINT, - m SMALLINT + m SMALLINT, + b1 BOOLEAN ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -428,7 +429,8 @@ POSTHOOK: query: CREATE EXTERNAL TABLE insert_to_ext_derby_table j TIMESTAMP, k DECIMAL(5,4), l TINYINT, - m SMALLINT + m SMALLINT, + b1 BOOLEAN ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -457,7 +459,8 @@ PREHOOK: query: CREATE TABLE test_insert_tbl j TIMESTAMP, k DECIMAL(5,4), l TINYINT, - m SMALLINT + m SMALLINT, + b1 BOOLEAN ) PREHOOK: type: CREATETABLE PREHOOK: Output: database:default @@ -476,21 +479,23 @@ POSTHOOK: query: CREATE TABLE test_insert_tbl j TIMESTAMP, k DECIMAL(5,4), l TINYINT, - m SMALLINT + m SMALLINT, + b1 BOOLEAN ) POSTHOOK: type: CREATETABLE POSTHOOK: Output: database:default POSTHOOK: Output: default@test_insert_tbl -PREHOOK: query: INSERT INTO test_insert_tbl VALUES(true, 342, 8900, 9.63, 1099.9999, '2019-04-11', 'abcd', 'efgh', 'k', '2019-05-01 00:00:00', 1.8899, 1, 2) +PREHOOK: query: INSERT INTO test_insert_tbl VALUES(true, 342, 8900, 9.63, 1099.9999, '2019-04-11', 'abcd', 'efgh', 'k', '2019-05-01 00:00:00', 1.8899, 1, 2, true) PREHOOK: type: QUERY PREHOOK: Input: _dummy_database@_dummy_table PREHOOK: Output: default@test_insert_tbl -POSTHOOK: query: INSERT INTO test_insert_tbl VALUES(true, 342, 8900, 9.63, 1099.9999, '2019-04-11', 'abcd', 'efgh', 'k', '2019-05-01 00:00:00', 1.8899, 1, 2) +POSTHOOK: query: INSERT INTO test_insert_tbl VALUES(true, 342, 8900, 9.63, 1099.9999, '2019-04-11', 'abcd', 'efgh', 'k', '2019-05-01 00:00:00', 1.8899, 1, 2, true) POSTHOOK: type: QUERY POSTHOOK: Input: _dummy_database@_dummy_table POSTHOOK: Output: default@test_insert_tbl POSTHOOK: Lineage: test_insert_tbl.a SCRIPT [] POSTHOOK: Lineage: test_insert_tbl.b SCRIPT [] +POSTHOOK: Lineage: test_insert_tbl.b1 SCRIPT [] POSTHOOK: Lineage: test_insert_tbl.c SCRIPT [] POSTHOOK: Lineage: test_insert_tbl.d SCRIPT [] POSTHOOK: Lineage: test_insert_tbl.e SCRIPT [] @@ -502,11 +507,11 @@ POSTHOOK: Lineage: test_insert_tbl.j SCRIPT [] POSTHOOK: Lineage: test_insert_tbl.k SCRIPT [] POSTHOOK: Lineage: test_insert_tbl.l SCRIPT [] POSTHOOK: Lineage: test_insert_tbl.m SCRIPT [] -PREHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(true, 10, 100, 2.63, 999.9999, '2019-01-11', 'test', 'test1', 'z', '2019-01-01 00:00:00', 1.7899, 1, 2) +PREHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(true, 10, 100, 2.63, 999.9999, '2019-01-11', 'test', 'test1', 'z', '2019-01-01 00:00:00', 1.7899, 1, 2, true) PREHOOK: type: QUERY PREHOOK: Input: _dummy_database@_dummy_table PREHOOK: Output: default@insert_to_ext_derby_table -POSTHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(true, 10, 100, 2.63, 999.9999, '2019-01-11', 'test', 'test1', 'z', '2019-01-01 00:00:00', 1.7899, 1, 2) +POSTHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(true, 10, 100, 2.63, 999.9999, '2019-01-11', 'test', 'test1', 'z', '2019-01-01 00:00:00', 1.7899, 1, 2, true) POSTHOOK: type: QUERY POSTHOOK: Input: _dummy_database@_dummy_table POSTHOOK: Output: default@insert_to_ext_derby_table @@ -526,25 +531,25 @@ POSTHOOK: query: select * from insert_to_ext_derby_table POSTHOOK: type: QUERY POSTHOOK: Input: default@insert_to_ext_derby_table #### A masked pattern was here #### -true 10 100 2.63 999.9999 2019-01-11 test test1 z 2019-01-01 00:00:00 1.7899 1 2 -true 342 8900 9.63 1099.9999 2019-04-11 abcd efgh k 2019-05-01 00:00:00 1.8899 1 2 -PREHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(false, 324, 53465, 2.6453, 599.9999, '2019-04-11', 'fgeg', 'asda', 'k', '2019-03-01 10:00:00', 1.7899, 1, 2) +true 10 100 2.63 999.9999 2019-01-11 test test1 z 2019-01-01 00:00:00 1.7899 1 2 true +true 342 8900 9.63 1099.9999 2019-04-11 abcd efgh k 2019-05-01 00:00:00 1.8899 1 2 true +PREHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(false, 324, 53465, 2.6453, 599.9999, '2019-04-11', 'fgeg', 'asda', 'k', '2019-03-01 10:00:00', 1.7899, 1, 2, false) PREHOOK: type: QUERY PREHOOK: Input: _dummy_database@_dummy_table PREHOOK: Output: default@insert_to_ext_derby_table -POSTHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(false, 324, 53465, 2.6453, 599.9999, '2019-04-11', 'fgeg', 'asda', 'k', '2019-03-01 10:00:00', 1.7899, 1, 2) +POSTHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(false, 324, 53465, 2.6453, 599.9999, '2019-04-11', 'fgeg', 'asda', 'k', '2019-03-01 10:00:00', 1.7899, 1, 2, false) POSTHOOK: type: QUERY POSTHOOK: Input: _dummy_database@_dummy_table POSTHOOK: Output: default@insert_to_ext_derby_table -PREHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(false, 10, 100, 2.63, 999.9999, '2019-11-11', 'test', 'test1', 'a', '2019-01-01 00:00:00', 1.7899, 1, 2), - (true, 100, 1000, 2.632, 9999.99999, '2019-12-11', 'test_1', 'test1_1', 'b', '2019-02-01 01:00:01', 5.7899, 3, 4), - (false, 10, 999, 23.632, 99999.99999, '2019-09-11', 'test_2', 'test1_2', 'c', '2019-03-01 11:00:01', 9.7899, 5, 6) +PREHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(false, 10, 100, 2.63, 999.9999, '2019-11-11', 'test', 'test1', 'a', '2019-01-01 00:00:00', 1.7899, 1, 2, false), + (true, 100, 1000, 2.632, 9999.99999, '2019-12-11', 'test_1', 'test1_1', 'b', '2019-02-01 01:00:01', 5.7899, 3, 4, false), + (false, 10, 999, 23.632, 99999.99999, '2019-09-11', 'test_2', 'test1_2', 'c', '2019-03-01 11:00:01', 9.7899, 5, 6, false) PREHOOK: type: QUERY PREHOOK: Input: _dummy_database@_dummy_table PREHOOK: Output: default@insert_to_ext_derby_table -POSTHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(false, 10, 100, 2.63, 999.9999, '2019-11-11', 'test', 'test1', 'a', '2019-01-01 00:00:00', 1.7899, 1, 2), - (true, 100, 1000, 2.632, 9999.99999, '2019-12-11', 'test_1', 'test1_1', 'b', '2019-02-01 01:00:01', 5.7899, 3, 4), - (false, 10, 999, 23.632, 99999.99999, '2019-09-11', 'test_2', 'test1_2', 'c', '2019-03-01 11:00:01', 9.7899, 5, 6) +POSTHOOK: query: INSERT INTO insert_to_ext_derby_table VALUES(false, 10, 100, 2.63, 999.9999, '2019-11-11', 'test', 'test1', 'a', '2019-01-01 00:00:00', 1.7899, 1, 2, false), + (true, 100, 1000, 2.632, 9999.99999, '2019-12-11', 'test_1', 'test1_1', 'b', '2019-02-01 01:00:01', 5.7899, 3, 4, false), + (false, 10, 999, 23.632, 99999.99999, '2019-09-11', 'test_2', 'test1_2', 'c', '2019-03-01 11:00:01', 9.7899, 5, 6, false) POSTHOOK: type: QUERY POSTHOOK: Input: _dummy_database@_dummy_table POSTHOOK: Output: default@insert_to_ext_derby_table @@ -564,10 +569,10 @@ POSTHOOK: query: select * from insert_to_ext_derby_table POSTHOOK: type: QUERY POSTHOOK: Input: default@insert_to_ext_derby_table #### A masked pattern was here #### -true 10 100 2.63 999.9999 2019-01-11 test test1 z 2019-01-01 00:00:00 1.7899 1 2 -true 342 8900 9.63 1099.9999 2019-04-11 abcd efgh k 2019-05-01 00:00:00 1.8899 1 2 -false 324 53465 2.6453 599.9999 2019-04-11 fgeg asda k 2019-03-01 10:00:00 1.7899 1 2 -false 10 100 2.63 999.9999 2019-11-11 test test1 a 2019-01-01 00:00:00 1.7899 1 2 -true 100 1000 2.632 9999.99999 2019-12-11 test_1 test1_1 b 2019-02-01 01:00:01 5.7899 3 4 -false 10 999 23.632 99999.99999 2019-09-11 test_2 test1_2 c 2019-03-01 11:00:01 9.7899 5 6 -true 342 8900 9.63 1099.9999 2019-04-11 abcd efgh k 2019-05-01 00:00:00 1.8899 1 2 +true 10 100 2.63 999.9999 2019-01-11 test test1 z 2019-01-01 00:00:00 1.7899 1 2 true +true 342 8900 9.63 1099.9999 2019-04-11 abcd efgh k 2019-05-01 00:00:00 1.8899 1 2 true +false 324 53465 2.6453 599.9999 2019-04-11 fgeg asda k 2019-03-01 10:00:00 1.7899 1 2 false +false 10 100 2.63 999.9999 2019-11-11 test test1 a 2019-01-01 00:00:00 1.7899 1 2 false +true 100 1000 2.632 9999.99999 2019-12-11 test_1 test1_1 b 2019-02-01 01:00:01 5.7899 3 4 false +false 10 999 23.632 99999.99999 2019-09-11 test_2 test1_2 c 2019-03-01 11:00:01 9.7899 5 6 false +true 342 8900 9.63 1099.9999 2019-04-11 abcd efgh k 2019-05-01 00:00:00 1.8899 1 2 true diff --git a/ql/src/test/results/clientpositive/llap/sysdb_schq.q.out b/ql/src/test/results/clientpositive/llap/sysdb_schq.q.out index b89f571..528bb3f 100644 --- a/ql/src/test/results/clientpositive/llap/sysdb_schq.q.out +++ b/ql/src/test/results/clientpositive/llap/sysdb_schq.q.out @@ -132,4 +132,4 @@ POSTHOOK: query: select schedule_name,enabled from scheduled_queries POSTHOOK: type: QUERY POSTHOOK: Input: sys@scheduled_queries #### A masked pattern was here #### -asd true +asd false
