| Bug #18100 | EXTRACT(QUARTER FROM {date}) returns unexpected results | ||
|---|---|---|---|
| Submitted: | 9 Mar 2006 10:02 | Modified: | 8 Apr 2006 13:46 |
| Reporter: | Giles McArdell | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | Linux (Linux (SUSE)) |
| Assigned to: | Hartmut Holzgraefe | CPU Architecture: | Any |
[9 Mar 2006 10:13]
Giles McArdell
Additional workaround, the following works in all cases: SELECT CAST((EXTRACT(MONTH FROM '2004-03-01') / 3) AS SIGNED)
[9 Mar 2006 11:52]
Hartmut Holzgraefe
Verified using the following statement which shows the result in a more meaningfull way,
also changed the day from 1st to 15th to rule out time zone related problem:
SELECT EXTRACT(QUARTER FROM '2004-00-15'),
EXTRACT(QUARTER FROM '2004-01-15'),
EXTRACT(QUARTER FROM '2004-02-15'),
EXTRACT(QUARTER FROM '2004-03-15'),
EXTRACT(QUARTER FROM '2004-04-15'),
EXTRACT(QUARTER FROM '2004-05-15'),
EXTRACT(QUARTER FROM '2004-06-15'),
EXTRACT(QUARTER FROM '2004-07-15'),
EXTRACT(QUARTER FROM '2004-08-15'),
EXTRACT(QUARTER FROM '2004-09-15'),
EXTRACT(QUARTER FROM '2004-10-15'),
EXTRACT(QUARTER FROM '2004-11-15'),
EXTRACT(QUARTER FROM '2004-12-15')\G
*************************** 1. row ***************************
EXTRACT(QUARTER FROM '2004-00-15'): 1
EXTRACT(QUARTER FROM '2004-01-15'): 1
EXTRACT(QUARTER FROM '2004-02-15'): 1
EXTRACT(QUARTER FROM '2004-03-15'): 2
EXTRACT(QUARTER FROM '2004-04-15'): 2
EXTRACT(QUARTER FROM '2004-05-15'): 2
EXTRACT(QUARTER FROM '2004-06-15'): 3
EXTRACT(QUARTER FROM '2004-07-15'): 3
EXTRACT(QUARTER FROM '2004-08-15'): 3
EXTRACT(QUARTER FROM '2004-09-15'): 4
EXTRACT(QUARTER FROM '2004-10-15'): 4
EXTRACT(QUARTER FROM '2004-11-15'): 4
EXTRACT(QUARTER FROM '2004-12-15'): 5
1 row in set, 1 warning (0.00 sec)
[22 Mar 2006 7:20]
Hartmut Holzgraefe
test case
Attachment: bug18100.tar.gz (application/x-gzip, text), 722 bytes.
[31 Mar 2006 23:22]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4377
[7 Apr 2006 9:15]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4606
[8 Apr 2006 13:46]
Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Documented bugfix in 5.0.21 and 5.1.9 changelogs. Closed.
Description: The calculation of quarters in the EXPORT() Function seems to be 1 month ahead of itself, IE January, february return 1 but March returns 2. This discrepency continues IE Apr=2, May=2, Jun=3, Jul=3, Aug=3, Sep=4, Oct=4, Nov=4, Dec = 5 (How many Quarters can a year have? ;-) Also The EXPORT function accepts months with a value of zero (suspect this may be related, as it gives NULL for months over 12). How to repeat: Try this statement: select EXTRACT(QUARTER FROM '2004-00-01') Q0M3, EXTRACT(QUARTER FROM '2004-01-01') Q1M1,EXTRACT(QUARTER FROM '2004-02-01') Q1M2,EXTRACT(QUARTER FROM '2004-03-01') Q1M3, EXTRACT(QUARTER FROM '2004-04-01') Q2M1,EXTRACT(QUARTER FROM '2004-05-01') Q2M2,EXTRACT(QUARTER FROM '2004-06-01') Q2M3, EXTRACT(QUARTER FROM '2004-07-01') Q3M1,EXTRACT(QUARTER FROM '2004-08-01') Q3M2,EXTRACT(QUARTER FROM '2004-09-01') Q3M3, EXTRACT(QUARTER FROM '2004-10-01') Q4M1,EXTRACT(QUARTER FROM '2004-11-01') Q4M2,EXTRACT(QUARTER FROM '2004-12-01') Q4M3, EXTRACT(QUARTER FROM '2004-13-01') Q5M1; Note particularly that '2004-00-01' returns a value whereas '2004-13-01' returns NULL Suggested fix: It is possible to work around by subtracting a month from the date IE: select EXTRACT(QUARTER FROM DATE_SUB('2004-03-01', INTERVAL 1 MONTH)) Q1M3; But this will then cease to work when this bug is fixed.