Bug #15839 MySQL is incorrectly using numeric and string comparision with datetime?
Submitted: 18 Dec 2005 23:40 Modified: 19 Jan 2006 9:20
Reporter: [ name withheld ] Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.24_Debian-10sarge1-log,4.0.21-max-lo OS:Linux (Linux,Windows XP)
Assigned to: CPU Architecture:Any

[18 Dec 2005 23:40] [ name withheld ] 
Description:
Following SQL statement incorectly doesn't select any row when issued from 20th to 59th second in every minute (tested on 18th december 2005 GMT +1).

SELECT *  FROM `table_datetime` WHERE column_datetime<(NOW()-60);

Notes:
* Results are correct under MySQL 4.1.13a
* Results are correct under MySQL 4.0.x without part "-60":

Workaround for 4.0.x - use:
SELECT *  FROM `table_datetime` WHERE (column_datetime+0)<(NOW()-60);

How to repeat:
CREATE TABLE `table_datetime` (
  `column_datetime` datetime default NULL
) TYPE=MyISAM;

INSERT INTO `table_datetime` ( `column_datetime` ) VALUES ('2005-12-04 20:23:53');
#OR INSERT INTO `table_datetime` ( `column_datetime` ) VALUES ('2005-12-18 19:56:47');

Try every second:
SELECT *  FROM `table_datetime` WHERE column_datetime<(NOW()-60);

You can use this PHP test script:
<?php
/*
CREATE TABLE `table_datetime_result` (
  `table_datetime_result_id` int NOT NULL AUTO_INCREMENT,
  `now_datetime` datetime default NULL,
  `num_rows` smallint default NULL,
  PRIMARY KEY ( `table_datetime_result_id` )
) TYPE=MyISAM;
*/
$server='xxx';
$user='xxx';
$password='xxx';
$database='xxx';
$q_select ='SELECT NOW(), NOW()-60, `table_datetime`.*  FROM `table_datetime` ';
$q_select.=' WHERE column_datetime<(NOW()-60)';

$q_result ='INSERT INTO `table_datetime_result` (`now_datetime`, `num_rows`) VALUES (NOW(), ';

mysql_pconnect($server, $user, $password);
mysql_select_db($database);

set_time_limit(150);
$incorrect_count=0;
for($i=1; $i<120; ++$i) {
	$q_select_id=mysql_query($q_select);
	$num_row=mysql_num_rows($q_select_id);
	$q_insert=$q_result.$num_row.')';
	mysql_query($q_insert);
	if(1!==$num_row) {
		$incorrect_count++;
		echo '#INCORRECT:<br>'."\r\n";
	}
	echo $q_insert.'<br>'."\r\n";
	sleep(1);
}
echo '<h1>Finished - incorrect count: '.$incorrect_count.'</h1>';
?>

