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: | |
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 |
[7 Jun 2010 9:49]
Øystein Grøvlen
[7 Jun 2010 11:01]
Sveta Smirnova
Thank for the report. Verified as described.
[15 Jul 2010 8:59]
Roy Lyseng
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.
[15 Sep 2010 8:45]
Roy Lyseng
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.