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

[16 Jul 2006 7:58] Svetoslav Naidenov
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 |
+---+------+
[16 Jul 2006 20:12] Svetoslav Naidenov
This bug is introduced in 5.0.21
Reproduced in 5.0.21 - 5.0.23 and 5.1.11
[17 Jul 2006 9:31] Hartmut Holzgraefe
Verified, 5.0.20 is ok, 5.0.21 up to current BK are broken
[17 Jul 2006 9:44] Hartmut Holzgraefe
mysqltest test case

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

[17 Aug 2006 4:03] Harry Long
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)
[8 Sep 2006 9:02] Tomash Brechko
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;
[9 Sep 2006 12:03] Bugs System
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.
[20 Sep 2006 18:36] Konstantin Osipov
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.
[27 Sep 2006 10:38] Bugs System
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.
[27 Sep 2006 10:44] Bugs System
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().
[27 Sep 2006 19:13] Bugs System
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().
[2 Oct 2006 17:26] Tomash Brechko
Queued to 5.0-runtime and 5.1-runtime.
[3 Oct 2006 19:33] Dmitry Lenev
Fixed in 5.0.27 and 5.1.12
[6 Oct 2006 3:02] Paul Dubois
Noted in 5.0.27, 5.1.12 changelogs.
[25 Oct 2006 16:42] Paul Dubois
The 5.0.x fix is in 5.0.30.
[9 Nov 2006 13:12] Tomash Brechko
Bug#23311 is a duplicate of this bug.