Bug #4355 Strange result on LEFT JOIN

• Submitted: 1 Jul 2004 3:22
• Modified: 17 Jul 2004 15:35
• Reporter: Quentin Ochem
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.2-alpha
• OS: Windows (windows 2000)
• CPU Architecture: Any

[1 Jul 2004 3:22] Quentin Ochem

Description:
I did a request where the right part of the LEFT JOIN should return no result. Then, if I count results on objects that are selected in the LEFT JOIN, I strangely found one.

The request is

SELECT COUNT (b.Key) FROM a LEFT JOIN b, c ON a.B_Key = b.Key AND b.C_Key = c.Key AND c.Val = 77 GROUP BY c.Key

and the result is 1, but there is no c.Val that is equal to 77 in my table !!!

How to repeat:
Do this request :

SELECT COUNT (b.Key) FROM a LEFT JOIN b, c ON a.B_Key = b.Key AND b.C_Key = c.Key AND c.Val = 77 GROUP BY c.Key

on the database constructed below :

CREATE TABLE `a` (
  `Key` int(11) NOT NULL default '0',
  `Val` int(11) NOT NULL default '0',
  `B_Key` int(11) NOT NULL default '0',
  PRIMARY KEY  (`Key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Contenu de la table `a`
#

INSERT INTO `a` VALUES (1, 89, 1);

# --------------------------------------------------------

#
# Structure de la table `b`
#

CREATE TABLE `b` (
  `Key` int(11) NOT NULL default '0',
  `Val` int(11) NOT NULL default '0',
  `C_Key` int(11) NOT NULL default '0',
  PRIMARY KEY  (`Key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Contenu de la table `b`
#

INSERT INTO `b` VALUES (1, 12, 1);

# --------------------------------------------------------

#
# Structure de la table `c`
#

CREATE TABLE `c` (
  `Key` int(11) NOT NULL default '0',
  `Val` int(11) NOT NULL default '0',
  PRIMARY KEY  (`Key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Contenu de la table `c`
#

INSERT INTO `c` VALUES (1, 25);

[14 Jul 2004 12:09] Jochen Riehm

We experience the same bug on a Linux System with MySQL 3.23.57. In addition we have the 
following effect when counting NULL values of two different fields of a LEFT JOIN: 
 
Using count(distinct name) on both returns 0 for both fields 
Using count(name) on both also returns 0 for both fields 
Using count(name) for one and count(distinct name) for the other returns 1 for the first and 0 
for the second field. 
 
The result was just one row with NULL for both fields (also only 1 row before grouping), so 0 
for both fields would be correct.

[14 Jul 2004 12:36] Jochen Riehm

Further research led to the following result: 
 
problem occured when the index used on the table with count(distinct name) had as 
cardinality NULL. Then the explain said: 
 
+-------+-------+---------------+------------+---------+------+------+------------------------------------------+ 
| table | type  | possible_keys | key        | key_len | ref  | rows | Extra                                    
| 
+-------+-------+---------------+------------+---------+------+------+------------------------------------------+ 
| b     | range | PRIMARY       | PRIMARY    |       4 | NULL |    1 | where used; Using index; 
Using temporary | 
| ovb1  | index | id1           | id1        |       4 | NULL |    1 | Using index                              
| 
| msg   | ref   | ******_id    | *****_id |       5 | b.id |   53 |                                          
| 
+-------+-------+---------------+------------+---------+------+------+------------------------------------------+ 
 
(Table ovb whas the one with incorrect index) 
 
and the problem occured. After an optimize table the Index cardinality was set to 1 and the 
explain read: 
 
+-------+-------+---------------+------------+---------+------+------+-------------------------+ 
| table | type  | possible_keys | key        | key_len | ref  | rows | Extra                   | 
+-------+-------+---------------+------------+---------+------+------+-------------------------+ 
| b     | range | PRIMARY       | PRIMARY    |       4 | NULL |    1 | where used; Using index | 
| ovb1  | ref   | id1           | id1        |       4 | b.id |    1 | Using index             | 
| msg   | ref   | *******_id    | *******_id |       5 | b.id |   53 |                         | 
+-------+-------+---------------+------------+---------+------+------+-------------------------+ 
 
and the problem did not occure anymore.

[17 Jul 2004 15:35] MySQL Verification Team

I wasn't able to repeat with version 4.1.3.

[21 Jul 2004 8:11] Quentin Ochem

Did you try my request (the first reported) ? I updated mysql to 4.1.3 beta and I still get one result where I should find nothing...