~drizzle-trunk/drizzle/development

#

# test of already fixed bugs

#

--disable_warnings

drop table if exists t1,t2,t3,t4,t5,t6;

drop database if exists mysqltest;

#

# Bug 10838

# Insert causes warnings for no default values and corrupts tables

#

CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ',

                 b varchar(1) binary NOT NULL DEFAULT ' ',

		 c varchar(4) binary NOT NULL DEFAULT '0000',

		 d tinyblob NULL,

		 e tinyblob NULL,

		 f tinyblob NULL,

		 g tinyblob NULL,

		 h tinyblob NULL,

		 i tinyblob NULL,

		 j tinyblob NULL,

		 k tinyblob NULL,

		 l tinyblob NULL,

		 m tinyblob NULL,

		 n tinyblob NULL,

		 o tinyblob NULL,

		 p tinyblob NULL,

                 q varchar(30) binary NOT NULL DEFAULT ' ',

                 r varchar(30) binary NOT NULL DEFAULT ' ',

		 s tinyblob NULL,

                 t varchar(4) binary NOT NULL DEFAULT ' ',

                 u varchar(1) binary NOT NULL DEFAULT ' ',

                 v varchar(30) binary NOT NULL DEFAULT ' ',

                 w varchar(30) binary NOT NULL DEFAULT ' ',

		 x tinyblob NULL,

                 y varchar(5) binary NOT NULL DEFAULT ' ',

                 z varchar(20) binary NOT NULL DEFAULT ' ',

                 a1 varchar(30) binary NOT NULL DEFAULT ' ',

		 b1 tinyblob NULL)

ENGINE=InnoDB DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin;

--enable_warnings

INSERT into t1 (b) values ('1');

SHOW WARNINGS;

SELECT * from t1;

CREATE TABLE t2 (a varchar(30) binary NOT NULL DEFAULT ' ',

                 b varchar(1) binary NOT NULL DEFAULT ' ',

		 c varchar(4) binary NOT NULL DEFAULT '0000',

		 d tinyblob NULL,

		 e tinyblob NULL,

		 f tinyblob NULL,

		 g tinyblob NULL,

		 h tinyblob NULL,

		 i tinyblob NULL,

		 j tinyblob NULL,

		 k tinyblob NULL,

		 l tinyblob NULL,

		 m tinyblob NULL,

		 n tinyblob NULL,

		 o tinyblob NULL,

		 p tinyblob NULL,

                 q varchar(30) binary NOT NULL DEFAULT ' ',

                 r varchar(30) binary NOT NULL DEFAULT ' ',

		 s tinyblob NULL,

                 t varchar(4) binary NOT NULL DEFAULT ' ',

                 u varchar(1) binary NOT NULL DEFAULT ' ',

                 v varchar(30) binary NOT NULL DEFAULT ' ',

                 w varchar(30) binary NOT NULL DEFAULT ' ',

		 x tinyblob NULL,

                 y varchar(5) binary NOT NULL DEFAULT ' ',

                 z varchar(20) binary NOT NULL DEFAULT ' ',

                 a1 varchar(30) binary NOT NULL DEFAULT ' ',

		 b1 tinyblob NULL)

ENGINE=MyISAM DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin;

SHOW CREATE TABLE t2;

INSERT into t2 (b) values ('1');

SHOW WARNINGS;

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;

create table t1 (id int not null);

insert into t1 values(default);

create view v1 (c) as select id from t1;

insert into t1 values(default);

drop view v1;

drop table t1;

--echo End of 5.0 tests.