bk commit into 5.0 tree (cmiller:1.2297) BUG#20691

[Chad MILLER]

Below is the list of changes that have just been committed into a local
5.0 repository of cmiller. When cmiller does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-11-09 18:33:58-05:00, cmiller@stripped +3 -0
  Bug#20691: DATETIME col (NOT NULL, NO DEFAULT) may insert garbage when \
  	specifying DEFAULT
  
  This was not specific to datetime.  When there is no default value 
  for a column, and the user inserted DEFAULT, we would write 
  uninitialized memory to the table.  
  
  Now, insist on writing a default value, a zero-ish value, the same 
  one that comes from inserting NULL into a not-NULL field.
  
  (This is, at best, really strange behavior that comes from allowing 
  sloppy usage, and serves as a good reason always to run one's server 
  in a strict SQL mode.)

  mysql-test/r/default.result@stripped, 2006-11-09 18:33:56-05:00, cmiller@stripped
+92 -0
    Verify that all kinds of types work, even others other than datetime.

  mysql-test/t/default.test@stripped, 2006-11-09 18:33:56-05:00, cmiller@stripped +58
-0
    Verify that all kinds of types work, even others other than datetime.

  sql/item.cc@stripped, 2006-11-09 18:33:56-05:00, cmiller@stripped +1 -0
    Even if we warn that there is no default value in the table definition,
    we have to insert /something/.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	cmiller
# Host:	zippy.cornsilk.net
# Root:	/home/cmiller/work/mysql/bug20691/my50-bug20691

--- 1.236/sql/item.cc	2006-11-09 18:34:02 -05:00
+++ 1.237/sql/item.cc	2006-11-09 18:34:02 -05:00
@@ -5379,6 +5379,7 @@ int Item_default_value::save_in_field(Fi
                             ER(ER_NO_DEFAULT_FOR_FIELD),
                             field_arg->field_name);
       }
+      field_arg->set_default();
       return 1;
     }
     field_arg->set_default();

--- 1.3/mysql-test/r/default.result	2006-11-09 18:34:02 -05:00
+++ 1.4/mysql-test/r/default.result	2006-11-09 18:34:02 -05:00
@@ -104,3 +104,95 @@ a	b	c	d	e	f	g	h	i	j	k	l	m	n	o	p	q	r	s	t	
  	1	0000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	 	 	NULL	 	 	 	
	NULL	 	 	 	NULL
 drop table t1;
 drop table t2;
+create table bug20691 (i int, d datetime NOT NULL, dn datetime not null default
'0000-00-00 00:00:00');
+insert into bug20691 values (1, DEFAULT, DEFAULT), (1, '1975-07-10 07:10:03', '1978-01-13
14:08:51'), (1, DEFAULT, DEFAULT);
+Warnings:
+Warning	1364	Field 'd' doesn't have a default value
+Warning	1364	Field 'd' doesn't have a default value
+insert into bug20691 (i) values (2);
+Warnings:
+Warning	1364	Field 'd' doesn't have a default value
+desc bug20691;
+Field	Type	Null	Key	Default	Extra
+i	int(11)	YES		NULL	
+d	datetime	NO			
+dn	datetime	NO		0000-00-00 00:00:00	
+insert into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13
14:08:51'), (3, DEFAULT, DEFAULT);
+Warnings:
+Warning	1364	Field 'd' doesn't have a default value
+Warning	1364	Field 'd' doesn't have a default value
+insert into bug20691 (i) values (4);
+Warnings:
+Warning	1364	Field 'd' doesn't have a default value
+insert into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13
14:08:51'), (5, DEFAULT, DEFAULT);
+Warnings:
+Warning	1364	Field 'd' doesn't have a default value
+Warning	1364	Field 'd' doesn't have a default value
+SET sql_mode = 'ALLOW_INVALID_DATES';
+insert into bug20691 values (6, DEFAULT, DEFAULT), (6, '1975-07-10 07:10:03', '1978-01-13
14:08:51'), (6, DEFAULT, DEFAULT);
+Warnings:
+Warning	1364	Field 'd' doesn't have a default value
+Warning	1364	Field 'd' doesn't have a default value
+SET sql_mode = 'STRICT_ALL_TABLES';
+insert into bug20691 values (7, DEFAULT, DEFAULT), (7, '1975-07-10 07:10:03', '1978-01-13
14:08:51'), (7, DEFAULT, DEFAULT);
+ERROR HY000: Field 'd' doesn't have a default value
+select * from bug20691 order by i asc;
+i	d	dn
[phone]:00:00	0000-00-00 00:00:00
[phone]:10:03	1978-01-13 14:08:51
[phone]:00:00	0000-00-00 00:00:00
[phone]:00:00	0000-00-00 00:00:00
[phone]:00:00	0000-00-00 00:00:00
[phone]:10:03	1978-01-13 14:08:51
[phone]:00:00	0000-00-00 00:00:00
[phone]:00:00	0000-00-00 00:00:00
[phone]:00:00	0000-00-00 00:00:00
[phone]:10:03	1978-01-13 14:08:51
[phone]:00:00	0000-00-00 00:00:00
[phone]:00:00	0000-00-00 00:00:00
[phone]:10:03	1978-01-13 14:08:51
[phone]:00:00	0000-00-00 00:00:00
+drop table bug20691;
+SET sql_mode = '';
+create table bug20691 (
+a set('one', 'two', 'three') not null,
+b enum('small', 'medium', 'large', 'enormous', 'ellisonego') not null,
+c time not null,
+d date not null,
+e int not null,
+f long not null,
+g blob not null,
+h datetime not null,
+i decimal not null,
+x int);
+insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00',
23, 1);
+insert into bug20691 (x) values (2);
+Warnings:
+Warning	1364	Field 'a' doesn't have a default value
+Warning	1364	Field 'c' doesn't have a default value
+Warning	1364	Field 'd' doesn't have a default value
+Warning	1364	Field 'e' doesn't have a default value
+Warning	1364	Field 'f' doesn't have a default value
+Warning	1364	Field 'g' doesn't have a default value
+Warning	1364	Field 'h' doesn't have a default value
+Warning	1364	Field 'i' doesn't have a default value
+insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00',
23, 3);
+insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, 4);
+Warnings:
+Warning	1364	Field 'a' doesn't have a default value
+Warning	1364	Field 'b' doesn't have a default value
+Warning	1364	Field 'c' doesn't have a default value
+Warning	1364	Field 'd' doesn't have a default value
+Warning	1364	Field 'e' doesn't have a default value
+Warning	1364	Field 'f' doesn't have a default value
+Warning	1364	Field 'g' doesn't have a default value
+Warning	1364	Field 'h' doesn't have a default value
+Warning	1364	Field 'i' doesn't have a default value
+select * from bug20691 order by x asc;
+a	b	c	d	e	f	g	h	i	x
+two	large	00:00:05	0007-01-01	11	13	17	0019-01-01 00:00:00	23	1
+	small	00:00:00	0000-00-00	0			0000-00-00 00:00:00	0	2
+two	large	00:00:05	0007-01-01	11	13	17	0019-01-01 00:00:00	23	3
+	small	00:00:00	0000-00-00	0			0000-00-00 00:00:00	0	4
+drop table bug20691;
+End of 5.0 tests.

