Bug #11906 Using a variable instead of a constant gives incorrect results in a query join
Submitted: 13 Jul 2005 9:52 Modified: 13 Jul 2005 17:39
Reporter: Are you mortal Then prepare to die. Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.11-standard-log OS:4.1.11-dec-osf5.1b-alphaev67
Assigned to: CPU Architecture:Any

[13 Jul 2005 9:52] Are you mortal Then prepare to die.
Description:

I am using some select statements to automatically create some tables. When I join the tables everything is fine. When I restrict the results set of the joined tables by a constant everything is fine. When I set the value of that constant in a user variable, and then try to restrict the same query by the variable, no results are returned. 

This is obviously wrong, as the variable should be synonymous with the constant. The optimizer is making a mistake, and this shows up in (some) EXPLAIN statements.

Finally, I had difficulty reproducing a test case for the bug, this is because it *does not* occur for all table / column / index types. I didn't investigate properly, but I did find a simple test case that reliably results in error on my system. Altering the column types can fix the problem on my system (the original types were selected by the 'CREATE TABLE SELECT ... ' I was using).

How to repeat:

DROP             TABLE IF EXISTS WHAT;
CREATE TEMPORARY TABLE           WHAT (ONE BIGINT(20) NOT NULL DEFAULT '0' PRIMARY KEY);

DROP   TABLE IF EXISTS THE;
CREATE TABLE           THE  (ONE BIGINT(20) DEFAULT NULL, INDEX X (ONE));

INSERT INTO WHAT VALUES (1),(2),(3),(4),(5),(6);
INSERT INTO THE  VALUES (4),(5),(6),(7),(8),(9);

-- Test (looks good)
SELECT * FROM WHAT INNER JOIN THE USING (ONE);

-- Test (shows correct behaviour)
SELECT * FROM WHAT a INNER JOIN 
               THE b USING (ONE) 
                   WHERE a.ONE = 5;

-- Now why would these be different?...
set @HECK = 5;

SELECT * FROM WHAT a INNER JOIN 
               THE b USING (ONE) 
                     WHERE a.ONE = @HECK;

-- AND

SELECT * FROM WHAT a INNER JOIN 
               THE b USING (ONE) 
                     WHERE b.ONE = @HECK;

-- Just in case you were in any doubt...

SELECT * FROM WHAT a WHERE ONE = @HECK;
SELECT * FROM  THE b WHERE ONE = @HECK;

-- BUT!

SELECT * FROM WHAT a INNER JOIN 
               THE b USING (ONE) 
                     WHERE a.ONE = @HECK
                        OR b.ONE = @HECK;

-- Its just strange (must be a bug)

EXPLAIN
SELECT * FROM WHAT a INNER JOIN 
               THE b USING (ONE) 
                     WHERE a.ONE = @HECK;

EXPLAIN
SELECT * FROM WHAT a INNER JOIN 
               THE b USING (ONE) 
                   WHERE b.ONE = @HECK;

Suggested fix:
+----+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY |       8 | const |    1 | Using index              |
|  1 | SIMPLE      | b     | ref   | X             | X       |       9 | const |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL |    NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

Find out what it is specifically about the table definition that causes the bug. Like is it the specific combination of keys / default values, or is it the column types. Why the heck should a constant be optimized differently from an instantiated variable, and why is the optimizer failing?
[13 Jul 2005 17:37] Jorge del Conde
Thanks for your bug report !

I was able to reproduce this bug in 4.1.11
[13 Jul 2005 17:39] Jorge del Conde
I wasn't able to reproduce this bug using 4.1.13bk.

mysql> 
mysql> -- Test (shows correct behaviour)
mysql> SELECT * FROM WHAT a INNER JOIN 
    ->                THE b USING (ONE) 
    ->                    WHERE a.ONE = 5;
+-----+------+
| ONE | ONE  |
+-----+------+
|   5 |    5 |
+-----+------+
1 row in set (0.00 sec)

mysql> 
mysql> -- Now why would these be different?...
mysql> set @HECK = 5;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> SELECT * FROM WHAT a INNER JOIN 
    ->                THE b USING (ONE) 
    ->                      WHERE a.ONE = @HECK;
+-----+------+
| ONE | ONE  |
+-----+------+
|   5 |    5 |
+-----+------+
1 row in set (0.00 sec)
[13 Jul 2005 17:55] Are you mortal Then prepare to die.
The problem is that 4.1.12 is not available for dec-osf ...
I am stuck with 4.1.11, and so I can definatly 'repeat' the bug :)

What do you suggest?