[
https://issues.apache.org/jira/browse/DRILL-3701?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Khurram Faraaz updated DRILL-3701:
----------------------------------
Summary: getTime and getTimestamp compatibility from JDBC (was: question :
getTime and getTimestamp compatibility from JDBC)
> getTime and getTimestamp compatibility from JDBC
> ------------------------------------------------
>
> Key: DRILL-3701
> URL: https://issues.apache.org/jira/browse/DRILL-3701
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Data Types
> Affects Versions: 1.2.0
> Reporter: Khurram Faraaz
> Assignee: Daniel Barclay (Drill)
>
> When we use getTime instead of getTimestamp function from JDBC program to get
> a timestamp type column from a parquet file, I see this Exception. Should we
> support this from JDBC and return only the TIME portion of the TIMESTAMP
> value when getTime is used over a column type timestamp ? (that is what Drill
> does today on sqlline prompt when we do an explicit cast to TIME)
> org.apache.drill.exec.vector.accessor.InvalidAccessException: Requesting
> value of type Time for an object of type TIMESTAMP:OPTIONAL is not allowed.
> Note from below query output that the sixth column (col5) is of type
> TIMESTAMP in the parquet file and holds timestamp data.
> {code}
> 0: jdbc:drill:schema=dfs.tmp> SELECT * FROM FEWRWSPQQ_101 limit 3;
> +-------+-----------+-----------+---------+---------------+--------------------------+-------------+--------+-------+-------------------------------------------------------+
> | col0 | col1 | col2 | col3 | col4 | col5
> | col6 | col7 | col8 | col9
> |
> +-------+-----------+-----------+---------+---------------+--------------------------+-------------+--------+-------+-------------------------------------------------------+
> | 1 | 65534 | 256.0 | 1234.9 | 20:26:18.580 | 2014-03-02
> 00:28:02.338 | 1952-08-14 | false | CA |
> AXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXZ |
> | 2 | 10000000 | -256.0 | 11.0 | 10:59:58.119 | 2014-01-02
> 00:28:02.228 | 1981-03-14 | true | WI |
> BXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXD |
> | 3 | -1 | 255.9993 | 0.0 | 22:49:49.300 | 2014-09-02
> 00:28:02.616 | 2000-01-03 | false | NY |
> CXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXB |
> +-------+-----------+-----------+---------+---------------+--------------------------+-------------+--------+-------+-------------------------------------------------------+
> 3 rows selected (0.185 seconds)
> {code}
> Stack trace reported on prompt from where JDBC program is executed.
> {code}
> ...
> field {
> major_type {
> minor_type: TIMESTAMP
> mode: OPTIONAL
> }
> name_part {
> type: NAME
> name: "col5"
> }
> value_count: 22
> buffer_length: 198
> ...
> Requesting value of type Time for an object of type TIMESTAMP:OPTIONAL is not
> allowed.
> org.apache.drill.exec.vector.accessor.InvalidAccessException: Requesting
> value of type Time for an object of type TIMESTAMP:OPTIONAL is not allowed.
> at
> org.apache.drill.exec.vector.accessor.AbstractSqlAccessor.newInvalidAccessException(AbstractSqlAccessor.java:116)
> at
> org.apache.drill.exec.vector.accessor.AbstractSqlAccessor.getTime(AbstractSqlAccessor.java:106)
> at
> org.apache.drill.exec.vector.accessor.BoundCheckingAccessor.getTime(BoundCheckingAccessor.java:129)
> at
> org.apache.drill.jdbc.impl.TypeConvertingSqlAccessor.getTime(TypeConvertingSqlAccessor.java:689)
> at
> org.apache.drill.jdbc.impl.AvaticaDrillSqlAccessor.getTime(AvaticaDrillSqlAccessor.java:219)
> at
> net.hydromatic.avatica.AvaticaResultSet.getTime(AvaticaResultSet.java:250)
> at
> org.apache.drill.jdbc.impl.DrillResultSetImpl.getTime(DrillResultSetImpl.java:248)
> at DataFromDrill.main(DataFromDrill.java:30)
> {code}
> JDBC snippet to execute the above SQL
> {code}
> import org.apache.log4j.Logger;
> import java.sql.Connection;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> import java.sql.Types;
> import java.sql.*;
> public class DataFromDrill {
> public static void main(String s[]) {
> try {
> final String URL_STRING =
> "jdbc:drill:schema=dfs.tmp;drillbit=<ip-address>";
> Class.forName("org.apache.drill.jdbc.Driver").newInstance();
> Connection conn =
> DriverManager.getConnection(URL_STRING,"root","mapr");
> Statement stmt = conn.createStatement();
> String query = "select * from FEWRWSPQQ_101";
>
> ResultSet rs = stmt.executeQuery(query);
> while (rs.next()) {
> System.out.println(rs.getTime(6));
> }
> if (rs != null)
> rs.close();
> conn.close();
> } catch ( Exception e ) {
> System.out.println(e.getMessage());
> e.printStackTrace();
> }
> }
> }
> {code}
> Explicit cast to TIME of the TIMESTAMP type column results in a successful
> cast from sqlline, and the TIME portion of the timestamp value is returned.
> {code}
> 0: jdbc:drill:schema=dfs.tmp> SELECT cast(col5 as time) FROM FEWRWSPQQ_101;
> +---------------+
> | EXPR$0 |
> +---------------+
> | 00:28:02.338 |
> | 00:28:02.228 |
> | 00:28:02.616 |
> | 00:28:02.404 |
> | 00:28:02.309 |
> | 00:28:02.638 |
> | 00:28:02.748 |
> | 00:28:02.321 |
> | 00:28:02 |
> | 00:28:02 |
> | 00:28:02.418 |
> | 00:28:02.418 |
> | 00:28:02.418 |
> | 00:28:02.418 |
> | 00:28:02.418 |
> | 00:28:02.418 |
> | 00:28:02.418 |
> | 00:28:02.118 |
> | 00:28:02.218 |
> | 00:28:02.418 |
> | 00:28:02.318 |
> | 20:28:02.318 |
> +---------------+
> 22 rows selected (0.176 seconds)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
