MySQL Bugs: #27011: Stored Procedures: bad data if view used inside a SP is dropped or modified

Bug #27011	Stored Procedures: bad data if view used inside a SP is dropped or modified
Submitted:	9 Mar 2007 20:08	Modified:	20 Jun 2012 16:44
Reporter:	William Chiquito	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.42-BK, 5.0.37-community	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DDL, stored procedure, VIEW

Description:
Problem with prepared statement in iterations.

How to repeat:
Script:

Stored Procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `bug`$$

CREATE DEFINER=`user`@`server` PROCEDURE `bug`()
BEGIN
DECLARE item INT DEFAULT 1;
REPEAT
	SET @stmt = CONCAT('CREATE VIEW myview AS (SELECT * FROM city WHERE id = ', item, ')');
	SELECT @stmt;
	PREPARE stmt1 FROM @stmt;
	EXECUTE stmt1;
	DEALLOCATE PREPARE stmt1;
	SELECT * FROM myview;
	DROP VIEW myview;
	SET item = item + 1;
UNTIL item > 3
END REPEAT;
END$$

DELIMITER ;

Result:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.37-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use world
Database changed
mysql> call bug;
+---------------------------------------------------------+
| @stmt                                                   |
+---------------------------------------------------------+
| CREATE VIEW myview AS (SELECT * FROM city WHERE id = 1) |
+---------------------------------------------------------+
1 row in set (0.00 sec)

+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)

+---------------------------------------------------------+
| @stmt                                                   |
+---------------------------------------------------------+
| CREATE VIEW myview AS (SELECT * FROM city WHERE id = 2) |
+---------------------------------------------------------+
1 row in set (0.00 sec)

+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)

+---------------------------------------------------------+
| @stmt                                                   |
+---------------------------------------------------------+
| CREATE VIEW myview AS (SELECT * FROM city WHERE id = 3) |
+---------------------------------------------------------+
1 row in set (0.00 sec)

+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Thank you for a bug report. Verified just as described with latest 5.0.42-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot world
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.42 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter $$

mysql> create procedure bug()
    -> BEGIN
    -> DECLARE item INT DEFAULT 1;
    -> REPEAT
    -> SET @stmt = CONCAT('CREATE VIEW myview AS (SELECT * FROM City WHERE id =
 ',
    -> item, ')');
    -> SELECT @stmt;
    -> PREPARE stmt1 FROM @stmt;
    -> EXECUTE stmt1;
    -> DEALLOCATE PREPARE stmt1;
    -> SELECT * FROM myview;
    -> DROP VIEW myview;
    -> SET item = item + 1;
    -> UNTIL item > 3
    -> END REPEAT;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call bug;
+---------------------------------------------------------+
| @stmt                                                   |
+---------------------------------------------------------+
| CREATE VIEW myview AS (SELECT * FROM City WHERE id = 1) |
+---------------------------------------------------------+
1 row in set (0.00 sec)

+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.01 sec)

+---------------------------------------------------------+
| @stmt                                                   |
+---------------------------------------------------------+
| CREATE VIEW myview AS (SELECT * FROM City WHERE id = 2) |
+---------------------------------------------------------+
1 row in set (0.01 sec)

+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.01 sec)

+---------------------------------------------------------+
| @stmt                                                   |
+---------------------------------------------------------+
| CREATE VIEW myview AS (SELECT * FROM City WHERE id = 3) |
+---------------------------------------------------------+
1 row in set (0.01 sec)

+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> select * from City where id=3;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  3 | Herat | AFG         | Herat    |     186800 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)

Duplicate of the bug#27430.

Re-opening this report.

The issue here seems that the content of the SP cache / sp_instr
is "tainted" by previous executions, which is a different issue than bug#27430

Test script in mysqltest format:

--disable_warnings
drop table if exists t_27011;
drop view if exists v_27011;
drop procedure if exists proc_27011;
--enable_warnings

create table t_27011(id int, name char(50));
insert into t_27011 values
  ((1), "Paris"),
  ((2), "Denver"),
  ((3), "Cupertino");

DELIMITER |;

CREATE PROCEDURE proc_27011()
BEGIN
  DECLARE item INT DEFAULT 1;
  REPEAT
    SET @stmt = CONCAT(
      'CREATE VIEW v_27011 AS (SELECT * FROM t_27011 WHERE id = ',
       item, ')');
    SELECT @stmt;
    PREPARE stmt1 FROM @stmt;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    SELECT * FROM v_27011;
    DROP VIEW v_27011;
    SET item = item + 1;
  UNTIL item > 3
  END REPEAT;
END|

DELIMITER ;|

