Bug #52100 wrong result set when using stored functions in a ON clause
Submitted: 16 Mar 2010 15:41 Modified: 16 Mar 2010 17:16
Reporter: hug capella Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.45-community OS:Linux (CentOS release 5.2 (Final))
Assigned to: CPU Architecture:Any
Tags: FUNCTION, join, multiple tables, ON clause, stored

[16 Mar 2010 15:41] hug capella
Description:
using a stored function in a "ON" clause gives a wrong result set: for all rows evaluates the "ON" clause using the function result got in the last row, instead of using the function result got in each current row.
The same problem happens if we use multiple tables with "WHERE", instead of "JOIN ... ON".
But it gives the correct result set if we use a built-in function instead.
Changing the function's return type or the "DETERMINISTIC" option has no effect in the wrong result.
In the example we have 2 tables, one with text values and other with length values, and we try to join these 2 tables using a stored function:
SELECT text_value,length_value from text_values JOIN length_values ON mylength(text_value)= length_value

I'm using the mysql client:
mysql  Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (i686) using readline 5.0

How to repeat:
drop table if exists a;drop table if exists b;drop table if exists c;drop function if exists mylength;
create table a (id int unsigned not null primary key auto_increment, txt varchar(255));
insert into a (txt) values ('abc'),('def'),('gh'),('ijk'),('lmno'),('pq');
create table b (lid int unsigned not null primary key auto_increment, len int);
insert into b (len) values (2),(3),(4);
create function mylength(txt varchar(255)) returns int deterministic return length(txt);
select *, mylength(txt) from a;                     #ok on a single table
select * from a join b on length(txt)=len;          #ok whith built-in function
select * from a join b on mylength(txt)=len;        #wrong on multiple tables
create table c select * from a join b on length(txt)=len;
select * from c where mylength(txt)=len;            #ok on a single table
[16 Mar 2010 15:45] Valeriy Kravchuk
Please, check with a newer version, 5.0.90, and inform about the results. Look:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.89-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists a;drop table if exists b;drop table if exists c;drop
 function if
Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

    -> exists mylength;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> create table a (id int unsigned not null primary key auto_increment, txt
varchar(255));
Query OK, 0 rows affected (0.25 sec)

mysql> insert into a (txt) values ('abc'),('def'),('gh'),('ijk'),('lmno'),('pq')
;
Query OK, 6 rows affected (0.09 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> create table b (lid int unsigned not null primary key auto_increment, len
 int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into b (len) values (2),(3),(4);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create function mylength(txt varchar(255)) returns int deterministic retu
rn length(txt);
Query OK, 0 rows affected (0.05 sec)

mysql> select *, mylength(txt) from a;
+----+------+---------------+
| id | txt  | mylength(txt) |
+----+------+---------------+
|  1 | abc  |             3 |
|  2 | def  |             3 |
|  3 | gh   |             2 |
|  4 | ijk  |             3 |
|  5 | lmno |             4 |
|  6 | pq   |             2 |
+----+------+---------------+
6 rows in set (0.03 sec)

mysql> select * from a join b on length(txt)=len;
+----+------+-----+------+
| id | txt  | lid | len  |
+----+------+-----+------+
|  1 | abc  |   2 |    3 |
|  2 | def  |   2 |    3 |
|  3 | gh   |   1 |    2 |
|  4 | ijk  |   2 |    3 |
|  5 | lmno |   3 |    4 |
|  6 | pq   |   1 |    2 |
+----+------+-----+------+
6 rows in set (0.03 sec)

mysql> select * from a join b on mylength(txt)=len;
+----+------+-----+------+
| id | txt  | lid | len  |
+----+------+-----+------+
|  1 | abc  |   2 |    3 |
|  2 | def  |   2 |    3 |
|  3 | gh   |   1 |    2 |
|  4 | ijk  |   2 |    3 |
|  5 | lmno |   3 |    4 |
|  6 | pq   |   1 |    2 |
+----+------+-----+------+
6 rows in set (0.00 sec)

I see nothing wrong in the above.
[16 Mar 2010 17:16] hug capella
I see. So my version is too old.
This is the wrong output I got:
mysql> select * from a join b on mylength(txt)=l;
+----+------+------+------+---+
| id | txt  | l    | lid  | c |
+----+------+------+------+---+
|  3 | gh   |    2 |    3 | 2 |
|  6 | pq   |    2 |    3 | 2 |
+----+------+------+------+---+
2 rows in set (0.00 sec)

Thank you for answer so quickly and apologies for this "not a bug"