MySQL Bugs: #46055: Odd date behavior with LAST

Bug #46055	Odd date behavior with LAST_DAY
Submitted:	8 Jul 2009 20:19	Modified:	10 Jul 2009 7:08
Reporter:	Todd Brewer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.0.4.0	OS:	Windows (Vista 64)
Assigned to:		CPU Architecture:	Any
Tags:	.net, connector, date

Description:
Using the LAST_DAY function in the WHERE clause returns results based upon date AND time.

How to repeat:
Here is an example of what I am seeing:

(This is the basic query that returns the correct results for last month)
SELECT
	*
FROM
	foo
WHERE
	created >= '2009-06-01' AND
	created < '2009-07-01';

(The following query should return the same results for the prior month. It works fine in the 5.x connector for .NET, but returns the wrong results in the 6.0.4.0 connector)
SELECT
	*
FROM
	foo
WHERE
	created >= DATE_ADD(LAST_DAY(NOW() - INTERVAL 2 MONTH ), INTERVAL 1 DAY) AND 
	created < DATE_ADD(LAST_DAY(NOW() - INTERVAL 1 MONTH ), INTERVAL 1 DAY)

(To get the above query to work as expected in the 6.0.4.0 connector, I need to wrap NOW() in a DATE() function to force LAST_DAY to return only the date)	
SELECT
	*
FROM
	foo
WHERE
	created >= DATE_ADD(LAST_DAY(DATE(NOW()) - INTERVAL 2 MONTH ), INTERVAL 1 DAY) AND 
	created < DATE_ADD(LAST_DAY(DATE(NOW()) - INTERVAL 1 MONTH ), INTERVAL 1 DAY)

(a simple SELECT will return the dates as expected; it only seems to have a problem when used in the WHERE clause)
SELECT
	DATE_ADD(LAST_DAY(DATE(NOW()) - INTERVAL 2 MONTH ), INTERVAL 1 DAY) as Date1,
	DATE_ADD(LAST_DAY(DATE(NOW()) - INTERVAL 1 MONTH ), INTERVAL 1 DAY) as Date2;

Hi Todd and thanks for your report.

Can you please fill in the necessary info (see Bug#46054) and attach small but complete test case demonstrating unwanted behavior.
It would also be good if you can run the same test in mysql command line client started with -T (MySQL server 5.0) or with --column-type-info (MySQL servers 5.1 and up) and see if you get different result. If relevant, paste output from command line here.

I have spent some time on this and believe I have pin-pointed it, and it isn't a connector issue.  I believe it is a bug in 5.0.36 enterprise.

If you do this:

SELECT LAST_DAY(NOW() - INTERVAL 2 MONTH ) + INTERVAL 1 DAY

It will return the DATE of the first day of last month.

BUT if you do this:

SELECT 
	*
FROM
    foo
WHERE
  created >= LAST_DAY(NOW() - INTERVAL 2 MONTH ) + INTERVAL 1 DAY and 
  created < LAST_DAY(NOW() - INTERVAL 1 MONTH ) + INTERVAL 1 DAY

the value returned from the LAST_DAY method in the WHERE clause seems to return a dateTIME, returning unexpected results. If you change the query to use CURDATE() instead of NOW() it works fine.

I can only replicate this on 5.0.36 enterprise.  If I run the same scenario in 5.0.67 community, or 5.1.36 community, it works fine.

I can recreate this from the command line and from connector 6.0.4.0.

Todd, I am not sure what are you testing and against which versions...

According to http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_curdate your difference between NOW() and CURDATE() is well documented and expected. Observe that NOW() returns DATETIME while CURDATE() returns date.

Now, I tested your code against MySQL server 5.1.31 on OpenSolaris and 5.0.84 on my local Win2K8x64 box. Both behave the same and according to specs:

Server version: 5.0.84-community-nt MySQL Community Edition (GPL)
Server version: 5.1.31-log MySQL Community Server (GPL)

mysql> SELECT * FROM date_test WHERE created >= DATE_ADD(LAST_DAY(NOW() - INTERVAL 2 MONTH ), INTERVAL 1 DAY)AND created < DATE_ADD(LAST_DAY(NOW() - INTERVAL 1 MONTH ), INTERVAL 1 DAY);
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `date_test`
Org_table:  `date_test`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

Field   2:  `created`
Catalog:    `def`
Database:   `test`
Table:      `date_test`
Org_table:  `date_test`
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      NOT_NULL BINARY NO_DEFAULT_VALUE

+----+---------------------+
| id | created             |
+----+---------------------+
|  6 | 2009-06-01 00:00:00 |
|  7 | 2009-06-01 01:00:00 |
|  8 | 2009-06-01 02:00:00 |
|  9 | 2009-06-01 03:00:00 |
| 10 | 2009-06-01 04:00:00 |
| 11 | 2009-06-01 05:00:00 |
| 12 | 2009-06-01 06:00:00 |
| 13 | 2009-06-01 07:00:00 |
| 14 | 2009-06-01 08:00:00 |
| 15 | 2009-06-01 09:00:00 |
| 16 | 2009-06-01 10:00:00 |
| 17 | 2009-06-01 11:00:00 |
| 18 | 2009-06-01 12:00:00 |
| 19 | 2009-06-01 13:00:00 |
| 20 | 2009-06-01 14:00:00 |
| 21 | 2009-06-01 15:00:00 |
| 22 | 2009-06-01 16:00:00 |
| 23 | 2009-06-01 17:00:00 |
| 24 | 2009-06-01 18:00:00 |
| 25 | 2009-06-01 19:00:00 |
| 26 | 2009-06-01 20:00:00 |
| 27 | 2009-06-01 21:00:00 |
| 28 | 2009-06-01 22:00:00 |
| 29 | 2009-06-01 23:00:00 |
| 30 | 2009-06-02 00:00:00 |
| 31 | 2009-06-02 00:01:00 |
| 32 | 2009-06-02 00:02:00 |
| 33 | 2009-06-02 00:03:00 |
| 34 | 2009-06-02 00:04:00 |
| 35 | 2009-06-02 00:05:00 |
+----+---------------------+
30 rows in set (0.00 sec)

So, where is the bug? Especially the one in c/NET 6.0.4.