Support new 8.0 functions

[derekperkins]

JSON

• JSON_TABLE(expr, path COLUMNS column_list) [AS] alias)
• Added the ->> (inline path) operator, which is equivalent to calling JSON_UNQUOTE() on the result of JSON_EXTRACT()
• JSON_ARRAYAGG(col_or_expr)
• JSON_OBJECTAGG(key, value)
• JSON_PRETTY(json_val)
• JSON_STORAGE_SIZE(json_val)
• JSON_STORAGE_FREE(json_val)
• JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
• JSON_MERGE() function has been renamed JSON_MERGE_PRESERVE(). JSON_MERGE() continues to be recognized as an alias for JSON_MERGE_PRESERVE()
• Added support in MySQL 8.0.2 for ranges such as $[1 to 5] in XPath expressions. Also added support in this version for the last keyword and relative addressing, such that $[last] always selects the last (highest-numbered) element in the array and $[last-1] the next to last element. last and expressions using it can also be included in range definitions; for example, $[last-2 to last-1] returns the last two elements but one from an array.

Window

• CUME_DIST() over_clause
• DENSE_RANK() over_clause
• FIRST_VALUE(expr) [null_treatment] over_clause
• LAG(expr [, N[, default]]) [null_treatment] over_clause
• LAST_VALUE(expr) [null_treatment] over_clause
• LEAD(expr [, N[, default]]) [null_treatment] over_clause
• NTH_VALUE(expr, N) [from_first_last] [null_treatment] over_clause
• NTILE(N) over_clause
• PERCENT_RANK() over_clause
• RANK() over_clause
• ROW_NUMBER() over_clause

Regular Expressions

• REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])
• REGEXP_LIKE(expr, pat[, match_type])
• REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
• REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

WITH Syntax (Common Table Expressions)

• This feels like a bigger change rather than just adding function parsing support https://dev.mysql.com/doc/refman/8.0/en/with.html

[derekperkins]

related to #8604

[andyli]

Just found another syntax not listed above not supported by vitess. It's JSON_VALUE( ..., RETURNING ...).

For example,

SELECT
	*
FROM
	`tgMessage`
WHERE
	(JSON_VALUE(`tgMessage`.`updateData`, '$.message.from.id' RETURNING SIGNED) = 807297799)
LIMIT 1

Result in

SQL Error [1105] [HY000]: syntax error at position 115 near 'SIGNED'

[GuptaManan100]

@derekperkins I can provide an update on this now. As part of this LFX term, these were some of the things that we @K-Kumar-01 worked on. I merged last of his PRs today. Now we have parsing support for JSON functions, window functions and regex functions! The common table expressions are still outstanding, though. And you are right, for common table expressions, it is not just the parser that needs to change, corresponding planning support would also be required.

Edit - We have parsing for common table expressions too which was added in #8918, but the planner rejects these queries explicitly