MySQL Bugs: #116222: mysql_parser_parse() gets different digest for query with trailing semicolon

Bug #116222	mysql_parser_parse() gets different digest for query with trailing semicolon
Submitted:	25 Sep 2024 10:56	Modified:	27 Jul 20:05
Reporter:	Kaiwang CHen (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	9.0.1, 8.4.2, 8.0.39	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
A query with trailing semicolon would get a different digest when it
is processed by the parser service, mysql_parser_parse(), comparing
to that by other functions taking user input such as
dispatch_command(). It would fail the match when the same query is
used both to define a rule for, say, Rewriter plugin and to run
as user input.

It is similar to Bug #115943 STATEMENT_DIGEST() adds trailing
semicolon to digest.

How to repeat:
Here the rewriter plugin is used to show the problem:

INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement )
VALUES ( 'SELECT ?;', 'SELECT "literal"' );

SELECT * FROM query_rewrite.rewrite_rules;
CALL query_rewrite.flush_rewrite_rules();
SELECT * FROM query_rewrite.rewrite_rules;

SELECT 1;

Expected Result:

mysql> SELECT * FROM query_rewrite.rewrite_rules;
+----+-----------+------------------+------------------+---------+---------+
| id | pattern   | pattern_database | replacement      | enabled | message |
+----+-----------+------------------+------------------+---------+---------+
|  5 | SELECT ?; | NULL             | SELECT "literal" | YES     | NULL    |
+----+-----------+------------------+------------------+---------+---------+
1 row in set (0.00 sec)

mysql> select 1;
+---------+
| literal |
+---------+
| literal |
+---------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------+
| Level | Code | Message                                                                    |
+-------+------+----------------------------------------------------------------------------+
| Note  | 1105 | Query 'select 1' rewritten to 'SELECT "literal"' by a query rewrite plugin |
+-------+------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

Actual Result:

mysql> SELECT * FROM query_rewrite.rewrite_rules;
+----+-----------+------------------+------------------+---------+---------+
| id | pattern   | pattern_database | replacement      | enabled | message |
+----+-----------+------------------+------------------+---------+---------+
|  4 | SELECT ?; | NULL             | SELECT "literal" | YES     | NULL    |
+----+-----------+------------------+------------------+---------+---------+
1 row in set (0.00 sec)

mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Suggested fix:
Invoke parser_state.init() with the trimmed query from alloc_query(),
just as processing user input.

See enclosed for a fix. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug_116222.patch (application/octet-stream, text), 2.83 KiB.

Hello Kaiwang,

Thank you for the report and contribution.

regards,
Umesh

Posted by developer: Bug status updated to 'Patch pending'

Posted by developer: Bug status updated to 'Documenting'

Posted by developer:
 
Added the following note to the MySQL Server 26.7.0 release notes:

Rewriter patterns containing trailing semicolons did not load 
and match consistently with query digests produced by normal statement execution. 
Our thanks to Kaiwang Chen and the team at Tencent for the contribution.