summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/csv.result106
-rw-r--r--mysql-test/r/heap_btree.result7
-rw-r--r--mysql-test/r/log_tables.result96
-rw-r--r--mysql-test/r/merge.result5
-rw-r--r--mysql-test/r/partition.result4
-rw-r--r--mysql-test/r/partition_innodb.result7
-rw-r--r--mysql-test/r/system_mysql_db.result18
-rw-r--r--mysql-test/t/csv.test87
-rw-r--r--mysql-test/t/heap_btree.test9
-rw-r--r--mysql-test/t/log_tables.test18
-rw-r--r--mysql-test/t/merge.test14
-rw-r--r--mysql-test/t/partition.test9
-rw-r--r--mysql-test/t/partition_innodb.test8
13 files changed, 274 insertions, 114 deletions
diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result
index 86ba5002af8..69f77dc3cd8 100644
--- a/mysql-test/r/csv.result
+++ b/mysql-test/r/csv.result
@@ -4929,7 +4929,7 @@ Note 1051 Unknown table 't2'
Note 1051 Unknown table 't3'
Note 1051 Unknown table 't4'
DROP TABLE IF EXISTS bug13894;
-CREATE TABLE bug13894 ( val integer ) ENGINE = CSV;
+CREATE TABLE bug13894 ( val integer not null ) ENGINE = CSV;
INSERT INTO bug13894 VALUES (5);
INSERT INTO bug13894 VALUES (10);
INSERT INTO bug13894 VALUES (11);
@@ -4949,7 +4949,7 @@ val
11
DROP TABLE bug13894;
DROP TABLE IF EXISTS bug14672;
-CREATE TABLE bug14672 (c1 integer) engine = CSV;
+CREATE TABLE bug14672 (c1 integer not null) engine = CSV;
INSERT INTO bug14672 VALUES (1), (2), (3);
SELECT * FROM bug14672;
c1
@@ -4975,7 +4975,7 @@ c1
4
5
DROP TABLE bug14672;
-CREATE TABLE test_concurrent_insert ( val integer ) ENGINE = CSV;
+CREATE TABLE test_concurrent_insert ( val integer not null ) ENGINE = CSV;
LOCK TABLES test_concurrent_insert READ LOCAL;
INSERT INTO test_concurrent_insert VALUES (1);
SELECT * FROM test_concurrent_insert;
@@ -4992,7 +4992,7 @@ val
2
UNLOCK TABLES;
DROP TABLE test_concurrent_insert;
-CREATE TABLE test_repair_table ( val integer ) ENGINE = CSV;
+CREATE TABLE test_repair_table ( val integer not null ) ENGINE = CSV;
CHECK TABLE test_repair_table;
Table Op Msg_type Msg_text
test.test_repair_table check status OK
@@ -5000,7 +5000,7 @@ REPAIR TABLE test_repair_table;
Table Op Msg_type Msg_text
test.test_repair_table repair status OK
DROP TABLE test_repair_table;
-CREATE TABLE test_repair_table2 ( val integer ) ENGINE = CSV;
+CREATE TABLE test_repair_table2 ( val integer not null ) ENGINE = CSV;
SELECT * from test_repair_table2;
val
Warnings:
@@ -5011,7 +5011,7 @@ CHECK TABLE test_repair_table2;
Table Op Msg_type Msg_text
test.test_repair_table2 check status OK
DROP TABLE test_repair_table2;
-CREATE TABLE test_repair_table3 ( val integer ) ENGINE = CSV;
+CREATE TABLE test_repair_table3 ( val integer not null ) ENGINE = CSV;
CHECK TABLE test_repair_table3;
Table Op Msg_type Msg_text
test.test_repair_table3 check error Corrupt
@@ -5114,7 +5114,7 @@ num magic_no company_name founded
1 0102 CORRECT 1876
1 0102 CORRECT2 1876
DROP TABLE test_repair_table5;
-create table t1 (a int) engine=csv;
+create table t1 (a int not null) engine=csv;
insert t1 values (1);
delete from t1;
affected rows: 1
@@ -5138,7 +5138,7 @@ insert t1 values (1),(2),(3),(4),(5);
truncate table t1;
affected rows: 0
drop table t1;
-create table t1 (v varchar(32));
+create table t1 (v varchar(32) not null);
insert into t1 values ('def'),('abc'),('hij'),('3r4f');
select * from t1;
v
@@ -5193,8 +5193,8 @@ select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');
i v
4 3r4f
drop table t1;
-create table bug15205 (val int(11) default null) engine=csv;
-create table bug15205_2 (val int(11) default null) engine=csv;
+create table bug15205 (val int(11) not null) engine=csv;
+create table bug15205_2 (val int(11) not null) engine=csv;
select * from bug15205;
ERROR HY000: Can't get stat of './test/bug15205.CSV' (Errcode: 2)
select * from bug15205_2;
@@ -5205,8 +5205,8 @@ drop table bug15205;
drop table bug15205_2;
set names latin1;
create table t1 (
-c varchar(1),
-name varchar(64)
+c varchar(1) not null,
+name varchar(64) not null
) character set latin1 engine=csv;
insert into t1 values (0xC0,'LATIN CAPITAL LETTER A WITH GRAVE');
insert into t1 values (0xE0,'LATIN SMALL LETTER A WITH GRAVE');
@@ -5224,9 +5224,9 @@ FE þ LATIN SMALL LETTER THORN
FF ÿ LATIN SMALL LETTER Y WITH DIAERESIS
drop table t1;
End of 5.0 tests
-create table bug22080_1 (id int,string varchar(64)) Engine=CSV;
-create table bug22080_2 (id int,string varchar(64)) Engine=CSV;
-create table bug22080_3 (id int,string varchar(64)) Engine=CSV;
+create table bug22080_1 (id int not null,string varchar(64) not null) Engine=CSV;
+create table bug22080_2 (id int not null,string varchar(64) not null) Engine=CSV;
+create table bug22080_3 (id int not null,string varchar(64) not null) Engine=CSV;
insert into bug22080_1 values(1,'string');
insert into bug22080_1 values(2,'string');
insert into bug22080_1 values(3,'string');
@@ -5237,7 +5237,7 @@ check table bug22080_3;
Table Op Msg_type Msg_text
test.bug22080_3 check error Corrupt
drop tables bug22080_1,bug22080_2,bug22080_3;
-create table float_test (id float,string varchar(64)) Engine=CSV;
+create table float_test (id float not null,string varchar(64) not null) Engine=CSV;
insert into float_test values(1.0,'string');
insert into float_test values(2.23,'serg.g');
insert into float_test values(0.03,'string');
@@ -5254,14 +5254,14 @@ id string
9.67 string
drop table float_test;
CREATE TABLE `bug21328` (
-`col1` int(11) DEFAULT NULL,
-`col2` int(11) DEFAULT NULL,
-`col3` int(11) DEFAULT NULL
+`col1` int(11) NOT NULL,
+`col2` int(11) NOT NULL,
+`col3` int(11) NOT NULL
) ENGINE=CSV;
-insert into bug21328 values (1,NULL,NULL);
+insert into bug21328 values (1,0,0);
alter table bug21328 engine=myisam;
drop table bug21328;
-create table t1(a blob, b int) engine=csv;
+create table t1(a blob not null, b int not null) engine=csv;
insert into t1 values('a', 1);
flush tables;
update t1 set b=2;
@@ -5269,7 +5269,7 @@ select * from t1;
a b
a 2
drop table t1;
-create table t1(a int) engine=csv;
+create table t1(a int not null) engine=csv;
insert into t1 values(-1), (-123.34), (2), (-23);
select * from t1;
a
@@ -5281,7 +5281,7 @@ check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
drop table t1;
-create table t1(a int, b int) engine=csv;
+create table t1(a int not null, b int not null) engine=csv;
repair table t1;
Table Op Msg_type Msg_text
test.t1 repair Warning Data truncated for column 'a' at row 5
@@ -5299,7 +5299,7 @@ check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
drop table t1;
-create table t1(a int) engine=csv;
+create table t1(a int not null) engine=csv;
insert into t1 values (0), (1), (2);
delete from t1 limit 2;
check table t1;
@@ -5315,4 +5315,62 @@ test.t1 check status OK
select * from t1;
a
drop table t1;
+create table t1(a datetime not null) engine=csv;
+insert into t1 values();
+Warnings:
+Warning 1364 Field 'a' doesn't have a default value
+select * from t1;
+a
+0000-00-00 00:00:00
+drop table t1;
+create table t1(a set('foo','bar') not null) engine=csv;
+insert into t1 values();
+Warnings:
+Warning 1364 Field 'a' doesn't have a default value
+select * from t1;
+a
+
+drop table t1;
+create table t1(a varchar(32) not null) engine=csv;
+insert into t1 values();
+Warnings:
+Warning 1364 Field 'a' doesn't have a default value
+select * from t1;
+a
+
+drop table t1;
+create table t1(a int not null) engine=csv;
+insert into t1 values();
+Warnings:
+Warning 1364 Field 'a' doesn't have a default value
+select * from t1;
+a
+0
+drop table t1;
+create table t1(a blob not null) engine=csv;
+insert into t1 values();
+Warnings:
+Warning 1364 Field 'a' doesn't have a default value
+select * from t1;
+a
+
+drop table t1;
+create table t1(a bit(1) not null) engine=csv;
+insert into t1 values();
+Warnings:
+Warning 1364 Field 'a' doesn't have a default value
+select BIN(a) from t1;
+BIN(a)
+0
+drop table t1;
+create table t1(a enum('foo','bar') default null) engine=csv;
+ERROR HY000: Can't create table 'test.t1' (errno: -1)
+create table t1(a enum('foo','bar') default 'foo') engine=csv;
+ERROR HY000: Can't create table 'test.t1' (errno: -1)
+create table t1(a enum('foo','bar') default 'foo' not null) engine=csv;
+insert into t1 values();
+select * from t1;
+a
+foo
+drop table t1;
End of 5.1 tests
diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result
index 44241563dda..9db03855c01 100644
--- a/mysql-test/r/heap_btree.result
+++ b/mysql-test/r/heap_btree.result
@@ -307,6 +307,13 @@ UNIQUE USING BTREE(c1)
) ENGINE= MEMORY DEFAULT CHARSET= utf8;
INSERT INTO t1 VALUES('1'), ('2');
DROP TABLE t1;
+CREATE TABLE t1 (a INT, KEY USING BTREE(a)) ENGINE=MEMORY;
+INSERT INTO t1 VALUES(1),(2),(2);
+DELETE FROM t1 WHERE a=2;
+SELECT * FROM t1;
+a
+1
+DROP TABLE t1;
End of 4.1 tests
CREATE TABLE t1(val INT, KEY USING BTREE(val)) ENGINE=memory;
INSERT INTO t1 VALUES(0);
diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result
index 337d6b0eca8..261e3292f4d 100644
--- a/mysql-test/r/log_tables.result
+++ b/mysql-test/r/log_tables.result
@@ -42,20 +42,20 @@ show create table mysql.general_log;
Table Create Table
general_log CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- `user_host` mediumtext,
- `thread_id` int(11) DEFAULT NULL,
- `server_id` int(11) DEFAULT NULL,
- `command_type` varchar(64) DEFAULT NULL,
- `argument` mediumtext
+ `user_host` mediumtext NOT NULL,
+ `thread_id` int(11) NOT NULL,
+ `server_id` int(11) NOT NULL,
+ `command_type` varchar(64) NOT NULL,
+ `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
show fields from mysql.general_log;
Field Type Null Key Default Extra
event_time timestamp NO CURRENT_TIMESTAMP
-user_host mediumtext YES NULL
-thread_id int(11) YES NULL
-server_id int(11) YES NULL
-command_type varchar(64) YES NULL
-argument mediumtext YES NULL
+user_host mediumtext NO NULL
+thread_id int(11) NO NULL
+server_id int(11) NO NULL
+command_type varchar(64) NO NULL
+argument mediumtext NO NULL
show create table mysql.slow_log;
Table Create Table
slow_log CREATE TABLE `slow_log` (
@@ -65,10 +65,10 @@ slow_log CREATE TABLE `slow_log` (
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
- `db` varchar(512) DEFAULT NULL,
- `last_insert_id` int(11) DEFAULT NULL,
- `insert_id` int(11) DEFAULT NULL,
- `server_id` int(11) DEFAULT NULL,
+ `db` varchar(512) NOT NULL,
+ `last_insert_id` int(11) NOT NULL,
+ `insert_id` int(11) NOT NULL,
+ `server_id` int(11) NOT NULL,
`sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
show fields from mysql.slow_log;
@@ -79,10 +79,10 @@ query_time time NO NULL
lock_time time NO NULL
rows_sent int(11) NO NULL
rows_examined int(11) NO NULL
-db varchar(512) YES NULL
-last_insert_id int(11) YES NULL
-insert_id int(11) YES NULL
-server_id int(11) YES NULL
+db varchar(512) NO NULL
+last_insert_id int(11) NO NULL
+insert_id int(11) NO NULL
+server_id int(11) NO NULL
sql_text mediumtext NO NULL
flush logs;
flush tables;
@@ -141,11 +141,11 @@ show create table mysql.general_log;
Table Create Table
general_log CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- `user_host` mediumtext,
- `thread_id` int(11) DEFAULT NULL,
- `server_id` int(11) DEFAULT NULL,
- `command_type` varchar(64) DEFAULT NULL,
- `argument` mediumtext
+ `user_host` mediumtext NOT NULL,
+ `thread_id` int(11) NOT NULL,
+ `server_id` int(11) NOT NULL,
+ `command_type` varchar(64) NOT NULL,
+ `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
show create table mysql.slow_log;
Table Create Table
@@ -156,10 +156,10 @@ slow_log CREATE TABLE `slow_log` (
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
- `db` varchar(512) DEFAULT NULL,
- `last_insert_id` int(11) DEFAULT NULL,
- `insert_id` int(11) DEFAULT NULL,
- `server_id` int(11) DEFAULT NULL,
+ `db` varchar(512) NOT NULL,
+ `last_insert_id` int(11) NOT NULL,
+ `insert_id` int(11) NOT NULL,
+ `server_id` int(11) NOT NULL,
`sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
alter table mysql.general_log engine=myisam;
@@ -168,11 +168,11 @@ show create table mysql.general_log;
Table Create Table
general_log CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- `user_host` mediumtext,
- `thread_id` int(11) DEFAULT NULL,
- `server_id` int(11) DEFAULT NULL,
- `command_type` varchar(64) DEFAULT NULL,
- `argument` mediumtext
+ `user_host` mediumtext NOT NULL,
+ `thread_id` int(11) NOT NULL,
+ `server_id` int(11) NOT NULL,
+ `command_type` varchar(64) NOT NULL,
+ `argument` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
show create table mysql.slow_log;
Table Create Table
@@ -183,10 +183,10 @@ slow_log CREATE TABLE `slow_log` (
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
- `db` varchar(512) DEFAULT NULL,
- `last_insert_id` int(11) DEFAULT NULL,
- `insert_id` int(11) DEFAULT NULL,
- `server_id` int(11) DEFAULT NULL,
+ `db` varchar(512) NOT NULL,
+ `last_insert_id` int(11) NOT NULL,
+ `insert_id` int(11) NOT NULL,
+ `server_id` int(11) NOT NULL,
`sql_text` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
set global general_log='ON';
@@ -241,11 +241,11 @@ use mysql;
CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
-`user_host` mediumtext,
-`thread_id` int(11) DEFAULT NULL,
-`server_id` int(11) DEFAULT NULL,
-`command_type` varchar(64) DEFAULT NULL,
-`argument` mediumtext
+`user_host` mediumtext NOT NULL,
+`thread_id` int(11) NOT NULL,
+`server_id` int(11) NOT NULL,
+`command_type` varchar(64) NOT NULL,
+`argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
CREATE TABLE `slow_log` (
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
@@ -255,10 +255,10 @@ ON UPDATE CURRENT_TIMESTAMP,
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
-`db` varchar(512) DEFAULT NULL,
-`last_insert_id` int(11) DEFAULT NULL,
-`insert_id` int(11) DEFAULT NULL,
-`server_id` int(11) DEFAULT NULL,
+`db` varchar(512) NOT NULL,
+`last_insert_id` int(11) NOT NULL,
+`insert_id` int(11) NOT NULL,
+`server_id` int(11) NOT NULL,
`sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
set global general_log='ON';
@@ -403,9 +403,9 @@ My own slow query sleep(2)
My own slow query 0
SELECT * FROM mysql.slow_log WHERE seq >= 2 LIMIT 3;
start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text seq
-START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test NULL NULL 1 SELECT "My own slow query", sleep(2) 2
-START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test NULL NULL 1 SELECT "My own slow query", sleep(2) 3
-START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test NULL NULL 1 SELECT "My own slow query", sleep(2) 4
+START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 2
+START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 3
+START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 4
SET GLOBAL slow_query_log = 0;
SET SESSION long_query_time =@old_long_query_time;
FLUSH LOGS;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 5aa4288500c..d6e19107ec4 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -879,4 +879,9 @@ CHECK TABLE tm1;
Table Op Msg_type Msg_text
test.tm1 check status OK
DROP TABLE tm1, t1, t2;
+CREATE TABLE t1(c1 INT);
+CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST;
+CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+DROP TABLE t1, t2;
End of 5.0 tests
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 7120e3ea9e6..4e4bd0bbc0a 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -1259,6 +1259,10 @@ INSERT INTO t1 SELECT a + 8, b FROM t1;
ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN (64));
ALTER TABLE t1 DROP PARTITION p1;
DROP TABLE t1;
+create table t (s1 int) engine=myisam partition by key (s1);
+create trigger t_ad after delete on t for each row insert into t values (old.s1);
+insert into t values (1);
+drop table t;
USE mysql;
SET GLOBAL general_log = 0;
ALTER TABLE general_log ENGINE = MyISAM;
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index 8282cfc212a..5b755b6bfd5 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -129,3 +129,10 @@ insert into t1 (time, first_name, last_name) values ('2007-02-07', 'Q', 'Robert'
SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake';
id time first_name last_name
drop table t1;
+CREATE TABLE t1 (a DOUBLE NOT NULL, KEY(a)) ENGINE=InnoDB
+PARTITION BY KEY(a) PARTITIONS 10;
+INSERT INTO t1 VALUES(1),(2);
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+2
+DROP TABLE t1;
diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result
index 93694db629d..d1aff0fa657 100644
--- a/mysql-test/r/system_mysql_db.result
+++ b/mysql-test/r/system_mysql_db.result
@@ -240,11 +240,11 @@ show create table general_log;
Table Create Table
general_log CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- `user_host` mediumtext,
- `thread_id` int(11) DEFAULT NULL,
- `server_id` int(11) DEFAULT NULL,
- `command_type` varchar(64) DEFAULT NULL,
- `argument` mediumtext
+ `user_host` mediumtext NOT NULL,
+ `thread_id` int(11) NOT NULL,
+ `server_id` int(11) NOT NULL,
+ `command_type` varchar(64) NOT NULL,
+ `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
show create table slow_log;
Table Create Table
@@ -255,10 +255,10 @@ slow_log CREATE TABLE `slow_log` (
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
- `db` varchar(512) DEFAULT NULL,
- `last_insert_id` int(11) DEFAULT NULL,
- `insert_id` int(11) DEFAULT NULL,
- `server_id` int(11) DEFAULT NULL,
+ `db` varchar(512) NOT NULL,
+ `last_insert_id` int(11) NOT NULL,
+ `insert_id` int(11) NOT NULL,
+ `server_id` int(11) NOT NULL,
`sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
show tables;
diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test
index 5c877557dfc..6c83fbfdc9c 100644
--- a/mysql-test/t/csv.test
+++ b/mysql-test/t/csv.test
@@ -1322,7 +1322,7 @@ drop table if exists t1,t2,t3,t4;
DROP TABLE IF EXISTS bug13894;
--enable_warnings
-CREATE TABLE bug13894 ( val integer ) ENGINE = CSV;
+CREATE TABLE bug13894 ( val integer not null ) ENGINE = CSV;
INSERT INTO bug13894 VALUES (5);
INSERT INTO bug13894 VALUES (10);
INSERT INTO bug13894 VALUES (11);
@@ -1340,7 +1340,7 @@ DROP TABLE bug13894;
DROP TABLE IF EXISTS bug14672;
--enable_warnings
-CREATE TABLE bug14672 (c1 integer) engine = CSV;
+CREATE TABLE bug14672 (c1 integer not null) engine = CSV;
INSERT INTO bug14672 VALUES (1), (2), (3);
SELECT * FROM bug14672;
DELETE FROM bug14672 WHERE c1 = 2;
@@ -1357,7 +1357,7 @@ DROP TABLE bug14672;
# Test CONCURRENT INSERT (5.1)
#
-CREATE TABLE test_concurrent_insert ( val integer ) ENGINE = CSV;
+CREATE TABLE test_concurrent_insert ( val integer not null ) ENGINE = CSV;
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
@@ -1393,7 +1393,7 @@ DROP TABLE test_concurrent_insert;
# Check that repair on the newly created table works fine
-CREATE TABLE test_repair_table ( val integer ) ENGINE = CSV;
+CREATE TABLE test_repair_table ( val integer not null ) ENGINE = CSV;
CHECK TABLE test_repair_table;
REPAIR TABLE test_repair_table;
@@ -1405,7 +1405,7 @@ DROP TABLE test_repair_table;
# restore the meta-file
#
-CREATE TABLE test_repair_table2 ( val integer ) ENGINE = CSV;
+CREATE TABLE test_repair_table2 ( val integer not null ) ENGINE = CSV;
--remove_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table2.CSM
# Should give a warning and perform autorepair. We also disable ps-protocol
@@ -1423,7 +1423,7 @@ DROP TABLE test_repair_table2;
# Corrupt csv file and see if we can repair it
-CREATE TABLE test_repair_table3 ( val integer ) ENGINE = CSV;
+CREATE TABLE test_repair_table3 ( val integer not null ) ENGINE = CSV;
--remove_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table3.CSV
--write_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table3.CSV
"1"
@@ -1517,7 +1517,7 @@ DROP TABLE test_repair_table5;
# BUG#13406 - incorrect amount of "records deleted"
#
-create table t1 (a int) engine=csv;
+create table t1 (a int not null) engine=csv;
insert t1 values (1);
--enable_info
delete from t1; # delete_row
@@ -1549,7 +1549,7 @@ drop table t1;
# whole alter table code is being tested all around the test suite already.
#
-create table t1 (v varchar(32));
+create table t1 (v varchar(32) not null);
insert into t1 values ('def'),('abc'),('hij'),('3r4f');
select * from t1;
# Fast alter, no copy performed
@@ -1583,8 +1583,8 @@ drop table t1;
# resulted in scanning through deleted memory and we were geting a crash.
# that's why we need two tables in the bugtest
-create table bug15205 (val int(11) default null) engine=csv;
-create table bug15205_2 (val int(11) default null) engine=csv;
+create table bug15205 (val int(11) not null) engine=csv;
+create table bug15205_2 (val int(11) not null) engine=csv;
--remove_file $MYSQLTEST_VARDIR/master-data/test/bug15205.CSV
# system error (can't open the datafile)
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
@@ -1604,8 +1604,8 @@ drop table bug15205_2;
#
set names latin1;
create table t1 (
- c varchar(1),
- name varchar(64)
+ c varchar(1) not null,
+ name varchar(64) not null
) character set latin1 engine=csv;
insert into t1 values (0xC0,'LATIN CAPITAL LETTER A WITH GRAVE');
insert into t1 values (0xE0,'LATIN SMALL LETTER A WITH GRAVE');
@@ -1623,9 +1623,9 @@ drop table t1;
# Bug#22080 "CHECK fails to identify some corruption"
#
-create table bug22080_1 (id int,string varchar(64)) Engine=CSV;
-create table bug22080_2 (id int,string varchar(64)) Engine=CSV;
-create table bug22080_3 (id int,string varchar(64)) Engine=CSV;
+create table bug22080_1 (id int not null,string varchar(64) not null) Engine=CSV;
+create table bug22080_2 (id int not null,string varchar(64) not null) Engine=CSV;
+create table bug22080_3 (id int not null,string varchar(64) not null) Engine=CSV;
insert into bug22080_1 values(1,'string');
insert into bug22080_1 values(2,'string');
insert into bug22080_1 values(3,'string');
@@ -1655,7 +1655,7 @@ drop tables bug22080_1,bug22080_2,bug22080_3;
#
# Testing float type
#
-create table float_test (id float,string varchar(64)) Engine=CSV;
+create table float_test (id float not null,string varchar(64) not null) Engine=CSV;
insert into float_test values(1.0,'string');
insert into float_test values(2.23,'serg.g');
insert into float_test values(0.03,'string');
@@ -1670,12 +1670,12 @@ drop table float_test;
#
CREATE TABLE `bug21328` (
- `col1` int(11) DEFAULT NULL,
- `col2` int(11) DEFAULT NULL,
- `col3` int(11) DEFAULT NULL
+ `col1` int(11) NOT NULL,
+ `col2` int(11) NOT NULL,
+ `col3` int(11) NOT NULL
) ENGINE=CSV;
-insert into bug21328 values (1,NULL,NULL);
+insert into bug21328 values (1,0,0);
alter table bug21328 engine=myisam;
drop table bug21328;
@@ -1683,7 +1683,7 @@ drop table bug21328;
# BUG#28971 - ALTER TABLE followed by UPDATE for a CSV table make server
# crash
#
-create table t1(a blob, b int) engine=csv;
+create table t1(a blob not null, b int not null) engine=csv;
insert into t1 values('a', 1);
flush tables;
update t1 set b=2;
@@ -1693,13 +1693,13 @@ drop table t1;
#
# Bug #29353: negative values
#
-create table t1(a int) engine=csv;
+create table t1(a int not null) engine=csv;
insert into t1 values(-1), (-123.34), (2), (-23);
select * from t1;
check table t1;
drop table t1;
-create table t1(a int, b int) engine=csv;
+create table t1(a int not null, b int not null) engine=csv;
--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.CSV
--write_file $MYSQLTEST_VARDIR/master-data/test/t1.CSV
1, 1E-2
@@ -1717,7 +1717,7 @@ drop table t1;
#
# Bug #29411: deleting from a csv table leads to the table corruption
#
-create table t1(a int) engine=csv;
+create table t1(a int not null) engine=csv;
insert into t1 values (0), (1), (2);
delete from t1 limit 2;
check table t1;
@@ -1727,4 +1727,43 @@ check table t1;
select * from t1;
drop table t1;
+#
+# Bug #31473: does not work with NULL value in datetime field
+#
+create table t1(a datetime not null) engine=csv;
+insert into t1 values();
+select * from t1;
+drop table t1;
+create table t1(a set('foo','bar') not null) engine=csv;
+insert into t1 values();
+select * from t1;
+drop table t1;
+create table t1(a varchar(32) not null) engine=csv;
+insert into t1 values();
+select * from t1;
+drop table t1;
+create table t1(a int not null) engine=csv;
+insert into t1 values();
+select * from t1;
+drop table t1;
+create table t1(a blob not null) engine=csv;
+insert into t1 values();
+select * from t1;
+drop table t1;
+create table t1(a bit(1) not null) engine=csv;
+insert into t1 values();
+select BIN(a) from t1;
+drop table t1;
+# We prevent creation of table with nullable ENUM
+--error ER_CANT_CREATE_TABLE
+create table t1(a enum('foo','bar') default null) engine=csv;
+--error ER_CANT_CREATE_TABLE
+create table t1(a enum('foo','bar') default 'foo') engine=csv;
+# Enum columns must be specified as NOT NULL
+create table t1(a enum('foo','bar') default 'foo' not null) engine=csv;
+insert into t1 values();
+select * from t1;
+drop table t1;
+
+
--echo End of 5.1 tests
diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test
index 204b820970c..b51eeb27331 100644
--- a/mysql-test/t/heap_btree.test
+++ b/mysql-test/t/heap_btree.test
@@ -213,6 +213,15 @@ CREATE TABLE t1 (
INSERT INTO t1 VALUES('1'), ('2');
DROP TABLE t1;
+#
+# BUG#30590 - delete from memory table with composite btree primary key
+#
+CREATE TABLE t1 (a INT, KEY USING BTREE(a)) ENGINE=MEMORY;
+INSERT INTO t1 VALUES(1),(2),(2);
+DELETE FROM t1 WHERE a=2;
+SELECT * FROM t1;
+DROP TABLE t1;
+
--echo End of 4.1 tests
#
diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test
index c4bce2fbf3d..12098b4543b 100644
--- a/mysql-test/t/log_tables.test
+++ b/mysql-test/t/log_tables.test
@@ -253,11 +253,11 @@ use mysql;
CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
- `user_host` mediumtext,
- `thread_id` int(11) DEFAULT NULL,
- `server_id` int(11) DEFAULT NULL,
- `command_type` varchar(64) DEFAULT NULL,
- `argument` mediumtext
+ `user_host` mediumtext NOT NULL,
+ `thread_id` int(11) NOT NULL,
+ `server_id` int(11) NOT NULL,
+ `command_type` varchar(64) NOT NULL,
+ `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
CREATE TABLE `slow_log` (
@@ -268,10 +268,10 @@ CREATE TABLE `slow_log` (
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
- `db` varchar(512) DEFAULT NULL,
- `last_insert_id` int(11) DEFAULT NULL,
- `insert_id` int(11) DEFAULT NULL,
- `server_id` int(11) DEFAULT NULL,
+ `db` varchar(512) NOT NULL,
+ `last_insert_id` int(11) NOT NULL,
+ `insert_id` int(11) NOT NULL,
+ `server_id` int(11) NOT NULL,
`sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index fd479276b3b..a50588b1e78 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -511,4 +511,18 @@ SELECT * FROM tm1;
CHECK TABLE tm1;
DROP TABLE tm1, t1, t2;
+#
+# Bug#15522 - create ... select and with merge tables
+#
+# This was fixed together with Bug#20662 (Infinite loop in CREATE TABLE
+# IF NOT EXISTS ... SELECT with locked tables).
+# The new behavior for MERGE tables is consistent with the
+# CREATE TABLE SELECT behavior for ordinary tables.
+#
+CREATE TABLE t1(c1 INT);
+CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST;
+--error ER_UPDATE_TABLE_USED
+CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
+DROP TABLE t1, t2;
+
--echo End of 5.0 tests
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
index 2be2ab83c88..2906b4640cd 100644
--- a/mysql-test/t/partition.test
+++ b/mysql-test/t/partition.test
@@ -1481,6 +1481,15 @@ ALTER TABLE t1 DROP PARTITION p1;
DROP TABLE t1;
#
+# Bug #30484: Partitions: crash with self-referencing trigger
+#
+
+create table t (s1 int) engine=myisam partition by key (s1);
+create trigger t_ad after delete on t for each row insert into t values (old.s1);
+insert into t values (1);
+drop table t;
+
+#
# Bug #27816: Log tables ran with partitions crashes the server when logging
# is enabled.
#
diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test
index f4320c5c56a..4a50332b3df 100644
--- a/mysql-test/t/partition_innodb.test
+++ b/mysql-test/t/partition_innodb.test
@@ -134,3 +134,11 @@ SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake';
drop table t1;
+#
+# BUG#30583 - Partition on DOUBLE key + INNODB + count(*) == crash
+#
+CREATE TABLE t1 (a DOUBLE NOT NULL, KEY(a)) ENGINE=InnoDB
+PARTITION BY KEY(a) PARTITIONS 10;
+INSERT INTO t1 VALUES(1),(2);
+SELECT COUNT(*) FROM t1;
+DROP TABLE t1;
View Lorry Controller Status