[jira] Created: (DERBY-4106) The INTEGER function doen't return an integer representation of a date

The INTEGER function doen't return an integer representation of a date
----------------------------------------------------------------------

                 Key: DERBY-4106
                 URL: https://issues.apache.org/jira/browse/DERBY-4106
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.4.2.0
         Environment: MS Windows XP Professional Version 2002 Service Pack 2, running NetBeans IDE 6.5
            Reporter: Nelson Rodrigues


When Derby Reference Manual, version 10.4, explains the INTEGER function is said that:

 "The INTEGER function returns an integer representation of a number, character string,
DATE, or time in the form of an integer constant"

But when I try to convert a DATE to INTEGER I receive an sqlstate 42846: "Cannot convert types 'DATE'  to 'INTEGER'."

This is a sample query where the expected result is the integer number 1:

select integer(date(1)) from SYSIBM.SYSDUMMY1

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4106?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12700515#action_12700515 ]

Nelson Rodrigues commented on DERBY-4106:
-----------------------------------------

This problem may be related to DERBY-4107 issue.

> The INTEGER function doen't return an integer representation of a date
> ----------------------------------------------------------------------
>
>                 Key: DERBY-4106
>                 URL: https://issues.apache.org/jira/browse/DERBY-4106
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.2.0
>         Environment: MS Windows XP Professional Version 2002 Service Pack 2, running NetBeans IDE 6.5
>            Reporter: Nelson Rodrigues
>
> When Derby Reference Manual, version 10.4, explains the INTEGER function is said that:
>  "The INTEGER function returns an integer representation of a number, character string,
> DATE, or time in the form of an integer constant"
> But when I try to convert a DATE to INTEGER I receive an sqlstate 42846: "Cannot convert types 'DATE'  to 'INTEGER'."
> This is a sample query where the expected result is the integer number 1:
> select integer(date(1)) from SYSIBM.SYSDUMMY1

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4106?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-4106:
---------------------------------

    Urgency: Normal

Triaged July 2, 2009: Assigned normal urgency.

> The INTEGER function doen't return an integer representation of a date
> ----------------------------------------------------------------------
>
>                 Key: DERBY-4106
>                 URL: https://issues.apache.org/jira/browse/DERBY-4106
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.2.0
>         Environment: MS Windows XP Professional Version 2002 Service Pack 2, running NetBeans IDE 6.5
>            Reporter: Nelson Rodrigues
>
> When Derby Reference Manual, version 10.4, explains the INTEGER function is said that:
>  "The INTEGER function returns an integer representation of a number, character string,
> DATE, or time in the form of an integer constant"
> But when I try to convert a DATE to INTEGER I receive an sqlstate 42846: "Cannot convert types 'DATE'  to 'INTEGER'."
> This is a sample query where the expected result is the integer number 1:
> select integer(date(1)) from SYSIBM.SYSDUMMY1

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4106?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-4106:
---------------------------------

    Component/s:     (was: SQL)
                 Documentation

Changing this issue to a docs bug. The documentation says that the INTEGER function can be applied to dates and times. However, if you attempt to do this, you will get a casting error, at least as far back as 10.1.

The documentation claims that INTEGER can be applied to numbers, strings, and date/times. The documentation then goes on to explain what you should expect to see if you apply INTEGER to numbers and strings. However, the documentation does not explain what should happen if you apply INTEGER to date/times.

I believe that someone overzealously claimed that INTEGER can be applied to date/times and then didn't bother to verify what actually happens.

If this is an important issue, then please file a separate enhancement request for applying INTEGER to date/time values. Please specify what behavior should be seen. For instance, should the INTEGER value of a DATE be the number of milliseconds or the number of days from the start of the UNIX epoch...or something else?

