phpmyadmin/sql-parser

GROUP BY modifier WITH ROLLUP is treated as a syntax error and prevents export of SQL query results

njandreasson opened this issue · 2 comments

njandreasson commented

Describe the bug

An SQL query using a GROUP BY .. WITH ROLLUP modifier to add extra summary rows is treated as a syntax error.
Running the query will work but if you try to export the query results as PDF, CSV etc the query will be cut from the point where WITH ROLLUP exists in the SQL query.

To Reproduce

Steps to reproduce the behavior:

  1. Go to an SQL query tab
  2. Write any query including GROUP BY .. WITH ROLLUP. It doesn't matter if table exists or not as it seems to be a SQL parser error. Example:
SELECT a, SUM(b)
FROM table_name
GROUP BY a WITH ROLLUP
  1. A red warning symbol will be shown next to the line number on the left. Keyword WITH will be underlined in red and if you hover it an error appears saying "A new statement was found, but no delimiter between it and the previous one. (near WITH)"
  2. If the query is valid, you will be able to execute it correctly. However, if you try to export the query results as PDF, CSV etc it will not work as SQL query will be cut at the point where WITH ROLLUP is in the query.

Expected behavior

WITH ROLLUP is a valid GROUP BY modifier (see https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html) since a very long time ago (added in MySQL 4.1.1, i.e. 1st of December 2003!) and should not result in a parse error.

Screenshots

phpMyAdmin_WITH_ROLLUP_syntax_error

Server configuration

  • phpMyAdmin version: 5.2.0 and 5.3.0-dev (latest Git revision 3d6e6de from master branch, committed on Jan 24, 2023)

Client configuration

  • Browser: Google Chrome 109.0.5414.120
  • Operating system: Windows 10
williamdes commented

Thank you for reporting this issue, I transferred this here

niconoe- commented

I made a proposal of a fix in #417

  • 👍2