Khurram Faraaz created DRILL-3701:
-------------------------------------
Summary: question : 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)
