MySQL Bugs: #57178: MySql Workbench flags WITH ROLLUP as error

Bug #57178	MySql Workbench flags WITH ROLLUP as error
Submitted:	1 Oct 2010 17:07	Modified:	20 Jan 2012 8:01
Reporter:	Chimdi Azubuike	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.28, 5.2.34	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	WITH ROLLUP ERROR CODE 1064

Description:
When executing a query with the command WITH ROLLUP, Mysql Workbench gives an error code: 1064.

How to repeat:
Create a query using GROUP BY eg. grouping the population of cats in 50 states. Then use WITH ROLLUP

GROUP BY state WITH ROLLUP.

The resulting error will appear.

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 '' at line x

Please, send complete SELECT statement used and CREATE TABLE statement(s) for the table(s) used.

select user, host, count(*) from mysql.user group by user, host with rollup;

work OK for me, for example...

Note that i get a red underline when I use the SYNTAX "WITH" . This occurs even before I run the script.

SELECT csf_account_type, COUNT(csf_account_no)
FROM csf_data_test
GROUP BY csf_account_type WITH ROLLUP;

CREATE TABLE `csf_data_test` (
  `csf_dataid` int(11) NOT NULL auto_increment,
  `csf_client_name` varchar(255) default NULL,
  `csf_billing_number` int(11) default NULL,
  `csf_account_no` int(11) default NULL,
  `csf_account_type` varchar(255) default NULL,
  `csf_account_status` varchar(255) default NULL,
  `csf_comm_start_date` varchar(255) default NULL,
  `csf_service_id` varchar(255) default NULL,
  `csf_service_name` varchar(255) default NULL,
  `csf_period_paid` varchar(255) default NULL,
  `csf_period` varchar(255) default NULL,
  `csf_payment_name` varchar(255) default NULL,
  `csf_service_price` varchar(255) default NULL,
  `csf_rate` varchar(255) default '0.1000',
  `csf_chbk` varchar(255) default '1.0000',
  PRIMARY KEY  (`csf_dataid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Verified just as described in the last comment.

I just want to point out that this isn't just affecting OS X, I have the same on Windows 7 so I'm guessing it's across all platforms

I tested with WB 5.2.30, same result. Test query:
select current_date from (select 1)a group by current_date with rollup
Editing window shows syntax error. "rollup" is in black letters, so it is not recognized as a keyword.

When executing sometimes (can't reproduce) the "rollup" keyword is omitted. Then error 1064.
In most cases the query is executed correctly. But still the red cross in the editor window with red line under keyword "with".

It worked correctly in WB 5.2.26 though.

More details for testcase
SELECT CURRENT_DATE FROM (SELECT 1)a GROUP BY CURRENT_DATE WITH ROLLUP;
tested with WB 5.2.31 on MacOS.

When executing with ctrl-enter "Executed current statement" Workbench omits the last keyword "ROLLUP". Maybe it truncates at the point of the presumed syntax error. (Although there is no syntax error)
If you look at the output window you see that the statement is truncated. No wonder it creates an error.

If you execute with ctrl-shift-enter "Execute all", it runs fine and gives a result.

It returns a BLOB value. which is strange as well. This is probably bug http://bugs.mysql.com/bug.php?id=56157

"Me too" - using 5.2.31/Linux (64 bit)

A query that includes 'WITH ROLLUP' is flagged by Workbench as erroneous:

---
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 '' at line 20
---

But the same query (exactly identical, copy-pasted) executed against the server directly using the mysql CLI client works just fine and returns results.

The query in question works fine in WB if I remove WITH ROLLUP - so it's just that part that makes it blow up.

Is there any movement on this at all??

It can't be a massive problem to fix surely? And it may be "minor" and "non-critical" to some people but it seriously affects me and I'm sure I can't be alone!

Bug #59962 has been marked as duplicate of this one

Bug #59962 has a work around for this problem, that I have tried and that seems to work.

Highlight the query and use Execute (All or Selection) to run it.

Also, as stated above, queries using GROUP BY ... WITH ROLLUP will work from MySQL command line.

BTW: I am running WB 5.2.31 under Mac OS X 10.6.7 and the problem still exists in my version.

This also occurs on 5.5. "WITH" generates a red underline, but with and rollup are recognised as keywords. statement generates a 1064 error when run. Exactly the same statement works ok when using mysql command line client.

bug #61681 is a duplicate

Also present in Windows version 5.2.34 CE.

Bug #62314 was marked as a duplicate of this one.

Still a problem in 5.2.35 under Ubuntu Linux

The same problem on Windows 7.
MySQL Worbench version 5.2.33 CE, Revision 7508.

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 '' at line 1

MySQL Server/Client version 5.1.58-1 on Ubuntu.

Bug #63756 was marked as a duplicate of this one.

Fixed as of 5.2.38:

Workbench would emit an error (error code: 1064) with queries
using WITH ROLLUP.