| Bug #61668 | SQL_CACHE COUNT not allowed inside a stored procedure sub query | ||
|---|---|---|---|
| Submitted: | 28 Jun 2011 10:37 | Modified: | 4 Nov 2014 3:41 |
| Reporter: | Mathieu Massebœuf | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Workbench: SQL Editor | Severity: | S3 (Non-critical) |
| Version: | 5.2.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | count, sql_cache, stored procedure, workbench | ||
[28 Jun 2011 16:38]
MySQL Verification Team
related: bug #57664 and bug #54491
[29 Jun 2011 7:37]
Valeriy Kravchuk
The fact that Workbench can not parse this syntax properly and consider it as error is a bug.
[1 Nov 2012 10:55]
Mike Lischke
This is not a bug of WB as it only reflects what the server does. The SQL_CACHE keyword is not allowed there: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(*) FROM Test); END' at line 4 so indicating this error in the editor is perfectly ok.
[1 Nov 2012 12:59]
Mathieu Massebœuf
Where did you saw the keyword was not allowed ?
I just redid that test, here is what I get :
mysql> CREATE PROCEDURE `xxxxxxx`.`p__test` () BEGIN SET @NbRecord = ( SELECT SQL_CACHE COUNT(*) FROM Test); END$$
Query OK, 0 rows affected (0.00 sec)
mysql> show create procedure p__test\G
*************************** 1. row ***************************
Procedure: p__test
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `p__test`()
BEGIN SET @NbRecord = ( SELECT SQL_CACHE COUNT(*) FROM Test); END
1 row in set (0.00 sec)
Tested on an earlier mysql version :
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2
[1 Nov 2012 14:46]
Mike Lischke
Ok, convinced. WB has to support 5.0, even though it's a very old version.
Description: The SELECT SQL_CACHE followed by COUNT is not allowed inside a sub-query inside a stored procedure. Such queries work fine and can be saved from the command line. How to repeat: Try to create the following procedure : DELIMITER $$ CREATE PROCEDURE `db`.`p__test` () BEGIN SET @NbRecord = ( SELECT SQL_CACHE COUNT(*) FROM Test); END