List:Commits« Previous MessageNext Message »
From:Alexander Nozdrin Date:September 20 2012 4:52am
Subject:bzr push into mysql-trunk branch (alexander.nozdrin:4210 to 4211)
View as plain text  
 4211 Alexander Nozdrin	2012-09-19
      Enhance wl6030.test.

    modified:
      mysql-test/r/wl6030.result
      mysql-test/t/wl6030.test
 4210 Alexander Nozdrin	2012-09-19
      Update wl6030.test to show case problem. wl6030.result contains WRONG results.

    modified:
      mysql-test/r/wl6030.result
      mysql-test/t/wl6030.test
=== modified file 'mysql-test/r/wl6030.result'
--- a/mysql-test/r/wl6030.result	2012-09-19 09:43:14 +0000
+++ b/mysql-test/r/wl6030.result	2012-09-19 12:51:44 +0000
@@ -1,111 +1,520 @@
 #
 # WL#6030: Triggers are not processed for NOT NULL columns.
 #
-# Test 1: BEFORE INSERT, NOT NULL
-CREATE TABLE t1(a INT NOT NULL);
+CREATE TABLE t2(a INT, b INT, c INT);
+
+###################################################################
+# Test 1: BEFORE INSERT, NOT NULL.
+#   - Test 1.1: SET to NOT NULL.
+###################################################################
+CREATE TABLE t1(a INT NOT NULL, b INT, c INT);
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a = 1;
-INSERT INTO t1 VALUES(NULL);
+INSERT INTO t2 VALUES (1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+
+# Single INSERT ... VALUES.
+INSERT INTO t1 VALUES (NULL, 2, 3);
+INSERT INTO t1(a, b, c) VALUES (NULL, 20, 30);
+INSERT INTO t1(b, c) VALUES (200, 300);
+INSERT INTO t1(a) VALUES (NULL);
+
 SELECT * FROM t1;
-a
-1
-DROP TRIGGER t1_bi;
+a	b	c
+1	2	3
+1	20	30
+1	200	300
+1	NULL	NULL
+
+DELETE FROM t1;
+
+# Multi INSERT ... VALUES.
+INSERT INTO t1 VALUES (-1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(a, b, c) VALUES (-2, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(b, c) VALUES (2, 3), (20, 30), (200, 300);
+INSERT INTO t1(a) VALUES (-3), (NULL), (NULL);
+
+SELECT * FROM t1;
+a	b	c
+1	2	3
+1	20	30
+1	200	300
+1	2	3
+1	20	30
+1	200	300
+1	2	3
+1	20	30
+1	200	300
+1	NULL	NULL
+1	NULL	NULL
+1	NULL	NULL
+
+DELETE FROM t1;
+
+# INSERT ... SELECT.
+INSERT INTO t1 SELECT * FROM t2;
+INSERT INTO t1(a, b, c) SELECT * FROM t2;
+INSERT INTO t1(b, c) SELECT b, c FROM t2;
+INSERT INTO t1(a) SELECT a FROM t2;
+
+SELECT * FROM t1;
+a	b	c
+1	2	3
+1	20	30
+1	200	300
+1	2	3
+1	20	30
+1	200	300
+1	2	3
+1	20	30
+1	200	300
+1	NULL	NULL
+1	NULL	NULL
+1	NULL	NULL
+
 DELETE FROM t1;
+DELETE FROM t2;
+DROP TRIGGER t1_bi;
+
+###################################################################
+#   - Test 1.2: SET to NULL.
+###################################################################
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a = NULL;
-INSERT INTO t1 VALUES(1);
+INSERT INTO t2 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+# Single INSERT ... VALUES.
+INSERT INTO t1 VALUES (1, 2, 3);
+ERROR 23000: Column 'a' cannot be null
+INSERT INTO t1(a, b, c) VALUES (1, 2, 3);
+ERROR 23000: Column 'a' cannot be null
+INSERT INTO t1(b, c) VALUES (2, 3);
 ERROR 23000: Column 'a' cannot be null
+INSERT INTO t1(a) VALUES (1);
+ERROR 23000: Column 'a' cannot be null
+
 SELECT * FROM t1;
-a
+a	b	c
+
+DELETE FROM t1;
+
+# Multi INSERT ... VALUES.
+INSERT INTO t1 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+ERROR 23000: Column 'a' cannot be null
+INSERT INTO t1(a, b, c) VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+ERROR 23000: Column 'a' cannot be null
+INSERT INTO t1(b, c) VALUES (2, 3), (20, 30), (200, 300);
+ERROR 23000: Column 'a' cannot be null
+INSERT INTO t1(a) VALUES (1), (10), (100);
+ERROR 23000: Column 'a' cannot be null
+
+SELECT * FROM t1;
+a	b	c
+
+DELETE FROM t1;
+
+# INSERT ... SELECT.
+INSERT INTO t1 SELECT * FROM t2;
+ERROR 23000: Column 'a' cannot be null
+INSERT INTO t1(a, b, c) SELECT * FROM t2;
+ERROR 23000: Column 'a' cannot be null
+INSERT INTO t1(b, c) SELECT b, c FROM t2;
+ERROR 23000: Column 'a' cannot be null
+INSERT INTO t1(a) SELECT a FROM t2;
+ERROR 23000: Column 'a' cannot be null
 DROP TRIGGER t1_bi;
-DROP TABLE t1;
-# Test 2: BEFORE UPDATE, NOT NULL
-CREATE TABLE t1(a INT NOT NULL);
-CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET NEW.a = 2;
-INSERT INTO t1 VALUES(1);
+
+###################################################################
+# Test 2: BEFORE UPDATE, NOT NULL.
+#   - Test 2.1: SET to NOT NULL.
+###################################################################
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET NEW.a = 999;
+INSERT INTO t1 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+# Regular UPDATE.
 UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL, c = NULL WHERE a = 100;
+
 SELECT * FROM t1;
-a
-2
-DROP TRIGGER t1_bu;
+a	b	c
+999	2	3
+10	20	30
+999	200	NULL
+
 DELETE FROM t1;
+INSERT INTO t1 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+# Multiple UPDATE.
+CREATE TABLE t3(a INT, b INT);
+INSERT INTO t3 VALUES (10, -10);
+UPDATE t1, t3 SET t1.a = NULL, t3.a = -20 WHERE t1.a = t3.a AND t3.a = 10;
+Warnings:
+Warning	1048	Column 'a' cannot be null
+
+SELECT * FROM t1;
+a	b	c
+1	2	3
+999	20	30
+100	200	300
+
+SELECT * FROM t3;
+a	b
+-20	-10
+
+DROP TABLE t3;
+DROP TRIGGER t1_bu;
+
+#   - Test 2.1: SET to NULL.
 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET NEW.a = NULL;
-INSERT INTO t1 VALUES(1);
-UPDATE t1 SET a = 2 WHERE a = 1;
+DELETE FROM t1;
+INSERT INTO t1 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+# Regular UPDATE.
+UPDATE t1 SET a = 99 WHERE a = 1;
+ERROR 23000: Column 'a' cannot be null
+UPDATE t1 SET a = 99, b = 99 WHERE a = 1;
 ERROR 23000: Column 'a' cannot be null
+
 SELECT * FROM t1;
-a
-1
+a	b	c
+1	2	3
+10	20	30
+100	200	300
+
+# Multiple UPDATE.
+CREATE TABLE t3(a INT, b INT);
+INSERT INTO t3 VALUES (10, -10);
+UPDATE t1, t3 SET t1.a = 99, t3.a = -10 WHERE t1.a = t3.a AND t3.a = 10;
+ERROR 23000: Column 'a' cannot be null
+
+SELECT * FROM t1;
+a	b	c
+1	2	3
+10	20	30
+100	200	300
+
+SELECT * FROM t3;
+a	b
+-10	-10
+
+DELETE FROM t1;
+DELETE FROM t2;
+DROP TABLE t3;
 DROP TRIGGER t1_bu;
-DROP TABLE t1;
-# Test 3: Using illegal NULL-value as r-value
-CREATE TABLE t1(a INT NOT NULL, b INT);
+
+###################################################################
+# Test 3: Using illegal NULL-value as r-value.
+#   - Test 3.1: using NULL.
+###################################################################
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
 BEGIN
 SET NEW.b = NEW.a;
 SET NEW.a = 1;
 END|
-INSERT INTO t1(a) VALUES(NULL);
+INSERT INTO t2 VALUES (1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+
+# Single INSERT ... VALUES.
+INSERT INTO t1 VALUES (NULL, 2, 3);
+INSERT INTO t1(a, b, c) VALUES (NULL, 20, 30);
+INSERT INTO t1(b, c) VALUES (200, 300);
+INSERT INTO t1(a) VALUES (NULL);
+
 SELECT * FROM t1;
-a	b
-1	NULL
-DROP TRIGGER t1_bi;
+a	b	c
+1	NULL	3
+1	NULL	30
+1	0	300
+1	NULL	NULL
+
+DELETE FROM t1;
+
+# Multi INSERT ... VALUES.
+INSERT INTO t1 VALUES (-1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(a, b, c) VALUES (-2, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(b, c) VALUES (2, 3), (20, 30), (200, 300);
+INSERT INTO t1(a) VALUES (-3), (NULL), (NULL);
+
+SELECT * FROM t1;
+a	b	c
+1	-1	3
+1	NULL	30
+1	NULL	300
+1	-2	3
+1	NULL	30
+1	NULL	300
+1	0	3
+1	0	30
+1	0	300
+1	-3	NULL
+1	NULL	NULL
+1	NULL	NULL
+
 DELETE FROM t1;
+
+# INSERT ... SELECT
+INSERT INTO t1 SELECT * FROM t2;
+INSERT INTO t1(a, b, c) SELECT * FROM t2;
+INSERT INTO t1(b, c) SELECT b, c FROM t2;
+INSERT INTO t1(a) SELECT a FROM t2;
+
+SELECT * FROM t1;
+a	b	c
+1	1	3
+1	NULL	30
+1	NULL	300
+1	1	3
+1	NULL	30
+1	NULL	300
+1	0	3
+1	0	30
+1	0	300
+1	1	NULL
+1	NULL	NULL
+1	NULL	NULL
+
+DELETE FROM t1;
+DROP TRIGGER t1_bi;
+
+###################################################################
+#   - Test 3.2: using NOT NULL.
+###################################################################
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
 BEGIN
-SET NEW.a = 1;
+SET NEW.a = 99;
 SET NEW.b = NEW.a;
 END|
-INSERT INTO t1(a) VALUES(NULL);
+
+# Single INSERT ... VALUES.
+INSERT INTO t1 VALUES (NULL, 2, 3);
+INSERT INTO t1(a, b, c) VALUES (NULL, 20, 30);
+INSERT INTO t1(b, c) VALUES (200, 300);
+INSERT INTO t1(a) VALUES (NULL);
+
 SELECT * FROM t1;
-a	b
-1	1
+a	b	c
+99	99	3
+99	99	30
+99	99	300
+99	99	NULL
+
+DELETE FROM t1;
+
+# Multi INSERT ... VALUES.
+INSERT INTO t1 VALUES (-1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(a, b, c) VALUES (-2, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(b, c) VALUES (2, 3), (20, 30), (200, 300);
+INSERT INTO t1(a) VALUES (-3), (NULL), (NULL);
+
+SELECT * FROM t1;
+a	b	c
+99	99	3
+99	99	30
+99	99	300
+99	99	3
+99	99	30
+99	99	300
+99	99	3
+99	99	30
+99	99	300
+99	99	NULL
+99	99	NULL
+99	99	NULL
+
+DELETE FROM t1;
+
+# INSERT ... SELECT
+INSERT INTO t1 SELECT * FROM t2;
+INSERT INTO t1(a, b, c) SELECT * FROM t2;
+INSERT INTO t1(b, c) SELECT b, c FROM t2;
+INSERT INTO t1(a) SELECT a FROM t2;
+
+SELECT * FROM t1;
+a	b	c
+99	99	3
+99	99	30
+99	99	300
+99	99	3
+99	99	30
+99	99	300
+99	99	3
+99	99	30
+99	99	300
+99	99	NULL
+99	99	NULL
+99	99	NULL
+
+DELETE FROM t1;
+DELETE FROM t2;
 DROP TRIGGER t1_bi;
-DROP TABLE t1;
+
+###################################################################
 # Test 4: Temporarily setting to illegal NULL-value in trigger
-CREATE TABLE t1(a INT NOT NULL);
+# (here we only check that it's possible to temporarily assign
+# NULL in triggers).
+###################################################################
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
 BEGIN
 SET NEW.a = NULL;
-SET NEW.a = 2;
+SET NEW.b = NEW.a;
+SET NEW.a = 99;
 END|
-INSERT INTO t1 VALUES(1);
-SELECT * FROM t1;
-a
-2
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+SET NEW.a = NULL;
+SET NEW.b = NEW.a;
+SET NEW.a = 199;
+END|
+
+# Checking BEFORE INSERT trigger.
+INSERT INTO t1 VALUES (1, 2, 3);
+INSERT INTO t1 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+SELECT * FROM t1;
+a	b	c
+99	NULL	3
+99	NULL	3
+99	NULL	30
+99	NULL	300
+
+# Checking BEFORE UPDATE trigger.
+UPDATE t1 SET b = 999 WHERE c = 300;
+SELECT * FROM t1;
+a	b	c
+99	NULL	3
+99	NULL	3
+99	NULL	30
+199	NULL	300
+
 DROP TRIGGER t1_bi;
-DROP TABLE t1;
-# Test 5: Using IS NULL inside trigger
-CREATE TABLE t1(a INT NOT NULL);
+DROP TRIGGER t1_bu;
+DELETE FROM t1;
+
+###################################################################
+# Test 5: Using IS NULL inside trigger.
+#   - Test 5.1: BEFORE INSERT trigger.
+###################################################################
+ALTER TABLE t1 ADD COLUMN a_new_is_null BOOLEAN DEFAULT NULL;
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
 BEGIN
-SET @a := NEW.a IS NULL;
-SET NEW.a = 1;
+SET NEW.a_new_is_null = NEW.a IS NULL;
+SET NEW.a = 99;
 END|
-INSERT INTO t1 VALUES(NULL);
-SELECT @a;
-@a
-1
-SELECT a FROM t1;
-a
-1
+INSERT INTO t2 VALUES (1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+
+# Single INSERT ... VALUES.
+INSERT INTO t1 VALUES (NULL, 2, 3, NULL);
+INSERT INTO t1(a, b, c) VALUES (NULL, 20, 30);
+INSERT INTO t1(b, c) VALUES (200, 300);
+INSERT INTO t1(a) VALUES (NULL);
+
+SELECT * FROM t1;
+a	b	c	a_new_is_null
+99	2	3	1
+99	20	30	1
+99	200	300	0
+99	NULL	NULL	1
+
+DELETE FROM t1;
+
+# Multi INSERT ... VALUES.
+INSERT INTO t1 VALUES
+(-1, 2, 3, NULL), (NULL, 20, 30, NULL), (NULL, 200, 300, NULL);
+INSERT INTO t1(a, b, c) VALUES (-2, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(b, c) VALUES (2, 3), (20, 30), (200, 300);
+INSERT INTO t1(a) VALUES (-3), (NULL), (NULL);
+
+SELECT * FROM t1;
+a	b	c	a_new_is_null
+99	2	3	0
+99	20	30	1
+99	200	300	1
+99	2	3	0
+99	20	30	1
+99	200	300	1
+99	2	3	0
+99	20	30	0
+99	200	300	0
+99	NULL	NULL	0
+99	NULL	NULL	1
+99	NULL	NULL	1
+
+DELETE FROM t1;
+
+# INSERT ... SELECT
+INSERT INTO t1 SELECT t2.*, NULL FROM t2;
+INSERT INTO t1(a, b, c) SELECT * FROM t2;
+INSERT INTO t1(b, c) SELECT b, c FROM t2;
+INSERT INTO t1(a) SELECT a FROM t2;
+
+SELECT * FROM t1;
+a	b	c	a_new_is_null
+99	2	3	0
+99	20	30	1
+99	200	300	1
+99	2	3	0
+99	20	30	1
+99	200	300	1
+99	2	3	0
+99	20	30	0
+99	200	300	0
+99	NULL	NULL	0
+99	NULL	NULL	1
+99	NULL	NULL	1
+
+DELETE FROM t1;
 DROP TRIGGER t1_bi;
-DROP TABLE t1;
-CREATE TABLE t1(a INT NOT NULL, b INT);
-INSERT INTO t1 VALUES(1, NULL);
+
+###################################################################
+#   - Test 5.2: BEFORE UPDATE trigger.
+###################################################################
+ALTER TABLE t1 ADD COLUMN a_old_is_null BOOLEAN DEFAULT NULL;
+ALTER TABLE t1 ADD COLUMN b_new_is_null BOOLEAN DEFAULT NULL;
+ALTER TABLE t1 ADD COLUMN b_old_is_null BOOLEAN DEFAULT NULL;
 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
 BEGIN
-SET @a = NEW.a IS NULL;
-SET @b = old.a IS NULL;
-SET @c = NEW.b IS NULL;
-SET @d = old.b IS NULL;
-SET NEW.a = 2;
+SET NEW.a_new_is_null = NEW.a IS NULL;
+SET NEW.a_old_is_null = OLD.a IS NULL;
+SET NEW.b_new_is_null = NEW.b IS NULL;
+SET NEW.b_old_is_null = OLD.b IS NULL;
+SET NEW.a = 99;
 END|
-UPDATE t1 SET a = NULL;
-SELECT @a, @b, @c, @d;
-@a	@b	@c	@d
-1	0	1	1
+INSERT INTO t1(a, b, c) VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+# Regular UPDATE.
+UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL, c = NULL WHERE a = 10;
+UPDATE t1 SET b = NULL WHERE a = 100;
+
+SELECT * FROM t1;
+a	b	c	a_new_is_null	a_old_is_null	b_new_is_null	b_old_is_null
+99	2	3	1	0	0	0
+99	20	NULL	1	0	0	0
+99	NULL	300	0	0	1	0
+
+DELETE FROM t1;
+INSERT INTO t1(a, b, c) VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+# Multiple UPDATE.
+CREATE TABLE t3(a INT, b INT);
+INSERT INTO t3 VALUES (10, -10);
+UPDATE t1, t3 SET t1.a = NULL, t3.a = -20 WHERE t1.a = t3.a AND t3.a = 10;
+Warnings:
+Warning	1048	Column 'a' cannot be null
+
+SELECT * FROM t1;
+a	b	c	a_new_is_null	a_old_is_null	b_new_is_null	b_old_is_null
+1	2	3	NULL	NULL	NULL	NULL
+99	20	30	0	0	0	0
+100	200	300	NULL	NULL	NULL	NULL
+
+SELECT * FROM t3;
+a	b
+-20	-10
+
+DROP TABLE t3;
 DROP TRIGGER t1_bu;
 DROP TABLE t1;
-# Test 6: Nullability of non-updated columns
+DROP TABLE t2;
+
+###################################################################
+# Test 6: Nullability of non-updated columns.
+###################################################################
 CREATE TABLE t1(a INT, b INT NOT NULL);
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET NEW.b = NULL;
 INSERT INTO t1(a) VALUES(1);
@@ -145,7 +554,10 @@ a	b
 5	0
 6	5
 DROP TABLE t1;
-# Test 7: Nullability of column being copied as result of INSERT SELECT
+
+###################################################################
+# Test 7: Nullability of column being copied as result of INSERT SELECT.
+###################################################################
 CREATE TABLE t1 (a INT NOT NULL);
 CREATE TABLE t2 (a INT);
 INSERT INTO t2 VALUES (NULL);
@@ -167,8 +579,11 @@ a
 2
 DROP TRIGGER t1_bu;
 DROP TABLE t1,t2;
+
+###################################################################
 # Test 8: Nullability of column being copied as result of
-#         LOAD DATA INFILE
+#         LOAD DATA INFILE.
+###################################################################
 CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(10) NOT NULL);
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
 BEGIN
@@ -189,7 +604,9 @@ a	b
 DROP TRIGGER t1_bi;
 DROP TABLE t1;
 
+###################################################################
 # Misc tests.
+###################################################################
 
 CREATE TABLE t1(a INT, b INT NOT NULL);
 CREATE VIEW v1 AS SELECT * FROM t1;
@@ -211,6 +628,8 @@ INSERT INTO t4 VALUES
 (NULL, NULL, NULL, NULL);
 SET @sql_mode_saved = @@sql_mode;
 SET sql_mode = '';
+
+# Expect exactly 1 warning here and 6 rows being inserted into t1.
 INSERT INTO t1(a) VALUES (1), (2), (3);
 Warnings:
 Warning	1364	Field 'b' doesn't have a default value
@@ -253,6 +672,8 @@ NULL	220
 310	0
 NULL	0
 
+
+# Expect exactly 3 warning here and 6 rows being inserted into t2.
 INSERT INTO t2(a) VALUES (1), (2), (3);
 Warnings:
 Warning	1364	Field 'b' doesn't have a default value
@@ -367,12 +788,7 @@ NULL	92	93	0
 DELETE FROM t2;
 SET sql_mode = 'traditional';
 
-SELECT * FROM t1;
-a	b
-
-SELECT * FROM v1;
-a	b
-
+# Expect 1 error here and no rows being inserted.
 INSERT INTO t1(a) VALUES (10), (20), (30);
 ERROR HY000: Field 'b' doesn't have a default value
 INSERT INTO v1(a) VALUES (40), (50), (60);
@@ -397,12 +813,7 @@ NULL	22
 NULL	220
 
 
-SELECT * FROM t2;
-a	b	c	d
-
-SELECT * FROM v2;
-a	b	c	d
-
+# Expect 1 error here and no rows being inserted.
 INSERT INTO t2(a) VALUES (10), (20), (30);
 ERROR HY000: Field 'b' doesn't have a default value
 INSERT INTO v2(a) VALUES (40), (50), (60);

=== modified file 'mysql-test/t/wl6030.test'
--- a/mysql-test/t/wl6030.test	2012-09-19 09:43:14 +0000
+++ b/mysql-test/t/wl6030.test	2012-09-19 12:51:44 +0000
@@ -2,48 +2,195 @@
 --echo # WL#6030: Triggers are not processed for NOT NULL columns.
 --echo #
 
---echo # Test 1: BEFORE INSERT, NOT NULL
+CREATE TABLE t2(a INT, b INT, c INT);
 
-CREATE TABLE t1(a INT NOT NULL);
+--echo
+--echo ###################################################################
+--echo # Test 1: BEFORE INSERT, NOT NULL.
+--echo #   - Test 1.1: SET to NOT NULL.
+--echo ###################################################################
+
+CREATE TABLE t1(a INT NOT NULL, b INT, c INT);
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a = 1;
-INSERT INTO t1 VALUES(NULL);
+
+INSERT INTO t2 VALUES (1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+
+--echo
+--echo # Single INSERT ... VALUES.
+INSERT INTO t1 VALUES (NULL, 2, 3);
+INSERT INTO t1(a, b, c) VALUES (NULL, 20, 30);
+INSERT INTO t1(b, c) VALUES (200, 300);
+INSERT INTO t1(a) VALUES (NULL);
+
+--echo
 SELECT * FROM t1;
-DROP TRIGGER t1_bi;
+--echo
+DELETE FROM t1;
+
+--echo
+--echo # Multi INSERT ... VALUES.
+INSERT INTO t1 VALUES (-1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(a, b, c) VALUES (-2, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(b, c) VALUES (2, 3), (20, 30), (200, 300);
+INSERT INTO t1(a) VALUES (-3), (NULL), (NULL);
+
+--echo
+SELECT * FROM t1;
+--echo
 DELETE FROM t1;
 
+--echo
+--echo # INSERT ... SELECT.
+INSERT INTO t1 SELECT * FROM t2;
+INSERT INTO t1(a, b, c) SELECT * FROM t2;
+INSERT INTO t1(b, c) SELECT b, c FROM t2;
+INSERT INTO t1(a) SELECT a FROM t2;
+
+--echo
+SELECT * FROM t1;
+--echo
+DELETE FROM t1;
+DELETE FROM t2;
+
+DROP TRIGGER t1_bi;
+
+--echo
+--echo ###################################################################
+--echo #   - Test 1.2: SET to NULL.
+--echo ###################################################################
+
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a = NULL;
-# This should not work
-# For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column.
-# Instead, the statement fails with an error.
+# For a single-row INSERT, no warning occurs when NULL is inserted into a NOT
+# NULL column. Instead, the statement fails with an error.
+
+INSERT INTO t2 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+--echo
+--echo # Single INSERT ... VALUES.
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1 VALUES (1, 2, 3);
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1(a, b, c) VALUES (1, 2, 3);
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1(b, c) VALUES (2, 3);
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1(a) VALUES (1);
+
+--echo
+SELECT * FROM t1;
+--echo
+DELETE FROM t1;
+
+--echo
+--echo # Multi INSERT ... VALUES.
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1(a, b, c) VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1(b, c) VALUES (2, 3), (20, 30), (200, 300);
 --error ER_BAD_NULL_ERROR
-INSERT INTO t1 VALUES(1);
+INSERT INTO t1(a) VALUES (1), (10), (100);
+
+--echo
 SELECT * FROM t1;
+--echo
+DELETE FROM t1;
+
+--echo
+--echo # INSERT ... SELECT.
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1 SELECT * FROM t2;
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1(a, b, c) SELECT * FROM t2;
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1(b, c) SELECT b, c FROM t2;
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1(a) SELECT a FROM t2;
+
 DROP TRIGGER t1_bi;
-DROP TABLE t1;
 
---echo # Test 2: BEFORE UPDATE, NOT NULL
+--echo
+--echo ###################################################################
+--echo # Test 2: BEFORE UPDATE, NOT NULL.
+--echo #   - Test 2.1: SET to NOT NULL.
+--echo ###################################################################
 
-CREATE TABLE t1(a INT NOT NULL);
-CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET NEW.a = 2;
-INSERT INTO t1 VALUES(1);
-# This should work
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET NEW.a = 999;
+INSERT INTO t1 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+--echo
+--echo # Regular UPDATE.
 UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL, c = NULL WHERE a = 100;
+
+--echo
 SELECT * FROM t1;
-DROP TRIGGER t1_bu;
+--echo
+
 DELETE FROM t1;
+INSERT INTO t1 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+--echo
+--echo # Multiple UPDATE.
+CREATE TABLE t3(a INT, b INT);
+INSERT INTO t3 VALUES (10, -10);
+
+# FIXME: there should be no warnings here.
+UPDATE t1, t3 SET t1.a = NULL, t3.a = -20 WHERE t1.a = t3.a AND t3.a = 10;
+
+--echo
+SELECT * FROM t1;
+--echo
+SELECT * FROM t3;
+--echo
+
+DROP TABLE t3;
+DROP TRIGGER t1_bu;
+
+--echo
+--echo #   - Test 2.1: SET to NULL.
 
 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET NEW.a = NULL;
-INSERT INTO t1 VALUES(1);
-# This should throw an error
+DELETE FROM t1;
+INSERT INTO t1 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+--echo
+--echo # Regular UPDATE.
+--error ER_BAD_NULL_ERROR
+UPDATE t1 SET a = 99 WHERE a = 1;
+--error ER_BAD_NULL_ERROR
+UPDATE t1 SET a = 99, b = 99 WHERE a = 1;
+
+--echo
+SELECT * FROM t1;
+
+--echo
+--echo # Multiple UPDATE.
+CREATE TABLE t3(a INT, b INT);
+INSERT INTO t3 VALUES (10, -10);
+
 --error ER_BAD_NULL_ERROR
-UPDATE t1 SET a = 2 WHERE a = 1;
+UPDATE t1, t3 SET t1.a = 99, t3.a = -10 WHERE t1.a = t3.a AND t3.a = 10;
+
+--echo
 SELECT * FROM t1;
+--echo
+SELECT * FROM t3;
+--echo
+
+DELETE FROM t1;
+DELETE FROM t2;
+DROP TABLE t3;
+
 DROP TRIGGER t1_bu;
-DROP TABLE t1;
 
---echo # Test 3: Using illegal NULL-value as r-value
+--echo
+--echo ###################################################################
+--echo # Test 3: Using illegal NULL-value as r-value.
+--echo #   - Test 3.1: using NULL.
+--echo ###################################################################
 
-CREATE TABLE t1(a INT NOT NULL, b INT);
 delimiter |;
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
 BEGIN
@@ -51,72 +198,262 @@ BEGIN
   SET NEW.a = 1;
 END|
 delimiter ;|
-INSERT INTO t1(a) VALUES(NULL);
+
+INSERT INTO t2 VALUES (1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+
+--echo
+--echo # Single INSERT ... VALUES.
+INSERT INTO t1 VALUES (NULL, 2, 3);
+INSERT INTO t1(a, b, c) VALUES (NULL, 20, 30);
+# FIXME: the next statement inserts b = 0, while it should be b = NULL.
+INSERT INTO t1(b, c) VALUES (200, 300);
+INSERT INTO t1(a) VALUES (NULL);
+
+--echo
 SELECT * FROM t1;
-DROP TRIGGER t1_bi;
+--echo
 DELETE FROM t1;
 
+--echo
+--echo # Multi INSERT ... VALUES.
+INSERT INTO t1 VALUES (-1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(a, b, c) VALUES (-2, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+# FIXME: the next statement inserts b = 0, while it should be b = NULL.
+INSERT INTO t1(b, c) VALUES (2, 3), (20, 30), (200, 300);
+INSERT INTO t1(a) VALUES (-3), (NULL), (NULL);
+
+--echo
+SELECT * FROM t1;
+--echo
+DELETE FROM t1;
+
+--echo
+--echo # INSERT ... SELECT
+INSERT INTO t1 SELECT * FROM t2;
+INSERT INTO t1(a, b, c) SELECT * FROM t2;
+# FIXME: the next statement inserts b = 0, while it should be b = NULL.
+INSERT INTO t1(b, c) SELECT b, c FROM t2;
+INSERT INTO t1(a) SELECT a FROM t2;
+
+--echo
+SELECT * FROM t1;
+--echo
+DELETE FROM t1;
+
+DROP TRIGGER t1_bi;
+
+--echo
+--echo ###################################################################
+--echo #   - Test 3.2: using NOT NULL.
+--echo ###################################################################
+
 delimiter |;
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
 BEGIN
-  SET NEW.a = 1;
+  SET NEW.a = 99;
   SET NEW.b = NEW.a;
 END|
 delimiter ;|
-INSERT INTO t1(a) VALUES(NULL);
+
+--echo
+--echo # Single INSERT ... VALUES.
+INSERT INTO t1 VALUES (NULL, 2, 3);
+INSERT INTO t1(a, b, c) VALUES (NULL, 20, 30);
+INSERT INTO t1(b, c) VALUES (200, 300);
+INSERT INTO t1(a) VALUES (NULL);
+
+--echo
 SELECT * FROM t1;
+--echo
+DELETE FROM t1;
+--echo
+--echo # Multi INSERT ... VALUES.
+INSERT INTO t1 VALUES (-1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(a, b, c) VALUES (-2, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+INSERT INTO t1(b, c) VALUES (2, 3), (20, 30), (200, 300);
+INSERT INTO t1(a) VALUES (-3), (NULL), (NULL);
+
+--echo
+SELECT * FROM t1;
+--echo
+DELETE FROM t1;
+
+--echo
+--echo # INSERT ... SELECT
+INSERT INTO t1 SELECT * FROM t2;
+INSERT INTO t1(a, b, c) SELECT * FROM t2;
+INSERT INTO t1(b, c) SELECT b, c FROM t2;
+INSERT INTO t1(a) SELECT a FROM t2;
+
+--echo
+SELECT * FROM t1;
+--echo
+DELETE FROM t1;
+DELETE FROM t2;
+
 DROP TRIGGER t1_bi;
-DROP TABLE t1;
 
+--echo
+--echo ###################################################################
 --echo # Test 4: Temporarily setting to illegal NULL-value in trigger
+--echo # (here we only check that it's possible to temporarily assign
+--echo # NULL in triggers).
+--echo ###################################################################
 
-CREATE TABLE t1(a INT NOT NULL);
 delimiter |;
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
 BEGIN
   SET NEW.a = NULL;
-  SET NEW.a = 2;
+  SET NEW.b = NEW.a;
+  SET NEW.a = 99;
+END|
+
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+  SET NEW.a = NULL;
+  SET NEW.b = NEW.a;
+  SET NEW.a = 199;
 END|
 delimiter ;|
-INSERT INTO t1 VALUES(1);
+
+--echo
+--echo # Checking BEFORE INSERT trigger.
+INSERT INTO t1 VALUES (1, 2, 3);
+INSERT INTO t1 VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+SELECT * FROM t1;
+
+--echo
+--echo # Checking BEFORE UPDATE trigger.
+UPDATE t1 SET b = 999 WHERE c = 300;
 SELECT * FROM t1;
+
+--echo
 DROP TRIGGER t1_bi;
-DROP TABLE t1;
+DROP TRIGGER t1_bu;
+DELETE FROM t1;
 
---echo # Test 5: Using IS NULL inside trigger
+--echo
+--echo ###################################################################
+--echo # Test 5: Using IS NULL inside trigger.
+--echo #   - Test 5.1: BEFORE INSERT trigger.
+--echo ###################################################################
+
+ALTER TABLE t1 ADD COLUMN a_new_is_null BOOLEAN DEFAULT NULL;
 
-CREATE TABLE t1(a INT NOT NULL);
 delimiter |;
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
 BEGIN
-  SET @a := NEW.a IS NULL;
-  SET NEW.a = 1;
+  SET NEW.a_new_is_null = NEW.a IS NULL;
+  SET NEW.a = 99;
 END|
 delimiter ;|
-INSERT INTO t1 VALUES(NULL);
-SELECT @a;
-SELECT a FROM t1;
+
+INSERT INTO t2 VALUES (1, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+
+--echo
+--echo # Single INSERT ... VALUES.
+INSERT INTO t1 VALUES (NULL, 2, 3, NULL);
+INSERT INTO t1(a, b, c) VALUES (NULL, 20, 30);
+# FIXME: the next statement inserts b = 0, while it should be b = NULL.
+INSERT INTO t1(b, c) VALUES (200, 300);
+INSERT INTO t1(a) VALUES (NULL);
+
+--echo
+SELECT * FROM t1;
+--echo
+DELETE FROM t1;
+
+--echo
+--echo # Multi INSERT ... VALUES.
+INSERT INTO t1 VALUES
+  (-1, 2, 3, NULL), (NULL, 20, 30, NULL), (NULL, 200, 300, NULL);
+INSERT INTO t1(a, b, c) VALUES (-2, 2, 3), (NULL, 20, 30), (NULL, 200, 300);
+# FIXME: the next statement inserts b = 0, while it should be b = NULL.
+INSERT INTO t1(b, c) VALUES (2, 3), (20, 30), (200, 300);
+INSERT INTO t1(a) VALUES (-3), (NULL), (NULL);
+
+--echo
+SELECT * FROM t1;
+--echo
+DELETE FROM t1;
+
+--echo
+--echo # INSERT ... SELECT
+INSERT INTO t1 SELECT t2.*, NULL FROM t2;
+INSERT INTO t1(a, b, c) SELECT * FROM t2;
+# FIXME: the next statement inserts b = 0, while it should be b = NULL.
+INSERT INTO t1(b, c) SELECT b, c FROM t2;
+INSERT INTO t1(a) SELECT a FROM t2;
+
+--echo
+SELECT * FROM t1;
+--echo
+
+DELETE FROM t1;
 DROP TRIGGER t1_bi;
-DROP TABLE t1;
 
-CREATE TABLE t1(a INT NOT NULL, b INT);
-INSERT INTO t1 VALUES(1, NULL);
+--echo
+--echo ###################################################################
+--echo #   - Test 5.2: BEFORE UPDATE trigger.
+--echo ###################################################################
+
+ALTER TABLE t1 ADD COLUMN a_old_is_null BOOLEAN DEFAULT NULL;
+ALTER TABLE t1 ADD COLUMN b_new_is_null BOOLEAN DEFAULT NULL;
+ALTER TABLE t1 ADD COLUMN b_old_is_null BOOLEAN DEFAULT NULL;
+
 delimiter |;
 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
 BEGIN
-  SET @a = NEW.a IS NULL;
-  SET @b = old.a IS NULL;
-  SET @c = NEW.b IS NULL;
-  SET @d = old.b IS NULL;
-  SET NEW.a = 2;
+  SET NEW.a_new_is_null = NEW.a IS NULL;
+  SET NEW.a_old_is_null = OLD.a IS NULL;
+
+  SET NEW.b_new_is_null = NEW.b IS NULL;
+  SET NEW.b_old_is_null = OLD.b IS NULL;
+
+  SET NEW.a = 99;
 END|
 delimiter ;|
-UPDATE t1 SET a = NULL;
-SELECT @a, @b, @c, @d;
+
+INSERT INTO t1(a, b, c) VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+--echo
+--echo # Regular UPDATE.
+UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL, c = NULL WHERE a = 10;
+UPDATE t1 SET b = NULL WHERE a = 100;
+
+--echo
+SELECT * FROM t1;
+--echo
+
+DELETE FROM t1;
+INSERT INTO t1(a, b, c) VALUES (1, 2, 3), (10, 20, 30), (100, 200, 300);
+
+--echo
+--echo # Multiple UPDATE.
+CREATE TABLE t3(a INT, b INT);
+INSERT INTO t3 VALUES (10, -10);
+
+# FIXME: there should be no warnings here.
+UPDATE t1, t3 SET t1.a = NULL, t3.a = -20 WHERE t1.a = t3.a AND t3.a = 10;
+
+--echo
+SELECT * FROM t1;
+--echo
+SELECT * FROM t3;
+--echo
+
+DROP TABLE t3;
+
 DROP TRIGGER t1_bu;
+
 DROP TABLE t1;
+DROP TABLE t2;
 
---echo # Test 6: Nullability of non-updated columns
+--echo
+--echo ###################################################################
+--echo # Test 6: Nullability of non-updated columns.
+--echo ###################################################################
 
 CREATE TABLE t1(a INT, b INT NOT NULL);
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET NEW.b = NULL;
@@ -149,14 +486,16 @@ BEGIN
 END|
 delimiter ;|
 
-# FIXME: the following statement must throw 3 warnings.
 INSERT INTO t1(a) VALUES (1), (2), (3), (4), (5), (6);
 
 SELECT * FROM t1;
 
 DROP TABLE t1;
 
---echo # Test 7: Nullability of column being copied as result of INSERT SELECT
+--echo
+--echo ###################################################################
+--echo # Test 7: Nullability of column being copied as result of INSERT SELECT.
+--echo ###################################################################
 
 CREATE TABLE t1 (a INT NOT NULL);
 CREATE TABLE t2 (a INT);
@@ -177,8 +516,11 @@ SELECT * FROM t1;
 DROP TRIGGER t1_bu;
 DROP TABLE t1,t2;
 
+--echo
+--echo ###################################################################
 --echo # Test 8: Nullability of column being copied as result of
---echo #         LOAD DATA INFILE
+--echo #         LOAD DATA INFILE.
+--echo ###################################################################
 
 CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(10) NOT NULL);
 
@@ -199,7 +541,9 @@ DROP TRIGGER t1_bi;
 DROP TABLE t1;
 
 --echo
+--echo ###################################################################
 --echo # Misc tests.
+--echo ###################################################################
 --echo
 
 CREATE TABLE t1(a INT, b INT NOT NULL);
@@ -229,7 +573,8 @@ SET @sql_mode_saved = @@sql_mode;
 
 SET sql_mode = '';
 
-# Expect exactly 1 warning here and 6 rows being inserted into t1.
+--echo
+--echo # Expect exactly 1 warning here and 6 rows being inserted into t1.
 INSERT INTO t1(a) VALUES (1), (2), (3);
 INSERT INTO v1(a) VALUES (4), (5), (6);
 
@@ -244,7 +589,8 @@ INSERT INTO v1 SELECT a * 10, b * 10 FRO
 SELECT * FROM t1;
 --echo
 
-# Expect exactly 3 warning here and 6 rows being inserted into t2.
+--echo
+--echo # Expect exactly 3 warning here and 6 rows being inserted into t2.
 INSERT INTO t2(a) VALUES (1), (2), (3);
 INSERT INTO v2(a) VALUES (4), (5), (6);
 
@@ -283,12 +629,7 @@ DELETE FROM t2;
 SET sql_mode = 'traditional';
 
 --echo
-SELECT * FROM t1;
---echo
-SELECT * FROM v1;
---echo
-
-# Expect 1 error here and no rows being inserted.
+--echo # Expect 1 error here and no rows being inserted.
 --error  ER_NO_DEFAULT_FOR_FIELD
 INSERT INTO t1(a) VALUES (10), (20), (30);
 --error  ER_NO_DEFAULT_FOR_VIEW_FIELD
@@ -301,18 +642,15 @@ INSERT INTO t1 SELECT * FROM t3;
 INSERT INTO v1 SELECT a * 10, b * 10 FROM t3;
 
 --echo
+# FIXME: should be no rows.
 SELECT * FROM t1;
 --echo
+# FIXME: should be no rows.
 SELECT * FROM v1;
 --echo
 
-# Expect 1 error here and no rows being inserted.
-
---echo
-SELECT * FROM t2;
---echo
-SELECT * FROM v2;
 --echo
+--echo # Expect 1 error here and no rows being inserted.
 
 --error  ER_NO_DEFAULT_FOR_FIELD
 INSERT INTO t2(a) VALUES (10), (20), (30);
@@ -333,8 +671,10 @@ LOAD DATA INFILE '../../std_data/wl6030_
 TERMINATED BY ',' ENCLOSED BY '"';
 
 --echo
+# FIXME: should be no rows.
 SELECT * FROM t2;
 --echo
+# FIXME: should be no rows.
 SELECT * FROM v2;
 --echo
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (alexander.nozdrin:4210 to 4211) Alexander Nozdrin20 Sep