Bug #112703 prepare statment has no warning comping to not prepare statment.

• Submitted: 12 Oct 2023 12:13
• Modified: 12 Oct 2023 12:43
• Reporter: haizhen xue
• Status: Verified
• Category: MySQL Server: Prepared statements
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[12 Oct 2023 12:13] haizhen xue

Description:
1. not prepare statment has a warnings,but prepare statment has not warnings;
2. <= 8.0.29， prepare statment has a result, I think it is reasonable.

CREATE TABLE test_time(`time_col` time(6) DEFAULT NULL);
insert into test_time values('02:59:59.000000');

mysql> select time_col from test_time where  time_col IN ('NULL','02:59:59.000000');
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Incorrect time value: 'NULL' for column 'time_col' at row 1 |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> prepare stmt from "select time_col from test_time where  time_col IN (?,'02:59:59.000000');";set @time_col1='NULL';execute stmt USING @time_col1;deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Query OK, 0 rows affected (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

How to repeat:
CREATE TABLE test_time(`time_col` time(6) DEFAULT NULL);
insert into test_time values('02:59:59.000000');

mysql> select time_col from test_time where  time_col IN ('NULL','02:59:59.000000');
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Incorrect time value: 'NULL' for column 'time_col' at row 1 |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> prepare stmt from "select time_col from test_time where  time_col IN (?,'02:59:59.000000');";set @time_col1='NULL';execute stmt USING @time_col1;deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Query OK, 0 rows affected (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Suggested fix:
prepare stmt from "select time_col from test_time where  time_col IN (?,'02:59:59.000000');";set @time_col1='NULL';execute stmt USING @time_col1;

Prepare statment should has a warning. or return a result.

[12 Oct 2023 12:43] MySQL Verification Team

HI Mr. xue,

Thank you for your bug report.

We have managed to repeat it with both, latest 8.0 and 8.1:

Level	Code	Message
Warning	1292	Incorrect time value: 'NULL' for column 'time_col' at row 1

Verified as reported.