Bug #98521 Core client cannot send any query with commentary characters in hints

Submitted: 8 Feb 2020 11:21 Modified: 8 Nov 2024 1:34
Reporter: Kaiwang CHen (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:8.0.18, 5.7.29, 8.0.19 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[8 Feb 2020 11:21] Kaiwang CHen
Description:
The number sign(#) belongs to character class HINT_CHR_CHAR in the hint lexer, and  QB_NAME(`select#1`) is valid to name a query block in the server code. Actually mysql-test supports such writings. However, the core interactive client mysql(1) does not.

The problem is due to the command processing loop in add_line() respects single-line comments in all contexts except for that of client-side multi-line comment. However, hint comments are designed to behave like regular client-side multi-line comments.

It might be introduced by wl#8016 Parser for optimizer hints, and was not recognized by:

commit ab45d19c674341c8e44f73a6b1c5b5c39ee98f9b
Author: V S Murthy Sidagam <venkata.sidagam@oracle.com>
Date:   Wed Sep 9 23:05:09 2015 +0530

    Description:
    1. Hint commentaries are designed to behave like regular /* ... */
    commentaries:
      SELECT /*+ " */ 1; -- a valid SQL query
    However, the mysql client program waits for the closing doublequote
    character.
    2. There is a similar issue with statement delimiters like ';', ''', '`'

    Analysis:
    This regression is introduced by the changes of wl#8016. When we are parsing the input string we need to consider '+' as hint character. Which is not handled.

    Fix: Introduced a hint flag for ss_comment and while parsing if we come across "+" we are making ss_comment as hint(SSC_HINT) and reseting it when we came across '*/'.

How to repeat:
The current defective behaviors are:

mysql> explain select /*+ QB_NAME(`select#1`) */ 1;
    -> ;
ERROR 1064 (42000): 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 '/*+ QB_NAME(`select' at line 1
mysql> explain select /*+ QB_NAME(`select-- `) */ 1;
    -> ;
ERROR 1064 (42000): 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 '/*+ QB_NAME(`select' at line 1

The expected behaviors are:

mysql> explain select /*+ QB_NAME(`select#1`) */ 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------+
| Level | Code | Message                                                   |
+-------+------+-----------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select /*+ QB_NAME(`select#1`) */ 1 AS `1` |
+-------+------+-----------------------------------------------------------+
1 row in set (0.01 sec)

mysql> explain select /*+ QB_NAME(select#1) */ 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1064 | Optimizer hint syntax error near '#1) */ 1' at line 1 |
| Note    | 1003 | /* select#1 */ select 1 AS `1`                        |
+---------+------+-------------------------------------------------------+
2 rows in set (0.01 sec)

Suggested fix:
The core client should allow characters that begin a single-line comment to appear in the c-style hint comment, and let the hint parser to verify the syntax.

The fixing line might be "ss_comment == SSC_NONE" instead, if the other server-side comment should be fixed as well.

diff --git a/client/mysql.cc b/client/mysql.cc
index fde2ad60025..ad612899fc7 100644
--- a/client/mysql.cc
+++ b/client/mysql.cc
@@ -2482,6 +2482,7 @@ static bool add_line(String &buffer, char *line, size_t line_length,
       }
       buffer.length(0);
     } else if (!*ml_comment &&
+               ss_comment != SSC_HINT &&
                (!*in_string &&
                 (inchar == '#' ||
                  (inchar == '-' && pos[1] == '-' &&
[10 Feb 2020 7:02] MySQL Verification Team
Hello Kaiwang,

Thank you for the feedback and contribution!
Please ensure to re-send the patch via "contribution" tab. Otherwise we would not be able to accept it.

regards,
Umesh
[18 Feb 2020 11:29] Kaiwang CHen
Changed synopsis
[18 Feb 2020 11:29] Kaiwang CHen
Changed synopsis
[18 Feb 2020 11:32] Kaiwang CHen
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: bugfix_98521.patch (application/octet-stream, text), 1.48 KiB.

[20 Feb 2020 6:29] MySQL Verification Team
Thank you Kaiwang CHen for the contribution.
[8 Nov 2024 1:34] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Server 8.0.41, 8.4.4, and 9.2.0 releases, and here's the proposed changelog entry from the documentation team:

The mysql client did not allow using '#' or '--' inside an optimizer hint comment.

Thank you for the bug report.
[8 Nov 2024 1:40] Philip Olson
Posted by developer:
 
In addition, the release note contains this attribution:

Our thanks to Kaiwang Chen for the contribution.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.