Bug #32714 | Wrong value for function in JOINs ON-clause if functions reads from joined table | ||
---|---|---|---|
Submitted: | 26 Nov 2007 13:14 | Modified: | 28 Nov 2007 17:35 |
Reporter: | Dennis Birkholz | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
Version: | 5.0.42 - 5.0.46 | OS: | Linux |
Assigned to: | Assigned Account | CPU Architecture: | Any |
Tags: | join on stored function, myisam, text |
[26 Nov 2007 13:14]
Dennis Birkholz
[27 Nov 2007 11:00]
Hartmut Holzgraefe
Can't reproduce, tested with 5.0.44 and current 5.1 sources. Please try the test script below to see whether you get the same results as me. If your results don't match mine or if you can come up with a modified script showing the problem please comment on this bug report. Test script: --echo # --echo # Bug#32714 - Wrong value for function in JOINs ON-clause if functions reads from joined table --echo # --disable_warnings DROP TABLE IF EXISTS `t1`; DROP TABLE IF EXISTS `t2`; DROP FUNCTION IF EXISTS `f1`; --enable_warnings CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `foo` varchar(100) DEFAULT NULL, `test2` varchar(100) DEFAULT NULL ); INSERT INTO `t1` VALUES (1,'Val1','bla'), (2,'val2','blubb'), (3,'val3','bla'), (4,'val4','klong'); CREATE TABLE `t2` ( `id` int(11) DEFAULT NULL, `name` varchar(100) DEFAULT NULL ); INSERT INTO `t2` VALUES (1,'bla'), (2,'blubb'), (3,'baz'), (4,'klong'); DELIMITER // ; CREATE FUNCTION `f1`( p1 VARCHAR(255) ) RETURNS INT unsigned DETERMINISTIC SQL SECURITY DEFINER BEGIN DECLARE retval INT unsigned; SELECT `id` INTO retval FROM t2 WHERE `name` = p1; RETURN retval; END; // DELIMITER ; // SELECT * FROM t1 LEFT JOIN t2 ON t2.name = t1.test2; SELECT * FROM t1 LEFT JOIN t2 on t2.id = f1(t1.test2); results: mysql> SELECT * FROM t1 LEFT JOIN t2 ON t2.name = t1.test2; +------+------+-------+------+-------+ | id | foo | test2 | id | name | +------+------+-------+------+-------+ | 1 | Val1 | bla | 1 | bla | | 2 | val2 | blubb | 2 | blubb | | 3 | val3 | bla | 1 | bla | | 4 | val4 | klong | 4 | klong | +------+------+-------+------+-------+ 4 rows in set (0.01 sec) mysql> mysql> SELECT * FROM t1 LEFT JOIN t2 on t2.id = f1(t1.test2); +------+------+-------+------+-------+ | id | foo | test2 | id | name | +------+------+-------+------+-------+ | 1 | Val1 | bla | 1 | bla | | 2 | val2 | blubb | 2 | blubb | | 3 | val3 | bla | 1 | bla | | 4 | val4 | klong | 4 | klong | +------+------+-------+------+-------+ 4 rows in set (0.02 sec)
[27 Nov 2007 12:00]
Dennis Birkholz
Hello, your script does not show the error on my machine, to, but the following script does: DROP DATABASE IF EXISTS `mytestdb`; CREATE DATABASE `mytestdb`; USE `mytestdb`; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL auto_increment, `foo` text, `test2` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `t2`; CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; DROP FUNCTION IF EXISTS `testfunc`; DELIMITER // CREATE DEFINER=`root`@`localhost` FUNCTION `testfunc`( the_name VARCHAR(255) ) RETURNS int(10) unsigned DETERMINISTIC BEGIN DECLARE the_id INT unsigned; DECLARE EXIT HANDLER FOR 1329 RETURN NULL; SELECT `id` INTO the_id FROM t2 WHERE `name` = the_name; RETURN the_id; END; // DELIMITER ; INSERT INTO t1 (foo, test2) VALUES ('Val1', 'bla'), ('Val2', 'blubb'), ('Val3', 'bla'), ('Val4','klong'); INSERT INTO t2 (name) VALUES ('bla'), ('blubb'), ('baz'), ('klong'); SELECT * from t1 LEFT JOIN t2 on t2.id=testfunc(t1.test2); SELECT * from t1 LEFT JOIN t2 on t2.name=t1.test2; Result: mysql> SELECT * from t1 LEFT JOIN t2 on t2.id=testfunc(t1.test2); +----+------+-------+------+-------+ | id | foo | test2 | id | name | +----+------+-------+------+-------+ | 5 | Val1 | bla | 8 | klong | | 6 | Val2 | blubb | 8 | klong | | 7 | Val3 | bla | 8 | klong | | 8 | Val4 | klong | 8 | klong | +----+------+-------+------+-------+ 4 rows in set (0.00 sec) mysql> SELECT * from t1 LEFT JOIN t2 on t2.name=t1.test2; +----+------+-------+------+-------+ | id | foo | test2 | id | name | +----+------+-------+------+-------+ | 5 | Val1 | bla | 5 | bla | | 6 | Val2 | blubb | 6 | blubb | | 7 | Val3 | bla | 5 | bla | | 8 | Val4 | klong | 8 | klong | +----+------+-------+------+-------+ 4 rows in set (0.00 sec) The only difference I see in both scripts is the primary key with auto increment value. I tested it on Gentoo with package dev-db/mysql-5.0.44-r2 and i will try it later on the 5.0.45 compiled from sources downloaded at mysql.com. The machine is AMD64 with Opteron processors. Greets, Dennis
[28 Nov 2007 11:29]
Hartmut Holzgraefe
Verified with provided new test script
[28 Nov 2007 11:29]
Hartmut Holzgraefe
5.1 not affected
[28 Nov 2007 11:46]
Hartmut Holzgraefe
mysqltest test case
Attachment: bug32714.tgz (application/x-gtar, text), 1.33 KiB.
[28 Nov 2007 11:52]
Hartmut Holzgraefe
The test only fails with - storage engine = MyISAM, works fine with InnoDB - field `foo` being a TEXT field, change it to VARCHAR(255) and the result changes even though `foo` is not used in the queries or the stored function
[28 Nov 2007 12:03]
Hartmut Holzgraefe
refined test case
Attachment: bug32714.tgz (application/x-gtar, text), 1.52 KiB.
[28 Nov 2007 12:06]
Hartmut Holzgraefe
Uploaded a refined test case that tries - MyISAM table with VARCHAR - MyISAM table with TEXT - InnoDB table with TEXT using ALTER TABLE to modify the table Here the first query after ALTER TABLE t1 MODIFY COLUMN `foo` TEXT; shows even more weired results, only when executing the query again the originally reported wrong result shows: $cat bug32714.diff --- bug32714.result 2007-11-28 13:02:10.000000000 +0100 +++ bug32714.reject 2007-11-28 13:02:32.000000000 +0100 @@ -43,15 +43,20 @@ ALTER TABLE t1 MODIFY COLUMN `foo` TEXT; SELECT * from t1 LEFT JOIN t2 on t2.id=f1(t1.test2); id foo test2 id name -5 Val1 bla 5 bla -6 Val2 blubb 6 blubb -7 Val3 bla 5 bla -8 Val4 klong 8 klong +5 Val1 bla NULL NULL +6 Val2 blubb NULL NULL +7 Val3 bla NULL NULL +8 Val4 klong NULL NULL +Warnings: +Warning 1366 Incorrect string value: '\xA5\xA5\xA5\xA5\xA5\xA5...' for column 'the_name' at row 0 +Warning 1366 Incorrect string value: '\xA5\xA5\xA5\xA5\xA5\xA5...' for column 'the_name' at row 5 +Warning 1366 Incorrect string value: '\xA5\xA5\xA5\xA5\xA5\xA5...' for column 'the_name' at row 5 +Warning 1366 Incorrect string value: '\xA5\xA5\xA5\xA5\xA5\xA5...' for column 'the_name' at row 5 SELECT * from t1 LEFT JOIN t2 on t2.id=f1(t1.test2); id foo test2 id name -5 Val1 bla 5 bla -6 Val2 blubb 6 blubb -7 Val3 bla 5 bla +5 Val1 bla 8 klong +6 Val2 blubb 8 klong +7 Val3 bla 8 klong 8 Val4 klong 8 klong ALTER TABLE t1 ENGINE=InnoDB; SELECT * from t1 LEFT JOIN t2 on t2.id=f1(t1.test2);
[28 Nov 2007 13:08]
Hartmut Holzgraefe
can't reproduce with current 5.0 sources anymore, trying to track down related changelog items now ...
[28 Nov 2007 17:03]
Hartmut Holzgraefe
Only reproduceable in 5.0.42 - .46 The only 5.0.48 changelog item that looks at least partly related is the one for bug #29604, but that doesn't really seem to be the same thing. So i'm not sure here whether the problem really got fixed or is still lurking in the code base, waiting to emerge again ...
[28 Nov 2007 17:35]
Dennis Birkholz
Thank you for your diagnostics. I wonder a little bit why this problem is not reproducible with InnoDB as storage engine because the reason I created a simplified test case was an error occurring in one of our applications that used InnoDB and not MyISAM so this bug seems not to be the bug I found killing our application :-( Greets, Dennis
[28 Nov 2013 11:37]
Hartmut Holzgraefe
Doesn't seem to have resurfaced, can probably be closed ...