Bug #119637 Data type of value changed by prepared statement

• Submitted: 7 Jan 8:15
• Modified: 7 Jan 9:34
• Reporter: chi zhang
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 9.5.0
• OS: Any
• CPU Architecture: Any

[7 Jan 8:15] chi zhang

Description:
Hi,

In the following test case, there is a prepared SELECT query, and an equivalent normal SELECT query. But they have inconsistent results.

```
CREATE TABLE t1(c0 DECIMAL ZEROFILL) ;
INSERT IGNORE INTO t1(c0) VALUES(0);
SELECT COALESCE(t1.c0, NULL) LIKE (0), c0 FROM t1; -- 1       0000000000
SET @a = NULL;
PREPARE prepare_query FROM 'SELECT COALESCE(t1.c0, ?) LIKE (0), c0 FROM t1';
EXECUTE prepare_query USING @a; -- 0       0000000000
```

Then I got the result of COALESCE and found the type of c0 is changed in the prepared statement:
```
SELECT COALESCE(t1.c0, NULL), c0 FROM t1; -- 0       0000000000
SET @a = NULL;
PREPARE prepare_query FROM 'SELECT COALESCE(t1.c0, ?), c0 FROM t1';
EXECUTE prepare_query USING @a; -- 0.000000000000000000000000000000        0000000000
```

How to repeat:
```
CREATE TABLE t1(c0 DECIMAL ZEROFILL) ;
INSERT IGNORE INTO t1(c0) VALUES(0);
SELECT COALESCE(t1.c0, NULL) LIKE (0), c0 FROM t1; -- 1       0000000000
SET @a = NULL;
PREPARE prepare_query FROM 'SELECT COALESCE(t1.c0, ?) LIKE (0), c0 FROM t1';
EXECUTE prepare_query USING @a; -- 0       0000000000
```

```
SELECT COALESCE(t1.c0, NULL), c0 FROM t1; -- 0       0000000000
SET @a = NULL;
PREPARE prepare_query FROM 'SELECT COALESCE(t1.c0, ?), c0 FROM t1';
EXECUTE prepare_query USING @a; -- 0.000000000000000000000000000000        0000000000
```

[7 Jan 9:34] Roy Lyseng

This is not a bug.
Slight changes in derived precision and scale when using dynamic parameters in prepared statements is expected.
You may control the derived data type better by substituting '?' with e.g.
'CAST(? AS DECIMAL(10,0))'