Bug#31473

  "CSV does not work with NULL value in datetime fields"
  Attempting to insert a row with a NULL value for a DATETIME field
  results in a CSV file which the storage engine cannot read.
  Don't blindly assume that "0" is acceptable for all field types,
  Since CSV does not support NULL, we find out from the field the
  default non-null value.
  Do not permit the creation of a table with a nullable columns.


mysql-test/r/csv.result:
  test for bug 31473
mysql-test/r/log_tables.result:
  change in results due to bugfix 31473
mysql-test/r/system_mysql_db.result:
  change in results due to bugfix 31473
mysql-test/t/csv.test:
  test for bug 31473
mysql-test/t/log_tables.test:
  due to bug31473, all columns in CSV tables must be declared as NOT NULL
scripts/mysql_system_tables.sql:
  due to bug31473, all columns in CSV tables must be declared as NOT NULL
storage/csv/ha_tina.cc:
  bug31473
    Don't blindly assume that "0" is acceptable for all field types,
    Since CSV does not support NULL, we find out from the field the
    default non-null value.
    Do not permit the creation of a table with a nullable columns;

1 files changed, 63 insertions, 24 deletions

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