MySQL Bugs: #119129: Unexpected error `BIGINT value is out of range in '--9223372036854775808'`

Bug #119129	Unexpected error `BIGINT value is out of range in '--9223372036854775808'`
Submitted:	10 Oct 2025 14:13	Modified:	8 Jan 2:48
Reporter:	chi zhang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

In the following test case, there are two equivalent queries, the normal query succeeds, but the prepared query triggers an error.

```
SELECT ((- (-9223372036854775808))); -- 9223372036854775808
SET @a = -9223372036854775808;
PREPARE prepare_query FROM 'SELECT ((- (?)))';
EXECUTE prepare_query USING @a;
DEALLOCATE PREPARE prepare_query;  -- BIGINT value is out of range in '--9223372036854775808'
```

How to repeat:
```
SELECT ((- (-9223372036854775808))); -- 9223372036854775808
SET @a = -9223372036854775808;
PREPARE prepare_query FROM 'SELECT ((- (?)))';
EXECUTE prepare_query USING @a;
DEALLOCATE PREPARE prepare_query;  -- BIGINT value is out of range in '--9223372036854775808'
```

Hi Chi Zhang,

Thank you for your bug report.
I am not able to reproduce this on MySQL 9.4.0.
Did use any non-default settings in your session (e.g., for sql_mode).

Hi Øystein Grøvlen,

Thanks for your work. I realized I was mistaken. I want to say that these two queries have different precision.

The non-prepared statement returns an integer, but the prepared statement returns a float value. Why are they inconsistent?

```
"test.sql" 6L, 221B                                                                                                                                                                                                                                           2,9           All
SELECT - (-(9223372036854775808));   -- 9223372036854775808
SET @a = -(9223372036854775808);
PREPARE prepare_query FROM 'SELECT - (?)';
EXECUTE prepare_query USING @a; -- 9.223372036854776e18
```

This is not a bug.
Prepared statements deduce the type of dynamic parameters according to
SQL standard rules, which are usually based on the context.
In this case, the context is a minus sign, which means the parameter is a
numeric value. The deduced parameter is then an integer, however the value will
overflow for a signed integer, hence the statement is internally reprepared with
the parameter as a double precision value.

If you want a specific type for a dynamic parameter, you should specify
CAST(? AS <type>) instead of simply ?.

Hi Øystein Grøvlen,

Thanks for your work! I try to reproduce the error and found that it can be triggered in MySQL 5.7. Not sure if it is meaningful as this version is too old.