You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Nelson Rodrigues (JIRA)" <ji...@apache.org> on 2009/03/21 13:59:50 UTC
[jira] Created: (DERBY-4107) DATE function returns wrong result for
integer argument
DATE function returns wrong result for integer argument
-------------------------------------------------------
Key: DERBY-4107
URL: https://issues.apache.org/jira/browse/DERBY-4107
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, lists Derby limitations for DATE is said that the smallest DATE value is 0001-01-01 and the largest DATE value is 9999-12-31.
When the same manual explains the DATE function is said that:
"The argument must be ... a positive number less than or equal to 2,932,897 ... The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number."
Testing for the largest integer returns the expected result:
select date(2932897) from SYSIBM.SYSDUMMY1 returns 9999-12-31 -> OK
The problem comes when testing the smallest integer. We get a result different than we expect:
select date(1) from SYSIBM.SYSDUMMY1 returns 1970-01-01, but it should have returned 0001-01-01
The smallest date we get using integer as an argument to date function should be the same we get when using the smallest string representation as an argument. In other words date(1) should be equal to date('0001-01-01').
select date('0001-01-01') from SYSIBM.SYSDUMMY1 returns 0001-01-01 -> OK
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-4107) DATE function returns wrong result
for integer argument
Posted by "Yun Lee (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4107?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Yun Lee reassigned DERBY-4107:
------------------------------
Assignee: Yun Lee
> DATE function returns wrong result for integer argument
> -------------------------------------------------------
>
> Key: DERBY-4107
> URL: https://issues.apache.org/jira/browse/DERBY-4107
> 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
> Assignee: Yun Lee
>
> When Derby Reference Manual, version 10.4, lists Derby limitations for DATE is said that the smallest DATE value is 0001-01-01 and the largest DATE value is 9999-12-31.
> When the same manual explains the DATE function is said that:
> "The argument must be ... a positive number less than or equal to 2,932,897 ... The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number."
> Testing for the largest integer returns the expected result:
> select date(2932897) from SYSIBM.SYSDUMMY1 returns 9999-12-31 -> OK
> The problem comes when testing the smallest integer. We get a result different than we expect:
> select date(1) from SYSIBM.SYSDUMMY1 returns 1970-01-01, but it should have returned 0001-01-01
> The smallest date we get using integer as an argument to date function should be the same we get when using the smallest string representation as an argument. In other words date(1) should be equal to date('0001-01-01').
> select date('0001-01-01') from SYSIBM.SYSDUMMY1 returns 0001-01-01 -> OK
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4107) The Reference Guide incorrectly
describes the behavior of the DATE function when applied to an integer
argument
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4107?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton updated DERBY-4107:
-----------------------------------
Attachment: rrefdatefunc.html
docs.diff
I checked and I see that test_DateAndDatetimeFunctionsMore in the DateTimeTest
in the lang suite has tests for the DATE function which concur with Rick's analysis,
so I've constructed a patch proposal to the DATE reference page in the manual which
corrects the description to read 1970, not 0001, for the year.
I also added an example to the page, to hopefully make it more clear.
> The Reference Guide incorrectly describes the behavior of the DATE function when applied to an integer argument
> ---------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4107
> URL: https://issues.apache.org/jira/browse/DERBY-4107
> 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, rrefdatefunc.html
>
>
> When Derby Reference Manual, version 10.4, lists Derby limitations for DATE is said that the smallest DATE value is 0001-01-01 and the largest DATE value is 9999-12-31.
> When the same manual explains the DATE function is said that:
> "The argument must be ... a positive number less than or equal to 2,932,897 ... The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number."
> Testing for the largest integer returns the expected result:
> select date(2932897) from SYSIBM.SYSDUMMY1 returns 9999-12-31 -> OK
> The problem comes when testing the smallest integer. We get a result different than we expect:
> select date(1) from SYSIBM.SYSDUMMY1 returns 1970-01-01, but it should have returned 0001-01-01
> The smallest date we get using integer as an argument to date function should be the same we get when using the smallest string representation as an argument. In other words date(1) should be equal to date('0001-01-01').
> select date('0001-01-01') from SYSIBM.SYSDUMMY1 returns 0001-01-01 -> OK
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4107) DATE function returns wrong result
for integer argument
Posted by "Nelson Rodrigues (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4107?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12700513#action_12700513 ]
Nelson Rodrigues commented on DERBY-4107:
-----------------------------------------
This problem may be related to DERBY-4106 issue.
> DATE function returns wrong result for integer argument
> -------------------------------------------------------
>
> Key: DERBY-4107
> URL: https://issues.apache.org/jira/browse/DERBY-4107
> 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
> Assignee: Yun Lee
>
> When Derby Reference Manual, version 10.4, lists Derby limitations for DATE is said that the smallest DATE value is 0001-01-01 and the largest DATE value is 9999-12-31.
> When the same manual explains the DATE function is said that:
> "The argument must be ... a positive number less than or equal to 2,932,897 ... The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number."
> Testing for the largest integer returns the expected result:
> select date(2932897) from SYSIBM.SYSDUMMY1 returns 9999-12-31 -> OK
> The problem comes when testing the smallest integer. We get a result different than we expect:
> select date(1) from SYSIBM.SYSDUMMY1 returns 1970-01-01, but it should have returned 0001-01-01
> The smallest date we get using integer as an argument to date function should be the same we get when using the smallest string representation as an argument. In other words date(1) should be equal to date('0001-01-01').
> select date('0001-01-01') from SYSIBM.SYSDUMMY1 returns 0001-01-01 -> OK
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4107) DATE function returns wrong result for
integer argument
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4107?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-4107:
---------------------------------
Component/s: (was: SQL)
Documentation
Bug behavior facts: (was: [Wrong query result])
I am changing this from a SQL bug to a docs bug. The documentation says that the DATE() function, applied to positive integer arguments, results in days counting up from the first day of the common era. This is not true. The days count up from the beginning of the UNIX epoch, that is, January 1, 1970. I think there is a typo in the documentation.
Note that the highest integer argument which you can pass to the DATE function is 2932897. That many days after the start of the UNIX epoch is 9999-12-31, I think. At least my back of the envelope calculation, which doesn't account for leap days, comes in around there. If DATE were counting from the beginning of the common era, then DATE( 2932897 ) would be something like 8029-12-31.
I am also unchecking the "wrong query result" box. I can't find this function in the SQL standard and I don't see any industry consensus about how DATE should behave when applied to integer arguments. I don't see any compelling reason to change Derby's behavior to something different, particularly since that kind of change would create compatibility problems.
> DATE function returns wrong result for integer argument
> -------------------------------------------------------
>
> Key: DERBY-4107
> URL: https://issues.apache.org/jira/browse/DERBY-4107
> 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: Yun Lee
>
> When Derby Reference Manual, version 10.4, lists Derby limitations for DATE is said that the smallest DATE value is 0001-01-01 and the largest DATE value is 9999-12-31.
> When the same manual explains the DATE function is said that:
> "The argument must be ... a positive number less than or equal to 2,932,897 ... The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number."
> Testing for the largest integer returns the expected result:
> select date(2932897) from SYSIBM.SYSDUMMY1 returns 9999-12-31 -> OK
> The problem comes when testing the smallest integer. We get a result different than we expect:
> select date(1) from SYSIBM.SYSDUMMY1 returns 1970-01-01, but it should have returned 0001-01-01
> The smallest date we get using integer as an argument to date function should be the same we get when using the smallest string representation as an argument. In other words date(1) should be equal to date('0001-01-01').
> select date('0001-01-01') from SYSIBM.SYSDUMMY1 returns 0001-01-01 -> OK
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4107) DATE function returns wrong result for
integer argument
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4107?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-4107:
---------------------------------
Urgency: Normal
Bug behavior facts: [Wrong query result]
Triaged July 2: Marked as WrongQueryResult and assigned normal urgency.
> DATE function returns wrong result for integer argument
> -------------------------------------------------------
>
> Key: DERBY-4107
> URL: https://issues.apache.org/jira/browse/DERBY-4107
> 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
> Assignee: Yun Lee
>
> When Derby Reference Manual, version 10.4, lists Derby limitations for DATE is said that the smallest DATE value is 0001-01-01 and the largest DATE value is 9999-12-31.
> When the same manual explains the DATE function is said that:
> "The argument must be ... a positive number less than or equal to 2,932,897 ... The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number."
> Testing for the largest integer returns the expected result:
> select date(2932897) from SYSIBM.SYSDUMMY1 returns 9999-12-31 -> OK
> The problem comes when testing the smallest integer. We get a result different than we expect:
> select date(1) from SYSIBM.SYSDUMMY1 returns 1970-01-01, but it should have returned 0001-01-01
> The smallest date we get using integer as an argument to date function should be the same we get when using the smallest string representation as an argument. In other words date(1) should be equal to date('0001-01-01').
> select date('0001-01-01') from SYSIBM.SYSDUMMY1 returns 0001-01-01 -> OK
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4107) The Reference Guide incorrectly
describes the behavior of the DATE function when applied to an integer
argument
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4107?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-4107:
---------------------------------
Summary: The Reference Guide incorrectly describes the behavior of the DATE function when applied to an integer argument (was: DATE function returns wrong result for integer argument)
> The Reference Guide incorrectly describes the behavior of the DATE function when applied to an integer argument
> ---------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4107
> URL: https://issues.apache.org/jira/browse/DERBY-4107
> 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: Yun Lee
>
> When Derby Reference Manual, version 10.4, lists Derby limitations for DATE is said that the smallest DATE value is 0001-01-01 and the largest DATE value is 9999-12-31.
> When the same manual explains the DATE function is said that:
> "The argument must be ... a positive number less than or equal to 2,932,897 ... The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number."
> Testing for the largest integer returns the expected result:
> select date(2932897) from SYSIBM.SYSDUMMY1 returns 9999-12-31 -> OK
> The problem comes when testing the smallest integer. We get a result different than we expect:
> select date(1) from SYSIBM.SYSDUMMY1 returns 1970-01-01, but it should have returned 0001-01-01
> The smallest date we get using integer as an argument to date function should be the same we get when using the smallest string representation as an argument. In other words date(1) should be equal to date('0001-01-01').
> select date('0001-01-01') from SYSIBM.SYSDUMMY1 returns 0001-01-01 -> OK
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-4107) The Reference Guide incorrectly
describes the behavior of the DATE function when applied to an integer
argument
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4107?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton reassigned DERBY-4107:
--------------------------------------
Assignee: Bryan Pendleton (was: Yun Lee)
> The Reference Guide incorrectly describes the behavior of the DATE function when applied to an integer argument
> ---------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4107
> URL: https://issues.apache.org/jira/browse/DERBY-4107
> 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, lists Derby limitations for DATE is said that the smallest DATE value is 0001-01-01 and the largest DATE value is 9999-12-31.
> When the same manual explains the DATE function is said that:
> "The argument must be ... a positive number less than or equal to 2,932,897 ... The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number."
> Testing for the largest integer returns the expected result:
> select date(2932897) from SYSIBM.SYSDUMMY1 returns 9999-12-31 -> OK
> The problem comes when testing the smallest integer. We get a result different than we expect:
> select date(1) from SYSIBM.SYSDUMMY1 returns 1970-01-01, but it should have returned 0001-01-01
> The smallest date we get using integer as an argument to date function should be the same we get when using the smallest string representation as an argument. In other words date(1) should be equal to date('0001-01-01').
> select date('0001-01-01') from SYSIBM.SYSDUMMY1 returns 0001-01-01 -> OK
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-4107) The Reference Guide incorrectly
describes the behavior of the DATE function when applied to an integer
argument
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4107?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton resolved DERBY-4107.
------------------------------------
Resolution: Fixed
Fix Version/s: 10.6.0.0
Committed the change to the docs trunk as revision 834310.
> The Reference Guide incorrectly describes the behavior of the DATE function when applied to an integer argument
> ---------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4107
> URL: https://issues.apache.org/jira/browse/DERBY-4107
> 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, rrefdatefunc.html
>
>
> When Derby Reference Manual, version 10.4, lists Derby limitations for DATE is said that the smallest DATE value is 0001-01-01 and the largest DATE value is 9999-12-31.
> When the same manual explains the DATE function is said that:
> "The argument must be ... a positive number less than or equal to 2,932,897 ... The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number."
> Testing for the largest integer returns the expected result:
> select date(2932897) from SYSIBM.SYSDUMMY1 returns 9999-12-31 -> OK
> The problem comes when testing the smallest integer. We get a result different than we expect:
> select date(1) from SYSIBM.SYSDUMMY1 returns 1970-01-01, but it should have returned 0001-01-01
> The smallest date we get using integer as an argument to date function should be the same we get when using the smallest string representation as an argument. In other words date(1) should be equal to date('0001-01-01').
> select date('0001-01-01') from SYSIBM.SYSDUMMY1 returns 0001-01-01 -> OK
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.