Derby
  1. Derby
  2. DERBY-896

Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'."

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.2.1
    • Fix Version/s: 10.11.0.0, 10.10.2.0
    • Component/s: SQL
    • Environment:
      --------- Derby Information --------
      JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
      [D:\Programme\Derby\bin\lib\derby.jar] 10.1.2.1 - (330608)
      [D:\Programme\Derby\bin\lib\derbytools.jar] 10.1.2.1 - (330608)
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached

      Description

      Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'.",
      e.g. in ij:
      select cast(cast ('2006-01-01' as date) as timestamp) from sys.sysschemas;
      or
      select cast (current_date as timestamp) from bradi_dat.lpaip01;

      The similar issue exists for time ("ERROR 42846: Cannot convert types 'TIME' to 'TIMESTAMP'.",

      Documentation clearly states that these conversions are allowed, see table 1 in a description of CAST function in the Derby Reference Manual and comment about "Conversion of date/time values" below that table (see below as well).

      Conversions of date/time values
      A date/time value can always be converted to and from a TIMESTAMP. If a DATE is
      converted to a TIMESTAMP, the TIME component of the resulting TIMESTAMP is always
      00:00:00. If a TIME data value is converted to a TIMESTAMP, the DATE component is
      set to the value of CURRENT_DATE at the time the CAST is executed. If a TIMESTAMP
      is converted to a DATE, the TIME component is silently truncated. If a TIMESTAMP is
      converted to a TIME, the DATE component is silently truncated.

      1. allow-casts-with-tests.diff
        23 kB
        Knut Anders Hatlen
      2. allow-casts.diff
        2 kB
        Knut Anders Hatlen
      3. w.sql
        0.2 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Satheesh Bandaram added a comment -

          I think Derby currently doesn't support this casting... The documentation is wrong here and is left over from OLD cloudscape product. The exact same paragraph is found in Cloudscape documentation.

          Derby's casting system was reworked to bring it closer to standards.... Though I don't know exactly what the standards say about this particular conversion. Derby does support two builtin functions, time() and date() that can be used to extract time or date from timestamp types, as follows.

          values date(current_timestamp);

          I would like to mark this bug as NEW FEATURE and possibly open a documentation bug to remove that paragraph from documentation to reflect current Derby behavior. Any changes to Derby behavior could then be correctly added to documentation when it happens.

          Show
          Satheesh Bandaram added a comment - I think Derby currently doesn't support this casting... The documentation is wrong here and is left over from OLD cloudscape product. The exact same paragraph is found in Cloudscape documentation. Derby's casting system was reworked to bring it closer to standards.... Though I don't know exactly what the standards say about this particular conversion. Derby does support two builtin functions, time() and date() that can be used to extract time or date from timestamp types, as follows. values date(current_timestamp); I would like to mark this bug as NEW FEATURE and possibly open a documentation bug to remove that paragraph from documentation to reflect current Derby behavior. Any changes to Derby behavior could then be correctly added to documentation when it happens.
          Hide
          Bernt M. Johnsen added a comment -

          The SQL standard (1999) states (ch. 6.22):

          DATE may be cast to DATE and TIMESTAMP
          TIME may be cast to TIME and TIMESTAMP
          TIMESTAMP may be cast to DATE, TIME and TIMESTAMP

          when DATE cast to TIMESTAMP, the time 00:00:00 is used
          when TIME is cast to TIMESTAMP, current date is used

          (Note: TIME/DATE/TIMESTAMP types may also be cast to CHAR/VARCHAR/CLOB and vice versa)

          Show
          Bernt M. Johnsen added a comment - The SQL standard (1999) states (ch. 6.22): DATE may be cast to DATE and TIMESTAMP TIME may be cast to TIME and TIMESTAMP TIMESTAMP may be cast to DATE, TIME and TIMESTAMP when DATE cast to TIMESTAMP, the time 00:00:00 is used when TIME is cast to TIMESTAMP, current date is used (Note: TIME/DATE/TIMESTAMP types may also be cast to CHAR/VARCHAR/CLOB and vice versa)
          Hide
          Oleksandr Alesinskyy added a comment -

          Casting from TIMESTAMP to DATE (as well as to TIME) is supported by Derby by means of normal CAST function, so built-in DATE(TIMESTAMP) and TIME(TIMESTAMP) are superflous (but probably have to be maintained for backward compatibility).

          e.g.
          CAST(current_timestamp as date) works
          CAST(current_date as timestamp) does NOT work

          Show
          Oleksandr Alesinskyy added a comment - Casting from TIMESTAMP to DATE (as well as to TIME) is supported by Derby by means of normal CAST function, so built-in DATE(TIMESTAMP) and TIME(TIMESTAMP) are superflous (but probably have to be maintained for backward compatibility). e.g. CAST(current_timestamp as date) works CAST(current_date as timestamp) does NOT work
          Hide
          Satheesh Bandaram added a comment -

          Thanks Bernt for your info about SQL standards. I do think this is a useful cast to support in Derby. But current code doesn't ,so we could 1) Leave the documentation as is and consider this to be a "bug" in Derby OR 2) Make the documentation match product code and mark this bug as Improvement.

          Any suggestions?

          Show
          Satheesh Bandaram added a comment - Thanks Bernt for your info about SQL standards. I do think this is a useful cast to support in Derby. But current code doesn't ,so we could 1) Leave the documentation as is and consider this to be a "bug" in Derby OR 2) Make the documentation match product code and mark this bug as Improvement. Any suggestions?
          Hide
          Rick Hillegas added a comment -

          Attaching repro for this issue: w.sql

          Show
          Rick Hillegas added a comment - Attaching repro for this issue: w.sql
          Hide
          Rick Hillegas added a comment -

          Triaged for 10.5.3: assigned normal urgency and attached a repro.

          Show
          Rick Hillegas added a comment - Triaged for 10.5.3: assigned normal urgency and attached a repro.
          Hide
          Lukas Eder added a comment -

          I can reproduce this issue with

          select current_date as "d", cast(current_date as timestamp) as "ts" from "SYSIBM"."SYSDUMMY1"
          

          Note that according to the Derby manual, this should be possible:
          http://db.apache.org/derby/docs/10.2/ref/rrefsqlj33562.html

          (I know, I'm just repeating what Oleksandr already said)

          Just to get some new life in this issue, which is probably easy to fix, which pulls its own weight, and which is a bit surprising

          Show
          Lukas Eder added a comment - I can reproduce this issue with select current_date as "d" , cast (current_date as timestamp) as "ts" from "SYSIBM" . "SYSDUMMY1" Note that according to the Derby manual, this should be possible: http://db.apache.org/derby/docs/10.2/ref/rrefsqlj33562.html (I know, I'm just repeating what Oleksandr already said) Just to get some new life in this issue, which is probably easy to fix, which pulls its own weight, and which is a bit surprising
          Hide
          Oleksandr Alesinskyy added a comment -

          H-m-m. this issue (that should require from a couple of hours to a couple of day of work, former is much more likely) now entered its 9th year...

          Show
          Oleksandr Alesinskyy added a comment - H-m-m. this issue (that should require from a couple of hours to a couple of day of work, former is much more likely) now entered its 9th year...
          Hide
          Knut Anders Hatlen added a comment -

          On the face of it, the attached patch (allow-casts.diff) seems to be sufficient. The casts seem to work, and the missing date or time component is filled with the correct values as described by Bernt:

          ij> values cast(current time as timestamp), cast(current date as timestamp);
          1                            
          -----------------------------
          2014-02-13 10:32:32.0        
          2014-02-13 00:00:00.0        
          
          2 rows selected
          

          I'll run the regression test suite to see if it breaks something. If everything looks good, I'll write some new tests for the casts and try to get it checked in.

          It would be good to put this issue to rest, at last...

          Show
          Knut Anders Hatlen added a comment - On the face of it, the attached patch (allow-casts.diff) seems to be sufficient. The casts seem to work, and the missing date or time component is filled with the correct values as described by Bernt: ij> values cast(current time as timestamp), cast(current date as timestamp); 1 ----------------------------- 2014-02-13 10:32:32.0 2014-02-13 00:00:00.0 2 rows selected I'll run the regression test suite to see if it breaks something. If everything looks good, I'll write some new tests for the casts and try to get it checked in. It would be good to put this issue to rest, at last...
          Hide
          Knut Anders Hatlen added a comment -

          Attaching a new patch which updates the regression tests. All the regression tests ran cleanly.

          The patch makes these changes:

          iapi/types/TypeId.java

          • Add method that tells if a type identifier represents TIMESTAMP.

          impl/sql/compile/DateTypeCompiler.java
          impl/sql/compile/TimeTypeCompiler.java

          • Make convertible() accept conversions to TIMESTAMP.

          functionTests/master/cast.out
          functionTests/tests/lang/cast.sql

          • Update existing test cases that expect the casts to fail.
          • Remove test of cast from TIME to TIMESTAMP since that cannot be tested reliably in the canon-based tests (because the result varies with the
            current date).

          functionTests/tests/lang/CastingTest.java

          • Update existing test cases that expect the casts to fail.
          • Add new test cases.
          Show
          Knut Anders Hatlen added a comment - Attaching a new patch which updates the regression tests. All the regression tests ran cleanly. The patch makes these changes: iapi/types/TypeId.java Add method that tells if a type identifier represents TIMESTAMP. impl/sql/compile/DateTypeCompiler.java impl/sql/compile/TimeTypeCompiler.java Make convertible() accept conversions to TIMESTAMP. functionTests/master/cast.out functionTests/tests/lang/cast.sql Update existing test cases that expect the casts to fail. Remove test of cast from TIME to TIMESTAMP since that cannot be tested reliably in the canon-based tests (because the result varies with the current date). functionTests/tests/lang/CastingTest.java Update existing test cases that expect the casts to fail. Add new test cases.
          Hide
          Rick Hillegas added a comment -

          The patch was produced by git. Any advice on how to apply a git patch to a subversion repository? Thanks.

          Show
          Rick Hillegas added a comment - The patch was produced by git. Any advice on how to apply a git patch to a subversion repository? Thanks.
          Hide
          Rick Hillegas added a comment -

          Ah, Kim tells me that the magic is to use

          patch -p 1 -i $patchFile

          Thanks, Kim!

          Show
          Rick Hillegas added a comment - Ah, Kim tells me that the magic is to use patch -p 1 -i $patchFile Thanks, Kim!
          Hide
          Rick Hillegas added a comment -

          That patch looks simple and straightforward to me. After applying it, the repro passes. +1

          Show
          Rick Hillegas added a comment - That patch looks simple and straightforward to me. After applying it, the repro passes. +1
          Hide
          ASF subversion and git services added a comment -

          Commit 1568924 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1568924 ]

          DERBY-896: Allow casts from DATE and TIME to TIMESTAMP

          Show
          ASF subversion and git services added a comment - Commit 1568924 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1568924 ] DERBY-896 : Allow casts from DATE and TIME to TIMESTAMP
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for reviewing the patch, Rick. Committed to trunk.

          Show
          Knut Anders Hatlen added a comment - Thanks for reviewing the patch, Rick. Committed to trunk.
          Hide
          Lukas Eder added a comment -

          That is great, thank you very much, guys!

          Show
          Lukas Eder added a comment - That is great, thank you very much, guys!
          Hide
          Mike Matrigali added a comment -

          consider for 10.10 backport

          Show
          Mike Matrigali added a comment - consider for 10.10 backport
          Hide
          Mike Matrigali added a comment -

          This is a behavior change so marking it not a candidate for backport to an existing release. I went back and forth on this one so if anyone feels strongly that this should be backported add a comment.

          Show
          Mike Matrigali added a comment - This is a behavior change so marking it not a candidate for backport to an existing release. I went back and forth on this one so if anyone feels strongly that this should be backported add a comment.
          Hide
          Myrna van Lunteren added a comment -

          It appears from the previous discussion that this is something that should've been allowed as per the SQL Standard, so I'd think it a good one to backport, at least to 10.10...

          Show
          Myrna van Lunteren added a comment - It appears from the previous discussion that this is something that should've been allowed as per the SQL Standard, so I'd think it a good one to backport, at least to 10.10...
          Hide
          Mike Matrigali added a comment -

          release manager thinks it should go into 10.10 release so removing the backport reject flag.

          Show
          Mike Matrigali added a comment - release manager thinks it should go into 10.10 release so removing the backport reject flag.
          Hide
          Mike Matrigali added a comment -

          reopening for 10.10 backport.

          Show
          Mike Matrigali added a comment - reopening for 10.10 backport.
          Hide
          ASF subversion and git services added a comment -

          Commit 1573815 from mikem@apache.org in branch 'code/branches/10.10'
          [ https://svn.apache.org/r1573815 ]

          DERBY-896: Allow casts from DATE and TIME to TIMESTAMP

          backported change #1568924 from trunk to 10.10. Backport needed minor
          hand editing to get it to compile in 10.10 because of use of java5
          syntax in the test changes.

          Show
          ASF subversion and git services added a comment - Commit 1573815 from mikem@apache.org in branch 'code/branches/10.10' [ https://svn.apache.org/r1573815 ] DERBY-896 : Allow casts from DATE and TIME to TIMESTAMP backported change #1568924 from trunk to 10.10. Backport needed minor hand editing to get it to compile in 10.10 because of use of java5 syntax in the test changes.
          Hide
          Mike Matrigali added a comment -

          I am looking at the following failure in nightly testing, 10.10, windows, jsr169 environment. passed in all
          other ibm environments.
          http://people.apache.org/~myrnavl/derby_test_results/v10_10/windows/testlog/weme6.2/1573845-suites.All_diff.txt
          1) testExplicitCasts(org.apache.derbyTesting.functionTests.tests.lang.CastingTest)java.lang.NoSuchMethodError: java/lang/String.replace(Ljava/lang/CharSequence;Ljava/lang/CharSequence;)Ljava/lang/String;
          at org.apache.derbyTesting.functionTests.tests.lang.CastingTest.testExplicitCasts(CastingTest.java:454)
          at java.lang.reflect.AccessibleObject.invokeV(AccessibleObject.java:195)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:118)
          at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:442)
          at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:459)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)

          Show
          Mike Matrigali added a comment - I am looking at the following failure in nightly testing, 10.10, windows, jsr169 environment. passed in all other ibm environments. http://people.apache.org/~myrnavl/derby_test_results/v10_10/windows/testlog/weme6.2/1573845-suites.All_diff.txt 1) testExplicitCasts(org.apache.derbyTesting.functionTests.tests.lang.CastingTest)java.lang.NoSuchMethodError: java/lang/String.replace(Ljava/lang/CharSequence;Ljava/lang/CharSequence;)Ljava/lang/String; at org.apache.derbyTesting.functionTests.tests.lang.CastingTest.testExplicitCasts(CastingTest.java:454) at java.lang.reflect.AccessibleObject.invokeV(AccessibleObject.java:195) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:118) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:442) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:459) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          Hide
          ASF subversion and git services added a comment -

          Commit 1574618 from mikem@apache.org in branch 'code/branches/10.10'
          [ https://svn.apache.org/r1574618 ]

          DERBY-896 Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'."

          10.10 test does not run on jsr169 as it needs following interface not avaliable
          in jsr169:
          replace(CharSequence target, CharSequence replacement)

          For now just changing tests to not run this test in jsr169, and will
          file separate JIRA to change this test to run in jsr169.

          Show
          ASF subversion and git services added a comment - Commit 1574618 from mikem@apache.org in branch 'code/branches/10.10' [ https://svn.apache.org/r1574618 ] DERBY-896 Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'." 10.10 test does not run on jsr169 as it needs following interface not avaliable in jsr169: replace(CharSequence target, CharSequence replacement) For now just changing tests to not run this test in jsr169, and will file separate JIRA to change this test to run in jsr169.
          Hide
          Mike Matrigali added a comment -

          backported to 10.10, do not plan on working on this any more for now. Could be backported further if someone wanted to. resetting original owner.

          Show
          Mike Matrigali added a comment - backported to 10.10, do not plan on working on this any more for now. Could be backported further if someone wanted to. resetting original owner.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Oleksandr Alesinskyy
            • Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development