Description:
MySQL produces different results for the same REPLACE() expression depending only on whether the query contains a GROUP BY clause.
The following two queries operate on the same single-row table and use exactly the same REPLACE() expression:
SELECT REPLACE('str16', t0.c1, 47) AS ref2
FROM t0;
and
SELECT REPLACE('str16', t0.c1, 47) AS ref2
FROM t0
GROUP BY t0.c1;
However, they produce different results:
Without GROUP BY:
str476
With GROUP BY:
str47
The table contains only one row, with t0.c1 = 1. Therefore, the GROUP BY clause does not change the number of input rows or the grouped value.
The difference is also unexpected from the semantics of the query: adding GROUP BY t0.c1 should not cause the same REPLACE() expression to produce a different value when there is only a single group containing the same input row.
This suggests that GROUP BY affects the evaluation, implicit conversion, or internal handling of the arguments to REPLACE() in a way that changes the resulting string.
How to repeat:
drop table if exists t0;
create table t0
(
c0 smallint,
c1 decimal
);
INSERT INTO t0 (c0, c1)
VALUES (null, 1);
SELECT REPLACE('str16', t0.c1, 47) AS ref2
FROM t0;
SELECT REPLACE('str16', t0.c1, 47) AS ref2
FROM t0
GROUP BY t0.c1;
Description: MySQL produces different results for the same REPLACE() expression depending only on whether the query contains a GROUP BY clause. The following two queries operate on the same single-row table and use exactly the same REPLACE() expression: SELECT REPLACE('str16', t0.c1, 47) AS ref2 FROM t0; and SELECT REPLACE('str16', t0.c1, 47) AS ref2 FROM t0 GROUP BY t0.c1; However, they produce different results: Without GROUP BY: str476 With GROUP BY: str47 The table contains only one row, with t0.c1 = 1. Therefore, the GROUP BY clause does not change the number of input rows or the grouped value. The difference is also unexpected from the semantics of the query: adding GROUP BY t0.c1 should not cause the same REPLACE() expression to produce a different value when there is only a single group containing the same input row. This suggests that GROUP BY affects the evaluation, implicit conversion, or internal handling of the arguments to REPLACE() in a way that changes the resulting string. How to repeat: drop table if exists t0; create table t0 ( c0 smallint, c1 decimal ); INSERT INTO t0 (c0, c1) VALUES (null, 1); SELECT REPLACE('str16', t0.c1, 47) AS ref2 FROM t0; SELECT REPLACE('str16', t0.c1, 47) AS ref2 FROM t0 GROUP BY t0.c1;