MySQL Bugs: #30672: Wrong optimization when using a join and a function

Bug #30672	Wrong optimization when using a join and a function
Submitted:	28 Aug 2007 15:49	Modified:	12 Oct 2007 14:12
Reporter:	Giorgio Calderone	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.20	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
The server fails optimizing a JOIN query with a call to a UDF in the WHERE clause.

The error can be reproduced creating the appropriate tables (as shown below), defining a 'fake' function (that is a function that simply returns its argument) and executing two queries which are supposed to return the same result. The only difference between the two queries is the use of the 'fake' function in the WHERE clause.

The origin of the error is probably in the way queries are optimized as can be seen using the EXPLAIN statement.

This happens on versions 5.1.19 and 5.1.20, but not on 5.0.27.

Giorgio.

How to repeat:
#
#Create data tables
#
DROP TABLE IF EXISTS main;
CREATE TABLE main (id INT, c CHAR(10), f FLOAT, INDEX (id));

INSERT INTO main VALUES(0, 'NORTH' , 0);
INSERT INTO main VALUES(0, 'NORTH-EAST', 45);
INSERT INTO main VALUES(1, 'EAST' , 90);
INSERT INTO main VALUES(1, 'SOUTH-EAST', 135);
INSERT INTO main VALUES(2, 'SOUTH' , 180);
INSERT INTO main VALUES(2, 'SOUTH-WEST', 225);
INSERT INTO main VALUES(3, 'WEST' , 270);
INSERT INTO main VALUES(3, 'NORTH-WEST', 315);

DROP TABLE IF EXISTS ref;
CREATE TABLE ref (id INT, flag INT);

INSERT INTO ref VALUES (0, 1);
INSERT INTO ref VALUES (1, 0);
INSERT INTO ref VALUES (2, 0);

#
#Define a 'fake' function, that is a function that simply returns its argument:
#
delimiter //
DROP FUNCTION IF EXISTS fiden //
CREATE FUNCTION fiden(f FLOAT) RETURNS FLOAT
  NOT DETERMINISTIC
  BEGIN
    RETURN f;
END//
delimiter ;

#
#The following query return the correct result (5 records):
#
SELECT *
FROM main INNER JOIN ref USING (id)
WHERE ( (ref.flag = 1)   OR   (main.f < 200));

#
#The following query uses the 'fake' function and the result is wrong
#(2 records): 
#
SELECT *
FROM main INNER JOIN ref USING (id)
WHERE ((ref.flag = 1)   OR   (fiden(main.f) < 200));

verified on 5.1.18, current 5.1bk doesn't seem to be affected anymore though

attaching mysqltest formatted test case ...

mysqltest test case

Attachment: bug30672.tgz (application/x-gtar, text), 1.29 KiB.

Version 5.037 does not have this bug either, but 5.045 does have it.

Seemingly it is not a good idea to run a version above that if using UDF.

mysql> SELECT *
    -> FROM main INNER JOIN ref USING (id)
    -> WHERE ( (ref.flag = 1)   OR   (main.f < 200));
+------+------------+------+------+
| id   | c          | f    | flag |
+------+------------+------+------+
|    0 | NORTH      |    0 |    1 | 
|    0 | NORTH-EAST |   45 |    1 | 
|    1 | EAST       |   90 |    0 | 
|    1 | SOUTH-EAST |  135 |    0 | 
|    2 | SOUTH      |  180 |    0 | 
+------+------------+------+------+
5 rows in set (0.02 sec)

mysql> 
mysql> #
mysql> #The following query uses the 'fake' function and the result is wrong
mysql> #(2 records): 
mysql> #
mysql> SELECT *
    -> FROM main INNER JOIN ref USING (id)
    -> WHERE ((ref.flag = 1)   OR   (fiden(main.f) < 200));
+------+------------+------+------+
| id   | c          | f    | flag |
+------+------------+------+------+
|    0 | NORTH      |    0 |    1 | 
|    0 | NORTH-EAST |   45 |    1 | 
|    1 | EAST       |   90 |    0 | 
|    1 | SOUTH-EAST |  135 |    0 | 
|    2 | SOUTH      |  180 |    0 | 
+------+------------+------+------+
5 rows in set (0.00 sec)

mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.0.50-valgrind-max-debug | 
+---------------------------+
1 row in set (0.00 sec)

Can't repeat in the latest tree. This is likely a duplicate of Bug#31035