Re: Regression in 1.3.168 with date handling ?

[Steve McLeod]

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 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();

}

}

}

}

[Wim Deblauwe]

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:

[Wim Deblauwe]

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:

[Wim Deblauwe]

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:

[Thomas Mueller]

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

--
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 h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

The date and date / time implementation was changed in H2, so it doesn't behave as it used to near daylight saving changes. I guess this is what you observe, but your test case doesn't currently prove to me that there is an obvious bug in H2, because the test case is too complex to me. Possibly it was a bug in the old version of H2 which is fixed now? Or maybe you ran into a problem related to java.util.Date - see also http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6772689 which is fixed now.

Regards,

Thomas