Bug #1444 Query/Subquery not returning expected results
Submitted: 30 Sep 2003 9:36 Modified: 23 Oct 2003 12:51
Reporter: Matthew Yonkovit Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.0 OS:Linux (Red Hat 7.3, 2.4.18-3smp)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[30 Sep 2003 9:36] Matthew Yonkovit 
Description:
the following query:
SELECT dbid, name
FROM taks_generator a, database_list b, task_list
WHERE dbid = - 1
AND primary_uid = '1'
 AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY)
>= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d')
        FROM daily_task_list
        WHERE dbid = b.db_id
        AND taskid = a.taskgenid)
        , '1950-01-01'))
AND task_list.task_id = taskid;

should returns:
+------+-------------------+
| dbid | name              |
+------+-------------------+
|   -1 | Valid             |
|   -1 | Valid 2           |
+------+-------------------+

instead it returns:

Empty set (0.00 sec)

But what is really odd is 1 out of every 20 times or so it will return:
+------+-------------------+
| dbid | name              |
+------+-------------------+
|   -1 | Valid             |
|   -1 | Valid 2           |
|   -1 | Should Not Return |
+------+-------------------+

I do not really know why.

How to repeat:
Here are the tables to run this query:

DROP TABLE IF EXISTS `daily_task_list`;
CREATE TABLE `daily_task_list` (
  `id` mediumint(9) NOT NULL auto_increment,
  `taskid` bigint(20) NOT NULL default '0',
  `dbid` int(11) NOT NULL default '0',
  `create_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_update` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `daily_task_list` (`id`, `taskid`, `dbid`, `create_date`, `last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36');
INSERT INTO `daily_task_list` (`id`, `taskid`, `dbid`, `create_date`, `last_update`) VALUES (2, 1, 21, now(), now());

DROP TABLE IF EXISTS `database_list`;
CREATE TABLE `database_list` (
  `db_id` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL default '',
  `primary_uid` smallint(6) NOT NULL default '0',
  `secondary_uid` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`db_id`),
  UNIQUE KEY `name_2` (`name`),
  FULLTEXT KEY `name` (`name`)
) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647 ;

INSERT INTO `database_list` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0);
INSERT INTO `database_list` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (19, 'Valid', 1, 2);
INSERT INTO `database_list` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (20, 'Valid 2', 1, 2);
INSERT INTO `database_list` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (21, 'Should Not Return', 1, 2);
INSERT INTO `database_list` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (26, 'Not Set 2', 0, 0);
INSERT INTO `database_list` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (-1, 'ALL DB\'S', 0, 0);

DROP TABLE IF EXISTS `taks_generator`;
CREATE TABLE `taks_generator` (
  `taskgenid` mediumint(9) NOT NULL auto_increment,
  `dbid` int(11) NOT NULL default '0',
  `taskid` int(11) NOT NULL default '0',
  `mon` tinyint(4) NOT NULL default '1',
  `tues` tinyint(4) NOT NULL default '1',
  `wed` tinyint(4) NOT NULL default '1',
  `thur` tinyint(4) NOT NULL default '1',
  `fri` tinyint(4) NOT NULL default '1',
  `sat` tinyint(4) NOT NULL default '0',
  `sun` tinyint(4) NOT NULL default '0',
  `how_often` smallint(6) NOT NULL default '1',
  `userid` smallint(6) NOT NULL default '0',
  `active` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`taskgenid`)
) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `taks_generator` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`, `wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1, -1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);

DROP TABLE IF EXISTS `task_list`;
CREATE TABLE `task_list` (
  `task_id` smallint(6) NOT NULL default '0',
  `description` varchar(200) NOT NULL default ''
) TYPE=MyISAM CHARSET=latin1;

INSERT INTO `task_list` (`task_id`, `description`) VALUES (1, 'Daily Check List');
INSERT INTO `task_list` (`task_id`, `description`) VALUES (2, 'Weekly Status');
[30 Sep 2003 11:41] Indrek Siitan 
On both of my test systems, this query produces the "wrong" results (including
"Should Not Return") on the first run, and then no results on all following runs.

mysql> SELECT dbid, name FROM taks_generator a, database_list b, task_list WHERE
 dbid = - 1 AND primary_uid = '1'  AND ((date_format(now() , '%Y-%m-%d') - INTER
VAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d')  F
ROM daily_task_list  WHERE dbid = b.db_id  AND taskid = a.taskgenid) , '1950-01-
01')) AND task_list.task_id = taskid;
+------+-------------------+
| dbid | name              |
+------+-------------------+
|   -1 | Valid             |
|   -1 | Valid 2           |
|   -1 | Should Not Return |
+------+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT dbid, name FROM taks_generator a, database_list b, task_list WHERE dbid = - 1 
AND primary_uid = '1'  AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= 
ifnull((SELECT date_format(max(create_date),'%Y-%m-%d')  FROM daily_task_list  WHERE dbid = 
b.db_id  AND taskid = a.taskgenid) , '1950-01-01')) AND task_list.task_id = taskid; 
Empty set (0.00 sec)
[16 Oct 2003 14:37] Oleksandr Byelkin 
ChangeSet 
  1.1609 03/10/17 00:36:01 bell@sanja.is.com.ua +5 -0 
  fixed support of used_tables() and const_item() in subqueries 
  (BUG#1444)
[23 Oct 2003 12:51] Oleksandr Byelkin 
Thank You for bug report. 
 
Fix for this bug is pushed in source repository of mysql 4.1 and will be 
present in next relese.