MySQL Bugs: #20245: create view with sql

Bug #20245	create view with sql_cache not working
Submitted:	3 Jun 2006 15:01	Modified:	29 Mar 2011 18:40
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.23-BK, 5.1.9	OS:	Linux (Linux, win xp sp2)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
creating a view with SQL_CACHE and using UTC_TIMESTAMP
make view insert SQL_NO_CACHE, but don't remove SQL_CACHE

How to repeat:
CREATE VIEW est_com_atu AS 
SELECT SQL_CACHE
unidade_id,centro_atividade,item_id,item_id_red,pessoa_tipo,pessoa_id,propriedade,
SUM(pecas_e-pecas_s) AS pecas,
	SUM(quant_e-quant_s) AS quant,
	SUM(pliq_e-pliq_s) AS pliq,
	SUM(pbruto_e-pbruto_s) AS pbruto,
	SUM(vbruto_e-vbruto_s) AS vbruto,
	SUM(vliq_e-vliq_s) AS vliq,
SUM(pecas_rastr_e-pecas_rastr_s) AS pecas_rastr,
	SUM(quant_rastr_e-quant_rastr_s) AS quant_rastr,
	SUM(pliq_rastr_e-pliq_rastr_s) AS pliq_rastr,
	SUM(pbruto_rastr_e-pbruto_rastr_s) AS pbruto_rastr,
	SUM(vbruto_rastr_e-vbruto_rastr_s) AS vbruto_rastr,
	SUM(vliq_rastr_e-vliq_rastr_s) AS vliq_rastr,
MAX(ultima_atualizacao) AS ultima_atualizacao,
SUM(custo_quant) AS custo_quant,SUM(custo_valor) AS custo_valor,
SUM(custo_quant_rastr) AS custo_quant_rastr,SUM(custo_valor_rastr) AS custo_valor_rastr,
custo_atual_compra,custo_atual_producao

FROM est_com_dia
WHERE 
data_gmt<=UTC_TIMESTAMP()
GROUP BY unidade_id,centro_atividade,item_id,item_id_red,pessoa_tipo,pessoa_id,propriedade

SHOW CREATE VIEW:

CREATE ALGORITHM=UNDEFINED DEFINER=`rspadim`@`%` SQL SECURITY DEFINER VIEW `est_com_atu` AS select sql_no_cache sql_cache `est_com_dia`.`unidade_id` AS `unidade_id`,`est_com_dia`.`centro_atividade` AS `centro_atividade`,`est_com_dia`.`item_id` AS `item_id`,`est_com_dia`.`item_id_red` AS `item_id_red`,`est_com_dia`.`pessoa_tipo` AS `pessoa_tipo`,`est_com_dia`.`pessoa_id` AS `pessoa_id`,`est_com_dia`.`propriedade` AS `propriedade`,sum((`est_com_dia`.`pecas_e` - `est_com_dia`.`pecas_s`)) AS `pecas`,sum((`est_com_dia`.`quant_e` - `est_com_dia`.`quant_s`)) AS `quant`,sum((`est_com_dia`.`pliq_e` - `est_com_dia`.`pliq_s`)) AS `pliq`,sum((`est_com_dia`.`pbruto_e` - `est_com_dia`.`pbruto_s`)) AS `pbruto`,sum((`est_com_dia`.`vbruto_e` - `est_com_dia`.`vbruto_s`)) AS `vbruto`,sum((`est_com_dia`.`vliq_e` - `est_com_dia`.`vliq_s`)) AS `vliq`,sum((`est_com_dia`.`pecas_rastr_e` - `est_com_dia`.`pecas_rastr_s`)) AS `pecas_rastr`,sum((`est_com_dia`.`quant_rastr_e` - `est_com_dia`.`quant_rastr_s`)) AS `quant_rastr`,sum((`est_com_dia`.`pliq_rastr_e` - `est_com_dia`.`pliq_rastr_s`)) AS `pliq_rastr`,sum((`est_com_dia`.`pbruto_rastr_e` - `est_com_dia`.`pbruto_rastr_s`)) AS `pbruto_rastr`,sum((`est_com_dia`.`vbruto_rastr_e` - `est_com_dia`.`vbruto_rastr_s`)) AS `vbruto_rastr`,sum((`est_com_dia`.`vliq_rastr_e` - `est_com_dia`.`vliq_rastr_s`)) AS `vliq_rastr`,max(`est_com_dia`.`ultima_atualizacao`) AS `ultima_atualizacao`,sum(`est_com_dia`.`custo_quant`) AS `custo_quant`,sum(`est_com_dia`.`custo_valor`) AS `custo_valor`,sum(`est_com_dia`.`custo_quant_rastr`) AS `custo_quant_rastr`,sum(`est_com_dia`.`custo_valor_rastr`) AS `custo_valor_rastr`,`est_com_dia`.`custo_atual_compra` AS `custo_atual_compra`,`est_com_dia`.`custo_atual_producao` AS `custo_atual_producao` from `est_com_dia` where (`est_com_dia`.`data_gmt` <= utc_timestamp()) group by `est_com_dia`.`unidade_id`,`est_com_dia`.`centro_atividade`,`est_com_dia`.`item_id`,`est_com_dia`.`item_id_red`,`est_com_dia`.`pessoa_tipo`,`est_com_dia`.`pessoa_id`,`est_com_dia`.`propriedade` 

Suggested fix:
remove sql_cache  from query and just show sql_no_cache

Thank you for a bug report. Verified just as described on 5.0.23-BK on Linux. Looks like this problem is repeatable on all versions that supports views:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
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 1 to server version: 5.0.23

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

mysql> create view v20245 as select sql_cache utc_timestamp();
Query OK, 0 rows affected (0.01 sec)

mysql> show create view v20245\G
*************************** 1. row ***************************
       View: v20245
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `v20245` AS select sql_no_cache sql_cache utc_timestamp() AS `utc_t
imestamp()`
1 row in set (0.00 sec)

Simplified test case:

create view v20245 as select sql_cache utc_timestamp();
show create view v20245;

not a problem. work with 5.5 without problem (remove SQL_CACHE from view, use it when select from view)