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?