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: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.2.1
    • Fix Version/s: 10.10.2.0, 10.11.1.1
    • 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. w.sql
        0.2 kB
        Rick Hillegas
      2. allow-casts.diff
        2 kB
        Knut Anders Hatlen
      3. allow-casts-with-tests.diff
        23 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Oleksandr Alesinskyy created issue -
          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?
          Mike Matrigali made changes -
          Field Original Value New Value
          Component/s SQL [ 11408 ]
          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
          Rick Hillegas made changes -
          Attachment w.sql [ 12414112 ]
          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.
          Rick Hillegas made changes -
          Urgency Normal
          Issue & fix info [Repro attached]
          Kathey Marsden made changes -
          Labels derby_triage10_5_2
          Gavin made changes -
          Workflow jira [ 12346416 ] Default workflow, editable Closed status [ 12801374 ]
          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...
          Knut Anders Hatlen made changes -
          Attachment allow-casts.diff [ 12628713 ]
          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.
          Knut Anders Hatlen made changes -
          Attachment allow-casts-with-tests.diff [ 12629007 ]
          Knut Anders Hatlen made changes -
          Assignee Knut Anders Hatlen [ knutanders ]
          Knut Anders Hatlen made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Knut Anders Hatlen made changes -
          Issue & fix info Repro attached [ 10424 ] Patch Available,Repro attached [ 10102, 10424 ]
          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.
          Knut Anders Hatlen made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Issue & fix info Patch Available,Repro attached [ 10102, 10424 ] Repro attached [ 10424 ]
          Fix Version/s 10.11.0.0 [ 12324243 ]
          Resolution Fixed [ 1 ]
          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!
          Mike Matrigali made changes -
          Link This issue is related to DERBY-6481 [ DERBY-6481 ]
          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.
          Mike Matrigali made changes -
          Labels derby_triage10_5_2 derby_backport_reject_10_10 derby_triage10_5_2
          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.
          Mike Matrigali made changes -
          Labels derby_backport_reject_10_10 derby_triage10_5_2 derby_triage10_5_2
          Hide
          Mike Matrigali added a comment -

          reopening for 10.10 backport.

          Show
          Mike Matrigali added a comment - reopening for 10.10 backport.
          Mike Matrigali made changes -
          Resolution Fixed [ 1 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Assignee Knut Anders Hatlen [ knutanders ] Mike Matrigali [ mikem ]
          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.
          Mike Matrigali made changes -
          Link This issue relates to DERBY-6501 [ DERBY-6501 ]
          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.
          Mike Matrigali made changes -
          Assignee Mike Matrigali [ mikem ] Knut Anders Hatlen [ knutanders ]
          Fix Version/s 10.10.1.4 [ 12324247 ]
          Mike Matrigali made changes -
          Status Reopened [ 4 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Myrna van Lunteren made changes -
          Fix Version/s 10.10.2.0 [ 12326659 ]
          Fix Version/s 10.10.1.4 [ 12324247 ]
          Hide
          Myrna van Lunteren added a comment -

          bulk change to close all issues resolved but not closed and not changed since June 1, 2014.

          Show
          Myrna van Lunteren added a comment - bulk change to close all issues resolved but not closed and not changed since June 1, 2014.
          Myrna van Lunteren made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open In Progress In Progress
          2935d 14h 33m 1 Knut Anders Hatlen 14/Feb/14 11:53
          In Progress In Progress Resolved Resolved
          2d 22h 4m 1 Knut Anders Hatlen 17/Feb/14 09:58
          Resolved Resolved Reopened Reopened
          11d 12h 28m 1 Mike Matrigali 28/Feb/14 22:27
          Reopened Reopened Resolved Resolved
          5d 18h 57m 1 Mike Matrigali 06/Mar/14 17:24
          Resolved Resolved Closed Closed
          320d 6h 58m 1 Myrna van Lunteren 21/Jan/15 00:23

            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