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

[7 Feb 2006 20:51] Dan Kloke
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.
[15 Feb 2006 13:22] Valeriy Kravchuk
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)
[26 Jun 2006 18:05] Bugs System
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
[27 Jun 2006 17:28] Bugs System
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
[28 Jun 2006 19:16] Konstantin Osipov
Pushed into 5.0-runtime
[29 Jun 2006 15:55] Konstantin Osipov
Merged into 5.0.23
[13 Jul 2006 16:13] Konstantin Osipov
Pushed into 5.0.25 and 5.1.12
[13 Jul 2006 16:14] Konstantin Osipov
Actually it's 5.0.23, not 5.0.25
[23 Jul 2006 3:55] Paul Dubois
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.