Bug #37849 An identical ENUM join which uses a FUNCTION performs differently on two servers
Submitted: 3 Jul 2008 19:02 Modified: 3 Jul 2008 21:02
Reporter: Kestas Kuliukas Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S1 (Critical)
Version:5.0.51a,5.0.45-log OS:FreeBSD (and Linux 2.4.32)
Assigned to: CPU Architecture:Any
Tags: cast, enum, FUNCTION, join

[3 Jul 2008 19:02] Kestas Kuliukas
Description:
A join which uses an ENUM behaves incorrectly on a slightly older version of MySQL, when using exactly the same script.

The problem is with joins on ENUM values; the second query on the second server clearly returns an incorrect result, but no-one can tell me why.

##########Server 1###########
VERSION()
5.0.51a
intCast(t1.col) intCast(t2.col) colFunction(t2.col)
1       1       1
2       2       2
3       3       3
4       4       3
intCast(t1.col) intCast(t2.col) colFunction(t2.col)
1       1       1
2       2       2
3       3       3
3       4       3
##########Server 2############
VERSION()
5.0.45-log
intCast(t1.col) intCast(t2.col) colFunction(t2.col)
1       1       1
2       2       2
3       3       3
4       4       3
intCast(t1.col) intCast(t2.col) colFunction(t2.col)
3       3       3
3       4       3

How to repeat:
CREATE TABLE `tab` ( `col` enum('a','b','c','d') );

INSERT INTO tab ( col ) VALUES ('a'),('b'),('c'),('d');

-- d becomes b, c becomes a
CREATE FUNCTION `colFunction`( c INT ) RETURNS INT RETURN IF(c=4, 2, IF( c=3, 1, c) );

SELECT t1.col, colFunction(t2.col) FROM tab AS t1 INNER JOIN tab AS t2 ON ( t1.col = t2.col );

SELECT t1.col, colFunction(t2.col) FROM tab AS t1 INNER JOIN tab AS t2 ON ( t1.col = colFunction(t2.col) );
[3 Jul 2008 19:04] Kestas Kuliukas
Also the following has exactly the same results; this isn't an INT to text casting issue:

SELECT intCast(t1.col), intCast(t2.col), colFunction(t2.col) FROM tab AS t1 INNER JOIN tab AS t2 ON ( intCast(t1.col) = intCast(t2.col) );

SELECT intCast(t1.col), intCast(t2.col), colFunction(t2.col) FROM tab AS t1 INNER JOIN tab AS t2 ON ( intCast(t1.col) = colFunction(t2.col) );
[3 Jul 2008 19:05] Kestas Kuliukas
(Increasing the severity to S1, since this problem breaks many queries which are vital to me, and I can't upgrade to 5.0.51a)
[3 Jul 2008 19:18] Sveta Smirnova
Thank you for the report.

If I understood you correctly you have wrong results with old version 5.0.45 and correct results with current 5.0.51a? Please confirm or reject.
[3 Jul 2008 20:45] Kestas Kuliukas
Exactly, 45-log seems to be giving the incorrect results here.
[3 Jul 2008 20:47] Kestas Kuliukas
(And also the results are different, whether or not either behavior specifically is incorrect I'm sure the results aren't supposed to be so different across two close versions.)
[3 Jul 2008 21:02] Sveta Smirnova
Thank you for the feedback.

I am sorry, but we don't backport bug fixes into previous minor versions. As all works as expected with current version I close the report.