Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 825035
Re: Wrong result on second execution of a prepared outer join with empty table
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;