> The INTEGER function doen't return an integer representation of a date
> ----------------------------------------------------------------------
>
>                 Key: DERBY-4106
>                 URL: https://issues.apache.org/jira/browse/DERBY-4106
>             Project: Derby
>          Issue Type: Bug
>          Components: Documentation
>    Affects Versions: 10.4.2.0
>         Environment: MS Windows XP Professional Version 2002 Service Pack 2, running NetBeans IDE 6.5
>            Reporter: Nelson Rodrigues
>
> When Derby Reference Manual, version 10.4, explains the INTEGER function is said that:
>  "The INTEGER function returns an integer representation of a number, character string,
> DATE, or time in the form of an integer constant"
> But when I try to convert a DATE to INTEGER I receive an sqlstate 42846: "Cannot convert types 'DATE'  to 'INTEGER'."
> This is a sample query where the expected result is the integer number 1:
> select integer(date(1)) from SYSIBM.SYSDUMMY1

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4106?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-4106:
---------------------------------

    Summary: The Reference Gulde claims that the INTEGER function can be applied to dates and times  (was: The INTEGER function doen't return an integer representation of a date)

> The Reference Gulde claims that the INTEGER function can be applied to dates and times
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-4106
>                 URL: https://issues.apache.org/jira/browse/DERBY-4106
>             Project: Derby
>          Issue Type: Bug
>          Components: Documentation
>    Affects Versions: 10.4.2.0
>         Environment: MS Windows XP Professional Version 2002 Service Pack 2, running NetBeans IDE 6.5
>            Reporter: Nelson Rodrigues
>
> When Derby Reference Manual, version 10.4, explains the INTEGER function is said that:
>  "The INTEGER function returns an integer representation of a number, character string,
> DATE, or time in the form of an integer constant"
> But when I try to convert a DATE to INTEGER I receive an sqlstate 42846: "Cannot convert types 'DATE'  to 'INTEGER'."
> This is a sample query where the expected result is the integer number 1:
> select integer(date(1)) from SYSIBM.SYSDUMMY1

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4106?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Bryan Pendleton reassigned DERBY-4106:
--------------------------------------

    Assignee: Bryan Pendleton

> The Reference Gulde claims that the INTEGER function can be applied to dates and times
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-4106
>                 URL: https://issues.apache.org/jira/browse/DERBY-4106
>             Project: Derby
>          Issue Type: Bug
>          Components: Documentation
>    Affects Versions: 10.4.2.0
>         Environment: MS Windows XP Professional Version 2002 Service Pack 2, running NetBeans IDE 6.5
>            Reporter: Nelson Rodrigues
>            Assignee: Bryan Pendleton
>
> When Derby Reference Manual, version 10.4, explains the INTEGER function is said that:
>  "The INTEGER function returns an integer representation of a number, character string,
> DATE, or time in the form of an integer constant"
> But when I try to convert a DATE to INTEGER I receive an sqlstate 42846: "Cannot convert types 'DATE'  to 'INTEGER'."
> This is a sample query where the expected result is the integer number 1:
> select integer(date(1)) from SYSIBM.SYSDUMMY1

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4106?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Bryan Pendleton updated DERBY-4106:
-----------------------------------

    Attachment: rrefbuiltinteger.html
                docs.diff

I removed the reference to date and time in the attached patch proposal.

I checked the manual pages for SMALLINT and BIGINT and observed that
they were correct, and made INTEGER follow their pattern.

I checked that CastingTest.java in the lang suite already verifies
the behavior of the INTEGER function, so no new tests are needed.

This seems like a straightforward fix to the docs.

> The Reference Gulde claims that the INTEGER function can be applied to dates and times
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-4106
>                 URL: https://issues.apache.org/jira/browse/DERBY-4106
>             Project: Derby
>          Issue Type: Bug
>          Components: Documentation
>    Affects Versions: 10.4.2.0
>         Environment: MS Windows XP Professional Version 2002 Service Pack 2, running NetBeans IDE 6.5
>            Reporter: Nelson Rodrigues
>            Assignee: Bryan Pendleton
>         Attachments: docs.diff, rrefbuiltinteger.html
>
>
> When Derby Reference Manual, version 10.4, explains the INTEGER function is said that:
>  "The INTEGER function returns an integer representation of a number, character string,
> DATE, or time in the form of an integer constant"
> But when I try to convert a DATE to INTEGER I receive an sqlstate 42846: "Cannot convert types 'DATE'  to 'INTEGER'."
> This is a sample query where the expected result is the integer number 1:
> select integer(date(1)) from SYSIBM.SYSDUMMY1

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4106?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Bryan Pendleton resolved DERBY-4106.
------------------------------------

       Resolution: Fixed
    Fix Version/s: 10.6.0.0

Commited to the documentation trunk as revision 834300.

> The Reference Gulde claims that the INTEGER function can be applied to dates and times
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-4106
>                 URL: https://issues.apache.org/jira/browse/DERBY-4106
>             Project: Derby
>          Issue Type: Bug
>          Components: Documentation
>    Affects Versions: 10.4.2.0
>         Environment: MS Windows XP Professional Version 2002 Service Pack 2, running NetBeans IDE 6.5
>            Reporter: Nelson Rodrigues
>            Assignee: Bryan Pendleton
>             Fix For: 10.6.0.0
>
>         Attachments: docs.diff, rrefbuiltinteger.html
>
>
> When Derby Reference Manual, version 10.4, explains the INTEGER function is said that:
>  "The INTEGER function returns an integer representation of a number, character string,
> DATE, or time in the form of an integer constant"
> But when I try to convert a DATE to INTEGER I receive an sqlstate 42846: "Cannot convert types 'DATE'  to 'INTEGER'."
> This is a sample query where the expected result is the integer number 1:
> select integer(date(1)) from SYSIBM.SYSDUMMY1

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4106?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12777571#action_12777571 ]

Dag H. Wanvik commented on DERBY-4106:
--------------------------------------

Fix looks good to me! Thanks for fixing this, Bryan.

> The Reference Gulde claims that the INTEGER function can be applied to dates and times
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-4106
>                 URL: https://issues.apache.org/jira/browse/DERBY-4106
>             Project: Derby
>          Issue Type: Bug
>          Components: Documentation
>    Affects Versions: 10.4.2.0
>         Environment: MS Windows XP Professional Version 2002 Service Pack 2, running NetBeans IDE 6.5
>            Reporter: Nelson Rodrigues
>            Assignee: Bryan Pendleton
>             Fix For: 10.6.0.0
>
>         Attachments: docs.diff, rrefbuiltinteger.html
>
>
> When Derby Reference Manual, version 10.4, explains the INTEGER function is said that:
>  "The INTEGER function returns an integer representation of a number, character string,
> DATE, or time in the form of an integer constant"
> But when I try to convert a DATE to INTEGER I receive an sqlstate 42846: "Cannot convert types 'DATE'  to 'INTEGER'."
> This is a sample query where the expected result is the integer number 1:
> select integer(date(1)) from SYSIBM.SYSDUMMY1

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.