MySQL Bugs: #50996: Wrong result for second call of prepared statement with view in subselect

Bug #50996	Wrong result for second call of prepared statement with view in subselect
Submitted:	8 Feb 2010 14:32	Modified:	27 Aug 2010 12:13
Reporter:	Tor Didriksen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Tor Didriksen	CPU Architecture:	Any

Description:
CREATE TABLE t1 (t1field integer, primary key (t1field));
CREATE TABLE t2 (t2field integer, primary key (t2field));
CREATE TABLE t3 (t3field integer, primary key (t3field));

CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM t2;
CREATE VIEW v3 AS SELECT * FROM t3;

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

PREPARE stmt FROM
"
SELECT t1field FROM t1 
WHERE t1field IN (SELECT * FROM t2 where t1.t1field=t2.t2field)
";

EXECUTE stmt;
EXECUTE stmt;

The second, and subsequent invokations of the prepared statement yields wrong result.

How to repeat:
See description.

See similar bug 49198 (in which case we failed to pull out all items when converting to semijoin).

In this case we pull out the necessary items, but on subsequent invokations we build new 'Item_direct_view_ref' objects which are marked as dependent on the outer select, event if the item it refers to has been pulled out.

Thank you for the bug report. Which source tree have you built the server which has that behavior?. Please print here the result. Thanks in advance.

Head of
bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-codebase-bugfixing/

The problem in the description seems to be solved by the fix for
http://bugs.mysql.com/bug.php?id=50089

HOWEVER: this one does not:

CREATE TABLE t1 (t1field integer, primary key (t1field));
CREATE TABLE t2 (t2field integer, primary key (t2field));
CREATE TABLE t3 (t3field integer, primary key (t3field));

CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT 2*t2field as v2field FROM t2;
CREATE VIEW v3 AS SELECT * FROM t3;

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

PREPARE stmt FROM
"
SELECT t1field FROM t1 
WHERE t1field IN (SELECT * FROM v2 where t1.t1field = v2.v2field)
";

EXECUTE stmt;
EXECUTE stmt;

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

still fails on head of
bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-codebase-bugfixing/

The view can be simplified even more:

CREATE TABLE t1 (t1field integer, primary key (t1field));
CREATE TABLE t2 (t2field integer, primary key (t2field));
CREATE TABLE t3 (t3field integer, primary key (t3field));
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT t2field as v2field FROM t2;
CREATE VIEW v3 AS SELECT * FROM t3;
INSERT INTO t1 VALUES(1),(2),(4);
INSERT INTO t2 VALUES(1),(2);
INSERT INTO t3 VALUES(1),(2);
PREPARE stmt FROM
"
SELECT t1field FROM t1 
WHERE t1field IN (SELECT * FROM v2 where t1.t1field = v2.v2field)
";
EXECUTE stmt;
t1field
1
2
EXECUTE stmt;
t1field
2
DROP TABLE t1, t2, t3;
DROP VIEW v1, v2, v3;

Await result of
Wait for https://intranet.mysql.com/worklog/Server-Sprint/?tid=5266
and
http://bugs.mysql.com/bug.php?id=49453
before working further on this.

No longer reproducible, fixed by refactoring and other bugfixes.