MySQL Bugs: #35035: Query using last_day does not work properly on bi sixth years

Bug #35035	Query using last_day does not work properly on bi sixth years
Submitted:	4 Mar 2008 14:46	Modified:	4 Mar 2008 23:06
Reporter:	Rafael Azevedo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	MySQL - 4.1.22-log	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
I was running a query and one of the columns of my database is date type.
I have one row with date 2008-02-29 (bi-sixth year).

When I tried this query:
SELECT SUM( Total_Cadastros )
FROM Agendamentos a
WHERE Data_Envio
BETWEEN '2008-02-01'
AND last_day( '2008-02-01' )
AND a.CustomerID = '75'

It was supposed to return the total of 'Total_Cadastros' including all days of february. The problem was that it did not sum the rows of '2008-02-29', only days before that date. After I changed the info to '2008-02-28', it worked properly.

How to repeat:
Create a table with a date column.
Insert a row with date: 2008-02-29

Run the query similar to this:
SELECT SUM( Total_Cadastros )
FROM Agendamentos a
WHERE Date
BETWEEN '2008-02-01'
AND last_day( '2008-02-01' )

Also I have noticed that on on 1 day of March (03-01) the CURDATE()-1 returns YYYY-03-00

MySQL version 5.0.32 (Debian)

One more thing YYYY-03-00 is returned only on 03-01 of a leap year (when february has 29 days)

Thank you for the bug report. Could you please provide a complete test case
with create table statement, insert data statements, query, the result you
get. Thanks in advance.

CREATE TABLE `Agendamentos` (
  `EnvioID` int(11) unsigned NOT NULL auto_increment,
  `CustomerID` int(11) NOT NULL default '0',
  `RemetenteID` int(11) NOT NULL default '0',
  `GrupoID` int(11) unsigned NOT NULL default '0',
  `MensagemID` int(11) unsigned NOT NULL default '0',
  `UsuarioID` int(11) unsigned NOT NULL default '0',
  `Assunto` varchar(200) character set utf8 NOT NULL default '',
  `Data_Envio` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `Data_Agendamento` datetime NOT NULL default '0000-00-00 00:00:00',
  `Status` enum('Aguardando','Processado','Cancelado') character set utf8 NOT NULL default 'Aguardando',
  `Email_Notificacao` varchar(250) collate latin1_general_ci default NULL,
  `Total_Cadastros` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`EnvioID`),
  KEY `idx_customer` (`CustomerID`),
  KEY `idx_users` (`UsuarioID`),
  KEY `idx_mensagem` (`MensagemID`),
  KEY `idx_grupo` (`GrupoID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=755 ;

-- 
-- Extraindo dados da tabela `Agendamentos`
-- 

INSERT INTO `Agendamentos` VALUES (605, 75, 786, 786, 332, 76, 'ALVORADA ESTOFADOS: Tendências, tecnologias e conforto.', '2008-02-18 16:08:08', '2008-02-18 16:08:08', 'Processado', 'email@email.com', 238);
INSERT INTO `Agendamentos` VALUES (654, 75, 0, 800, 358, 76, 'FLORENSE NEWS - o informativo eletrônico da Florense!', '2008-02-22 16:06:01', '2008-02-22 16:02:04', 'Processado', 'email@email.com', 505);
INSERT INTO `Agendamentos` VALUES (655, 75, 0, 801, 360, 76, 'AOS ASSOCIADOS: Prospecção de Patrocínio para 2008', '2008-02-22 16:30:01', '2008-02-22 16:24:38', 'Processado', 'email@email.com', 69);
INSERT INTO `Agendamentos` VALUES (735, 75, 94, 0, 386, 76, 'BEATRIZ TELLES FERREIRA - Novidade: visite nosso mezanino!', '2008-02-29 16:02:45', '2008-02-29 16:55:01', 'Processado', 'email@email.com', 924);

-- query
SELECT SUM( Total_Cadastros )
FROM Agendamentos a
WHERE Data_Envio
BETWEEN '2008-02-01'
AND last_day( '2008-02-01' )
AND a.CustomerID = '75'

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

$mysql41
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 630 to server version: 4.1.24-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select cast(last_day( '2008-02-01' ) as datetime);
+--------------------------------------------+
| cast(last_day( '2008-02-01' ) as datetime) |
+--------------------------------------------+
| 2008-02-29 00:00:00                        |
+--------------------------------------------+
1 row in set (0.25 sec)

'2008-02-29 00:00:00' < '2008-02-29 16:02:45'

But why the query didnt work?
Did you try using the SQL schema I posted?

Yes, I run the query.

You have value '2008-02-29 16:02:45' in Data_Envio field and compare it with last_day( '2008-02-01'). Server does implicit conversion when compares DATE and TIMESTAMP. Result of this conversion I showed in previous comment. So '2008-02-01' is '2008-02-29 00:00:00' which is less than '2008-02-29 16:02:45'. As result '2008-02-29 16:02:45' can not be BETWEEN '2008-02-01' AND last_day( '2008-02-01').