Bug #79910 NULLIF fails with ERROR 1271 with date and multi-type character

• Submitted: 11 Jan 2016 6:41
• Modified: 17 Feb 2021 12:00
• Reporter: Su Dylan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any

[11 Jan 2016 6:41] Su Dylan

Description:
Output:
=====
mysql> SELECT NULLIF( date '2016-01-01', '⑴' );
ERROR 1271 (HY000): Illegal mix of collations for operation 'nullif'

Problem:
=====
Success is expected.

How to repeat:
SELECT NULLIF( date '2016-01-01', '⑴' );

Suggested fix:
success.

[11 Jan 2016 7:14] MySQL Verification Team

Hello Su Dylan,

Thank you for the report and test case.
Observed that 5.6.28/5.7.10 affected.

Thanks,
Umesh

[17 Feb 2021 11:58] Tor Didriksen

8.0 says the same for both of these:

mysql> SELECT NULLIF( date '2016-01-01', '⑴' );
ERROR 1525 (HY000): Incorrect DATE value: '⑴'
mysql> SELECT NULLIF( date '2016-01-01', '(1)' );
ERROR 1525 (HY000): Incorrect DATE value: '(1)'

which I believe is correct.