You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by kg...@apache.org on 2020/01/24 16:20:24 UTC
[hive] 01/03: HIVE-22706: Jdbc storage handler incorrectly
interprets boolean column value in derby (Zoltan Haindrich reviewed by Syed
Shameerur Rahman, Miklos Gergely)
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 <ki...@rxd.hu>
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 <ki...@rxd.hu>
---
.../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