Wrong result on second execution of a prepared outer join with empty table

[Philip Stoev]

If one of the operands of an outer join is an empty table, the second execution as a prepared statement returns no rows.

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
1 SIMPLE t2 ALL NULL NULL NULL NULL 4

test case:

CREATE TABLE t1 (a int);

CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (1),(2),(3),(4);

CREATE PROCEDURE sp1 ()
SELECT *
FROM t2
LEFT JOIN t1 ON t1.a = t2.a ;

CALL sp1();
CALL sp1();

bzr version-info:

revision-id: <email address hidden>
date: 2011-08-11 22:34:41 -0700
build-date: 2011-08-12 11:01:03 +0300
revno: 3151
branch-nick: maria-5.3

Reproducible with maria-5.3. Not reproducible with maria-5.2, mysql-5.5. This bug prevents further testing of subqueries in a prepared statement context.

[Timour Katchaounov (timour)]

Reproduced with prepared statements:

CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (1),(2),(3),(4);

prepare st1 from "
SELECT * FROM t2 LEFT JOIN t1 ON t1.a = t2.a";

execute st1;
execute st1;