MySQL Bugs: #969: combination of subselects, joins and auto

Bug #969	combination of subselects, joins and auto_increments gives an incorrect result
Submitted:	31 Jul 2003 12:21	Modified:	8 Aug 2003 13:42
Reporter:	Travis Basevi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.0-alpha	OS:	Linux (Linux)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:

When a table is joined with a subselect and that table has an auto_increment column in the result, you can get significantly incorrect results.

How to repeat:
create table mat (
 mat_id          MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 matintnum       CHAR(6)            NOT NULL,
 test            MEDIUMINT UNSIGNED NULL);

create table mat_pla (
 mat_id          MEDIUMINT UNSIGNED NOT NULL,
 pla_id          MEDIUMINT UNSIGNED NOT NULL);

insert into mat values
(NULL, 'a', 1),
(NULL, 'b', 2),
(NULL, 'c', 3),
(NULL, 'd', 4),
(NULL, 'e', 5),
(NULL, 'f', 6),
(NULL, 'g', 7),
(NULL, 'h', 8),
(NULL, 'i', 9);

insert into mat_pla values
(1, 100),
(1, 101),
(1, 102),
(2, 100),
(2, 103),
(2, 104),
(3, 101),
(3, 102),
(3, 105);

SELECT
 d.pla_id,
 m2.mat_id
FROM mat m2
 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum
             FROM mat_pla mp
              INNER JOIN mat m1 ON mp.mat_id=m1.mat_id
             GROUP BY mp.pla_id) d
  ON d.matintnum=m2.matintnum;

# This returns plain nonsense (we want the mat_id of the lowest
# matintnum for each pla_id):

+--------+--------+
| pla_id | mat_id |
+--------+--------+
|    105 |      1 |
|    105 |      2 |
|    105 |      3 |
|    105 |      4 |
|    105 |      5 |
|    105 |      6 |
|    105 |      7 |
|    105 |      8 |
|    105 |      9 |
+--------+--------+

# Note that mat_id and test are the same values in mat:

+--------+-----------+------+
| mat_id | matintnum | test |
+--------+-----------+------+
|      1 | a         |    1 |
|      2 | b         |    2 |
|      3 | c         |    3 |
|      4 | d         |    4 |
|      5 | e         |    5 |
|      6 | f         |    6 |
|      7 | g         |    7 |
|      8 | h         |    8 |
|      9 | i         |    9 |
+--------+-----------+------+

# So we change m2.mat_id to m2.test in the SELECT statement:

SELECT
 d.pla_id,
 m2.test
FROM mat m2
 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum
             FROM mat_pla mp
              INNER JOIN mat m1 ON mp.mat_id=m1.mat_id
             GROUP BY mp.pla_id) d
  ON d.matintnum=m2.matintnum;

# And we get a correct result:

+--------+------+
| pla_id | test |
+--------+------+
|    100 |    1 |
|    101 |    1 |
|    102 |    1 |
|    103 |    2 |
|    104 |    2 |
|    105 |    3 |
+--------+------+

AUTO_INCREMENT is unrelated. What makes difference is presence of Primary Key. Without using PK both queries return same result.

The difference is:

EXPLAIN
SELECT
  d.pla_id,
  m2.mat_id
 FROM mat m2
  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum
              FROM mat_pla mp
                INNER JOIN mat m1 ON mp.mat_id=m1.mat_id
              GROUP BY mp.pla_id) d
     ON d.matintnum=m2.matintnum;

+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                           |
+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | m2         | index | NULL          | PRIMARY |       3 | NULL |    9 | Using index                     |
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    |    NULL | NULL |    6 | Using where                     |
|  2 | DERIVED     | mp         | ALL   | NULL          | NULL    |    NULL | NULL |    9 | Using temporary; Using filesort |
|  2 | DERIVED     | m2         | index | NULL          | PRIMARY |       3 | NULL |    9 | Using index                     |
+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------+
4 rows in set (0.01 sec)
 
EXPLAIN
SELECT
    d.pla_id,
    m2.test
    FROM mat m2
    INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum
                FROM mat_pla mp
                 INNER JOIN mat m1 ON mp.mat_id=m1.mat_id
                GROUP BY mp.pla_id) d
     ON d.matintnum=m2.matintnum;
+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                           |
+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | m2         | ALL   | NULL          | NULL    |    NULL | NULL |    9 |                                 |
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    |    NULL | NULL |    6 | Using where                     |
|  2 | DERIVED     | mp         | ALL   | NULL          | NULL    |    NULL | NULL |    9 | Using temporary; Using filesort |
|  2 | DERIVED     | m2         | index | NULL          | PRIMARY |       3 | NULL |    9 | Using index                     |
+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------+

SELECT
  d.pla_id,
  m2.mat_id
  FROM mat m2 IGNORE KEY(Primary)
  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum
               FROM mat_pla mp
                INNER JOIN mat m1 ON mp.mat_id=m1.mat_id
               GROUP BY mp.pla_id) d
    ON d.matintnum=m2.matintnum;
+--------+--------+
| pla_id | mat_id |
+--------+--------+
|    100 |      1 |
|    101 |      1 |
|    102 |      1 |
|    103 |      2 |
|    104 |      2 |
|    105 |      3 |
+--------+--------+

MySql on Windows XP crashes on second query.

Thank you for good bugreport! 
 
This bug is already fixed by Sinisa: 
ChangeSet 1.1538.20.1 2003/05/31 18:31:57 Sinisa@sinisa.nasamreza.org 
  Fix for the optimiser problem caused by the fact that with derived 
  tables one (or more tables) is opened / closed twice. 
 
this fix will be included in next 4.1 release