1 #-----------------------------------------------------------------------------
2 # csv_not_null.test - .test file for MySQL regression suite
3 # Purpose: To test the behavior of the CSV engine
4 # Bug#31473 resulted in strict enforcement of non-nullable
5 # columns in CSV engine.
6 # NOTE: Main functionality tested - NOT NULL restrictions on CSV tables
7 # CREATE, INSERT, and UPDATE statements
8 # ALTER statements in separate file due to BUG#33696
10 # Last modified: 2008-01-04
11 #-----------------------------------------------------------------------------
13 --source include/have_csv.inc
15 #############################################################################
16 # Testcase csv_not_null.1: CREATE TABLE for CSV Engine requires explicit
17 # NOT NULL for each column
18 #############################################################################
19 -- echo # ===== csv_not_null.1 =====
21 DROP TABLE IF EXISTS t1, t2;
25 --echo # === Will fail -- no NOT NULL ===
26 --error ER_CHECK_NOT_IMPLEMENTED
27 CREATE TABLE t1 (a int) ENGINE = CSV;
29 --echo # === Good CREATE ===
30 CREATE TABLE t1 (a int NOT NULL) ENGINE = CSV;
32 --echo # === Will fail -- ALL columns need NOT NULL ==
33 --error ER_CHECK_NOT_IMPLEMENTED
34 CREATE TABLE t2 (a int NOT NULL, b char(20)) ENGINE = CSV;
38 ##############################################################################
39 # Testcase csv_not_null.2: INSERT tests -- validating behavior of INSERT
41 ##############################################################################
42 -- echo # ===== csv_not_null.2 =====
44 DROP TABLE IF EXISTS t1;
48 CREATE TABLE t1 (a int NOT NULL, b blob NOT NULL, c CHAR(20) NOT NULL,
49 d VARCHAR(20) NOT NULL, e enum('foo','bar') NOT NULL,f DATE NOT NULL)
51 -- echo # === should result in default for each datatype ===
53 INSERT INTO t1 VALUES();
58 # Bug#33717 - INSERT...(default) fails for enum.
59 INSERT INTO t1 VALUES(default,default,default,default,default,default);
63 INSERT INTO t1 VALUES(0,'abc','def','ghi','bar','1999-12-31');
65 -- echo # === insert failures ===
66 --error ER_BAD_NULL_ERROR
67 INSERT INTO t1 VALUES(NULL,'ab','a','b','foo','2007-01-01');
68 --error ER_NO_DEFAULT_FOR_FIELD
69 INSERT INTO t1 VALUES(default(a),default(b), default(c), default(d),
70 default(e), default(f));
75 ##############################################################################
76 # Testcase csv_not_null.3: UPDATE tests -- examining behavior of UPDATE
78 ##############################################################################
79 -- echo # ===== csv_not_null.3 =====
81 DROP TABLE IF EXISTS t1;
85 CREATE TABLE t1 (a int NOT NULL, b char(10) NOT NULL) ENGINE = CSV;
87 INSERT INTO t1 VALUES();
91 UPDATE t1 set b = 'new_value' where a = 0;
94 UPDATE t1 set b = NULL where b = 'new_value';