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:
None 
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
Description:
Assume i have the following two tables:

mysql> explain test1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| foo   | text             | YES  |     | NULL    |                |
| test2 | varchar(255)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

mysql> explain test2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

The field test2 of table test1 contains values that appear in field name of table test2.

I now have a stored function that gets a varchar(255) as a parameter and returns the id-field of table test2 if a row exists with that parameter as field name:

CREATE FUNCTION `testfunc`(
    the_name VARCHAR(255)
) RETURNS INT unsigned DETERMINISTIC SQL SECURITY DEFINER
BEGIN
    DECLARE the_id INT unsigned;
    SELECT `id` INTO the_id FROM test2 WHERE `name` = the_name;
    RETURN the_id;
END;

The following query returns a wrong result-set:
select * from test1 LEFT JOIN test2 on test2.id=testfunc(test1.test2);

The function call in the ON-clause is replace by the value that testfunc returns for the last row of test1 and not by the value of the current row!

How to repeat:
Create the tables and the function i mentioned.

Insert values:
For test1:
+----+------+-------+
| id | foo  | test2 |
+----+------+-------+
|  1 | Val1 | bla   |
|  2 | val2 | blubb |
|  3 | val3 | bla   |
|  4 | val4 | klong |
+----+------+-------+

For test2:
+----+-------+
| id | name  |
+----+-------+
|  1 | bla   |
|  2 | blubb |
|  3 | baz   |
|  4 | klong |
+----+-------+

Run the query:
select * from test1 LEFT JOIN test2 on test2.id=testfunc(test1.test2);

My result is:
+----+------+-------+------+-------+
| id | foo  | test2 | id   | name  |
+----+------+-------+------+-------+
|  1 | Val1 | bla   |    4 | klong |
|  2 | val2 | blubb |    4 | klong |
|  3 | val3 | bla   |    4 | klong |
|  4 | val4 | klong |    4 | klong |
+----+------+-------+------+-------+

The result should be the same as from the query
mysql> select * from test1 LEFT JOIN test2 on test2.name=test1.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 |
+----+------+-------+------+-------+

And that does not match!!
[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 ...