Data generated by above example PHP script (zero in num_rows means error):
INSERT INTO `table_datetime_result` (`table_datetime_result_id`, `now_datetime`, `num_rows`) VALUES 
(1, '2005-12-19 00:15:31', 0),
(2, '2005-12-19 00:15:33', 0),
(3, '2005-12-19 00:15:34', 0),
(4, '2005-12-19 00:15:35', 0),
(5, '2005-12-19 00:15:36', 0),
(6, '2005-12-19 00:15:37', 0),
(7, '2005-12-19 00:15:38', 0),
(8, '2005-12-19 00:15:39', 0),
(9, '2005-12-19 00:15:40', 0),
(10, '2005-12-19 00:15:41', 0),
(11, '2005-12-19 00:15:42', 0),
(12, '2005-12-19 00:15:43', 0),
(13, '2005-12-19 00:15:44', 0),
(14, '2005-12-19 00:15:45', 0),
(15, '2005-12-19 00:15:46', 0),
(16, '2005-12-19 00:15:47', 0),
(17, '2005-12-19 00:15:48', 0),
(18, '2005-12-19 00:15:49', 0),
(19, '2005-12-19 00:15:50', 0),
(20, '2005-12-19 00:15:51', 0),
(21, '2005-12-19 00:15:52', 0),
(22, '2005-12-19 00:15:53', 0),
(23, '2005-12-19 00:15:54', 0),
(24, '2005-12-19 00:15:55', 0),
(25, '2005-12-19 00:15:56', 0),
(26, '2005-12-19 00:15:57', 0),
(27, '2005-12-19 00:15:58', 0),
(28, '2005-12-19 00:15:59', 0),
(29, '2005-12-19 00:16:00', 1),
(30, '2005-12-19 00:16:01', 1),
(31, '2005-12-19 00:16:02', 1),
(32, '2005-12-19 00:16:03', 1),
(33, '2005-12-19 00:16:04', 1),
(34, '2005-12-19 00:16:05', 1),
(35, '2005-12-19 00:16:06', 1),
(36, '2005-12-19 00:16:07', 1),
(37, '2005-12-19 00:16:08', 1),
(38, '2005-12-19 00:16:09', 1),
(39, '2005-12-19 00:16:10', 1),
(40, '2005-12-19 00:16:11', 1),
(41, '2005-12-19 00:16:12', 1),
(42, '2005-12-19 00:16:13', 1),
(43, '2005-12-19 00:16:14', 1),
(44, '2005-12-19 00:16:15', 1),
(45, '2005-12-19 00:16:16', 1),
(46, '2005-12-19 00:16:17', 1),
(47, '2005-12-19 00:16:18', 1),
(48, '2005-12-19 00:16:19', 1),
(49, '2005-12-19 00:16:20', 0),
(50, '2005-12-19 00:16:21', 0),
(51, '2005-12-19 00:16:22', 0),
(52, '2005-12-19 00:16:23', 0),
(53, '2005-12-19 00:16:24', 0),
(54, '2005-12-19 00:16:25', 0),
(55, '2005-12-19 00:16:26', 0),
(56, '2005-12-19 00:16:27', 0),
(57, '2005-12-19 00:16:28', 0),
(58, '2005-12-19 00:16:29', 0),
(59, '2005-12-19 00:16:30', 0),
(60, '2005-12-19 00:16:31', 0),
(61, '2005-12-19 00:16:32', 0),
(62, '2005-12-19 00:16:33', 0),
(63, '2005-12-19 00:16:34', 0),
(64, '2005-12-19 00:16:35', 0),
(65, '2005-12-19 00:16:36', 0),
(66, '2005-12-19 00:16:37', 0),
(67, '2005-12-19 00:16:38', 0),
(68, '2005-12-19 00:16:39', 0),
(69, '2005-12-19 00:16:40', 0),
(70, '2005-12-19 00:16:41', 0),
(71, '2005-12-19 00:16:42', 0),
(72, '2005-12-19 00:16:43', 0),
(73, '2005-12-19 00:16:44', 0),
(74, '2005-12-19 00:20:28', 0),
(75, '2005-12-19 00:20:29', 0),
(76, '2005-12-19 00:20:30', 0),
(77, '2005-12-19 00:20:31', 0),
(78, '2005-12-19 00:20:32', 0),
(79, '2005-12-19 00:20:33', 0),
(80, '2005-12-19 00:20:34', 0),
(81, '2005-12-19 00:20:35', 0),
(82, '2005-12-19 00:20:36', 0),
(83, '2005-12-19 00:20:37', 0),
(84, '2005-12-19 00:20:38', 0),
(85, '2005-12-19 00:20:39', 0),
(86, '2005-12-19 00:20:40', 0),
(87, '2005-12-19 00:20:41', 0),
(88, '2005-12-19 00:20:42', 0),
(89, '2005-12-19 00:20:43', 0),
(90, '2005-12-19 00:20:44', 0),
(91, '2005-12-19 00:20:45', 0),
(92, '2005-12-19 00:20:46', 0),
(93, '2005-12-19 00:20:47', 0),
(94, '2005-12-19 00:20:48', 0),
(95, '2005-12-19 00:20:49', 0),
(96, '2005-12-19 00:20:50', 0),
(97, '2005-12-19 00:20:51', 0),
(98, '2005-12-19 00:20:52', 0),
(99, '2005-12-19 00:20:53', 0),
(100, '2005-12-19 00:20:54', 0),
(101, '2005-12-19 00:20:55', 0),
(102, '2005-12-19 00:20:56', 0),
(103, '2005-12-19 00:20:57', 0),
(104, '2005-12-19 00:20:58', 0),
(105, '2005-12-19 00:20:59', 0),
(106, '2005-12-19 00:21:00', 1),
(107, '2005-12-19 00:21:01', 1),
(108, '2005-12-19 00:21:02', 1),
(109, '2005-12-19 00:21:03', 1),
(110, '2005-12-19 00:21:04', 1),
(111, '2005-12-19 00:21:06', 1),
(112, '2005-12-19 00:21:07', 1),
(113, '2005-12-19 00:21:08', 1),
(114, '2005-12-19 00:21:09', 1),
(115, '2005-12-19 00:21:10', 1),
(116, '2005-12-19 00:21:11', 1),
(117, '2005-12-19 00:21:12', 1),
(118, '2005-12-19 00:21:13', 1),
(119, '2005-12-19 00:21:14', 1),
(120, '2005-12-19 00:21:15', 1),
(121, '2005-12-19 00:21:16', 1),
(122, '2005-12-19 00:21:17', 1),
(123, '2005-12-19 00:21:18', 1),
(124, '2005-12-19 00:21:19', 1),
(125, '2005-12-19 00:21:20', 0),
(126, '2005-12-19 00:21:21', 0),
(127, '2005-12-19 00:21:22', 0),
(128, '2005-12-19 00:21:23', 0),
(129, '2005-12-19 00:21:24', 0),
(130, '2005-12-19 00:21:25', 0),
(131, '2005-12-19 00:21:26', 0),
(132, '2005-12-19 00:21:27', 0),
(133, '2005-12-19 00:21:28', 0),
(134, '2005-12-19 00:21:29', 0),
(135, '2005-12-19 00:21:30', 0),
(136, '2005-12-19 00:21:31', 0),
(137, '2005-12-19 00:21:32', 0),
(138, '2005-12-19 00:21:33', 0),
(139, '2005-12-19 00:21:34', 0),
(140, '2005-12-19 00:21:35', 0),
(141, '2005-12-19 00:21:36', 0),
(142, '2005-12-19 00:21:37', 0),
(143, '2005-12-19 00:21:38', 0),
(144, '2005-12-19 00:21:39', 0),
(145, '2005-12-19 00:21:40', 0),
(146, '2005-12-19 00:21:41', 0),
(147, '2005-12-19 00:21:42', 0),
(148, '2005-12-19 00:21:43', 0),
(149, '2005-12-19 00:21:44', 0);

