Bug #110672 QUOTE function doesn't work as expected on tables that are utf16

• Submitted: 12 Apr 2023 21:23
• Modified: 18 Apr 2023 16:02
• Reporter: Victor Rodriguez
• Status: Closed
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[12 Apr 2023 21:23] Victor Rodriguez

Description:
CREATE TABLE LATIN1_TABLE (
  COLUMN1 varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO LATIN1_TABLE (COLUMN1) VALUES (NULL);
INSERT INTO LATIN1_TABLE (COLUMN1) VALUES ('NOTNULL');

SELECT QUOTE(COLUMN1) FROM LATIN1_TABLE; -- returns NULL, and 'NOTNULL' as expected.

CREATE TABLE UTF16_TABLE (
  COLUMN1 varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

INSERT INTO UTF16_TABLE (COLUMN1) VALUES (NULL);
INSERT INTO UTF16_TABLE (COLUMN1) VALUES ('NOTNULL');

SELECT QUOTE(COLUMN1) FROM UTF16_TABLE; -- returns Japanese/Mojibake and 'NOTNULL'.

How to repeat:
CREATE TABLE LATIN1_TABLE (
  COLUMN1 varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO LATIN1_TABLE (COLUMN1) VALUES (NULL);
INSERT INTO LATIN1_TABLE (COLUMN1) VALUES ('NOTNULL');

SELECT QUOTE(COLUMN1) FROM LATIN1_TABLE; -- returns NULL, and 'NOTNULL' as expected.

CREATE TABLE UTF16_TABLE (
  COLUMN1 varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

INSERT INTO UTF16_TABLE (COLUMN1) VALUES (NULL);
INSERT INTO UTF16_TABLE (COLUMN1) VALUES ('NOTNULL');

SELECT QUOTE(COLUMN1) FROM UTF16_TABLE; -- returns Japanese/Mojibake and 'NOTNULL'.

Suggested fix:
Make QUOTE function work as expected regardless of the charset of the table.

[13 Apr 2023 6:54] MySQL Verification Team

Hello Victor Rodriguez,

Thank you for the report and test case.

regards,
Umesh

[17 Apr 2023 13:11] huahua xu

hi Victor Rodriguez，  The patch would be helpful to you.

Attachment: quote_function_null_on_multibyte_charset.patch (application/octet-stream, text), 492 bytes.

[17 Apr 2023 16:34] Victor Rodriguez

Thanks huahua xu!  We use Amazon RDS.  Do you have any idea how often Amazon patches their systems?  Also, locally we use mysql docker images.  Can you suggest how I could apply this patch to our local docker images?  Thanks!

[17 Apr 2023 16:37] Victor Rodriguez

huahua xu, fyi, our Amazon RDS environments are on 5.6.10 and 5.7.12...

[18 Apr 2023 16:02] Christine Cole

Posted by developer:
 
Fixed as of the upcoming MySQL 8.1.0 release, and here's the proposed changelog entry from the documentation team:

The Quote() function returned unexpected results with columns selected
from a table having the utf16 character set.

Thank you for the bug report.