Description:
When using the following two tables:
CREATE TABLE `dc_integer` (
`collect_id` int(11) NOT NULL,
`value` bigint(20) default NULL,
`begin_time` bigint(20) NOT NULL,
`end_time` bigint(20) NOT NULL,
PRIMARY KEY (`collect_id`,`end_time`,`begin_time`),
KEY `collect_id` (`collect_id`,`begin_time`),
KEY `end_time` (`end_time`),
CONSTRAINT `dc_integer_ibfk_1` FOREIGN KEY (`collect_id`) REFERENCES `dc_schedule` (`collect_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `dc_float` (
`collect_id` int(11) NOT NULL,
`value` float default NULL,
`begin_time` bigint(20) NOT NULL,
`end_time` bigint(20) NOT NULL,
PRIMARY KEY (`collect_id`,`end_time`,`begin_time`),
KEY `collect_id` (`collect_id`,`begin_time`),
KEY `end_time` (`end_time`),
CONSTRAINT `dc_float_ibfk_1` FOREIGN KEY (`collect_id`) REFERENCES `dc_schedule` (`collect_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Queries of the following type *used* to return LONGLONG or DOUBLE as the type for the second column, but now return a data type of VARSTRING. This breaks our application, because we use JDBC's getObject() call, and cast to the supertype of all numbers, and java.lang.String is not a subclass of Number:
SELECT @DELTA:=(value-@LAST_VALUE), IF( @DELTA IS NULL or @DELTA >= 0, @DELTA, value) , IF(begin_time < 1204667765203, 1204667765203, begin_time) AS v_begin_time, IF(end_time > 1204671425203, 1204671425203, end_time) AS v_end_time, @LAST_VALUE:=value FROM dc_integer WHERE 1204667765203 <= end_time AND 1204671425203 >= begin_time AND collect_id = 19 ORDER BY end_time;
Notice that in 5.0.54a and newer, the query returns the correct type for the *first* column, so mysql is aware of the correct type, it's just not using it as documented in the *second* column:
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if
"If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used."
Yes, we are aware of the following warning about user variables:
"The general rule is never to assign a value to a user variable in one part of a statement and use the same variable in some other part of the same statement. You might get the results you expect, but this is not guaranteed."
and this one
"Another issue with setting a variable and using it in the same statement is that the default result type of a variable is based on the type of the variable at the start of the statement. The following example illustrates this:"
But the output of -T will shows that at least for the first column the type of the user variable is correct (perhaps it is coerced by the "-"?).
How to repeat:
Create the above tables, execute the query with "-T" with the mysql client, and observe that the data types are different between 5.0.44 (correct) and 5.0.54a or newer (incorrect).
© 2025, Oracle Corporation and/or its affiliates