Suggested fix:
Consider always both:
* column_datetime and 
* NOW() +/- 123
as integers

Ideally as I suggested in Bug #15837: Convert datetime to integer as number of seconds from "zero time"
[19 Dec 2005 9:20] Valeriy Kravchuk 
Thank you for a problem report. It can be a 4.0-specific problem only. Works OK on 5.0.18-BK on Linux. The results of similar PHP script (yours has some problems like incorrect table name in $q_select...) are:

...
|                      113 | 2005-12-19 12:07:44 |        1 |
|                      114 | 2005-12-19 12:07:45 |        1 |
|                      115 | 2005-12-19 12:07:46 |        1 |
|                      116 | 2005-12-19 12:07:47 |        1 |
|                      117 | 2005-12-19 12:07:48 |        1 |
|                      118 | 2005-12-19 12:07:49 |        1 |
|                      119 | 2005-12-19 12:07:50 |        1 |
|                      120 | 2005-12-19 12:07:51 |        1 |
|                      121 | 2005-12-19 12:07:52 |        1 |
|                      122 | 2005-12-19 12:07:53 |        1 |
|                      123 | 2005-12-19 12:07:54 |        1 |
|                      124 | 2005-12-19 12:07:55 |        1 |
|                      125 | 2005-12-19 12:07:56 |        1 |
|                      126 | 2005-12-19 12:07:57 |        1 |
...

Can you, please, try to install the latest 4.0 version, 4.0.26, and run you test script on it? Inform about the results.
[20 Jan 2006 0:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".