[MDEV-15960] Wrong data type on CREATE..SELECT char_or_enum_or_text_spvar Created: 2018-04-21  Updated: 2018-04-22  Resolved: 2018-04-22

Status: Closed
Project: MariaDB Server
Component/s: Data types, Stored routines
Affects Version/s: 10.1, 10.2, 10.3
Fix Version/s: 10.3.7

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-4912 Data type plugin API version 1 Closed
Relates
relates to MDEV-13232 Assertion `(&(&share->intern_lock)->m... Closed

 Description    

DROP TABLE IF EXISTS t1;
 
DELIMITER $$
 
BEGIN NOT ATOMIC
 
  DECLARE var TEXT CHARACTER SET utf8;
 
  CREATE TABLE t1 AS SELECT var;
 
END;
 
$$
 
DELIMITER ;
 
SHOW CREATE TABLE t1;


+-------+---------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                  |
 
+-------+---------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `var` mediumtext CHARACTER SET utf8 DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+---------------------------------------------------------------------------------------------------------------+

Notice, the column data type in the table does not match the variable data type.
The expected data type for t1.var should be TEXT CHARACTER SET utf8 rather than MEDIUMTEXT CHARACTER SET utf8.

 Comments   
Comment by Alexander Barkov [ 2018-04-21 ]

The problem happens in adjust_max_effective_column_length(). It's supposed to adjust the maximum length for the integer data types according to their capacity (instead of the user-specified length), e.g. convert int(3) to int(11).

However, the code also erroneously adjusts the length for the blob data types, because their max_display_length() returns something bigger than max_length, which was previously set to char_length() by Type_std_attributes::set(). So later Type_handler::blob_type_handler() converts a wrong (too large) length to a wrong (longer) BLOB variant.

Another option would be to fix Item_splocal::create_field_for_create_select() from:

   Field *create_field_for_create_select(TABLE *table)
 
   { return tmp_table_field_from_field_type(table); }

to:

   Field *create_field_for_create_select(TABLE *table)
 
  { return create_table_field_from_handler(table); }

This will make sure preserve the data type of the variable.

Comment by Alexander Barkov [ 2018-04-22 ]

The same problem:

I start mysql --column-type-info test an run this script:

DELIMITER $$
 
BEGIN NOT ATOMIC
 
  DECLARE a ENUM('a') DEFAULT 'a';
 
  SELECT a;
 
END;
 
$$


Field   1:  `a`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       STRING
 
Collation:  utf8_general_ci (33)
 
Length:     3
 
Max_length: 1
 
Decimals:   0
 
Flags:      ENUM

Notice, the variable reports itself STRING (that is CHAR). Ok.

Now I use a ENUM column in a UNION with a CHAR column:

CREATE OR REPLACE TABLE t1 (a CHAR(1), b ENUM('a'));
 
CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1;
 
DESC t2;


+-------+---------+------+-----+---------+-------+
 
| Field | Type    | Null | Key | Default | Extra |
 
+-------+---------+------+-----+---------+-------+
 
| a     | char(1) | YES  |     | NULL    |       |
 
+-------+---------+------+-----+---------+-------+

Notice, the ENUM column worked as CHAR and the result column t2.a is also of the type CHAR. This is also OK.

Now I use a ENUM variable in a UNION with a CHAR column

DELIMITER $$
 
BEGIN NOT ATOMIC
 
  DECLARE b ENUM('b') DEFAULT 'b';
 
  CREATE OR REPLACE TABLE t1 (a CHAR(1));
 
  CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT b;
 
  DESC t2;
 
END;
 
$$


+-------+---------+------+-----+---------+-------+
 
| Field | Type    | Null | Key | Default | Extra |
 
+-------+---------+------+-----+---------+-------+
 
| a     | char(1) | YES  |     | NULL    |       |
 
+-------+---------+------+-----+---------+-------+

Notice, the ENUM variable worked as CHAR and the result column t2.a is also of the type CHAR. This is also OK.

But if I now create a table from an ENUM variable alone, without a UNION, it works as VARCHAR:

DELIMITER $$
 
BEGIN NOT ATOMIC
 
  DECLARE a ENUM('a') DEFAULT 'a';
 
  CREATE OR REPLACE TABLE t1 AS SELECT a;
 
  DESC t1;
 
END;
 
$$


+-------+------------+------+-----+---------+-------+
 
| Field | Type       | Null | Key | Default | Extra |
 
+-------+------------+------+-----+---------+-------+
 
| a     | varchar(1) | YES  |     | NULL    |       |
 
+-------+------------+------+-----+---------+-------+

This is wrong. It should create at least a column of the char(1) data type.
(eventually it could be fixed to even create an ENUM column)

Comment by Alexander Barkov [ 2018-04-22 ]

The same problem is repeatable with a CHAR variable:

DELIMITER $$
 
BEGIN NOT ATOMIC
 
  DECLARE a CHAR(1) DEFAULT 'a';
 
  CREATE OR REPLACE TABLE t1 AS SELECT a;
 
  DESC t1;
 
END;
 
$$


+-------+------------+------+-----+---------+-------+
 
| Field | Type       | Null | Key | Default | Extra |
 
+-------+------------+------+-----+---------+-------+
 
| a     | varchar(1) | YES  |     | NULL    |       |
 
+-------+------------+------+-----+---------+-------+

The expected column data type should be char(1).

Generated at Thu Feb 08 08:25:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.