Description:
MySQL produces different results for two REPLACE() expressions that should be semantically equivalent.
In the following example, t0.c0 is a TINYINT column containing the value 1. The first expression uses the integer literal 1, while the second expression uses the column t0.c0.
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 TINYINT
);
INSERT INTO t0 (c0) VALUES (1);
SELECT
REPLACE('str11', 1, 21) AS ref1,
REPLACE('str11', t0.c0, 21) AS ref2
FROM t0;
The result is:
+--------+--------+
| ref1 | ref2 |
+--------+--------+
| str2121| str21 |
+--------+--------+
However, t0.c0 contains exactly the value 1, so replacing 1 with 21 in str11 should produce the same result as the literal expression:
REPLACE('str11', 1, 21)
which returns:
str2121
Instead, when the TINYINT column is used, MySQL returns:
str21
This means that the result of REPLACE() depends on whether the value 1 is supplied as an integer literal or as a TINYINT column value, even though both expressions evaluate to the same numeric value.
The discrepancy can be further minimized to:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 TINYINT
);
INSERT INTO t0 (c0) VALUES (1);
SELECT REPLACE('str11', 1, 21)
FROM t0;
which returns:
str2121
while:
SELECT REPLACE('str11', t0.c0, 21)
FROM t0;
returns:
str21
The behavior appears related to implicit type handling inside REPLACE(). MySQL documentation states that implicit conversion occurs between numeric and string values when required, and REPLACE() is a string-returning function.
The two expressions nevertheless produce observably different results for the same value.
How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 TINYINT
);
INSERT INTO t0 (c0)
VALUES (1);
SELECT
REPLACE('str11', 1, 21) AS literal_case,
REPLACE('str11', t0.c0, 21) AS column_case
FROM t0;
Actual result:
+--------------+-------------+
| literal_case | column_case |
+--------------+-------------+
| str2121 | str21 |
+--------------+-------------+
Expected result:
+--------------+-------------+
| literal_case | column_case |
+--------------+-------------+
| str2121 | str2121 |
+--------------+-------------+
The second expression should return str2121, because t0.c0 contains the value 1.
Description: MySQL produces different results for two REPLACE() expressions that should be semantically equivalent. In the following example, t0.c0 is a TINYINT column containing the value 1. The first expression uses the integer literal 1, while the second expression uses the column t0.c0. DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 TINYINT ); INSERT INTO t0 (c0) VALUES (1); SELECT REPLACE('str11', 1, 21) AS ref1, REPLACE('str11', t0.c0, 21) AS ref2 FROM t0; The result is: +--------+--------+ | ref1 | ref2 | +--------+--------+ | str2121| str21 | +--------+--------+ However, t0.c0 contains exactly the value 1, so replacing 1 with 21 in str11 should produce the same result as the literal expression: REPLACE('str11', 1, 21) which returns: str2121 Instead, when the TINYINT column is used, MySQL returns: str21 This means that the result of REPLACE() depends on whether the value 1 is supplied as an integer literal or as a TINYINT column value, even though both expressions evaluate to the same numeric value. The discrepancy can be further minimized to: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 TINYINT ); INSERT INTO t0 (c0) VALUES (1); SELECT REPLACE('str11', 1, 21) FROM t0; which returns: str2121 while: SELECT REPLACE('str11', t0.c0, 21) FROM t0; returns: str21 The behavior appears related to implicit type handling inside REPLACE(). MySQL documentation states that implicit conversion occurs between numeric and string values when required, and REPLACE() is a string-returning function. The two expressions nevertheless produce observably different results for the same value. How to repeat: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 TINYINT ); INSERT INTO t0 (c0) VALUES (1); SELECT REPLACE('str11', 1, 21) AS literal_case, REPLACE('str11', t0.c0, 21) AS column_case FROM t0; Actual result: +--------------+-------------+ | literal_case | column_case | +--------------+-------------+ | str2121 | str21 | +--------------+-------------+ Expected result: +--------------+-------------+ | literal_case | column_case | +--------------+-------------+ | str2121 | str2121 | +--------------+-------------+ The second expression should return str2121, because t0.c0 contains the value 1.