| 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; |