1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
#
# Tests inserting/deleting/updating null values
#
--disable_warnings
DROP TABLE IF EXISTS t1, t2, t3, t4;
--enable_warnings
CREATE TABLE t1 (a INT NOT NULL, b CHAR(1000), PRIMARY KEY (a));
INSERT INTO t1 VALUES (1,"update me");
INSERT INTO t1 VALUES (2,'');
INSERT INTO t1 VALUES (3,NULL);
UPDATE t1 SET b= "updated" WHERE a= 2;
UPDATE t1 SET a= 4 WHERE b IS NULL;
UPDATE t1 SET b= NULL WHERE a= 1;
DELETE FROM t1 where b is NULL;
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT, b INT, PRIMARY KEY(a));
INSERT INTO t2 (b) VALUES(NULL);
INSERT INTO t2 (b) VALUES(0);
CREATE TABLE t3 (a INT NOT NULL, b ENUM ('1','2'), PRIMARY KEY(a));
INSERT INTO t3 VALUES (1,'1');
INSERT INTO t3 VALUES (2,NULL);
CREATE TABLE t4 (id INT NOT NULL, col0_int INT DEFAULT NULL, col1_int INT DEFAULT NULL, PRIMARY KEY (id));
INSERT INTO t4 VALUES (1, NULL, 1);
UPDATE t4 SET col0_int= 0 WHERE col1_int != 2;
UPDATE t4 SET col0_int= NULL WHERE col1_int != 2;
DROP TABLE t1, t2, t3, t4;
|