Bug #48525 trigger changes "Column 'id' cannot be null" behaviour

• Submitted: 4 Nov 2009 8:23
• Modified: 18 Jun 2010 1:50
• Reporter: Sergei Golubchik
• Status: Closed
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.0.88, 5.1+
• OS: Any
• Assigned to: Tatiana Azundris Nuernberg
• CPU Architecture: Any

[4 Nov 2009 8:23] Sergei Golubchik

Description:
mysql_insert() at the end sets thd->count_cuted_fields to the default value.

   thd->count_cuted_fields= CHECK_FIELD_IGNORE;

When this insert is executed from inside a trigger it changes thd->count_cuted_fields settings of the top-level statement.

How to repeat:
create table t1 (id int not null);
create table t2 (id int not null);
insert t1 values (1),(2),(3);
update t1 set id=null;
create trigger t1_bu before update on t1 for each row insert into t2 values (3);
update t1 set id=null;

[4 Nov 2009 8:39] Valeriy Kravchuk

Verified with 5.0.88 and 5.1.41 from bzr:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.41-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (id int not null);
Query OK, 0 rows affected (0.06 sec)

mysql> create table t2 (id int not null);
Query OK, 0 rows affected (0.12 sec)

mysql> insert t1 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update t1 set id=null;
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 3

mysql> create trigger t1_bu before update on t1 for each row insert into t2
    -> values (3);
Query OK, 0 rows affected (0.14 sec)

mysql> update t1 set id=null;
ERROR 1048 (23000): Column 'id' cannot be null

[4 Nov 2009 12:29] Peter Laursen

I am very surprised that the statement

UPDATE t1 SET id=NULL;

succeeds (it inserts ZERO's - at least in '' (empty) sql_mode).

[5 Nov 2009 15:12] Sergei Golubchik

this bug was found when I looked at why CHECK_FIELD_IGNORE is treated as CHECK_FIELD_ERROR_FOR_NULL.

Attached is a patch that fixes both problems.

[5 Nov 2009 15:13] Sergei Golubchik

fix for Bug#48525

Attachment: bug48525.patch (text/x-patch), 4.21 KiB.

[18 Feb 2010 17:03] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/100793

3346 Tatiana A. Nurnberg	2010-02-18
      Bug#48525: trigger changes "Column 'id' cannot be null" behaviour
      
      CHECK_FIELD_IGNORE was treated as CHECK_FIELD_ERROR_FOR_NULL;
      UPDATE...SET...NULL on NOT NULL fields behaved differently after
      a trigger.
      
      Now distinguishes between IGNORE and ERROR_FOR_NULL and save/restores
      check-field options.
     @ mysql-test/r/trigger.result
        Show that UPDATE...SET...NULL on NOT NULL columns doesn't behave differently
        when run after a trigger.
     @ mysql-test/t/trigger.test
        Show that UPDATE...SET...NULL on NOT NULL columns doesn't behave differently
        when run after a trigger.
     @ sql/field_conv.cc
        CHECK_FIELD_IGNORE was treated as CHECK_FIELD_ERROR_FOR_NULL.
        Distinguish between the two.
     @ sql/sp_head.cc
        raise error as needed
     @ sql/sql_class.cc
        Save and restore check-fields options.
     @ sql/sql_class.h
        Make room so we can save check-fields options.
     @ sql/sql_insert.cc
        raise error as needed

[1 Mar 2010 8:42] Bugs System

Pushed into 5.1.45 (revid:[e-mail]-20100301083827-xnimmrjg6bh33o1o) (version source revid:[e-mail]-20100218170217-do91q8chknubwzqb) (merge vers: 5.1.45) (pib:16)

[2 Mar 2010 14:33] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:[e-mail]-20100225090938-2j5ybqoau570mytu) (merge vers: 6.0.14-alpha) (pib:16)

[2 Mar 2010 14:39] Bugs System

Pushed into 5.5.3-m2 (revid:[e-mail]-20100302072233-t3uqgjzdukt1pyhe) (version source revid:[e-mail]-20100223132645-fj9ybhpzs9fl1voe) (merge vers: 5.5.2-m2) (pib:16)

[2 Mar 2010 14:44] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100302072432-k8xvfkgcggkwgi94) (version source revid:[e-mail]-20100224135227-rcqs9pe9b2in80pf) (pib:16)

[29 Mar 2010 3:11] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/104528

3431 Tatiana A. Nurnberg	2010-03-29
      Bug#48525: trigger changes "Column 'id' cannot be null" behaviour
      
      CHECK_FIELD_IGNORE was treated as CHECK_FIELD_ERROR_FOR_NULL;
      UPDATE...SET...NULL on NOT NULL fields behaved differently after
      a trigger.
      
      Now distinguishes between IGNORE and ERROR_FOR_NULL and save/restores
      check-field options.
     @ mysql-test/r/trigger.result
        Show that UPDATE...SET...NULL on NOT NULL columns doesn't behave differently
        when run after a trigger.
     @ mysql-test/t/trigger.test
        Show that UPDATE...SET...NULL on NOT NULL columns doesn't behave differently
        when run after a trigger.
     @ sql/field_conv.cc
        CHECK_FIELD_IGNORE was treated as CHECK_FIELD_ERROR_FOR_NULL.
        Distinguish between the two.
     @ sql/sp_head.cc
        Raise error as needed.
     @ sql/sql_class.cc
        Save and restore check-fields options.
     @ sql/sql_class.h
        Make room so we can save check-fields options.
     @ sql/sql_insert.cc
        Raise error as needed.

[6 Apr 2010 7:56] Bugs System

Pushed into 5.1.46 (revid:[e-mail]-20100405111026-7kz1p8qlzglqgfmu) (version source revid:[e-mail]-20100329023230-kgpoe9j8irm13kta) (merge vers: 5.1.46) (pib:16)

[28 Apr 2010 3:10] Tatiana Azundris Nuernberg

queued in -bugteam for 5.1.47, 6.0.14

[28 May 2010 5:48] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100422150750-vp0n37kp9ywq5ghf) (pib:16)

[28 May 2010 6:17] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)

[28 May 2010 6:45] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100403173551-52r97erqowlqwkon) (merge vers: 5.5.4-m3) (pib:16)

[8 Jun 2010 18:39] Paul DuBois

Noted in 5.1.46, 5.5.5, 6.0.14 changelogs.

A trigger could change the behavior of assigning NULL to a NOT NULL
column.

[17 Jun 2010 11:48] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:25] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

[17 Jun 2010 13:13] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)