[MDEV-26634] Feature request: add STATEMENT_DIGEST_TEXT Created: 2021-09-17  Updated: 2023-05-01  Resolved: 2023-05-01

Status: Closed
Project: MariaDB Server
Component/s: Parser, Performance Schema
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Roel Van de Paar Assignee: Unassigned
Resolution: Duplicate Votes: 2
Labels: affects-tests, beginner-friendly

Issue Links:
Blocks
is blocked by MDEV-19569 Assertion `table_list->table' failed ... Closed
Duplicate
is duplicated by MDEV-26259 P_S functions STATEMENT_DIGEST_TEXT a... Stalled
Relates
relates to MDEV-8943 Syntax check for SQL files or SQL que... Open
relates to MDEV-28906 MySQL 8.0 desired compatibility Open
relates to MDEV-4680 PARSER - new sql function Open
relates to MDEV-6882 TOKENIZE query Open

 Description   

STATEMENT_DIGEST_TEXT is a very handy function in MySQL 8 which can be used programmatically and quickly check thousands of SQL statements for validity, without actually executing them, and returning an error on CLI-only statements:

MS 10.8 (Debug)

 
8.0.26-dbg>SELECT STATEMENT_DIGEST_TEXT('SELECT 1')G
 
 
 
*************************** 1. row ***************************
 
STATEMENT_DIGEST_TEXT('SELECT 1'): SELECT ?
 
1 row in set (0.00 sec)
 
 
 
8.0.26-dbg>SELECT STATEMENT_DIGEST_TEXT('INCORRECT QUERY')G
 
ERROR 3676 (HY000): Could not parse argument to digest function: "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 'INCORRECT QUERY' at line 1".
 
8.0.26-dbg>SELECT STATEMENT_DIGEST_TEXT('HELP')G
 
ERROR 3676 (HY000): Could not parse argument to digest function: "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".

The same function does not exist in MariaDB:

MD 10.7.0 d552e092c9f3e20da078d1b62b976f629f73d3a4 (Debug)

 
10.7.0-dbg>SELECT STATEMENT_DIGEST_TEXT('SELECT 1');
 
ERROR 1305 (42000): FUNCTION test.STATEMENT_DIGEST_TEXT does not exist

So whilst the MySQL 8 function can be used to check SQL statements for validity (via yes/no error), MariaDB specific syntax will not be recognized and thus using MySQL 8 for SQL verification is not very helpful.

Implementing the same would help for testing.

 Comments   
Comment by Sergei Golubchik [ 2022-02-21 ]

Roel, can you use PREPARE instead?

MariaDB [test]> PREPARE p FROM 'SELECT 1';
 
Query OK, 0 rows affected (0.002 sec)
 
Statement prepared
 
MariaDB [test]> PREPARE p FROM 'INCORRECT QUERY';
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INCORRECT QUERY' at line 1
 
MariaDB [test]> PREPARE p FROM 'HELP';
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

Comment by Roel Van de Paar [ 2022-03-03 ]

serg Fantastic, thank you very much. This seems to work better, and easier.

Comment by Roel Van de Paar [ 2022-03-03 ]

Perhaps, when/if we implement this feature, this is also an easy way to work it into the code, by using a temporary prepare statement?

Comment by Roel Van de Paar [ 2022-03-03 ]

Notes to self

sed "s|'|\\\'|g;s|^|PREPARE p FROM '|;s|$|';|" spider_se_2.sql > test.sql

And test_pquery (~/start) was updated to log errors.

One issue is that PREPARE will still crash on single-line crashing queries like the one in MDEV-19569. It would be hard to filter all such queries from the source files.

Next; writing scripting to automate the lot & restart instance when crashes are observed (line-by-line execution and immediate classification into buckets).

Comment by Daniel Black [ 2022-12-06 ]

maxscale has the feature if its portable.

Comment by Weijun Huang [ 2023-04-10 ]

Does this still need to be completed? If so, I want to work on it.

Comment by Daniel Black [ 2023-04-11 ]

Also has some good application in assisting tooling to normalize like queries with different constants. So sure Weijun Huang, love to see an implementation.

Comment by Roel Van de Paar [ 2023-04-24 ]

Thank you Weijun Huang, I too am looking forward to your work.

Generated at Thu Feb 08 09:46:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.