# Debugging helper, written to investigate
SET SESSION debug="d,DEBUG_execute_command";

call proc_27011();

drop procedure proc_27011;

Analysis

In each iteration in the loop,
- the prepared statement is executed correctly
- the view is created correctly, as seen by inspecting the view .frm file
- the function mysql_make_view() is called each time, to build the view
from the .frm file

So, so far the code behavior is correct.

Where things diverge are inside mysql_execute_command:

1)
The first time, the field:
thd->lex->select_lex->where
is null

Below is a debug trace for it:

##################################################################
OBJECT: THD
  - THD::query = CREATE VIEW v_27011 AS (SELECT * FROM t_27011 WHERE id = 1)
OBJECT: LEX
  - LEX::sql_command = SQLCOM_CREATE_VIEW
##################################################################
##################################################################
OBJECT: THD
  - THD::query = DEALLOCATE PREPARE stmt1
OBJECT: LEX
  - LEX::sql_command = SQLCOM_DEALLOCATE_PREPARE
##################################################################
##################################################################
OBJECT: THD
  - THD::query = SELECT * FROM v_27011
OBJECT: LEX
  - LEX::sql_command = SQLCOM_SELECT
  - LEX::describe = FALSE
  - LEX::select_lex
    OBJECT: SELECT_LEX
      - SELECT_LEX::db =
      - SELECT_LEX::where
        NULL
##################################################################

This is expected, since "select * from <view>" does not have a where clause.

2) On subsequent calls,
the field
thd->lex->select_lex->where

contain a where clause that appear to be a left over from the first execution:

##################################################################
OBJECT: THD
  - THD::query = CREATE VIEW v_27011 AS (SELECT * FROM t_27011 WHERE id = 2)
OBJECT: LEX
  - LEX::sql_command = SQLCOM_CREATE_VIEW
##################################################################
##################################################################
OBJECT: THD
  - THD::query = DEALLOCATE PREPARE stmt1
OBJECT: LEX
  - LEX::sql_command = SQLCOM_DEALLOCATE_PREPARE
##################################################################
##################################################################
OBJECT: THD
  - THD::query = SELECT * FROM v_27011
OBJECT: LEX
  - LEX::sql_command = SQLCOM_SELECT
  - LEX::describe = FALSE
  - LEX::select_lex
    OBJECT: SELECT_LEX
      - SELECT_LEX::db =
      - SELECT_LEX::where
        OBJECT: Item
          - Item::type() = FUNC_ITEM
          - Item::print() = (`test`.`t_27011`.`id` = 1)
          - Item::rsize = 0
          - Item::str_value =
          - Item::name =
          - Item::orig_name =
          - Item::max_length = 1
          - Item::name_length = 0
          - Item::marker = 0
          - Item::decimals = 0
          - Item::maybe_null = TRUE
          - Item::null_value = FALSE
          - Item::unsigned_flag = FALSE
          - Item::with_sum_func = FALSE
          - Item::fixed = FALSE
          - Item::is_autogenerated_name = TRUE
          - Item::with_subselect = FALSE
##################################################################

So, even when mysql_make_view() is called on the correct view,
the statement execution is tainted by the previous optimizations,
which leads to the result seen.

It is unknown is the where clause is the only difference here,
more analysis is needed to compare the complete SELECT_LEX tree structure.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37799

ChangeSet@1.2610, 2007-11-14 17:14:40-07:00, malff@lambda.hsd1.co.comcast.net. +7 -0
  Bug#27011 (Problem with prepared statement in iterations)
  
  NOT TO PUSH -- Debugging code to help investigation only, not a fix.
  
  This patch provide some very raw and brute force print utility,
  that was used to print the LEX structure during statement execution,
  in execute_command().
  
  Provided as-is to help developers to investigate, not a finished utility.

I've stumbled upon this issue while working on Bug#32890. The test case can be further reduce to:

create view v1 as select 1;
prepare bar from "select * from v1";
execute bar;
drop view v1;
# not strictly necessary, ps execution would succeed anyway
create view v1 as select 2;
execute bar;

The problem is that views opened inside SP/PS are only opened once during the execution.

Expanding my previous comment, this only happens inside prepared statements (SP) because the view is "miscached" after it's first usage, all subsequent executions of the PS will use the definition of the "cached" view, no matter if the view was dropped or modified.

This bug is similar to issues found with Prepared Statements,
but affects statements inside a stored procedure.

In the reported case,
- the prepared statements execute as expected, without error or bad data,
- the statement that fails is "SELECT * FROM myview;", which is not prepared.

Noted in 5.6.6 changelog.

Bad data could be returned from a view used within a stored program
if the view definition was changed between executions of the program
or while the view was used within a program loop.