MySQL Bugs: #40576: query that run forever, gives expect results adding some fields in select stat.

Bug #40576	query that run forever, gives expect results adding some fields in select stat.
Submitted:	7 Nov 2008 13:06	Modified:	9 Jan 2009 8:22
Reporter:	nicola tesser	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	Ver 14.12 Distrib 5.0.51a, for debian-li	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	SQL

Description:
I've a join between 3 table, and I query them selecting just the ids of them:

SELECT ,p.id as person_id,o.id as organization_id,r.id as relation_id
...

the query run forever.
If i add another field

SELECT p.firstname,p.id as person_id,o.id as organization_id,r.id as relation_id
....

the query behave normally.

How to repeat:
###table definitions

CREATE TABLE IF NOT EXISTS `people` (
  `id` int(11) NOT NULL auto_increment,
  `salutation` varchar(255) default NULL,
  `honorary_title` varchar(255) default NULL,
  `academic_degree` varchar(255) default NULL,
  `firstname` varchar(255) default NULL,
  `lastname` varchar(255) default NULL,
  `profession` varchar(255) default NULL,
  `remark` text,
  `street` varchar(255) default NULL,
  `city` varchar(255) default NULL,
  `postal_code` varchar(255) default NULL,
  `country` varchar(255) default NULL,
  `phone` varchar(255) default NULL,
  `mobile_phone` varchar(255) default NULL,
  `fax` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `website` varchar(255) default NULL,
  `created_by` int(11) default NULL,
  `updated_by` int(11) default NULL,
  `created_at` date default NULL,
  `updated_at` date default NULL,
  `date_of_birth` varchar(255) default NULL,
  `created_on` datetime default NULL,
  `updated_on` datetime default NULL,
  `director_id` int(11) default NULL,
  `no_address` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `person_salutation` (`salutation`),
  KEY `person_firstname` (`firstname`),
  KEY `person_lastname` (`lastname`),
  KEY `person_honorary_title` (`honorary_title`),
  KEY `person_academic_degree` (`academic_degree`),
  KEY `person_profession` (`profession`),
  KEY `person_street` (`street`),
  KEY `person_city` (`city`),
  KEY `person_postal_code` (`postal_code`),
  KEY `person_country` (`country`),
  KEY `person_created_by` (`created_by`),
  KEY `person_updated_by` (`updated_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4610 ;

CREATE TABLE IF NOT EXISTS `relations` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(255) default NULL,
  `start_date` varchar(255) default NULL,
  `end_date` varchar(255) default NULL,
  `comment` text,
  `source_url` varchar(255) default NULL,
  `source_content` text,
  `employee_id` int(11) default NULL,
  `employer_id` int(11) default NULL,
  `investment_id` int(11) default NULL,
  `investor_id` int(11) default NULL,
  `institutional_investor_id` int(11) default NULL,
  `share` double default NULL,
  `volume` double default NULL,
  `created_by` int(11) default NULL,
  `updated_by` int(11) default NULL,
  `created_at` date default NULL,
  `updated_at` date default NULL,
  `management_position_type_id` int(11) default NULL,
  `investment_type_id` int(11) default NULL,
  `is_founder` tinyint(1) default NULL,
  `fixed_income` double default NULL,
  `variable_income` double default NULL,
  `total_income` double default NULL,
  `event_id` int(11) default NULL,
  `previous_management_position_id` int(11) default NULL,
  `created_on` datetime default NULL,
  `updated_on` datetime default NULL,
  `validity` varchar(255) default NULL,
  `selling_status` varchar(255) default NULL,
  `end_share` float default NULL,
  `shares_number` int(11) default NULL,
  `shares_volume` float default NULL,
  `share_options_number` int(11) default NULL,
  `share_options_volume` float default NULL,
  `pension_provision` float default NULL,
  PRIMARY KEY  (`id`),
  KEY `relation_type` (`type`),
  KEY `relation_start_date` (`start_date`),
  KEY `relation_end_date` (`end_date`),
  KEY `relation_employee` (`employee_id`),
  KEY `relation_employer` (`employer_id`),
  KEY `relation_investment` (`investment_id`),
  KEY `relation_investor` (`investor_id`),
  KEY `relation_institutional_investor` (`institutional_investor_id`),
  KEY `relation_share` (`share`),
  KEY `relation_volume` (`volume`),
  KEY `relation_is_founder` (`is_founder`),
  KEY `relation_fixed_income` (`fixed_income`),
  KEY `relation_variable_income` (`variable_income`),
  KEY `relation_total_income` (`total_income`),
  KEY `relation_total_event` (`event_id`),
  KEY `relation_total_validity` (`validity`),
  KEY `relation_management_position_type` (`management_position_type_id`),
  KEY `relation_investment_type` (`investment_type_id`),
  KEY `relation_total_previous_management_position` (`previous_management_position_id`),
  KEY `relation_created_by` (`created_by`),
  KEY `relation_updated_by` (`updated_by`),
  KEY `event_id` (`event_id`),
  KEY `investment_id` (`investment_id`),
  KEY `investor_id` (`investor_id`),
  KEY `employee_id` (`employee_id`),
  KEY `employer_id` (`employer_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8399 ;

CREATE TABLE IF NOT EXISTS `organizations` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `remark` text,
  `street` varchar(255) default NULL,
  `city` varchar(255) default NULL,
  `postal_code` varchar(255) default NULL,
  `country` varchar(255) default NULL,
  `phone` varchar(255) default NULL,
  `fax` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `website` varchar(255) default NULL,
  `created_by` int(11) default NULL,
  `updated_by` int(11) default NULL,
  `created_at` date default NULL,
  `updated_at` date default NULL,
  `legal_form_type_id` int(11) default NULL,
  `industry_type_id` int(11) default NULL,
  `validity` varchar(255) default NULL,
  `director_id` int(11) default NULL,
  `trade_register_number` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `organization_idx` (`name`),
  KEY `organization_validity` (`validity`),
  KEY `organization_street` (`street`),
  KEY `organization_city` (`city`),
  KEY `organization_postal_code` (`postal_code`),
  KEY `organization_country` (`country`),
  KEY `organization_legal_form_type` (`legal_form_type_id`),
  KEY `organization_industry_type` (`industry_type_id`),
  KEY `organization_created_by` (`created_by`),
  KEY `organization_updated_by` (`updated_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4307 ;

####bad query
SELECT p.id as person_id,o.id as organization_id,r.id as relation_id

FROM people p,
     organizations o, 
	 	 relations r

WHERE
((r.investment_id = o.id AND r.investor_id = p.id) 
OR 
(r.employer_id = o.id AND r.employee_id = p.id))
AND r.id=6200

#####good query
SELECT firstname,p.id as person_id,o.id as organization_id,r.id as relation_id

FROM people p,
     organizations o, 
	 	 relations r

WHERE
((r.investment_id = o.id AND r.investor_id = p.id) 
OR 
(r.employer_id = o.id AND r.employee_id = p.id))
AND r.id=6200

Thank you for a problem report. Please, send the results of EXPLAIN for both "good" and "bad" queries.

####bad query
SELECT p.id as person_id,o.id as organization_id,r.id as relation_id

FROM people p,
    organizations o,
                relations r

WHERE
((r.investment_id = o.id AND r.investor_id = p.id)
OR
(r.employer_id = o.id AND r.employee_id = p.id))

#####explain:
+----+-------------+-------+-------+-----------------------------------------------------------------------------------------------------------------------------+---------+---------+------+------+------------------------------------------------------+
| id | select_type | table | type  | possible_keys                                                                                                               | key     | key_len | ref  | rows | Extra                                                |
+----+-------------+-------+-------+-----------------------------------------------------------------------------------------------------------------------------+---------+---------+------+------+------------------------------------------------------+
|  1 | SIMPLE      | o     | index | PRIMARY                                                                                                                     | PRIMARY | 4       | NULL | 3983 | Using index                                          | 
|  1 | SIMPLE      | p     | index | PRIMARY                                                                                                                     | PRIMARY | 4       | NULL | 4492 | Using index                                          | 
|  1 | SIMPLE      | r     | ALL   | relation_employee,relation_employer,relation_investment,relation_investor,investment_id,investor_id,employee_id,employer_id | NULL    | NULL    | NULL | 7308 | Range checked for each record (index map: 0x78000F0) | 
+----+-------------+-------+-------+-----------------------------------------------------------------------------------------------------------------------------+---------+---------+------+------+------------------------------------------------------+

#####good query
SELECT firstname,p.id as person_id,o.id as organization_id,r.id as
relation_id

FROM people p,
    organizations o,
                relations r

WHERE
((r.investment_id = o.id AND r.investor_id = p.id)
OR
(r.employer_id = o.id AND r.employee_id = p.id))

#####explain:
+----+-------------+-------+-------+-----------------------------------------------------------------+---------+---------+------+------+------------------------------------------------------+
| id | select_type | table | type  | possible_keys                                                   | key     | key_len | ref  | rows | Extra                                                |
+----+-------------+-------+-------+-----------------------------------------------------------------+---------+---------+------+------+------------------------------------------------------+
|  1 | SIMPLE      | o     | index | PRIMARY                                                         | PRIMARY | 4       | NULL | 3983 | Using index                                          | 
|  1 | SIMPLE      | r     | ALL   | relation_employer,relation_investment,investment_id,employer_id | NULL    | NULL    | NULL | 7308 | Range checked for each record (index map: 0x4800060) | 
|  1 | SIMPLE      | p     | ALL   | PRIMARY                                                         | NULL    | NULL    | NULL | 4492 | Range checked for each record (index map: 0x1)       | 
+----+-------------+-------+-------+-----------------------------------------------------------------+---------+---------+------+------+------------------------------------------------------+

Please, try to repeat with a newer version, 5.0.67. In case of the same problem, please, upload a compressed dump of tables used, if possible.

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".