Bug #44489 Error 2021 (msg. CPF5035) while inserting invalid date using trigger

• Submitted: 27 Apr 2009 9:24
• Modified: 21 Feb 2014 18:08
• Reporter: Nidhi Shrotriya
• Status: Unsupported
• Category: MySQL Server: DB2SE for IBM i
• Severity: S3 (Non-critical)
• Version: mysql-5.1.33
• OS: IBM i (5.4 & 6.1)
• CPU Architecture: Any

[27 Apr 2009 9:24] Nidhi Shrotriya

Description:
With db2i:
------------
set sql_mode="traditional";
create table t1 (a date);
insert into t1 values ('2004-01-00');
ERROR 22007: Incorrect date value: '2004-01-00' for column 'a' at row 1
set sql_mode="";
create trigger t1_bi before insert on t1 for each row set new.a = '2004-01-00';
set sql_mode="traditional";
insert into t1 values ('2004-01-01');
ERROR HY000: Got error 2021 'See message CPF5035 in joblog for job 141032/QUSER/QSQSRVR.' from IBMDB2I
select * from t1;
a

With MyISAM/Innodb:
-------------------
et sql_mode="traditional";
create table t1 (a date);
insert into t1 values ('2004-01-00');
ERROR 22007: Incorrect date value: '2004-01-00' for column 'a' at row 1
set sql_mode="";
create trigger t1_bi before insert on t1 for each row set new.a = '2004-01-00';
set sql_mode="traditional";
insert into t1 values ('2004-01-01');
select * from t1;
a
2004-01-00

In case, the error is expected with db2i and documented, the error msg. should be improved.
From this-
ERROR HY000: Got error 2021 'See message CPF5035 in joblog for job 141032/QUSER/QSQSRVR.' from IBMDB2I
to something like-
Incorrect date value.

How to repeat:
As mentioned in the Description.