Add support for WITH clause (enhancement for syntax highlighter)

[stevenwdv]

Currently the SQL syntax highlighter marks the WITH keyword as Unrecognized statement type.
MariaDB supports a WITH clause in combination with a SELECT statement since version 10.2.1 (stable=10.2.8), see https://mariadb.com/kb/en/the-mariadb-library/with/

phpMyAdmin version: 4.7.4

Sort of relates to phpmyadmin/phpmyadmin#13615 (but not really)

[ibennetch]

'WITH' will also be a part of MySQL beginning with 8.0 [1] [2] [3]

[ibennetch]

I opened a bug against sql-parser for this: #165

[stevenwdv]

@ibennetch Ah, I looked at the MySQL 8.0 SELECT syntax but apparently it wasn't updated yet to include WITH. Sorry for reporting the bug in the wrong location!

[ibennetch]

@stevenwdv No need to be sorry, this is great. I appreciate you taking the time to let us know about it. And since MySQL 8 is still under heavy development it's not surprising that the docs didn't have that section. Sometimes I can be a bit concise and I'm just adding some information I found during my research of your report.

No one would know to report anything to sql-parser; since it's bundled in the distribution it really just looks like part of phpMyAdmin. We appreciate your report!

[stevenwdv]

@ibennetch I didn't interpret your previous comments as concise, but thanks for your nice reaction! I think i came across phpmyadmin/sql-parser some time ago, but I indeed forgot about it.

[williamdes]

Related to #165

[sturtevant]

I believe this issue is also disrupting the way phpMyAdmin result pagination works. Any query using a CTE ("WITH" clause) will return all results to the browser (even while the interface says "Showing rows 0 - 24"). This typically results in an HTTP timeout for large tables / queries.

To reproduce (any table of sufficient length will do):

SELECT id FROM mytable; -- pagination works for this query

WITH my_cte AS ( SELECT id FROM mytable ) SELECT id FROM my_cte; -- pagination doesn't work (all rows returned)

[soyuka]

Can we close this one as duplicate from #165 ?

[williamdes]

Can we close this one as duplicate from #165 ?

Now that a PR is done I would say this one can stay open