[MDEV-15960] Wrong data type on CREATE..SELECT char_or_enum_or_text_spvar - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.3.7
Component/s: Data types, Stored routines
Labels:
None

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.

Attachments

Issue Links

blocks

MDEV-4912 Data type plugin API version 1

Closed

relates to

MDEV-13232 Assertion `(&(&share->intern_lock)->m_mutex)->count > 0 && pthread_equal(pthread_self(), (&(&share->intern_lock)->m_mutex)->thread)' failed in _ma_state_info_write

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2018-04-21 19:40 - edited

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.

Alexander Barkov added a comment - 2018-04-21 19:40 - edited 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.

Alexander Barkov added a comment - 2018-04-22 10:45 - edited

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)

Alexander Barkov added a comment - 2018-04-22 10:45 - edited 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)

Alexander Barkov added a comment - 2018-04-22 10:53

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).

MariaDB Server

Wrong data type on CREATE..SELECT char_or_enum_or_text_spvar

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration