MySQL Bugs: #26676: VIEW using old table schema in a session

Bug #26676	VIEW using old table schema in a session
Submitted:	27 Feb 2007 10:15	Modified:	18 Dec 2007 4:38
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.34, 5.1 BK, 5.2-falcon	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
executing a Stored Procedure after issuing an ALTER TABLE statement returns result from before that ALTER TABLE

How to repeat:
use test;
drop table if exists t1 ;
drop procedure if exists testpr;
drop view if exists testv;
create table `test`.`t1` (  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT , `t` 

varchar (50) , PRIMARY KEY ( `id`));
insert into `t1`(`id`,`t`) values ( NULL,'a');
insert into `t1`(`id`,`t`) values ( NULL,'b');
delimiter |
CREATE PROCEDURE `test`.`testpr`() BEGIN select * from t1; END|
delimiter ;
CREATE VIEW `test`.`testv` AS (SELECT * FROM t1) ;
call testpr();
select * from testv;
alter table `test`.`t1` add column `t2` varchar (50)  NULL  after `t`;
call testpr();
select * from testv;

/* returns for both

    id  t     
------  ------
     1  a     
     2  b     

whereas */ 

use test;
drop table if exists t1 ;
drop procedure if exists testpr;
drop view if exists testv;
create table `test`.`t1` (  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT , `t` 

varchar (50) , PRIMARY KEY ( `id`));
insert into `t1`(`id`,`t`) values ( NULL,'a');
insert into `t1`(`id`,`t`) values ( NULL,'b');
delimiter |
CREATE PROCEDURE `test`.`testpr`() BEGIN select * from t1; END|
delimiter ;
CREATE VIEW `test`.`testv` AS (SELECT * FROM t1) ;
-- call testpr();
-- select * from testv;
alter table `test`.`t1` add column `t2` varchar (50)  NULL  after `t`;
call testpr();
select * from testv;

/* returns for the View

    id  t     
------  ------
     1  a     
     2  b     

and for the procedure

    id  t       t2    
------  ------  ------
     1  a       (NULL)
     2  b       (NULL)

However after the ALTER TABLE you can open another client instance and the change has immediately effect here - both the VIEW and the SP returns three rows in the other client!!

*/

Suggested fix:
Any ALTER TABLE should immediately have effect - also from the client where it is executed. 

This must be some caching issue, but I cannot figure out where it happens, but I guess it is in the server. This behaviour is the same for 'mysql' command line client and 3rd party client SQLyog (that is compiled with the MySQL C-API).

Let me add that the table was (as per default) created as INNODB in the database.

Thank you for the report.

Verified as described.

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

After investigations, the issue can be narrowed down to views only:

--disable_warnings
drop table if exists t_26676;
drop view if exists v_26676;
drop view if exists v_26676_b;
--enable_warnings

create table t_26676 (
  id bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  t varchar (50),
  PRIMARY KEY(id));

insert into t_26676(id, t) values ( NULL, 'a');
insert into t_26676(id, t) values ( NULL, 'b');

create view v_26676 as (select * from t_26676) ;

# Works as expected
select * from v_26676;

alter table t_26676 add column `t2` varchar (50) NULL after `t`;

# Table is ok
select * from t_26676;

# broken, uses the old schema
select * from v_26676;

flush tables;

# broken, uses the old schema
select * from v_26676;

alter table t_26676 drop column `t`;

# broken, uses the old schema and fails since `t` can not be resolved
--error 1356
select * from v_26676;

create view v_26676_b as (select * from t_26676) ;

# works as expected
select * from v_26676_b;

drop view v_26676;
drop view v_26676_b;
drop table t_26676;

Re verified for 5.0.52, 5.1.23, 6.0.4

Marc, what you've just found is a different bug it is related to '*' expansion.

You can verify it by issuing SHOW CREATE VIEW. It will print a list of fully-qualified column names instead of '*' in the view definition:

mysql> show create view v_26676\G
*************************** 1. row ***************************
                View: v_26676
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`kostja`@`localhost` SQL SECURITY DEFINER VIEW `v_26676` AS (select `t_26676`.`id` AS `id`,`t_26676`.`t` AS `t` from `t_26676`)
character_set_client: latin1
collation_connection: latin1_swedish_ci

It is very likely that there is a duplicate of this bug closed with 'To be fixed later', hard to believe this hasn't been spotted before.

The bug should get fixed along wiht Bug#30217. 
Meanwhile I suggest that you use views without '*' in your experiments.

This is not a bug. Standard SQL is quite clear that,
if new columns are added to a table after a view on
that table is created with "select *", the new columns
will not become part of the view. In all cases, the 
view definition is frozen at CREATE time, so changes
to the underlying tables do not affetc the view
definition.

Trudy, thank you for the standard perspective, but it is still the bug, at least in our documentation.
Alik: please add a test case with the clarification above, and set the bug to 'Verified, Server: Documentation'.
Thank you.

Trudy, thinking more of it, what's the standard semantics if one of the columns is altered then?

I totally agree that clarification is required!!

And BTW if a VIEW is 'frozen' at CREATE time there is a bug with VIEWS and STORED FUNCTIONS!  See example below!

DELIMITER |
CREATE FUNCTION `test7`.`testfc`() RETURNS integer
BEGIN
return 7;
END |
DELIMITER ;

DROP VIEW IF EXISTS `test7`.`testview`;
CREATE VIEW `test7`.`testview` AS select testfc();
SELECT * FROM testview; -- returns 7

DROP function testfc;
DELIMITER |
CREATE FUNCTION `test7`.`testfc`() RETURNS integer
BEGIN
return 9;
END |
DELIMITER ;

SELECT * FROM testview; -- returns 9

Probably I should not have used the word 'bug'. It is structure that gets 'frozen' not data.  But also this needs clarification!

But 'frozen at create' time would in my opinion then mean that also the function code at create time would be valid for ever inside the view!

According to the discussion with Trudy and Peter this is not a bug.
"SELECT *" in views should be expanded at CREATE time.
Also, the standard does not prohibit further table alterations.

A test case pushed into 5.1-runtime.

A clarification in the manual is requested.

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

This is a documentation problem so I am recategorizing as Server:Documentation and assigning to myself.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated CREATE VIEW section with the information in Trudy's comment about views being frozen at creation time.