[MDEV-3257] LP:825035 - Wrong result on second execution of a prepared outer join with empty table Created: 2011-08-12  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug825035.xml    

 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.

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-08-12 ]

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;

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 825035

Generated at Thu Feb 08 06:47:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.