MySQL Bugs: #17203: "sql_no_cache sql_cache" in views created from prepared statement

Bug #17203	"sql_no_cache sql_cache" in views created from prepared statement
Submitted:	7 Feb 2006 20:51	Modified:	23 Jul 2006 3:55
Reporter:	Dan Kloke (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.0.18	OS:	Linux (Linux, Windows 2000)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
using prepared statments inside of procedures to create views, the views are always created with NO_SQL_CACHE even if SQL_CACHE is specified in the creation string.

this can be seen in the SHOW CREATE VIEW for such a view, where the following "sql_no_cache sql_cache" sequence occurs:

CREATE ALGORITHM=UNDEFINED DEFINER=`me`@`%` SQL SECURITY DEFINER VIEW `poopoo` AS select sql_no_cache sql_cache `voodoo`.`a` AS `a` from `voodoo`

I am fairly certain (ok, absolutely positive) that such views have the (in this case unwanted) NO_SQL_CACHE behavior.

this makes me sad. i really need that cacheing, and i really need to use prepared statements to create some particular views from end-user specifications, as part of my application.

by the way, it would be nice if we could use UNION with at least some forms of SHOW xxxx, as in:

show create view view_1 
union
show create view view_2;

or even something like:

create view uhoh as 
  show create view view_1 
  union 
  show create view view_2;

thanks for your attention and good work all around.

How to repeat:
DELIMITER :(
DROP PROCEDURE IF EXISTS `test`.`views_from_prepared_statements` :(
CREATE PROCEDURE `test`.`views_from_prepared_statements` ()
BEGIN
  drop table if exists `test`.`some_table`;
  create table `test`.`some_table` (a integer default 0);
  create or replace view `test`.`view_1` as select sql_cache * from `test`.`some_table`;
  set @s= 'create or replace view `test`.`view_2` as select sql_cache * from `test`.`some_table`';
  prepare stmt from @s;
  execute stmt;
  drop prepare stmt;
END :(
DELIMITER ;

# now run this from the command line:
#
# use test;
# call views_from_prepared_statements();
# show create view view_1;
# show create view view_2;

Suggested fix:
let CREATE VIEW specifications via prepared statements parse for and yield to an SQL_CACHE specifier if found.

Thank you for a bug report. Verified just as described with 5.0.19-BK (ChangeSet@1.2038.2.1, 2006-02-14 16:22:37+03:00) on Linux:

mysql> DELIMITER :(
mysql> CREATE PROCEDURE `test`.`views_from_prepared_statements` ()
    -> BEGIN
    ->   drop table if exists `test`.`some_table`;
    ->   create table `test`.`some_table` (a integer default 0);
    ->   create or replace view `test`.`view_1` as select sql_cache * from
    -> `test`.`some_table`;
    ->   set @s= 'create or replace view `test`.`view_2` as select sql_cache *
from
    '> `test`.`some_table`';
    ->   prepare stmt from @s;
    ->   execute stmt;
    ->   drop prepare stmt;
    -> END :(
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> call views_from_prepared_statements();
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show create view view_2\G
*************************** 1. row ***************************
       View: view_2
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `view_2` AS select sql_no_cache sql_cache `some_table`.`a` AS `a` from `some_table`
1 row in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

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/8260

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/8340

Pushed into 5.0-runtime

Merged into 5.0.23

Pushed into 5.0.25 and 5.1.12

Actually it's 5.0.23, not 5.0.25

Noted in 5.0.23, 5.1.12 changelogs.

Views created from prepared statements inside of stored procedures 
were created with a definition that included both SQL_CACHE and
SQL_NO_CACHE.