Hi, I recently updated from 1.2.141 to 1.3.168 because I needed support for ....
However, when I did this, one of my unit tests started failing. I managed to extract this into a small test program (see bottom of this email). When using version 1.2.141, the 2nd query returns the single row that is present in the database. However, just switching to version 1.3.168 makes it fail and returns no rows. The changelog<http://www.h2database.com/html/changelog.html>only contains information up to version 1.3.159, so it was impossible for me to check all the changes notes to see if something might have changed in date handling. I hope somebody who knows the code well can help me out to see if it is a real bug and hopefully a bugfix then. regards, Wim === Below is the full code of the test program. You will need to use JodaTime as well to compile and run it. === package com.traficon.tmsng.server.common.service.persistence.impl.hibernate; import org.joda.time.DateTime; import org.joda.time.DateTimeZone; import org.joda.time.Minutes; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; public class H2TimeZonesTest { public static void main( String[] args ) throws SQLException, ClassNotFoundException { Class.forName( "org.h2.Driver" ); Connection conn = DriverManager.getConnection( "jdbc:h2:mem:testdb;MODE=MYSQL;TRACE_LEVEL_SYSTEM_OUT=0;DB_CLOSE_DELAY=-1;AUTOCOMMIT=FALSE", "sa", "" ); try { createTable( conn ); DateTime endTime = new DateTime( 2010, 10, 31, 2, 0, 0, 0, DateTimeZone.forID( "+01:00" ) ); DateTime startTime = endTime.minusMinutes( 5 ).withZone( DateTimeZone.forID( "+02:00" ) ); insertTestdata( conn, startTime, endTime ); printData( conn, "SELECT * FROM TestTable" ); System.out.println( "---"); printQueryData( conn, startTime, endTime ); } finally { conn.close(); } } private static void printQueryData( Connection conn, DateTime startTime, DateTime endTime ) throws SQLException { String sql = "SELECT * FROM TestTable where ((? < end_time and ? >= end_time) or (? > end_time and ? <= end_time and ? > start_time))"; PreparedStatement statement = conn.prepareStatement( sql ); Timestamp startTimestamp = new Timestamp( startTime.withMillisOfSecond( 0 ).toDate().getTime() ); Timestamp endTimestamp = new Timestamp( endTime.withMillisOfSecond( 0 ).toDate().getTime() ); Timestamp endTimestampPlusInterval = new Timestamp( endTime.plus( Minutes.minutes( 5 ) ).withMillisOfSecond( 0 ).toDate().getTime() ); statement.setTimestamp( 1, startTimestamp ); statement.setTimestamp( 2, endTimestamp ); statement.setTimestamp( 3, endTimestampPlusInterval ); statement.setTimestamp( 4, endTimestamp ); statement.setTimestamp( 5, endTimestamp ); ResultSet resultSet = statement.executeQuery(); printResultSet( resultSet ); } private static void printData( Connection conn, String sql ) throws SQLException { Statement statement = null; try { statement = conn.createStatement(); ResultSet resultSet = statement.executeQuery( sql ); printResultSet( resultSet ); } finally { if (statement != null) { statement.close(); } } } private static void printResultSet( ResultSet resultSet ) throws SQLException { int rows = 0; while (resultSet.next()) { System.out.println( "detectorId: " + resultSet.getInt( "detector_id" ) ); System.out.println( "data_number: " + resultSet.getInt( "data_number" ) ); System.out.println( "end_time: " + resultSet.getTimestamp( "end_time" ) ); System.out.println( "start_time: " + resultSet.getTimestamp( "start_time" ) ); System.out.println( "is_combined: " + resultSet.getBoolean( "is_combined" ) ); rows++; } System.out.println( "Printed " + rows + " rows" ); } private static void insertTestdata( Connection conn, DateTime startTime, DateTime endTime ) throws SQLException { Statement statement = null; try { PreparedStatement preparedStatement = conn.prepareStatement( "INSERT INTO TestTable VALUES(" + " ?," + " ?," + " ?," + " ?," + " ?)" ); preparedStatement.setInt( 1, 1 ); preparedStatement.setInt( 2, 1 ); preparedStatement.setTimestamp( 3, new Timestamp( endTime.toDate().getTime() ) ); preparedStatement.setTimestamp( 4, new Timestamp( startTime.toDate().getTime() ) ); preparedStatement.setBoolean( 5, false ); preparedStatement.executeUpdate(); } finally { if (statement != null) { statement.close(); } } } private static void createTable( Connection conn ) throws SQLException { Statement statement = null; try { statement = conn.createStatement(); statement.executeUpdate( "create table TestTable (\n" + " detector_id integer not null,\n" + " data_number bigint not null,\n" + " end_time datetime not null,\n" + " start_time datetime not null,\n" + " is_combined bit,\n" + " primary key (detector_id, end_time, data_number)\n" + ")"); } finally { if (statement != null) { statement.close(); } } } } -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/VNz13r330VYJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
