MySQL Bugs: #103458: PREPARE type checking error

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:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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:
CREATE TABLE tests (t_date DATETIME NULL);

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;

Bug present on 8.0.23 and 8.0.24

An empty string is not a valid date, so this should not be considered a bug.

> 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.

Hi Mr. F.,

Thank you for your bug report.

This is indeed a regression bug in 8.0.24.

Verified as reported.

Posted by developer:
 
Fixed in 8.0.27.

In prepared statements, NULLIF() result type determination could be
incorrect.

Thank you, Paul.