MySQL Bugs: #21081: SELECT inside stored procedure returns wrong results

Bug #21081	SELECT inside stored procedure returns wrong results
Submitted:	16 Jul 2006 7:58	Modified:	6 Oct 2006 3:02
Reporter:	Svetoslav Naidenov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22	OS:
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
Wrong results when a SELECT query in which one table refers to the same table is 
invoked whithin stored procedure.
Once NULL value is returned for LEFT JOINed columt, subsequent invocations of the stored procedure always produce NULL value for that column.

How to repeat:
create table t(a int primary key, b int);
insert into t values (1,null),(2,null),(3,1);

create procedure sp_t(in x int)
select t1.a, t2.a
 from t t1
   left join t t2 on t2.a = t1.b
 where t1.a=x;

call sp_t(3);
+---+---+
| a | a |
+---+---+
| 3 | 1 |
+---+---+
call sp_t(1);
+---+------+
| a | a    |
+---+------+
| 1 | NULL |
+---+------+
call sp_t(3);
+---+------+
| a | a    |
+---+------+
| 3 | NULL |
+---+------+

This bug is introduced in 5.0.21
Reproduced in 5.0.21 - 5.0.23 and 5.1.11

Verified, 5.0.20 is ok, 5.0.21 up to current BK are broken

mysqltest test case

Attachment: bug21081.tgz (application/x-gtar, text), 712 bytes.

This bug applies to all use of joins in stored procedures and function, not just left joins on the same table.
Perhaps its Severity and Priority should be raised since it makes use of stored procedures or functions a risky business and it appears that there is no other workaround than downgrading to 5.0.20-.

This is a more common test case:

create table a(id int primary key, ref int);
create table b(id int primary key);

insert into a values(1,1),(2,null),(3,3);
insert into b values(1),(2);

delimiter //
create function f(x int) returns int
begin
  declare b int;

  select b.id into b
  from a
   left join b on b.id = a.ref
  where a.id = x;
 
  return b;
end
//

create procedure sp(in x int)
begin
  select a.id, a.ref, b.id
  from a
    left join b on b.id = a.ref
  where a.id = x;
end
//
delimiter ;

mysql> select f(1),f(2),f(1);
+------+------+------+
| f(1) | f(2) | f(1) |
+------+------+------+
|    1 | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)

mysql> call sp(1); call sp(2); call sp(1);
+----+------+----+
| id | ref  | id |
+----+------+----+
|  1 |    1 |  1 |
+----+------+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----+------+------+
| id | ref  | id   |
+----+------+------+
|  2 | NULL | NULL |
+----+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----+------+------+
| id | ref  | id   |
+----+------+------+
|  1 |    1 | NULL |
+----+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

After restarting session:

mysql> select f(1),f(3),f(1);
+------+------+------+
| f(1) | f(3) | f(1) |
+------+------+------+
|    1 | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)

With 5.0.25-BK I can't repeat most of the tests, the result is correct.  However, one is still repeatable:

DROP FUNCTION IF EXISTS f1;
DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 (id INT KEY);
CREATE TABLE t2 (id INT);

INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1);

CREATE FUNCTION f1(x INT) RETURNS INT
  RETURN (SELECT t2.id FROM t1 LEFT JOIN t2 ON t2.id = t1.id
          WHERE t1.id = x);

SELECT f1(1), f1(2), f1(1);
#+-------+-------+-------+
#| f1(1) | f1(2) | f1(1) |
#+-------+-------+-------+
#|     1 |  NULL |  NULL | <= Wrong third column
#+-------+-------+-------+

DROP FUNCTION f1;
DROP TABLE t1, t2;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11649

ChangeSet@1.2261, 2006-09-09 16:02:36+04:00, kroki@moonlight.intranet +3 -0
  BUG#21081: SELECT inside stored procedure returns wrong results
  
  Re-execution of a parametrized prepared statement or a stored routine
  with a SELECT that use LEFT JOIN with second table having only one row
  could yield incorrect result.
  
  The problem appeared only for left joins with second table having only
  one row (aka const table) and equation conditions in ON or WHERE clauses
  that depend on the argument passed. Once the condition was false for
  second const table, a NULL row was created for it, and any field involved
  got NULL-value flag, which then was never reset.
  
  The cause of the problem was that Item_field::null_value was tested
  before it was updated for current execution (and no update happened if
  it was already set).  The solution is to use Item_field::is_null()
  instead.

Setting to approved, but suggested an alternative solution.
Both patches are tiny, so in case the proposal is accepted, there is nothing more to review.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12599

ChangeSet@1.2261, 2006-09-27 14:36:39+04:00, kroki@moonlight.intranet +3 -0
  BUG#21081: SELECT inside stored procedure returns wrong results
  
  Re-execution of a parametrized prepared statement or a stored routine
  with a SELECT that use LEFT JOIN with second table having only one row
  could yield incorrect result.
  
  The problem appeared only for left joins with second table having only
  one row (aka const table) and equation conditions in ON or WHERE clauses
  that depend on the argument passed. Once the condition was false for
  second const table, a NULL row was created for it, and any field involved
  got NULL-value flag, which then was never reset.
  
  The cause of the problem was that Item_field::null_value was tested
  before it was updated for current execution (and no update happened if
  it was already set).  The solution is to use Item_field::is_null()
  instead.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12601

ChangeSet@1.2261, 2006-09-27 14:43:14+04:00, kroki@moonlight.intranet +3 -0
  BUG#21081: SELECT inside stored procedure returns wrong results
  
  Re-execution of a parametrized prepared statement or a stored routine
  with a SELECT that use LEFT JOIN with second table having only one row
  could yield incorrect result.
  
  The problem appeared only for left joins with second table having only
  one row (aka const table) and equation conditions in ON or WHERE clauses
  that depend on the argument passed.  Once the condition was false for
  second const table, a NULL row was created for it, and any field involved
  got NULL-value flag, which then was never reset.
  
  The cause of the problem was that Item_field::null_value could be set
  without being reset for re-execution.  The solution is to reset
  Item_field::null_value in Item_field::cleanup().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12647

ChangeSet@1.2257, 2006-09-27 23:11:45+04:00, kroki@moonlight.intranet +3 -0
  BUG#21081: SELECT inside stored procedure returns wrong results
  
  Re-execution of a parametrized prepared statement or a stored routine
  with a SELECT that use LEFT JOIN with second table having only one row
  could yield incorrect result.
  
  The problem appeared only for left joins with second table having only
  one row (aka const table) and equation conditions in ON or WHERE clauses
  that depend on the argument passed.  Once the condition was false for
  second const table, a NULL row was created for it, and any field involved
  got NULL-value flag, which then was never reset.
  
  The cause of the problem was that Item_field::null_value could be set
  without being reset for re-execution.  The solution is to reset
  Item_field::null_value in Item_field::cleanup().

Queued to 5.0-runtime and 5.1-runtime.

Fixed in 5.0.27 and 5.1.12

Noted in 5.0.27, 5.1.12 changelogs.

The 5.0.x fix is in 5.0.30.

Bug#23311 is a duplicate of this bug.