|
MAKETIME() returns bad results in some cases:
SELECT
|
MAKETIME(1e10,0,0),
|
MAKETIME(-1e10,0,0),
|
MAKETIME(1e50,0,0),
|
MAKETIME(-1e50,0,0),
|
MAKETIME(COALESCE(1e50),0,0),
|
MAKETIME(COALESCE(-1e50),0,0)\G
|
*************************** 1. row ***************************
|
MAKETIME(1e10,0,0): 838:59:59
|
MAKETIME(-1e10,0,0): -838:59:59
|
MAKETIME(1e50,0,0): 838:59:59
|
MAKETIME(-1e50,0,0): -00:00:00
|
MAKETIME(COALESCE(1e50),0,0): -00:00:00
|
MAKETIME(COALESCE(-1e50),0,0): -00:00:00
|
1 row in set, 3 warnings (0.00 sec)
|
Notice wrong '-00:00:00' value. The expected value is '838:59:59' or '-838:59:59' (depending on the sign).
The problem happens for two reasons:
Item_func_hybrid_result_type::val_int is affected
SELECT LEFT('a', COALESCE(1e30));
|
+---------------------------+
|
| LEFT('a', COALESCE(1e30)) |
|
+---------------------------+
|
| |
|
+---------------------------+
|
Field_float::val_int is also affected
A similar problem is repeatable in this script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a FLOAT);
|
INSERT INTO t1 VALUES (1e30);
|
SELECT * FROM t1;
|
SELECT MAKETIME(a,0,0) FROM t1;
|
+-----------------+
|
| MAKETIME(a,0,0) |
|
+-----------------+
|
| -00:00:00 |
|
+-----------------+
|
A similar problem is repeatable in this script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a FLOAT);
|
INSERT INTO t1 VALUES (1e30);
|
SELECT * FROM t1;
|
SELECT LEFT('a',a), LEFT('a',1e30) FROM t1;
|
+-------------+----------------+
|
| LEFT('a',a) | LEFT('a',1e30) |
|
+-------------+----------------+
|
| | a |
|
+-------------+----------------+
|
The value for the first column is wrong. The length argument is treated as negative instead of big positive.
Item_param::val_int is also affected
A similar problem is repeatable in this script:
PREPARE stmt FROM 'SELECT LEFT(111,?)';
|
SET @a=1e30;
|
EXECUTE stmt USING @a;
|
+-------------+
|
| LEFT(111,?) |
|
+-------------+
|
| |
|
+-------------+
|
and in this script (in 10.3 and 10.4):
EXECUTE IMMEDIATE 'SELECT LEFT(111,?)' USING 1e30;
|
+-------------+
|
| LEFT(111,?) |
|
+-------------+
|
| |
|
+-------------+
|
The expected result is '111'. The length argument is treated as negative instead of big positive.
Item_cache_real::val_int is also affected
A similar problem is repeatable in this script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (1),(2),(3);
|
SELECT LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1));
|
+-----------------------------------------+
|
| LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)) |
|
+-----------------------------------------+
|
| |
|
+-----------------------------------------+
|
The expected result is 'a'. The length argument is treated as negative instead of big positive.
Item_real_func::val_int is also affected
SELECT LEFT('a',EXP(50));
|
+-------------------+
|
| LEFT('a',EXP(50)) |
|
+-------------------+
|
| |
|
+-------------------+
|
The expected result is 'a'.
A few method in item_sum.h and item_sum.cc are also affected
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DOUBLE);
|
INSERT INTO t1 VALUES (1e30),(0);
|
SELECT LEFT('a', SUM(a)) FROM t1;
|
+-------------------+
|
| LEFT('a', SUM(a)) |
|
+-------------------+
|
| |
|
+-------------------+
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DOUBLE);
|
INSERT INTO t1 VALUES (1e30),(0);
|
SELECT LEFT('a', AVG(a)) FROM t1;
|
+-------------------+
|
| LEFT('a', AVG(a)) |
|
+-------------------+
|
| |
|
+-------------------+
|
|