[MDEV-4215] Incorrect enum values returned when using union after "create as select" Created: 2013-02-28  Updated: 2021-03-01

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.1, 5.5.29, 5.1.67, 5.2.14, 5.3.12, 10.3.4, 10.5.9
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Pavel Ivanov Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: upstream-fixed

Attachments: File MDEV-4215.sql    

 Description   

Consider the following terminal session demonstrating the bug:

MariaDB [(none)]> create database d1;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [(none)]> use d1;
 
Database changed
 
MariaDB [d1]> create table t1 (e enum('one', 'two'));
 
Query OK, 0 rows affected (0.75 sec)
 
 
 
MariaDB [d1]> insert into t1 values ('one');
 
Query OK, 1 row affected (0.40 sec)
 
 
 
MariaDB [d1]> create table t2 (e enum('one', 'two')) as select * from t1;
 
Query OK, 1 row affected (0.78 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [d1]> select * from t2;
 
+------+
 
| e    |
 
+------+
 
| one  |
 
+------+
 
1 row in set (0.01 sec)
 
 
 
MariaDB [d1]> select * from t2 union all select * from t2;
 
+------+
 
| e    |
 
+------+
 
| o    |
 
| o    |
 
+------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [d1]> alter table t2 engine MyISAM;
 
Query OK, 1 row affected (0.91 sec)                
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [d1]> select * from t2 union all select * from t2;
 
+------+
 
| e    |
 
+------+
 
| one  |
 
| one  |
 
+------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [d1]>

This bug exists in MySQL 5.1 too. It seems that the following one-liner fixes the problem but I'm not sure if it can break something else.

--- a/sql/sql_table.cc
 
+++ b/sql/sql_table.cc
 
@@ -3144,7 +3144,7 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
 
          sql_field->charset=           (dup_field->charset ?
 
                                         dup_field->charset :
 
                                         create_info->default_table_charset);
 
-         sql_field->length=            dup_field->char_length;
 
+         sql_field->length=            dup_field->length;
 
           sql_field->pack_length=      dup_field->pack_length;
 
           sql_field->key_length=       dup_field->key_length;
 
          sql_field->decimals=          dup_field->decimals;



 Comments   
Comment by Pavel Ivanov [ 2013-02-28 ]

It looks like http://bugs.mysql.com/bug.php?id=14139 is relevant and the actual fix should look differently.

Comment by Elena Stepanova [ 2013-03-05 ]

(The priority has been decreased because it's an upstream bug, reproducible on all of MySQL 5.1-5.6).

Hi Pavel,

Since you mentioned that it was reproducible on MySQL – have you already filed it for MySQL, or are you planning to?
If you have, could you please provide the bug number?
If you haven't and aren't planning to, we can do it on your behalf.

Comment by Pavel Ivanov [ 2013-03-05 ]

I haven't filed and am not planning to. Feel free to file yourself.

Comment by Elena Stepanova [ 2013-03-05 ]

Filed as http://bugs.mysql.com/bug.php?id=68577

Comment by Daniel Black [ 2017-12-30 ]

FWIW I tried pivanof first patch and despite fixing this bug it did reintroduce http://bugs.mysql.com/bug.php?id=14139 (which isn't in the mtr).

Packaging up and a little debugging:

mysql -S /tmp/s.sock -u root -v --table < /tmp/MDEV-4215.sql 
--------------
 
create database if not exists d1
 
--------------
 
 
 
--------------
 
drop table t1
 
--------------
 
 
 
--------------
 
create table if not exists t1 (e enum('one', 'two'))
 
--------------
 
 
 
--------------
 
insert ignore into t1 values ('one')
 
--------------
 
 
 
--------------
 
drop table if exists t2
 
--------------
 
 
 
--------------
 
create table t2 (e enum('one', 'two')) as select * from t1
 
--------------
 
 
 
--------------
 
select * from t2
 
--------------
 
 
 
+------+
 
| e    |
 
+------+
 
| one  |
 
+------+
 
--------------
 
select * from t2 union all select * from t2
 
--------------
 
 
 
+------+
 
| e    |
 
+------+
 
|      |
 
|      |
 
+------+
 
--------------
 
alter table t2 engine MyISAM
 
--------------
 
 
 
--------------
 
select * from t2 union all select * from t2
 
--------------
 
 
 
+------+
 
| e    |
 
+------+
 
| one  |
 
| one  |
 
+------+

GDB where all the fields are copied - 10.3 at 7a66e0ab8f52f3bd32850463daa05f9a2401e6b1

(gdb) break Column_definition::redefine_stage1_common
 
Breakpoint 1 at 0x55ae087e6630: file /home/dan/software_projects/mariadb-server/sql/sql_type.h, line 2860.
 
(gdb) c
 
Continuing.
 
[New Thread 0x7ff32133f700 (LWP 14069)]
 
[Switching to Thread 0x7ff32133f700 (LWP 14069)]
 
 
 
Thread 31 "mysqld" hit Breakpoint 1, Column_definition::redefine_stage1_common (this=this@entry=0x7ff2a8013028, dup_field=0x7ff2a8012400, file=0x7ff2a8013130, schema=0x7ff32133d118)
 
    at /home/dan/software_projects/mariadb-server/sql/field.cc:10788
 
10788	  set_handler(dup_field->type_handler());
 
(gdb) list
 
10783	void
 
10784	Column_definition::redefine_stage1_common(const Column_definition *dup_field,
 
10785	                                          const handler *file,
 
10786	                                          const Schema_specification_st *schema)
 
10787	{
 
10788	  set_handler(dup_field->type_handler());
 
10789	  default_value= dup_field->default_value;
 
10790	  charset=       dup_field->charset ? dup_field->charset :
 
10791	                                      schema->default_table_charset;
 
10792	  length=       dup_field->char_length;
 
(gdb) print *dup_field
 
$1 = {<Sql_alloc> = {<No data fields>}, <Type_handler_hybrid_field_type> = {m_type_handler = 0x55ae09545758 <type_handler_enum>}, compression_method_ptr = 0x0, field_name = {
 
    str = 0x7ff2a8011af8 "e", length = 1}, comment = {str = 0x0, length = 0}, on_update = 0x0, length = 3, field_visibility = NOT_INVISIBLE, char_length = 0, decimals = 0, flags = 0, 
  pack_length = 1, key_length = 1, unireg_check = Field::NONE, interval = 0x7ff2a80138a0, interval_list = {<base_list> = {<Sql_alloc> = {<No data fields>}, 
      first = 0x55ae095ded40 <end_of_list>, last = 0x7ff2a8012468, elements = 0}, <No data fields>}, charset = 0x55ae095871a0 <my_charset_latin1>, srid = 0, 
  geom_type = Field::GEOM_GEOMETRY, option_list = 0x0, pack_flag = 0, vcol_info = 0x0, default_value = 0x0, check_constraint = 0x0}
 
 
 
(gdb) bt
 
#0  Column_definition::redefine_stage1_common (this=this@entry=0x7ff2a8013028, dup_field=0x7ff2a8012400, file=0x7ff2a8013130, schema=0x7ff32133d118)
 
    at /home/dan/software_projects/mariadb-server/sql/field.cc:10788
 
#1  0x000055ae087575b5 in Type_handler_typelib::Column_definition_redefine_stage1 (this=<optimized out>, def=0x7ff2a8013028, dup=<optimized out>, file=<optimized out>, 
    schema=<optimized out>) at /home/dan/software_projects/mariadb-server/sql/sql_type.cc:1758
 
#2  0x000055ae086be167 in Column_definition::redefine_stage1 (schema=0x7ff32133d118, file=0x7ff2a8013130, dup_field=0x7ff2a8012400, this=0x7ff2a8013028)
 
    at /home/dan/software_projects/mariadb-server/sql/field.h:4173
 
#3  mysql_prepare_create_table (thd=thd@entry=0x7ff2a80009a8, create_info=create_info@entry=0x7ff32133cfd0, alter_info=alter_info@entry=0x7ff32133cf20, 
    db_options=db_options@entry=0x7ff32133a458, file=file@entry=0x7ff2a8013130, key_info_buffer=key_info_buffer@entry=0x7ff32133b978, key_count=0x7ff32133b974, create_table_mode=1)
 
    at /home/dan/software_projects/mariadb-server/sql/sql_table.cc:3501
 
#4  0x000055ae086c05df in mysql_create_frm_image (thd=thd@entry=0x7ff2a80009a8, db=db@entry=0x7ff2a8011af0 "d1", table_name=table_name@entry=0x7ff2a80114a8 "t2", 
    create_info=create_info@entry=0x7ff32133cfd0, alter_info=alter_info@entry=0x7ff32133cf20, create_table_mode=create_table_mode@entry=1, key_info=0x7ff32133b978, 
    key_count=0x7ff32133b974, frm=0x7ff32133b980) at /home/dan/software_projects/mariadb-server/sql/sql_table.cc:4641
 
