[HIVE-24888] JDBC Query with where clause containing timestamp field in long doesn't return any result.

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Component/s: JDBC
• Assignee: Unassigned
• Reporter: Anurag Shekhar
• Created: 16/Mar/21 06:53
• Updated: 17/Mar/21 07:21

Description

Steps to reproduce

The following code creates a table with timestamp column and inserts a value, using java.sql.PreparedStament.setLong.

When same value used to query the table it returns no results. 

Other variant, using timestamp (setTimestamp) string (setString) works fine.

public static void main(String [] args) throws SQLException {
    TimeZone.setDefault(TimeZone.getTimeZone("IST"));
    Connection conn = DriverManager.getConnection ("jdbc:hive2://host:port", "hive", "hive");
    Statement stmt = conn.createStatement();
    stmt.execute("drop table if exists ts_table");
    stmt.execute("create table ts_table (ts timestamp) stored as orc");
    PreparedStatement pStmt = conn.prepareStatement("insert into ts_table (ts) values (?)");
    long timeStamp = System.currentTimeMillis();
    pStmt.setLong(1, timeStamp);
    pStmt.execute();
    pStmt.close();

    pStmt = conn.prepareStatement("select * from ts_table where ts = ?");
    pStmt.setLong(1, timeStamp);
    ResultSet rs = pStmt.executeQuery();

    if (rs.next()) {
        Timestamp resultTs = rs.getTimestamp(1);
        System.out.println("Retrieved " + resultTs + " In millis " + resultTs.getTime());
        System.out.println("where ts = " + timeStamp);
    }
    else
        System.out.println ("No result where ts = " + timeStamp);

    rs.close();
    pStmt.close();
    conn.close();
}

Issue Links

• is caused by: HIVE-24891 Tablestamp field returns a value different from what was inserted using PreparedStaement.setLong