Bug #118591 empty string parse to 0 in SET statement, which is different from that in the SELECT query

• Submitted: 7 Jul 11:28
• Modified: 10 Jul 7:11
• Reporter: chi zhang
• Status: Duplicate
• Category: MySQL Server: Data Types
• Severity: S1 (Critical)
• Version: 9.3.0
• OS: Any
• CPU Architecture: Any

[7 Jul 11:28] chi zhang

Description:
Hi,

there are two equivalent queries and the first one is a normal SELECT and the second one is a prepared statement, but they return different result.

```
CREATE TABLE IF NOT EXISTS t0(c0 FLOAT) ;
INSERt INTO t0(c0) VALUES(NULL), (0);
SELECT COALESCE(CAST(t0.c0 AS SIGNED), '') AS ref0 FROM t0;  -- '', 0

SET @a = '';
PREPARE prepare_query FROM 'SELECT COALESCE(CAST(t0.c0 AS SIGNED), ?) AS ref0 FROM t0';
EXECUTE prepare_query USING @a; -- 0, 0
```

How to repeat:
this is the workload

```
CREATE TABLE IF NOT EXISTS t0(c0 FLOAT) ;
INSERt INTO t0(c0) VALUES(NULL), (0);
SELECT COALESCE(CAST(t0.c0 AS SIGNED), '') AS ref0 FROM t0;  -- '', 0

SET @a = '';
PREPARE prepare_query FROM 'SELECT COALESCE(CAST(t0.c0 AS SIGNED), ?) AS ref0 FROM t0';
EXECUTE prepare_query USING @a; -- 0, 0
```

[10 Jul 7:11] MySQL Verification Team

Hello Chi Zhang,

Thank you for the bug report.
Imho this is duplicate of Bug #118595, please see Bug #118595.

Regards,
Ashwini Patil