Bug #30617 Round() function not working under some circumstances in InnoDB
Submitted: 24 Aug 2007 6:27 Modified: 21 Dec 2007 16:16
Reporter: Shu Liao Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.41-community-nt-log, 4.1, 5.0, 5.1, 5.2 BK OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: innodb, round
Triage: D3 (Medium)

[24 Aug 2007 6:27] Shu Liao
Description:
What I'm trying to do is to use the round function to get rid of the decimals by using an if statement. 

Eg. (FIELD is a decimal(14,4)) SELECT IF(1=2, FIELD, ROUND(FIELD))

This should return values without the decimals, however that's not the case under these conditions:

-BOTH the THEN and ELSE FIELD in the IF statement are the same. If they are different, the ROUND() will apply. 

-Joined with another table and ORDER BY a FIELD in that other table. If the ORDER BY is not used, then the ROUND() will work as normal.

I've tested it with myISAM and it works normally.

How to repeat:
CREATE TABLE `test` (
  `c1` int(11) NOT NULL auto_increment,
  `c2` decimal(14,4) NOT NULL,
  PRIMARY KEY  (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test` (`c1`, `c2`) VALUES (1, 1.0000);

CREATE TABLE `test2` (
  `c1` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test2` (`c1`) VALUES (1);

SELECT IF(1=2, c2, round(c2)) 
FROM test
INNER JOIN test2 ON ( test.c1 = test2.c1 ) 
ORDER BY test2.c1
[24 Aug 2007 8:32] Sveta Smirnova
Thank you for the report.

Verified as described.
[27 Aug 2007 12:21] Heikki Tuuri
It is unlikely that the bug is in InnoDB. ROUND(...) is processed in the MySQL layer of code.
[27 Aug 2007 12:28] Shu Liao
I've tested it using MyISAM, but could not replicate the bug.
[11 Dec 2007 13:20] Timour Katchaounov
One possible reason for the bug is that columns materialized
in a temp table get their types changed in the temp table.
Ask Evgen for advice if this is the case.
[19 Dec 2007 18:14] Martin Hansson
I think this is too big for a bug fix. Consider:

CREATE TABLE t1 (
  c1 INT,
  c2 [DECIMAL|FLOAT|REAL|DOUBLE] (5, 4)
);

INSERT INTO t1 VALUES (0, 1.0000), (1, 1.0000);

SELECT IF( c1 = 1, c2, ROUND(c2) )
FROM t1
GROUP BY c1;

I use GROUP BY because MySQL uses a temporary table to compute the groups in the execution phase. The columns of which are c1 and the result of the IF. Now, the IF is run before insertion in the temporary table, so the result is stored in the second column. The number of decimals is set on the columns by looking at the IF branch that has the most decimals. Unfortunately, this information is thrown away right before data is read from the temporary table. 

In order for this to work like an execution without temporary table, we would have to "remember" the number of decimals from the value that was inserted, and then fix that after reading the temporary table. 

Like I said, I doubt this should be done as a simple bug fix.
[21 Dec 2007 16:16] Martin Hansson
This bug will be fixed as part of the fix for bug#33143
[24 Jan 2008 11:56] Bugs System
Pushed into 6.0.5-alpha
[24 Jan 2008 12:01] Bugs System
Pushed into 5.1.24-rc
[24 Jan 2008 12:02] Bugs System
Pushed into 5.0.56