MySQL Bugs: #30617: Round() function not working under some circumstances in InnoDB

Bug #30617	Round() function not working under some circumstances in InnoDB
Submitted:	24 Aug 2007 8:27	Modified:	21 Dec 2007 17:16
Reporter:	Shu Liao
Status:	Duplicate
Category:	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:	Martin Hansson	Target Version:
Tags:	round, innodb
Triage:	D3 (Medium)

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

Thank you for the report.

Verified as described.

It is unlikely that the bug is in InnoDB. ROUND(...) is processed in the MySQL layer of
code.

I've tested it using MyISAM, but could not replicate the bug.

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.

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.

This bug will be fixed as part of the fix for bug#33143

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.56