SET NEW = (row subquery) in a trigger silently assigns NULL to all columns and skips the single-row check

CREATE TABLE src (a INT, b INT);

INSERT INTO src VALUES (10,20);

CREATE TABLE t (a INT, b INT) ;

CREATE TRIGGER t_bi BEFORE INSERT ON t FOR EACH ROW

  SET NEW = (SELECT a,b FROM src);

INSERT INTO t VALUES (1,2);

--echo # prints NULL,NULL instead of 10,20 (silent data loss) and skips the single-row check

SELECT a,b FROM t;

# cleanup

DROP TABLE t, src;

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT

--------------------------------------------------------------------------

worker[01] Using MTR_BUILD_THREAD 300, with reserved ports 19000..19029

CREATE TABLE src (a INT, b INT);

INSERT INTO src VALUES (10,20);

CREATE TABLE t (a INT, b INT) ;

CREATE TRIGGER t_bi BEFORE INSERT ON t FOR EACH ROW

SET NEW = (SELECT a,b FROM src);

INSERT INTO t VALUES (1,2);

# prints NULL,NULL instead of 10,20 (silent data loss)

SELECT a,b FROM t;

a	b

NULL	NULL

INSERT INTO src VALUES (30,40);

CREATE TABLE t2 (a INT, b INT);

CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW

SET NEW = (SELECT a,b FROM src);

# skips the single-row check and silently inserts NULL,NULL

INSERT INTO t2 VALUES (1,2);

SELECT a,b FROM t2;

a	b

NULL	NULL

DROP TABLE t, t2, src;

main.mytest                              [ pass ]     21

--------------------------------------------------------------------------