Bug #116861 prepare statement with underscore_charset report error

• Submitted: 4 Dec 2024 2:12
• Modified: 6 Dec 2024 2:01
• Reporter: zkong kong
• Status: Can't repeat
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 9.1.0
• OS: Any
• CPU Architecture: Any

[4 Dec 2024 2:12] zkong kong

Description:
Ps with underscore charset report syntax error

How to repeat:
CREATE TABLE `t` (
  `id` int NOT NULL,
  `c` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

mysql> select * from t where c=_latin1'01';
Empty set (0.00 sec)

mysql> prepare stmt from 'select * from t where c=_latin1?';
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 '?' at line 1

Suggested fix:
add the grammer of param_marker:
  | UNDERSCORE_CHARSET PARAM_MARKER

[4 Dec 2024 10:39] MySQL Verification Team

Hi Mr. kong,

Thank you for your bug report.

However, we can not repeat it ......

Here is the command that worked just fine for us :

prepare stmt from "select * from t where c = _latin1 '?'";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Can't repeat.

[5 Dec 2024 1:52] zkong kong

Hi:
 '?' is constant, not param holder.
 please verify again, thx!

[5 Dec 2024 10:36] MySQL Verification Team

Hi Mr. kong,

If '?' is a constant, then you are not using introducers properly.

[6 Dec 2024 2:01] zkong kong

Hi:
 just want to prepare this sql: select * from t where c=_latin1'01';
 let the '01' to be a var, The usage is normal ?

[6 Dec 2024 10:59] MySQL Verification Team

Hi Mr. kong,

No, that is not a normal way of using introducers.

This is all explained in our Reference Manual.