polardbxengine/mysql-test/t/trigger_before_not_null_myisam.test

--source include/force_myisam_default.inc
--source include/have_myisam.inc

SET sql_mode = 'traditional';

--echo
--echo #------------------------------------------------------------------
--echo # Checking INSERT INTO ... SELECT ...
--echo #------------------------------------------------------------------

--echo
--echo #   - No column list (all columns) + NULL-value for NOT NULL column.

# Following scenario is to test the functionality of MyISAM

# A table with one NOT NULL column.
CREATE TABLE t1(a INT, b INT NOT NULL) ENGINE=MyISAM;
CREATE VIEW v1 AS SELECT * FROM t1;

# A table with a few NOT NULL columns.
CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL) ENGINE=MyISAM;
CREATE VIEW v2 AS SELECT * FROM t2;

# Aux table with data for t1.
CREATE TABLE t1_data(a INT, b INT) ENGINE=MyISAM;
INSERT INTO t1_data VALUES
  (11, 12),
  (NULL, 22),
  (31, NULL),
  (NULL, NULL);

# Aux table with data for t2.
CREATE TABLE t2_data(a INT, b INT, c INT, d INT) ENGINE=MyISAM;
INSERT INTO t2_data VALUES
  (11, 12, 13, 14),
  (NULL, 22, 23, 24),
  (31, NULL, 33, 34),
  (41, 42, NULL, 44),
  (51, 52, 53, NULL),
  (NULL, NULL, NULL, NULL);

--echo
--error ER_BAD_NULL_ERROR
INSERT INTO t1 SELECT * FROM t1_data;
--error ER_BAD_NULL_ERROR
INSERT INTO v1 SELECT a * 10, b * 10 FROM t1_data;

--echo
--echo # The following SELECT output should have 4 rows.
--echo # t1's engine is MyISAM, so the transaction can not be fully rolled back.
--sorted_result
SELECT * FROM t1;
DELETE FROM t1;

--echo
--error ER_BAD_NULL_ERROR
REPLACE INTO t1 SELECT * FROM t1_data;
--error ER_BAD_NULL_ERROR
REPLACE INTO v1 SELECT a * 10, b * 10 FROM t1_data;

--echo
--echo # The following SELECT output should have 4 rows.
--echo # t1's engine is MyISAM, so the transaction can not be fully rolled back.
--sorted_result
SELECT * FROM t1;
DELETE FROM t1;

--echo
--echo #   - No column list (all columns) + NULL-value for NOT NULL column.
--error ER_BAD_NULL_ERROR
INSERT INTO t2 SELECT * FROM t2_data;
--error ER_BAD_NULL_ERROR
INSERT INTO v2 SELECT a * 10, b * 10, c * 10, d * 10 FROM t2_data;

--echo
--echo # The following SELECT output should have 4 rows.
--echo # t2's engine is MyISAM, so the transaction can not be fully rolled back.
--sorted_result
SELECT * FROM t2;
DELETE FROM t2;

--echo
--echo #   - No column list (all columns) + NULL-value for NOT NULL column.
--error ER_BAD_NULL_ERROR
REPLACE INTO t2 SELECT * FROM t2_data;
--error ER_BAD_NULL_ERROR
REPLACE INTO v2 SELECT a * 10, b * 10, c * 10, d * 10 FROM t2_data;

--echo
--echo # The following SELECT output should have 4 rows.
--echo # t2's engine is MyISAM, so the transaction can not be fully rolled back.
--sorted_result
SELECT * FROM t2;
DELETE FROM t2;

--echo
--echo #   - All columns + NULL-value for NOT NULL column.

--echo
--error ER_BAD_NULL_ERROR
INSERT INTO t1(a, b) SELECT * FROM t1_data;
--error ER_BAD_NULL_ERROR
INSERT INTO v1(a, b) SELECT a * 10, b * 10 FROM t1_data;

--echo
--echo # The following SELECT output should have 4 rows.
--echo # t1's engine is MyISAM, so the transaction can not be fully rolled back.
--sorted_result
SELECT * FROM t1;
DELETE FROM t1;

--echo
--error ER_BAD_NULL_ERROR
REPLACE INTO t1(a, b) SELECT * FROM t1_data;
--error ER_BAD_NULL_ERROR
REPLACE INTO v1(a, b) SELECT a * 10, b * 10 FROM t1_data;

--echo
--echo # The following SELECT output should have 4 rows.
--echo # t1's engine is MyISAM, so the transaction can not be fully rolled back.
--sorted_result
SELECT * FROM t1;
DELETE FROM t1;

--echo
--error ER_BAD_NULL_ERROR
INSERT INTO t2(a, b, c, d) SELECT * FROM t2_data;
--error ER_BAD_NULL_ERROR
INSERT INTO v2(a, b, c, d) SELECT a * 10, b * 10, c * 10, d * 10 FROM t2_data;

--echo
--echo # The following SELECT output should have 4 rows.
--echo # t2's engine is MyISAM, so the transaction can not be fully rolled back.
--sorted_result
SELECT * FROM t2;
DELETE FROM t2;

--echo
--error ER_BAD_NULL_ERROR
REPLACE INTO t2(a, b, c, d) SELECT * FROM t2_data;
--error ER_BAD_NULL_ERROR
REPLACE INTO v2(a, b, c, d) SELECT a * 10, b * 10, c * 10, d * 10 FROM t2_data;

--echo
--echo # The following SELECT output should have 4 rows.
--echo # t2's engine is MyISAM, so the transaction can not be fully rolled back.
--sorted_result
SELECT * FROM t2;
DELETE FROM t2;

# Clean up
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t1_data;
DROP TABLE t2_data;
DROP VIEW v1;
DROP VIEW v2;

--echo "Test for multi table update (across engines)"
CREATE TABLE t1(a1 INT NOT NULL, a2 INT) ENGINE = INNODB;
CREATE TABLE t2(a1 INT NOT NULL, a2 INT) ENGINE = MyISAM;
INSERT INTO t1 VALUES (1, NULL),(2, 2);
INSERT INTO t2 VALUES (1, NULL),(2876543, 2098);

CREATE TRIGGER t1_bu BEFORE UPDATE ON t1
FOR EACH ROW
SET NEW.a1 = 10;

CREATE TRIGGER t2_bu BEFORE UPDATE ON t2
FOR EACH ROW
SET NEW.a1 = 20;

UPDATE t1, t2 SET t1.a1 = NULL , t2.a1 = NULL;
SELECT * FROM t1;
SELECT * FROM t2;

DROP TRIGGER t1_bu;
DROP TRIGGER t2_bu;
DROP TABLE t1, t2;

--echo "Test for multi table update (across engines)"
CREATE TABLE t1(a1 INT NOT NULL, a2 INT) ENGINE = INNODB;
CREATE TABLE t2(a1 INT NOT NULL, a2 INT) ENGINE = MyISAM;
INSERT INTO t1 VALUES (1, NULL),(2, 2);
INSERT INTO t2 VALUES (1, NULL),(2876543, 20111098);

CREATE TRIGGER t1_bu BEFORE UPDATE ON t1
FOR EACH ROW
SET NEW.a1 = NULL;
--error ER_BAD_NULL_ERROR
UPDATE t1, t2 SET t1.a1 = 1 , t2.a1 = 2;
SELECT * FROM t1;
SELECT * FROM t2;

DROP TRIGGER t1_bu;
DROP TABLE t1, t2;