Description:
It's documented in code, but it's still a bug. Here's the code of
create_table_from_items() which is called by CREATE SELECT:
/* create and lock table */
/* QQ: This should be done atomic ! */
if (mysql_create_table(thd,db,name,create_info,*extra_fields,
*keys,0,1,select_field_count)) // no logging
DBUG_RETURN(0);
if (!(table=open_table(thd,db,name,name,(bool*) 0)))
Indeed, I added a sleep(10) between the two if(),
then I did
CREATE TABLE t (a int);
INSERT INTO t values(1);
CREATE TABLE u SELECT * from t;
and while it was sleeping I did
ALTER TABLE u add (b int);
All commands succeeded ("query ok") (ALTER terminated before CREATE), but results show the confusion:
MASTER> select * from t;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
MASTER> show create table u;
+-------+------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------+
| u | CREATE TABLE `u` (
`a` int(11) default NULL,
`b` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MASTER> select * from u;
+------+------+
| a | b |
+------+------+
| NULL | 1 |
+------+------+
See: 'b' was added, but the record 'a=1' of 't' got copied to 'a=NULL,b=1' in 'u', whereas we would expect 'a=1,b=NULL' if there was proper locking. It shows that ALTER confused CREATE TABLE u.
How to repeat:
See description.
Suggested fix:
I'm not discovering anything as the fault is already documented in code, I'm just entering it here so that it gets fixed.
Description: It's documented in code, but it's still a bug. Here's the code of create_table_from_items() which is called by CREATE SELECT: /* create and lock table */ /* QQ: This should be done atomic ! */ if (mysql_create_table(thd,db,name,create_info,*extra_fields, *keys,0,1,select_field_count)) // no logging DBUG_RETURN(0); if (!(table=open_table(thd,db,name,name,(bool*) 0))) Indeed, I added a sleep(10) between the two if(), then I did CREATE TABLE t (a int); INSERT INTO t values(1); CREATE TABLE u SELECT * from t; and while it was sleeping I did ALTER TABLE u add (b int); All commands succeeded ("query ok") (ALTER terminated before CREATE), but results show the confusion: MASTER> select * from t; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.01 sec) MASTER> show create table u; +-------+------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------+ | u | CREATE TABLE `u` ( `a` int(11) default NULL, `b` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MASTER> select * from u; +------+------+ | a | b | +------+------+ | NULL | 1 | +------+------+ See: 'b' was added, but the record 'a=1' of 't' got copied to 'a=NULL,b=1' in 'u', whereas we would expect 'a=1,b=NULL' if there was proper locking. It shows that ALTER confused CREATE TABLE u. How to repeat: See description. Suggested fix: I'm not discovering anything as the fault is already documented in code, I'm just entering it here so that it gets fixed.