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 Nozdrin | 20 Sep |
