MySQL Bugs: #31000: Left join using stored function fails with corrupted result

Bug #31000	Left join using stored function fails with corrupted result
Submitted:	13 Sep 2007 12:52	Modified:	29 Oct 2009 16:53
Reporter:	Zigmund Bulinsh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
select 
  A.id,
  A.name,
  if(A.id = 4, 4, 1),
  Func(A.id),
  (select GROUP_CONCAT(value) from detail where main_id = Func(A.id)),
  (select MAX(value) from detail where main_id = Func(A.id)),
  MAX(B.value)
from
  main A
    left join detail B
    on B.main_id = Func(A.id)
group by
  A.id, A.name

Results in incorrect resultset!

How to repeat:
CREATE TABLE `main` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(84) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;
INSERT INTO `main` VALUES ('1', 'First');
INSERT INTO `main` VALUES ('2', 'Second');
INSERT INTO `main` VALUES ('3', 'Third');
INSERT INTO `main` VALUES ('4', 'Fourth');

CREATE TABLE `detail` (
  `id` int(11) NOT NULL auto_increment,
  `main_id` int(11) default NULL,
  `value` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;
INSERT INTO `detail` VALUES ('1', '1', '1');
INSERT INTO `detail` VALUES ('2', '4', '0');

CREATE FUNCTION `Func`(p_id int(10)) RETURNS int(10)
begin
  if p_id = 4 then
    return 4;
  else
    return 1;
  end if;
end;

NOW WE RUN THIS QUERY:
select 
  A.id,
  A.name,
  if(A.id = 4, 4, 1),
  Func(A.id),
  (select GROUP_CONCAT(value) from detail where main_id = Func(A.id)),
  (select MAX(value) from detail where main_id = Func(A.id)),
  MAX(B.value)
from
  main A
    left join detail B
    on B.main_id = Func(A.id)
group by
  A.id, A.name

AND GET FOLLOWING RESULT
1 First  1 1 1 1 1
2 Second 1 1 1 1 1
3 Third  1 1 1 1 1
4 Fourth 4 4 0 0 1

If we change query to this:
select 
  A.id,
  A.name,
  if(A.id = 4, 4, 1),
  Func(A.id),
  (select GROUP_CONCAT(value) from detail where main_id = Func(A.id)),
  (select MAX(value) from detail where main_id = Func(A.id)),
  MAX(B.value)
from
  main A
    left join detail B
    on B.main_id = if(A.id = 4, 4, 1)
    #comment this Func(A.id)
group by
  A.id, A.name

Then result is correct:
1 First  1 1 1 1 1
2 Second 1 1 1 1 1
3 Third  1 1 1 1 1
4 Fourth 4 4 0 0 0

Suggested fix:
Something wrong with optimizer - it determines that function is constant and does not query it for each row.

some mistake in title of report

Thank you for the bug report. I can't repeat with server from current
source server:

c:\dev\5.0>bin\mysql -uroot -N test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.50-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select
    ->   A.id,
    ->   A.name,
    ->   if(A.id = 4, 4, 1),
    ->   Func(A.id),
    ->   (select GROUP_CONCAT(value) from detail where main_id = Func(A.id)),
    ->   (select MAX(value) from detail where main_id = Func(A.id)),
    ->   MAX(B.value)
    -> from
    ->   main A
    ->     left join detail B
    ->     on B.main_id = Func(A.id)
    -> group by
    ->   A.id, A.name;
+---+--------+---+------+------+------+------+
| 1 |  First | 1 |    1 |    1 | 1    |    1 |
| 2 | Second | 1 |    1 |    1 | 1    |    1 |
| 3 |  Third | 1 |    1 |    1 | 1    |    1 |
| 4 | Fourth | 4 |    4 |    0 | 0    |    0 |
+---+--------+---+------+------+------+------+
4 rows in set (0.01 sec)

mysql> select
    ->   A.id,
    ->   A.name,
    ->   if(A.id = 4, 4, 1),
    ->   Func(A.id),
    ->   (select GROUP_CONCAT(value) from detail where main_id = Func(A.id)),
    ->   (select MAX(value) from detail where main_id = Func(A.id)),
    ->   MAX(B.value)
    -> from
    ->   main A
    ->     left join detail B
    ->     on B.main_id = if(A.id = 4, 4, 1)
    ->     #comment this Func(A.id)
    -> group by
    ->   A.id, A.name;
+---+--------+---+------+------+------+------+
| 1 |  First | 1 |    1 |    1 | 1    |    1 |
| 2 | Second | 1 |    1 |    1 | 1    |    1 |
| 3 |  Third | 1 |    1 |    1 | 1    |    1 |
| 4 | Fourth | 4 |    4 |    0 | 0    |    0 |
+---+--------+---+------+------+------+------+
4 rows in set (0.01 sec)

mysql>

Sorry, but I have no access to 5.0.50 community edition version!
How I can fix this asap?

Maybe it is some case of Bug#29604... When I can get 5.0.50 on which it works..?

This can be repeated on 4 mysql server 5.0.45 versions! As with turned on query cache and also without.

This works for some time. Waiting for new release...

select 
  A.id,
  A.name,
  if(A.id = 4, 4, 1),
  Func(A.id),
  (select GROUP_CONCAT(value) from detail where main_id = Func(A.id)),
  (select MAX(value) from detail where main_id = Func(A.id)),
  MAX(B.value)
from
  main A
    left join detail B
    on B.main_id = if(A.id = 0, 0, Func(A.id))
group by
  A.id, A.name

It works on 5.1.40. I just tested. So we close the issue.