Bug #103458 PREPARE type checking error
Submitted: 23 Apr 2021 15:22 Modified: 21 Jun 2021 14:43
Reporter: Sébastien F. Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[23 Apr 2021 15:22] Sébastien F.
PREPARE statement gives a type checking error while I use two strings with NULLIF().

The param contains empty string or datetime string representation as first NULLIF() operand.

The 2nd NULLIF() operand is always an empty string.

The same non-prepared INSERT request works fine.

How to repeat:

PREPARE i FROM 'INSERT INTO tests SET t_date = NULLIF(?, '''')';
-- Gives one these :
--   Error Code: 1292. Incorrect datetime value: '' for column '?' at row 1
--   Error Code: 1525. Incorrect DATETIME value: ''
-- OK with MySQL 8.0.20

-- PREPARE i FROM 'INSERT INTO tests SET t_date = IF(? = '''', NULL, ?)';
-- OK with IF() and MySQL 8.0.23

SET @t_date = NOW();
EXECUTE i USING @t_date;
[23 Apr 2021 16:04] Sébastien F.
Bug present on 8.0.23 and 8.0.24
[23 Apr 2021 16:50] Roy Lyseng
An empty string is not a valid date, so this should not be considered a bug.
[23 Apr 2021 17:47] Sébastien F.
> An empty string is not a valid date, so this should not be considered a bug.

Absolutely, that's why I use NULLIF() to compare the param and an empty string, so NULL or a valid date will be SET.
[26 Apr 2021 13:38] MySQL Verification Team
Hi Mr. F.,

Thank you for your bug report.

This is indeed a regression bug in 8.0.24.

Verified as reported.
[21 Jun 2021 14:43] Paul DuBois
Posted by developer:
Fixed in 8.0.27.

In prepared statements, NULLIF() result type determination could be
[28 Jun 2021 11:41] MySQL Verification Team
Thank you, Paul.