#5  0x000055ae086c41ee in create_table_impl (thd=thd@entry=0x7ff2a80009a8, orig_db=orig_db@entry=0x7ff2a8011af0 "d1", orig_table_name=orig_table_name@entry=0x7ff2a80114a8 "t2", 
    db=db@entry=0x7ff2a8011af0 "d1", table_name=table_name@entry=0x7ff2a80114a8 "t2", path=path@entry=0x7ff32133b990 "./d1/t2", options=..., create_info=0x7ff32133cfd0, 
    alter_info=0x7ff32133cf20, create_table_mode=1, is_trans=0x0, key_info=<optimized out>, key_count=<optimized out>, frm=<optimized out>)
 
    at /home/dan/software_projects/mariadb-server/sql/sql_table.cc:4885
 
#6  0x000055ae086c48a8 in mysql_create_table_no_lock (thd=thd@entry=0x7ff2a80009a8, db=0x7ff2a8011af0 "d1", table_name=0x7ff2a80114a8 "t2", create_info=create_info@entry=0x7ff32133cfd0, 
    alter_info=alter_info@entry=0x7ff32133cf20, is_trans=is_trans@entry=0x0, create_table_mode=1, table_list=0x7ff2a80114e0)
 
    at /home/dan/software_projects/mariadb-server/sql/sql_table.cc:5002
 
