Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4375

WEME6.2 : Handful of test fixtures in InbetweenTest are failing with java.sql.SQLException: The syntax of the string representation of a datetime value is incorrect.

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Invalid
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: None
    • Component/s: Test
    • Labels:
      None
    • Environment:
      weme 6.2
    • Bug behavior facts:
      Regression Test Failure

      Description

      On weme6.2, 8 test fixtures from InBetweenTest are failing with following error
      java.sql.SQLException: The syntax of the string representation of a datetime value is incorrect.

      The failing test fixtures are
      1)testReproductionBeetle5135
      2)testBeetle4316
      3)testNestedQueries
      4)testCheckQueries
      5)testBigInList
      6)testInBetween
      7)testInList
      8)testBetween

      The stack trace for testBetween looks as follows
      1) testBetween(org.apache.derbyTesting.functionTests.tests.lang.InbetweenTest)java.sql.SQLException: The syntax of the string representation of a datetime value is incorrect.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown Source)
      at org.apache.derbyTesting.functionTests.tests.lang.InbetweenTest.createTestObjects(InbetweenTest.java:195)
      at org.apache.derbyTesting.functionTests.tests.lang.InbetweenTest.testBetween(InbetweenTest.java:214)
      at java.lang.reflect.AccessibleObject.invokeV(AccessibleObject.java:195)
      at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
      at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
      at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
      at junit.extensions.TestSetup.run(TestSetup.java:23)
      at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
      at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
      at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
      at junit.extensions.TestSetup.run(TestSetup.java:23)
      at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
      Caused by: ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.iapi.types.DateTimeParser.parseInt(Unknown Source)
      at org.apache.derby.iapi.types.SQLTimestamp.parseDateOrTimestamp(Unknown Source)
      at org.apache.derby.iapi.types.SQLTimestamp.parseTimestamp(Unknown Source)
      at org.apache.derby.iapi.types.SQLTimestamp.<init>(Unknown Source)
      at org.apache.derby.iapi.types.SQLChar.getTimestamp(Unknown Source)
      at org.apache.derby.iapi.types.SQLChar.getTimestamp(Unknown Source)
      at org.apache.derby.iapi.types.SQLTimestamp.setFrom(Unknown Source)
      at org.apache.derby.iapi.types.DataType.setValue(Unknown Source)
      at org.apache.derby.iapi.types.DataType.normalize(Unknown Source)
      at org.apache.derby.iapi.types.DataTypeDescriptor.normalize(Unknown Source)
      at org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeColumn(Unknown Source)
      at org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeRow(Unknown Source)
      at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
      ... 39 more

      1. mamta.java
        0.6 kB
        Mamta A. Satoor
      2. timeStampDiffValue.java
        0.3 kB
        Mamta A. Satoor

        Activity

        Hide
        mamtas Mamta A. Satoor added a comment -

        I wonder if anyone has noticed these kind of failures on Sun's J2ME testing.

        Show
        mamtas Mamta A. Satoor added a comment - I wonder if anyone has noticed these kind of failures on Sun's J2ME testing.
        Hide
        mamtas Mamta A. Satoor added a comment -

        The failure seem to have started occuring after the old test inbetween.sql(based on old harness) was converted into a junit test as part of DERBY-4318. The code that is failing is a new piece of test code which I do not think existed in the old test.
        st.executeUpdate(
        " insert into bt2 values (28, 82, null, '15:47:28', "
        + "'"Timestamp.valueOf("0000-00-00 15:47:28.0")"', null)");

        Show
        mamtas Mamta A. Satoor added a comment - The failure seem to have started occuring after the old test inbetween.sql(based on old harness) was converted into a junit test as part of DERBY-4318 . The code that is failing is a new piece of test code which I do not think existed in the old test. st.executeUpdate( " insert into bt2 values (28, 82, null, '15:47:28', " + "'" Timestamp.valueOf("0000-00-00 15:47:28.0") "', null)");
        Hide
        bryanpendleton Bryan Pendleton added a comment -

        I agree, I don't think that statement existed in the old test. The corresponding section
        of the old test looked like this:

        -insert into bt2 values (8, -800.0, '1992-03-22', '03:22:28', '2007-01-04 16:17:23.303', '2992-01-02');
        -insert into bt2 values (1, 200.0, '1998-03-22', '13:22:28', '2007-01-04 16:17:36.912', '3999-08-08');
        -insert into bt2 values (-8, 800, '3999-08-08', '02:28:22', '2007-01-05 16:03:52.364', '1992-01-02');
        -insert into bt2 values (18, 180.00, '2007-02-23', '15:47:27', null, null);
        -insert into bt2 values (22, 202.010, '2007-02-23', '15:47:27', null, null);
        -insert into bt2 values (23, 322.002, null, '15:47:28', null, null);
        -insert into bt2 values (28, 82, null, '15:47:28', '2007-02-23 15:47:27.544', null);

        I think that the SQLToJUnit tool did something strange with the 'xxxxxxFILTERED-TIMESTAMPxxxxx'
        data in the 'inbetween.out' file as it was converting it to JUnit, and I didn't catch this during
        the code review (probably because the test passed on all the platforms I was running).

        What do you think we should do here? It's certainly a strange timestamp; is the WEME
        platform justified in complaining about it?

        If you change the timestamp to '2007-02-23 15:47:27.544', (the value from the original test),
        does the test then pass?

        If so, that seems like a reasonable fix to me.

        Show
        bryanpendleton Bryan Pendleton added a comment - I agree, I don't think that statement existed in the old test. The corresponding section of the old test looked like this: -insert into bt2 values (8, -800.0, '1992-03-22', '03:22:28', '2007-01-04 16:17:23.303', '2992-01-02'); -insert into bt2 values (1, 200.0, '1998-03-22', '13:22:28', '2007-01-04 16:17:36.912', '3999-08-08'); -insert into bt2 values (-8, 800, '3999-08-08', '02:28:22', '2007-01-05 16:03:52.364', '1992-01-02'); -insert into bt2 values (18, 180.00, '2007-02-23', '15:47:27', null, null); -insert into bt2 values (22, 202.010, '2007-02-23', '15:47:27', null, null); -insert into bt2 values (23, 322.002, null, '15:47:28', null, null); -insert into bt2 values (28, 82, null, '15:47:28', '2007-02-23 15:47:27.544', null); I think that the SQLToJUnit tool did something strange with the 'xxxxxxFILTERED-TIMESTAMPxxxxx' data in the 'inbetween.out' file as it was converting it to JUnit, and I didn't catch this during the code review (probably because the test passed on all the platforms I was running). What do you think we should do here? It's certainly a strange timestamp; is the WEME platform justified in complaining about it? If you change the timestamp to '2007-02-23 15:47:27.544', (the value from the original test), does the test then pass? If so, that seems like a reasonable fix to me.
        Hide
        mamtas Mamta A. Satoor added a comment -

        I took Bryan's suggestion and changed the test to use Timestamp.valueOf("2007-02-23 15:47:27.544") rather than Timestamp.valueOf("0000-00-00 15:47:28.0"). This makes the test pass on weme. The change went in with revision 815452.

        Also, I wrote a standalone very simple test case which demonstrates the problem with weme. I am wondering if this reproduces on any other implementation of J2ME. Would appreciate if someone can give it a try.

        Show
        mamtas Mamta A. Satoor added a comment - I took Bryan's suggestion and changed the test to use Timestamp.valueOf("2007-02-23 15:47:27.544") rather than Timestamp.valueOf("0000-00-00 15:47:28.0"). This makes the test pass on weme. The change went in with revision 815452. Also, I wrote a standalone very simple test case which demonstrates the problem with weme. I am wondering if this reproduces on any other implementation of J2ME. Would appreciate if someone can give it a try.
        Hide
        mamtas Mamta A. Satoor added a comment -

        A very simple repro (attaching as mamta.java) which demonstrates the problem with the datetime format with weme. I am wondering if this reproduces on any other implementation of J2ME. Would appreciate if someone can give it a try.

        Show
        mamtas Mamta A. Satoor added a comment - A very simple repro (attaching as mamta.java) which demonstrates the problem with the datetime format with weme. I am wondering if this reproduces on any other implementation of J2ME. Would appreciate if someone can give it a try.
        Hide
        mamtas Mamta A. Satoor added a comment -

        BTW, the test is not as standalone as I claimed yesterday. Realized last night that I am actually using the helper class TestUtil for Derby test harness to reproduce the problem. TestUtil gets a JDBC connection which will/will not use DriverManager depending on what implementation of jvm is being used ie it will detect that DriverManager can't be used if working with J2ME.

        Show
        mamtas Mamta A. Satoor added a comment - BTW, the test is not as standalone as I claimed yesterday. Realized last night that I am actually using the helper class TestUtil for Derby test harness to reproduce the problem. TestUtil gets a JDBC connection which will/will not use DriverManager depending on what implementation of jvm is being used ie it will detect that DriverManager can't be used if working with J2ME.
        Hide
        mamtas Mamta A. Satoor added a comment -

        I am attaching (timeStampDiffValue.java) which is even simpler version of the problem. This test case does not require any JDBC connection.

        The problem is that the output of
        Timestamp.valueOf("0000-00-00 15:47:28.0")
        is different on weme compared to IBM jdk1.6 and Sun jdk1.6. The o/p on IBM 1.6 and Sun 1.6 is same

        Ouptut of System.out.println(Timestamp.valueOf("0000-00-00 15:47:28.0"));
        a)on weme 6.2 is
        2-11-30 15:47:28.0
        b)on IBM jdk1.6
        0002-11-30 15:47:28.0
        3)on Sun jdk16
        0002-11-30 15:47:28.0

        This probably should be raised with weme team but it is interesting to note how ("0000-00-00 15:47:28.0") is interpreted as 0002-11-30 15:47:28.0 on IBM and Sun jdk. Does anyone has any clues on how that conversion might be happening?

        Show
        mamtas Mamta A. Satoor added a comment - I am attaching (timeStampDiffValue.java) which is even simpler version of the problem. This test case does not require any JDBC connection. The problem is that the output of Timestamp.valueOf("0000-00-00 15:47:28.0") is different on weme compared to IBM jdk1.6 and Sun jdk1.6. The o/p on IBM 1.6 and Sun 1.6 is same Ouptut of System.out.println(Timestamp.valueOf("0000-00-00 15:47:28.0")); a)on weme 6.2 is 2-11-30 15:47:28.0 b)on IBM jdk1.6 0002-11-30 15:47:28.0 3)on Sun jdk16 0002-11-30 15:47:28.0 This probably should be raised with weme team but it is interesting to note how ("0000-00-00 15:47:28.0") is interpreted as 0002-11-30 15:47:28.0 on IBM and Sun jdk. Does anyone has any clues on how that conversion might be happening?
        Hide
        knutanders Knut Anders Hatlen added a comment -

        The javadoc for java.util.Date says:

        > In all cases, arguments given to methods for these purposes need not
        > fall within the indicated ranges; for example, a date may be
        > specified as January 32 and is interpreted as meaning February 1.

        This explains the month and day parts of the date. 0/0 is one month
        and one day before 1/1. This should however give year -1, not year
        2. I found this explanation in the bug database
        (http://bugs.sun.com/view_bug.do?bug_id=6278731), where the evaluation
        note says:

        > GregorianCalendar represents a date with ERA and YEAR. 0 and
        > negative year values are converted to (1 - year) with an ERA change
        > to support the Julian calendar year numbering.

        So the year becomes (1 - year) == (1 - -1) = 2.

        Show
        knutanders Knut Anders Hatlen added a comment - The javadoc for java.util.Date says: > In all cases, arguments given to methods for these purposes need not > fall within the indicated ranges; for example, a date may be > specified as January 32 and is interpreted as meaning February 1. This explains the month and day parts of the date. 0/0 is one month and one day before 1/1. This should however give year -1, not year 2. I found this explanation in the bug database ( http://bugs.sun.com/view_bug.do?bug_id=6278731 ), where the evaluation note says: > GregorianCalendar represents a date with ERA and YEAR. 0 and > negative year values are converted to (1 - year) with an ERA change > to support the Julian calendar year numbering. So the year becomes (1 - year) == (1 - -1) = 2.
        Hide
        mamtas Mamta A. Satoor added a comment -

        Knut, thanks for taking the time to explain how 0000-00-00 is converted to 0002-11-30

        Show
        mamtas Mamta A. Satoor added a comment - Knut, thanks for taking the time to explain how 0000-00-00 is converted to 0002-11-30
        Hide
        mamtas Mamta A. Satoor added a comment -

        The problem was with weme jdk. Kathey will file an issue with weme team.

        Show
        mamtas Mamta A. Satoor added a comment - The problem was with weme jdk. Kathey will file an issue with weme team.
        Hide
        bryanpendleton Bryan Pendleton added a comment -

        Thank you Knut and Mamta for tracking this down and resolving it.

        I was a little bit puzzled by the "11-30" part of the result, since as Knut
        observes, Month=0,Day=0 should be the month and day before 1/1, which
        in my mind is December 31, not November 30.

        Indeed, when I wrote this little program:

        Calendar c = Calendar.getInstance();
        c.set(0, 0, 0, 12, 13, 14);
        System.out.println(c.getTime());

        It prints

        Wed Dec 31 12:13:14 PST 0002

        Which confirms Knut's understanding of the year 2 anomaly, and also computes
        December 31 for Month=0,day=0, which is what I expected.

        So I looked a little bit further and I see that when Timestamp.valueOf() parses
        its string argument and constructs its timestamp, it internally uses the constructor
        http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Timestamp.html#Timestamp%28int,%20int,%20int,%20int,%20int,%20int,%20int%29
        which expects that day_of_month is numbered from 1-31 but month is numbered from 0-11,
        so Timestamp.valueOf() subtracts 1 from the month, meaning that

        Timestamp.valueOf("0000-00-00 15:47:28.0"));

        is actually analogous to the Calendar code:

        c = Calendar.getInstance();
        c.set(0, -1, 0, 12, 13, 14);
        System.out.println(c.getTime());

        and that code, inded, prints:

        Sun Nov 30 12:13:14 PST 0002

        All of this is a long and roundabout way of saying that I completely agree with everything
        Mamta and Knut discovered, but wanted to post this anyway just in case anyone else
        was wondering about the December 31 vs November 30 bit like I was. Here's my complete
        program, for the curious:

        import java.util.Calendar;
        import java.sql.Timestamp;
        public class Test
        {
        public static void main(String []args)

        { System.out.println(Timestamp.valueOf("0000-00-00 15:47:28.0")); Calendar c = Calendar.getInstance(); c.set(0, 0, 0, 12, 13, 14); System.out.println(c.getTime()); c.set(0, -1, 0, 12, 13, 14); System.out.println(c.getTime()); }

        }

        Show
        bryanpendleton Bryan Pendleton added a comment - Thank you Knut and Mamta for tracking this down and resolving it. I was a little bit puzzled by the "11-30" part of the result, since as Knut observes, Month=0,Day=0 should be the month and day before 1/1, which in my mind is December 31, not November 30. Indeed, when I wrote this little program: Calendar c = Calendar.getInstance(); c.set(0, 0, 0, 12, 13, 14); System.out.println(c.getTime()); It prints Wed Dec 31 12:13:14 PST 0002 Which confirms Knut's understanding of the year 2 anomaly, and also computes December 31 for Month=0,day=0, which is what I expected. So I looked a little bit further and I see that when Timestamp.valueOf() parses its string argument and constructs its timestamp, it internally uses the constructor http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Timestamp.html#Timestamp%28int,%20int,%20int,%20int,%20int,%20int,%20int%29 which expects that day_of_month is numbered from 1-31 but month is numbered from 0-11, so Timestamp.valueOf() subtracts 1 from the month, meaning that Timestamp.valueOf("0000-00-00 15:47:28.0")); is actually analogous to the Calendar code: c = Calendar.getInstance(); c.set(0, -1, 0, 12, 13, 14); System.out.println(c.getTime()); and that code, inded, prints: Sun Nov 30 12:13:14 PST 0002 All of this is a long and roundabout way of saying that I completely agree with everything Mamta and Knut discovered, but wanted to post this anyway just in case anyone else was wondering about the December 31 vs November 30 bit like I was. Here's my complete program, for the curious: import java.util.Calendar; import java.sql.Timestamp; public class Test { public static void main(String []args) { System.out.println(Timestamp.valueOf("0000-00-00 15:47:28.0")); Calendar c = Calendar.getInstance(); c.set(0, 0, 0, 12, 13, 14); System.out.println(c.getTime()); c.set(0, -1, 0, 12, 13, 14); System.out.println(c.getTime()); } }
        Hide
        mamtas Mamta A. Satoor added a comment -

        This is very interesting, Bryan. I had thought that since Month=0,Day=0 is month and day before 1/1, we will apply the month part first and then the day part to it, in other workd, when we will apply month before 1, we will get Dec 31st and then we will apply day before, it will get us Nov 30th.

        Show
        mamtas Mamta A. Satoor added a comment - This is very interesting, Bryan. I had thought that since Month=0,Day=0 is month and day before 1/1, we will apply the month part first and then the day part to it, in other workd, when we will apply month before 1, we will get Dec 31st and then we will apply day before, it will get us Nov 30th.

          People

          • Assignee:
            mamtas Mamta A. Satoor
            Reporter:
            mamtas Mamta A. Satoor
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development