MySQL Bugs: #54281: Procedure fails on second call with subquery materialization and views

Bug #54281	Procedure fails on second call with subquery materialization and views
Submitted:	7 Jun 2010 9:49	Modified:	15 Oct 2012 14:40
Reporter:	Øystein Grøvlen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	mysql-next-mr-opt-backporting	OS:	Any
Assigned to:	Roy Lyseng	CPU Architecture:	Any
Tags:	materialization, optimizer_switch, subquery

Description:
If semijoin is turned off so that subquery materialization is used instead, the second call of the following procedure fails with "query 'CALL p1' failed: 1054: Unknown column 'materialized subselect.vf_inner' in 'order clause'"

CREATE PROCEDURE p1 () 
  BEGIN 
    SELECT v1field
    FROM v1 
    WHERE v1field IN ( SELECT v2field as vf_inner FROM v2 );
  END|

If semijoin is turned on, or both semijoin and materialization is turned off,
the error does not occur.

How to repeat:
SET SESSION optimizer_switch = 'semijoin=off,materialization=on';
CREATE TABLE t1 ( t1field integer, primary key (t1field));
CREATE TABLE t2 ( t2field integer, primary key (t2field));

CREATE VIEW v1 AS 
  SELECT t1field as v1field
  FROM t1 A 
  WHERE A.t1field IN (SELECT t1field FROM t2 );

CREATE VIEW v2 AS 
  SELECT t2field as v2field
  FROM t2 A 
  WHERE A.t2field IN (SELECT t2field FROM t2 );

DELIMITER |;
CREATE PROCEDURE p1 () 
  BEGIN 
    SELECT v1field
    FROM v1 
    WHERE v1field IN ( SELECT v2field as vf_inner FROM v2 );
  END|
DELIMITER ;|

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

CALL p1;
CALL p1;

DROP TABLE t1,t2;
DROP VIEW v1,v2;
DROP PROCEDURE p1;

Thank for the report.

Verified as described.

Some notes:

The problem is also reproducible with a prepared statement, and with a simpler case where one of the view references (v1) is replaced by the underlying base table (t1), ie:

prepare stmt from "
SELECT t1field FROM t1 WHERE t1field IN(SELECT v2field AS vf_inner FROM v2)
";

The query will fail on second execution with the same error message as reported originally.

The query does not fail, however, without the "AS vf_inner" clause.

Neither does the query fail if the inner query keeps the AS clause, but the selection is instead against the base table (t2) instead of against the view (v2).

Further investigation showed that the problem is with creation of a temporary table used in subquery materialization. The temporary table is created with one field named "vf_inner". This name is a reference to the name from the underlying item. The name string is erased after first execution, indicating that we do not have proper control over the lifetime of this name object.

IMHO, a future-proof solution for this problem is to extend the lifetime of the parsed view definition to be the same as the lifetime of the query (ie., until the query is closed or re-prepared). With this solution, there would be no need to worry about the lifetime of memory objects.

One way to implement this would be to materialize the parsed view definition in the statement memory context.

Allowing this would probably not introduce any change of behavior, as the query preparation system is not able to pick up changes in view descriptions anyway. However, when query preparation is also able to detect changes in a referenced view, the query preparation system would be able to re-prepare the complete query, picking up changes in both tables and view.