| 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: | |
| 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: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.
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) );