Details
Major Description
Summary
There is a logic inconsistency and data integrity issue where a CONCAT expression involving implicit type conversion results in silent data truncation when evaluated within a derived table, but remains intact in a direct query. The bug appears to stem from the optimizer underestimating the required character width for the materialized temporary table column.
Reproduction Steps
CREATE TABLE t0(c0 INT UNIQUE NOT NULL); |
|
|
INSERT INTO t0 VALUES (-1622683835); |
-- Query A
|
SELECT CONCAT(t0.c0, '#',(CONCAT((- ''), 793811274))) AS sig FROM t0 WHERE 1 GROUP BY t0.c0 HAVING (COUNT(t0.c0) != 0); |
-- Query B
|
SELECT ref0 FROM (SELECT CONCAT(t0.c0, '#', CONCAT((- ''), 793811274)) AS ref0, ((COUNT(t0.c0) != 0)) AS ref1 FROM t0 WHERE 1 GROUP BY t0.c0) AS s WHERE ref1; |
Observed Behavior
In Query B, the result of the CONCAT operation is truncated. Specifically, the string "0793811274" (result of CONCAT(( ''), 793811274)) is stored as "-079381127". The last character '4' is missing.
mysql> -- Query A |
mysql> SELECT CONCAT(t0.c0, '#',(CONCAT((- ''), 793811274))) AS sig FROM t0 WHERE 1 GROUP BY t0.c0 HAVING (COUNT(t0.c0) != 0); |
+-------------------------+ |
| sig |
|
+-------------------------+ |
| -1622683835#-0793811274 |
|
+-------------------------+ |
1 row in set, 1 warning (0.00 sec) |
|
|
mysql> -- Query B |
mysql> SELECT ref0 FROM (SELECT CONCAT(t0.c0, '#', CONCAT((- ''), 793811274)) AS ref0, ((COUNT(t0.c0) != 0)) AS ref1 FROM t0 WHERE 1 GROUP BY t0.c0) AS s WHERE ref1; |
+------------------------+ |
| ref0 |
|
+------------------------+ |
| -1622683835#-079381127 |
|
+------------------------+ |
1 row in set, 1 warning (0.00 sec) |