Unexpected results of TIME value under JDBC

[DerZc]

Describe the problem

Please describe the issue you observed, and any steps we can take to reproduce it:

Consider the following program:

USE test;
DROP DATABASE IF EXISTS database18 CASCADE;
CREATE DATABASE database18;
USE database18;

SET CLUSTER SETTING diagnostics.reporting.enabled    = false;
SET CLUSTER SETTING diagnostics.reporting.send_crash_reports = false;
SET CLUSTER SETTING sql.metrics.statement_details.plan_collection.enabled = 'off';
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = 'off';
SET CLUSTER SETTING timeseries.storage.enabled = 'off';
set experimental_enable_hash_sharded_indexes='on';

CREATE TABLE t0 (c0 TIMETZ);

INSERT INTO t0 (c0) VALUES (TIMETZ '1969-12-17T16:27:27');

SELECT t0.c0 FROM t0 WHERE ((t0.c0)>(TIME '16:27:27'));

I run this program with CLI and get an empty result:

> SELECT t0.c0 FROM t0 WHERE ((t0.c0)>(TIME '16:27:27'));
  c0
------
(0 rows)

Time: 0ms

But I get an non-empty result with JDBC driver, I write this program to reproduce this:

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;

import java.text.SimpleDateFormat;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.io.IOException;


public class reproduce_cockroach {
    public static void main(String[] args) throws Exception {
        CockroachHandle sh = new CockroachHandle();
        try {
            sh.runSqlByReadFileContent(args[0]);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sh.ReleaseConnect();
        }
    }

    static class CockroachHandle {
        private Connection connection;

        String host = "127.0.0.1";
        int port = 26257;
        String URL = String.format("jdbc:postgresql://%s:%d/test", host, port);

        public CockroachHandle(){
            try {
                connection = DriverManager.getConnection(URL, "root", "");
            } catch (Exception e) {
                e.printStackTrace();
                connection = null;
            }
        }

        public Connection getConnection(){
            return connection;
        }

        public void ReleaseConnect(){
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        public int batchDate(ArrayList<String> sql){
            try (Statement statement = connection.createStatement()){
                for(String script: sql) {
                    if (script.startsWith("--")) {
                        continue;
                    }
                    System.out.println("Query: " + script);
                    if (script.startsWith("SELECT") || script.startsWith("WITH")) {
                        try (ResultSet rs = statement.executeQuery(script)) {
                            ResultSetMetaData metaData = rs.getMetaData();
                            Integer columnCount = metaData.getColumnCount();
                            while (rs.next()) {
                                for (int i = 1; i <= columnCount; i++) {
                                    try {
                                        Object value = rs.getObject(i);
                                        if (rs.wasNull()) {
                                            System.out.println("NULL");
                                        }
                                        else if (value instanceof Boolean) {
                                            System.out.println((Boolean) value);
                                        }
                                        else if (value instanceof Integer) {
                                            System.out.println(Long.valueOf((Integer) value));
                                        } else if (value instanceof Short) {
                                            System.out.println(Long.valueOf((Short) value));
                                        } else if (value instanceof Long) {
                                            System.out.println((Long) value);
                                        }  else if (value instanceof Double) {
                                            System.out.println((Double) value);
                                        }
                                        
                                        else if (value instanceof java.sql.Timestamp) {
                                            Timestamp timestamp = (Timestamp) value;
                                            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
                                            String textRepr = dateFormat.format(timestamp);
                                            System.out.println(textRepr);
                                        } else if (value instanceof Time) {
                                            Timestamp timestamp = new Timestamp(((Time) value).getTime());
                                            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
                                            String textRepr = dateFormat.format(timestamp);
                                            System.out.println(textRepr);
                                        } else if (value instanceof BigDecimal) {
                                            System.out.println((BigDecimal) value);
                                        } 
                                        else if (value == null) {
                                            System.out.println("NULL");
                                        } else {
                                            System.out.println("Unsupported type!!!");
                                        }
                                    } catch (SQLException e) {
                                        if (e.getMessage().contains("valueFromFunction")) {
                                            System.out.println(e.getMessage());
                                            System.exit(0);
                                        }
                                        throw new Exception();
                                    }
                                }
                            }
                        } catch(Exception e) {
                            System.out.println(e.getMessage());
                            if (e.getMessage().contains("valueFromFunction")) {
                                System.exit(0);
                            }
                        }
                        
                        
                    } else {
                        try {
                            statement.execute(script);
                        } catch (Exception e) {
                            if (e.getMessage().contains("valueFromFunction")) {
                                System.out.println(e.getMessage());
                                System.exit(0);
                            }
                        }
                    }
                }
                return 1;
            }catch (Exception e) {
                e.printStackTrace();
                return 0;
            }
        }

        private ArrayList<String> readFileByLines(String filePath) throws Exception {
            ArrayList<String> listStr=new ArrayList<>();
            BufferedReader reader = null;
            try {
                reader = new BufferedReader(new InputStreamReader(
                        new FileInputStream(filePath), "UTF-8"));
                String tempString = null;
                while ((tempString = reader.readLine()) != null) {
                    if(tempString.trim().equals(""))
                        continue;
                    if(tempString.startsWith("--"))
                        continue;
                    listStr.add(tempString);
                }
                reader.close();
            } catch (IOException e) {
                e.printStackTrace();
                throw e;
            } finally {
                if (reader != null) {
                    try {
                        reader.close();
                    } catch (IOException e1) {
                    }
                }
            }

            return listStr;
        }

        public void runSqlByReadFileContent(String sqlPath){
            try {
                ArrayList<String> sqlStr = readFileByLines(sqlPath);
                if (sqlStr.size() > 0) {
                    int num=batchDate(sqlStr);
                    if(num>0)
                        System.out.println("success!");
                    else
                        System.out.println("failed!");
                }
                else{
                    System.out.println("no sql statements!");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

and get the result:

Query: SELECT t0.c0 FROM t0 WHERE ((t0.c0)>(TIME '16:27:27'));
1970-01-01T16:27:27

To Reproduce

I build CockroachDB from the latest commit version 51fcf79, I download the latest Postgres JDBC driver from https://jdbc.postgresql.org/download/postgresql-42.6.0.jar.

The command for java program is java -cp postgresql-42.6.0.jar reproduce_cockroach.java database18.sql

Expected behavior
Should have same results with CLI and JDBC driver.

Environment:

• CockroachDB version 51fcf79
• Server OS: [Ubuntu]
• Client app [JDBC, CLI]

Jira issue: CRDB-27044

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-schema (found keywords: DROP DATABASE)
• @cockroachdb/sql-sessions (found keywords: sql statement)
• @cockroachdb/sql-queries (found keywords: plan,import)
• @dhartunian (found keywords: metrics)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[DerZc]

Hi, can you reproduce this, I found it can still reproduce with latest commit version 962c06b on my machine.