Bug #118601 Inconstant type between constants in prepare statement and SELECT statement

• Submitted: 8 Jul 9:30
• Modified: 9 Jul 8:47
• Reporter: chi zhang
• Status: Not a Bug
• Category: MySQL Server: Prepared statements
• Severity: S3 (Non-critical)
• Version: 8.0.42, 8.4.5
• OS: Any
• CPU Architecture: Any

[8 Jul 9:30] chi zhang

Description:
Hi,

The following equivalent queries return different results:

```
CREATE TABLE IF NOT EXISTS t2(c0 DECIMAL) ;
INSERT INTO t2(c0) VALUES (-1);
SELECT (LEAST(t2.c0, 1)) LIKE t2.c0 FROM t2;

SET @a = 1;
PREPARE prepare_query FROM 'SELECT (LEAST(t2.c0, ?)) LIKE t2.c0 from t2';
EXECUTE prepare_query USING @a;
```
The SELECT query returns 1 but the prepared statement returns 0;

I find the reason might be (LEAST(t2.c0, ?)) returns -1.000000000000000000000000000000

How to repeat:
```
CREATE TABLE IF NOT EXISTS t2(c0 DECIMAL) ;
INSERT INTO t2(c0) VALUES (-1);
SELECT (LEAST(t2.c0, 1)) LIKE t2.c0 FROM t2;

SET @a = 1;
PREPARE prepare_query FROM 'SELECT (LEAST(t2.c0, ?)) LIKE t2.c0 from t2';
EXECUTE prepare_query USING @a;
```

[8 Jul 11:06] MySQL Verification Team

Hello chi zhang,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

[9 Jul 6:50] Roy Lyseng

Posted by developer:
 
This is not a bug.
The non-prepared query works by coincidence and is dependent on the metadata from the LEAST function to give the desired result.
E.g, the following query will return false because the returned value is now a decimal value with a fraction:

  SELECT (LEAST(t2.c0, 1.1)) LIKE t2.c0 FROM t2;

You can force the problematic query to return true by coercing the parameter to have the correct metadata, like this:

  PREPARE ps FROM "SELECT (LEAST(t2.c0, CAST(? AS DECIMAL(10, 0)))) LIKE t2.c0 FROM t2;

However, LIKE works on string values, so you are in any case dependent upon the conversion from DECIMAL to string.

[9 Jul 8:47] chi zhang

Hi, 

I got it, thanks for your work on analysing this! I apologize for this false report.