MySQL Bugs: #98535: invalid DATE value '0' in triggers not strict mode

Bug #98535	invalid DATE value '0' in triggers not strict mode
Submitted:	10 Feb 2020 12:54	Modified:	13 Feb 2020 13:33
Reporter:	Eimantas Jatkonis	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
DATE value comparison in triggers inconsistent.
Disabled strict mode - same result.
See atached example.

How to repeat:
SET SESSION sql_mode = '';

create database if not exists test;

use test;

drop table if exists a;
create table a (d date);

insert into a values (now());

delimiter //
CREATE TRIGGER b BEFORE UPDATE ON a
FOR EACH ROW 
BEGIN
  IF (0 < NEW.d) THEN  #No error
    SET @V := 'lessA';
  END IF;

  IF (IFNULL(@N, 0) < NEW.d) THEN #Produces error
    SET @V := 'lessB';
  END IF;
END//

delimiter ;  

update a set d = NOW();

SELECT @V;
SET SESSION sql_mode = '';

create database if not exists test;

use test;

drop table if exists a;
create table a (d date);

insert into a values (now());

delimiter //
CREATE TRIGGER b BEFORE UPDATE ON a
FOR EACH ROW 
BEGIN
  IF (0 < NEW.d) THEN 
    SET @V := 'lessA';
  END IF;

  IF (IFNULL(@N, 0) < NEW.d) THEN 
    SET @V := 'lessB';
  END IF;
END//

delimiter ;  

update a set d = NOW();
#ERROR 1525 (HY000): Incorrect DATE value: '0'

SELECT @V;
#+-------+
#| @V    |
#+-------+
#| lessA |
#+-------+

Suggested fix:
Changing IFNULL(@N, 0) to DATE(IFNULL(@N, 0)) - no error
but probably related to this BUG
https://bugs.mysql.com/bug.php?id=77103

"how to repeat" script repeated 2 times.

Hi Mr. Jatkonis,

Thank you for your bug report.

However, I can not see how is this a bug ......

Since @N is not defined, IFNULL(@N,0) returns 0 and zero is not a valid date type. Hence, you have got the correct result.

Not a bug.

Yes, i know @N is not defined.

So both conditions should behave same:

  IF (0 < NEW.d) THEN  #No error
    SET @V := 'lessA';
  END IF;

  IF (IFNULL(@N, 0) < NEW.d) THEN #Produces error
    SET @V := 'lessB';
  END IF;

But only second one condition produce error.
Why first comparision is OK??

And I am not saving any data to NEW.d variable, just comparison.
And scrict mode is OFF - so PLEASE recheck.

Hi,

There are two issues to consider. First one, please check that you are using 8.0.19.

Second issue is in type setting. 0 as a constant can be internally set to one integer type, while IFNULL() can return completely different type. This can be checked in the mysql CLI, which can optionally return types if you use it simply with SELECT ......

I am using 8.0.19,

Checked your way 
with undefined @N: IFNULL(@N, 0) is LONGBLOB (BINARY)

mysql> SELECT CONVERT(0, BINARY) < DATE(NOW());
ERROR 1525 (HY000): Incorrect DATE value: '0'

mysql> SELECT CONVERT(0, CHAR) < DATE(NOW());
ERROR 1525 (HY000): Incorrect DATE value: '0'

mysql> SELECT CAST('2020-02-13' AS CHAR) < DATE(NOW());
+------------------------------------------+
| CAST('2020-02-13' AS CHAR) < DATE(NOW()) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+

And if this problem with TYPES - error message missleads to "value".
And prvided CAST to CHAR gives different results with different values.

Thank you for proving that this is not a bug.

You can not convert any CLOB into a datetime, nor a BINARY not. a CHAR.

Not a bug.

You CAN convert CHAR to DATE

mysql> SELECT CAST('2020-02-13' AS CHAR) < DATE(NOW());
+------------------------------------------+
| CAST('2020-02-13' AS CHAR) < DATE(NOW()) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+

This is MIXED behaviour and depends on value, not the TYPE only.

Yes, you are right in that respect.

But, that is also expected behaviour.

Not a bug.