#7  0x000055ae08628f23 in create_table_from_items (thd=0x7ff2a80009a8, create_info=0x7ff32133cfd0, create_table=0x7ff2a80114e0, alter_info=0x7ff32133cf20, 
    items=items@entry=0x7ff2a8004d78, lock=lock@entry=0x7ff32133cc98, hooks=0x7ff32133cca0) at /home/dan/software_projects/mariadb-server/sql/sql_insert.cc:4122
 
#8  0x000055ae08629268 in select_create::prepare (this=0x7ff2a80126e0, values=..., u=<optimized out>) at /home/dan/software_projects/mariadb-server/sql/sql_insert.cc:4299
 
#9  0x000055ae0868a382 in JOIN::prepare (this=0x7ff2a80127d8, tables_init=<optimized out>, wild_num=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, 
    order_init=<optimized out>, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7ff2a8004c58, unit_arg=0x7ff2a8004520)
 
    at /home/dan/software_projects/mariadb-server/sql/sql_select.cc:1028
 
#10 0x000055ae08696440 in mysql_select (thd=thd@entry=0x7ff2a80009a8, tables=0x7ff2a8011dc8, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2416184064, result=0x7ff2a80126e0, unit=0x7ff2a8004520, select_lex=0x7ff2a8004c58) at /home/dan/software_projects/mariadb-server/sql/sql_select.cc:3797
 
#11 0x000055ae086965a7 in handle_select (thd=thd@entry=0x7ff2a80009a8, lex=lex@entry=0x7ff2a8004458, result=result@entry=0x7ff2a80126e0, 
    setup_tables_done_option=setup_tables_done_option@entry=0) at /home/dan/software_projects/mariadb-server/sql/sql_select.cc:366
 
#12 0x000055ae0864e884 in mysql_execute_command (thd=thd@entry=0x7ff2a80009a8) at /home/dan/software_projects/mariadb-server/sql/sql_parse.cc:4214
 
#13 0x000055ae0864ed1d in mysql_parse (thd=0x7ff2a80009a8, rawbuf=<optimized out>, length=58, parser_state=0x7ff32133e4f0, is_com_multi=<optimized out>, is_next_command=<optimized out>)
 
    at /home/dan/software_projects/mariadb-server/sql/sql_parse.cc:7966
 
#14 0x000055ae0865185f in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7ff2a80009a8, 
    packet=packet@entry=0x7ff2a8009119 "create table t2 (e enum('one', 'two')) as select * from t1", packet_length=packet_length@entry=58, is_com_multi=is_com_multi@entry=false, 
    is_next_command=is_next_command@entry=false) at /home/dan/software_projects/mariadb-server/sql/sql_parse.cc:1824
 
#15 0x000055ae08652111 in do_command (thd=0x7ff2a80009a8) at /home/dan/software_projects/mariadb-server/sql/sql_parse.cc:1368
 
#16 0x000055ae0870c754 in do_handle_one_connection (connect=connect@entry=0x55ae0bd34e48) at /home/dan/software_projects/mariadb-server/sql/sql_connect.cc:1420
 
#17 0x000055ae0870c8c4 in handle_one_connection (arg=0x55ae0bd34e48) at /home/dan/software_projects/mariadb-server/sql/sql_connect.cc:1326
 
#18 0x00007ff32883173a in start_thread (arg=0x7ff32133f700) at pthread_create.c:333
 
#19 0x00007ff3266f3e7f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Comment by Ian Gilfillan [ 2021-03-01 ]

This seems to have been fixed in MySQL 8.0.14

Generated at Thu Feb 08 06:54:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.