--- 1.4/mysql-test/t/default.test	2006-11-09 18:34:02 -05:00
+++ 1.5/mysql-test/t/default.test	2006-11-09 18:34:02 -05:00
@@ -82,3 +82,61 @@ SELECT * from t2;
 drop table t1;
 drop table t2;
 
+
+#
+# Bug#20691: DATETIME col (NOT NULL, NO DEFAULT) may insert garbage when specifying
DEFAULT
+#
+# From the docs:
+#  If the column can take NULL as a value, the column is defined with an
+#  explicit DEFAULT NULL clause. This is the same as before 5.0.2.
+#
+#  If the column cannot take NULL as the value, MySQL defines the column with
+#  no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE
+#  statement includes no value for the column, MySQL handles the column
+#  according to the SQL mode in effect at the time:
+#
+#    * If strict SQL mode is not enabled, MySQL sets the column to the
+#      implicit default value for the column data type.
+#
+#    * If strict mode is enabled, an error occurs for transactional tables and
+#      the statement is rolled back. For non-transactional tables, an error
+#      occurs, but if this happens for the second or subsequent row of a
+#      multiple-row statement, the preceding rows will have been inserted.
+#
+create table bug20691 (i int, d datetime NOT NULL, dn datetime not null default
'0000-00-00 00:00:00');
+insert into bug20691 values (1, DEFAULT, DEFAULT), (1, '1975-07-10 07:10:03', '1978-01-13
14:08:51'), (1, DEFAULT, DEFAULT);
+insert into bug20691 (i) values (2);
+desc bug20691;
+insert into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13
14:08:51'), (3, DEFAULT, DEFAULT);
+insert into bug20691 (i) values (4);
+insert into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13
14:08:51'), (5, DEFAULT, DEFAULT);
+SET sql_mode = 'ALLOW_INVALID_DATES';
+insert into bug20691 values (6, DEFAULT, DEFAULT), (6, '1975-07-10 07:10:03', '1978-01-13
14:08:51'), (6, DEFAULT, DEFAULT);
+SET sql_mode = 'STRICT_ALL_TABLES';
+--error 1364
+insert into bug20691 values (7, DEFAULT, DEFAULT), (7, '1975-07-10 07:10:03', '1978-01-13
14:08:51'), (7, DEFAULT, DEFAULT);
+select * from bug20691 order by i asc;
+drop table bug20691;
+
+SET sql_mode = '';
+create table bug20691 (
+  a set('one', 'two', 'three') not null,
+  b enum('small', 'medium', 'large', 'enormous', 'ellisonego') not null,
+  c time not null,
+  d date not null,
+  e int not null,
+  f long not null,
+  g blob not null,
+  h datetime not null,
+  i decimal not null,
+  x int);
+insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00',
23, 1);
+insert into bug20691 (x) values (2);
+insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00',
23, 3);
+insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, 4);
+select * from bug20691 order by x asc;
+drop table bug20691;
+
+###
+--echo End of 5.0 tests.
+