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: | |
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
[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"