Bug #86305 CREATE TABLE .. AS SELECT ... QUOTE() GIVE WRONG VARCHAR LENGTH

• Submitted: 12 May 2017 11:46
• Modified: 13 Oct 2017 16:34
• Reporter: Bernt Marius Johnsen
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0, 5.7, 5.6, 5.5
• OS: Any
• CPU Architecture: Any

[12 May 2017 11:46] Bernt Marius Johnsen

Description:
create table .. as select ... quote() gives wrong varchar length for all
character sets. 

For 5.7 and older, quote('a') will give varchar(4), not varchar(3) as expected.
For 8.0 the length of the column varies with the charset variables:

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 as select quote('a');
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `quote('a')` varchar(4) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 as select quote('a');
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `quote('a')` varchar(8) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t3 as select quote('a');
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t3;
+-------+-----------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `quote('a')` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

How to repeat:
set names latin1;
create table t1 as select quote('a');
show create table t1;
set names utf8;
create table t2 as select quote('a');
show create table t2;
set names utf8mb4;
create table t3 as select quote('a');
show create table t3;

[13 Oct 2017 16:34] Paul DuBois

Posted by developer:
 
Fixed in 8.0.4, 9.0.0.

An incorrect formula was used to calculate maximum length of result
strings for a few string functions: QUOTE(), AES_DECRYPT(), and
WEIGHT_STRING(). This could affect, for example, the length of
character columns created for CREATE TABLE ... AS SELECT ... QUOTE().