MySQL Bugs: #33256: CREATE ... SELECT creates obsolete table w/ Field_date instead of Field

Bug #33256	CREATE ... SELECT creates obsolete table w/ Field_date instead of Field_newdate
Submitted:	15 Dec 2007 6:03	Modified:	24 Jan 2008 20:39
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0-bk	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
MySQL should use class Field_newdate to handle columns of type DATE. Field_date class is there only to handle tables created by older (pre-4.1?) versions. 

However, even in the latest 5.0, CREATE ... SELECT syntax will create table that will use the obsolete Field_date class.
 

How to repeat:
# Create the table t2:
create table t1 (f1 date, f2 datetime, f3 varchar(20));
create table t2 as select coalesce(f1,f1) as f4 from t1;
show create table t2;

# This will show this:
CREATE TABLE `t2` (
  `f4` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

# Now let's copy-paste the DDL  and create an identical table:
CREATE TABLE `t3` (
  `f4` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

## The tables are the same:

show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `f4` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `f4` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

# Let's see what's in:

insert into t2 values (NOW()),(NOW());
insert into t3 values (NOW()),(NOW());

(gdb) b sub_select

select * from t2;
select * from t3;

  Breakpoint 24, sub_select (join=0x8d93e78, join_tab=0x8d94fd0, end_of_records=false) at sql_select.cc:10587
(gdb) p join_tab->table->alias
  $131 = 0x8d415d0 "t2"
(gdb) p join_tab->table->field[0]
  $132 = (Field_date *) 0x8d92f58
(gdb) c
  Continuing.

...

  Breakpoint 24, sub_select (join=0x8d93e78, join_tab=0x8d94fd0, end_of_records=false) at sql_select.cc:10587
(gdb) p join_tab->table->alias
  $133 = 0x8d75640 "t3"
(gdb) p join_tab->table->field[0]
  $134 = (Field_newdate *) 0x8d9a478

# ^ Here we see that one table uses Field_date and the other uses Field_newdate.

Suggested fix:
Make the server to use Field_newdate when  handling CREATE ... SELECT (and other)  statements.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40140

ChangeSet@1.2591, 2007-12-18 18:01:36+02:00, gkodinov@magare.gmz +6 -0
  Bug #33256: CREATE ... SELECT creates obsolete table 
   w/ Field_date instead of Field_newdate
  
  Field_date was still used in temp table creation.
  Fixed by using Field_newdate consistently throughout the server
  except when reading tables defined with older MySQL version.
  No test suite is possible because both Field_date and Field_newdate
  return the same values in all the metadata calls.

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.56

Noted in 5.0.56, 5.1.23, 6.0.5 changelogs.

CREATE TABLE ... SELECT created tables that for date columns used the
obsolete Field_date type instead of Field_newdate.