Hi, I'm not sure what the problem is, but given that you have to use special MySQL config options (useLegacyDatetimeCode=false&**serverTimezone=UTC) tells me that you have a rather unusual test case. I guess all you can prove is that H2 doesn't work in exactly the same way as MySQL does. But H2 doesn't claim to be 100% compatible in all situations with MySQL, specially if you use those MySQL options.
It's great to have a test case, but could you try to further simplify it so that it doesn't need JodaTime and doesn't need such a complex table and data? I guess one table with one column should be enough (not sure). I would prefer if you send emails to the group instead of logging an issue at this time. Regards, Thomas On Wed, Oct 17, 2012 at 7:19 PM, Wim Deblauwe <[email protected]>wrote: > Do you guys have a bug tracker where I can register the issue or do you > want to have a look first before I do that ? > > Op maandag 15 oktober 2012 14:21:33 UTC+2 schreef Wim Deblauwe het > volgende: > >> To prove that is really a h2 problem, I ran my test program against MySQL >> (version 5.5.24 on Mac OS X): In that case, it reacts as the old h2 >> version. To try it out, change the first 2 lines of the test program to: >> >> Class.forName( "com.mysql.jdbc.Driver" ); >> Connection conn = DriverManager.getConnection( >> "jdbc:mysql://localhost:3306/**tmsng?useUnicode=true&** >> characterEncoding=utf8&**socketTimeout=120000&** >> useLegacyDatetimeCode=false&**serverTimezone=UTC", >> "root", "" ); >> >> >> I enabled the MySQL query log and it shows that these queries are >> executed: >> >> create table TestTable ( >> detector_id integer not null, >> data_number bigint not null, >> end_time datetime not null, >> start_time datetime not null, >> is_combined bit, >> primary key (detector_id, end_time, data_number)); >> INSERT INTO TestTable VALUES( 1, 1, '2010-10-31 01:00:00.0', >> '2010-10-31 00:55:00.0', 0); >> SELECT * FROM TestTable where (('2010-10-31 00:55:00.0' < end_time and >> '2010-10-31 01:00:00.0' >= end_time) or ('2010-10-31 01:05:00.0' > end_time >> and '2010-10-31 01:00:00.0' <= end_time and '2010-10-31 01:00:00.0' > >> start_time)) >> >> I hope this helps to find the problem. >> >> regards, >> >> Wim >> >> >> Op maandag 15 oktober 2012 13:58:56 UTC+2 schreef Wim Deblauwe het >> volgende: >>> >>> These are the SQL statements that are executed: >>> >>> create table TestTable ( >>> detector_id integer not null, >>> data_number bigint not null, >>> end_time datetime not null, >>> start_time datetime not null, >>> is_combined bit, >>> primary key (detector_id, end_time, data_number) >>> ); >>> INSERT INTO TestTable VALUES( ?, ?, ?, ?, ?) {1: 1, 2: 1, >>> 3: TIMESTAMP '2010-10-31 02:00:00.0', 4: TIMESTAMP '2010-10-31 02:55:00.0', >>> 5: FALSE}; >>> SELECT * FROM TestTable; >>> SELECT * FROM TestTable where ((? < end_time and ? >= end_time) or (? > >>> end_time and ? <= end_time and ? > start_time)) {1: TIMESTAMP '2010-10-31 >>> 02:55:00.0', 2: TIMESTAMP '2010-10-31 02:00:00.0', 3: TIMESTAMP '2010-10-31 >>> 02:05:00.0', 4: TIMESTAMP '2010-10-31 02:00:00.0', 5: TIMESTAMP '2010-10-31 >>> 02:00:00.0'}; >>> >>> regards, >>> >>> Wim >>> >>> Op woensdag 10 oktober 2012 10:31:15 UTC+2 schreef Steve McLeod het >>> volgende: >>>> >>>> Surely you can reduce your example code to two or three SQL queries >>>> that demonstrate the problem? That would make it much easier for us to >>>> help. >>>> >>>> >>>> On Tuesday, 9 October 2012 11:46:53 UTC+2, Wim Deblauwe wrote: >>>>> >>>>> 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/-/-XcTvd1SBRQJ. > > 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. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. 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.
