Prepared Statement Query Returns Duplicate Records but CrateDB Console Query Doesn’t

CrateDB
1

Hello,

When we execute a query using a prepared statement, it returns duplicate records, while running the same query directly on the CrateDB console gives the correct results.

This issue can be reproduced using this data

We expect a single record in the result set since the database has only one record with the given ID. Below is a sample query and the corresponding Java code:

String query = "select * from uu.records where  rday=?     AND  ( ( id  IN  ('161070') AND recordid=? )  OR  ( id  IN  ('161070') AND recordid=? ) )";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            List<Object> ids = new ArrayList<>();
            con = getCrateConnection();
            ps = con.prepareStatement(query);

            ps.setLong(1, 1727375400000L);
            ps.setLong(2, 110);
            ps.setLong(3, 110);

            rs = ps.executeQuery();
            while(rs.next()){
                ids.add(rs.getString(1));
            }
            System.out.println("Number of records: " + ids.size());

        }catch (Exception e){
            e.printStackTrace();
        }
1 Like
2

Just in case anyone comes across this thread, this is now being reviewed under Prepared Statement Query Returns Duplicate Records. CrateDB Console Query works fine. · Issue #17266 · crate/crate · GitHub

1 Like