MySQL Bugs: #26963: Incorrect query results with CONST join compared to RANGE or ALL

Bug #26963	Incorrect query results with CONST join compared to RANGE or ALL
Submitted:	8 Mar 2007 16:49	Modified:	21 Mar 2007 18:19
Reporter:	Harrison Fisk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.36	OS:	MacOS (Mac OS X, Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	const, incorrect result, regression

Description:
The query given in the How to repeat section returns an incorrect result set.  Rows are being omitted when they should be included, leading to an empty set, when there should be 1 row.

It appears to be related to the CONST join type, as if you change the query (or indexes) slightly to get a RANGE or ALL, then it no longer returns the incorrect amount of rows.

The query returns the correct amount of rows on MySQL 4.1, so it appears to be a REGRESSION BUG.

How to repeat:
Create tables with the following definition and data:

DROP TABLE IF EXISTS faq_access;
CREATE TABLE `faq_access` (
`access_id` smallint(6) NOT NULL default '0',
`name` varchar(20) character set latin1 collate latin1_bin default NULL,
`rank` smallint(6) NOT NULL default '0',
KEY `faq_access$access_id` (`access_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS faqs;
CREATE TABLE `faqs` (
`faq_group_id` int(11) NOT NULL default '0',
`faq_id` int(11) NOT NULL default '0',
`access_id` smallint(6) default NULL,
UNIQUE KEY `faqs$faq_id` (`faq_id`),
KEY `faqs$group_id$faq_id` (`faq_group_id`,`faq_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `faq_access` VALUES (1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
INSERT INTO `faqs` VALUES (261,265,1),(490,494,1);

-- issue the following query which returns 2 rows

SELECT faqs.faq_id FROM faq_access f_acc INNER JOIN faqs ON (f_acc.access_id = faqs.access_id) LEFT JOIN faqs f2 on (f2.faq_group_id = faqs.faq_group_id AND find_in_set(f2.access_id, '1,4') < find_in_set(faqs.access_id, '1,4')) WHERE faqs.access_id IN (1,4) AND f2.access_id IS NULL AND faqs.faq_id in (265,494);

-- issue the following query which returns 2 rows

SELECT faqs.faq_id FROM faq_access f_acc INNER JOIN faqs ON (f_acc.access_id = faqs.access_id) LEFT JOIN faqs f2 on (f2.faq_group_id = faqs.faq_group_id AND find_in_set(f2.access_id, '1,4') < find_in_set(faqs.access_id, '1,4')) WHERE faqs.access_id IN (1,4) AND f2.access_id IS NULL AND faqs.faq_id in (265,494);

-- issue the following query which returns 0 rows, but should return 1, works in 4.1.22

SELECT faqs.faq_id FROM faq_access f_acc INNER JOIN faqs ON (f_acc.access_id = faqs.access_id) LEFT JOIN faqs f2 on (f2.faq_group_id = faqs.faq_group_id AND find_in_set(f2.access_id, '1,4') < find_in_set(faqs.access_id, '1,4')) WHERE faqs.access_id IN (1,4) AND f2.access_id IS NULL AND faqs.faq_id in (265);

-- if you ignore the unique index, then you get the 1 row returned

SELECT faqs.faq_id FROM faq_access f_acc INNER JOIN faqs IGNORE INDEX (faqs$faq_id) ON (f_acc.access_id = faqs.access_id) LEFT JOIN faqs f2 on (f2.faq_group_id = faqs.faq_group_id AND find_in_set(f2.access_id, '1,4') < find_in_set(faqs.access_id, '1,4')) WHERE faqs.access_id IN (1,4) AND f2.access_id IS NULL AND faqs.faq_id in (265);

Suggested fix:
Make it work properly.

The following EXPLAIN EXTENDED command reveals the source of the problem:

mysql> EXPLAIN EXTENDED
    -> SELECT faqs.faq_id FROM faq_access f_acc INNER JOIN faqs ON (f_acc.access_id =
    -> faqs.access_id) LEFT JOIN faqs f2 on (f2.faq_group_id = faqs.faq_group_id AND
    -> find_in_set(f2.access_id, '1,4') < find_in_set(faqs.access_id, '1,4')) WHERE
    -> faqs.access_id IN (1,4) AND f2.access_id IS NULL AND faqs.faq_id in (265) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: faqs
         type: const
possible_keys: faqs$faq_id
          key: faqs$faq_id
      key_len: 4
          ref: const
         rows: 1
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: f2
         type: ref
possible_keys: faqs$group_id$faq_id
          key: faqs$group_id$faq_id
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: f_acc
         type: ref
possible_keys: faq_access$access_id
          key: faq_access$access_id
      key_len: 2
          ref: const
         rows: 1
        Extra: Using where; Using index
3 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select '265' AS `faq_id` from `test`.`faq_access` `f_acc` join `test`.`faqs` left join `test`.`faqs` `f2` on(((`test`.`f2`.`faq_group_id` = '261') and (find_in_set(`test`.`f2`.`access_id`,_latin1'1,4') < find_in_set(`test`.`f_acc`.`access_id`,_latin1'1,4')))) where ((`test`.`f_acc`.`access_id` = '1') and (`test`.`f_acc`.`access_id` in (1,4)) and isnull(`test`.`f2`.`access_id`))
1 row in set (0.01 sec)

We can see that in the predicate find_in_set(faqs.access_id, '1,4')
the field faqs.access_id was substituted for the test.f_acc.access_id.
This is not correct. It has be substituted for a constant after constant
table substitution.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21697

ChangeSet@1.2475, 2007-03-11 23:34:40-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #26963: invalid optimization of the pushdown conditions
  after single-row table substitution could lead to a wrong result set.
  The bug happened because the function Item_field::replace_equal_field
  erroniously assumed that any field included in a multiple equality
  with a constant has been already substituted for this constant.
  This not true for fields becoming constant after row substitutions
  for constant tables.

Pushed in 5.0.40, 5.1.17

Noted in 5.0.40, 5.1.17 changelogs.

Invalid optimization of pushdown conditions for queries where an
outer join was guaranteed to read only one row from the outer table
led to results with too few rows.