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.
