Debian Codegraph @

mysql-test/suite/csv/csv_not_null.test

raw
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
9 # Author pcrews
10 # Last modified: 2008-01-04
11 #-----------------------------------------------------------------------------
12
13 --source include/have_csv.inc
14
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 =====
20 -- disable_warnings
21 DROP TABLE IF EXISTS t1, t2;
22 -- enable_warnings
23
24
25 --echo # === Will fail -- no NOT NULL ===
26 --error ER_CHECK_NOT_IMPLEMENTED
27 CREATE TABLE t1 (a int) ENGINE = CSV;
28
29 --echo # === Good CREATE ===
30 CREATE TABLE t1 (a int NOT NULL) ENGINE = CSV;
31
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;
35
36
37 DROP TABLE t1;
38 ##############################################################################
39 # Testcase csv_not_null.2: INSERT tests -- validating behavior of INSERT
40 # statements for CSV
41 ##############################################################################
42 -- echo # ===== csv_not_null.2 =====
43 -- disable_warnings
44 DROP TABLE IF EXISTS t1;
45 --enable_warnings
46
47
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)
50 ENGINE = CSV;
51 -- echo # === should result in default for each datatype ===
52 -- disable_warnings
53 INSERT IGNORE INTO t1 VALUES();
54 -- enable_warnings
55 SELECT * FROM t1;
56
57 -- disable_warnings
58 # Bug#33717 - INSERT...(default) fails for enum.
59 INSERT IGNORE INTO t1 VALUES(default,default,default,default,default,default);
60 -- enable_warnings
61
62 SELECT * FROM t1;
63 INSERT INTO t1 VALUES(0,'abc','def','ghi','bar','1999-12-31');
64 SELECT * FROM t1;
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));
71
72
73 DROP TABLE t1;
74
75 ##############################################################################
76 # Testcase csv_not_null.3: UPDATE tests -- examining behavior of UPDATE
77 # statements for CSV
78 ##############################################################################
79 -- echo # ===== csv_not_null.3 =====
80 -- disable_warnings
81 DROP TABLE IF EXISTS t1;
82 --enable_warnings
83
84
85 CREATE TABLE t1 (a int NOT NULL, b char(10) NOT NULL) ENGINE = CSV;
86 --disable_warnings
87 INSERT IGNORE INTO t1 VALUES();
88 --enable_warnings
89 SELECT * FROM t1;
90 --disable_warnings
91 UPDATE t1 set b = 'new_value' where a = 0;
92 --enable_warnings
93 SELECT * FROM t1;
94 UPDATE IGNORE t1 set b = NULL where b = 'new_value';
95 SELECT * FROM t1;
96
97 DROP TABLE t1;