MySQL Bugs: #33143: Incorrect ORDER BY for ROUND()/TRUNCATE() result

Bug #33143	Incorrect ORDER BY for ROUND()/TRUNCATE() result
Submitted:	11 Dec 2007 16:19	Modified:	1 Feb 2008 18:04
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0 and up	OS:	Any
Assigned to:	Martin Hansson	CPU Architecture:	Any

Description:
For DECIMAL/NUMERIC columns used in a ROUND() or TRUNCATE() operation, adding an ORDER BY for the ROUND() or TRUNCATE() result produces misordered output.

How to repeat:
Test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1( a DECIMAL(4, 3), b INT );
INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 4 ), ( 3, 3 ), ( 4, 2 ), ( 5, 1 );
SELECT a, b, ROUND( a, b ) FROM t1;
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c;
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
SELECT a, b, TRUNCATE( a, b ) FROM t1;
SELECT a, b, TRUNCATE( a, b ) AS c FROM t1 ORDER BY c;
SELECT a, b, TRUNCATE( a, b ) AS c FROM t1 ORDER BY c DESC;

Result:

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t1( a DECIMAL(4, 3), b INT );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 4 ), ( 3, 3 ), ( 4, 2 ), ( 5, 1 );
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT a, b, ROUND( a, b ) FROM t1;
+-------+------+---------------+
| a     | b    | ROUND( a, b ) |
+-------+------+---------------+
| 1.000 |    5 |         1.000 | 
| 2.000 |    4 |         2.000 | 
| 3.000 |    3 |         3.000 | 
| 4.000 |    2 |          4.00 | 
| 5.000 |    1 |           5.0 | 
+-------+------+---------------+
5 rows in set (0.00 sec)

mysql> SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c;
+-------+------+------+
| a     | b    | c    |
+-------+------+------+
| 5.000 |    1 |  5.0 | 
| 1.000 |    5 |  1.0 | 
| 2.000 |    4 |  2.0 | 
| 3.000 |    3 |  3.0 | 
| 4.000 |    2 |  4.0 | 
+-------+------+------+
5 rows in set (0.00 sec)

mysql> SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
+-------+------+------+
| a     | b    | c    |
+-------+------+------+
| 4.000 |    2 |  4.0 | 
| 3.000 |    3 |  3.0 | 
| 2.000 |    4 |  2.0 | 
| 1.000 |    5 |  1.0 | 
| 5.000 |    1 |  5.0 | 
+-------+------+------+
5 rows in set (0.01 sec)

mysql> SELECT a, b, TRUNCATE( a, b ) FROM t1;
+-------+------+------------------+
| a     | b    | TRUNCATE( a, b ) |
+-------+------+------------------+
| 1.000 |    5 |            1.000 | 
| 2.000 |    4 |            2.000 | 
| 3.000 |    3 |            3.000 | 
| 4.000 |    2 |             4.00 | 
| 5.000 |    1 |              5.0 | 
+-------+------+------------------+
5 rows in set (0.00 sec)

mysql> SELECT a, b, TRUNCATE( a, b ) AS c FROM t1 ORDER BY c;
+-------+------+------+
| a     | b    | c    |
+-------+------+------+
| 5.000 |    1 |  5.0 | 
| 1.000 |    5 |  1.0 | 
| 2.000 |    4 |  2.0 | 
| 3.000 |    3 |  3.0 | 
| 4.000 |    2 |  4.0 | 
+-------+------+------+
5 rows in set (0.01 sec)

mysql> SELECT a, b, TRUNCATE( a, b ) AS c FROM t1 ORDER BY c DESC;
+-------+------+------+
| a     | b    | c    |
+-------+------+------+
| 4.000 |    2 |  4.0 | 
| 3.000 |    3 |  3.0 | 
| 2.000 |    4 |  2.0 | 
| 1.000 |    5 |  1.0 | 
| 5.000 |    1 |  5.0 | 
+-------+------+------+
5 rows in set (0.01 sec)

Thank you for the bug report. Verified as described.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40192

ChangeSet@1.2546, 2007-12-19 12:23:33+01:00, mhansson@linux-st28.site +5 -0
  Bug#33143: Incorrect ORDER BY for ROUND()/TRUNCATE() result
  
  The ROUND(X, D) function changes the scale of a result field X to D digits.
  When D is not constant, the binary encoding of a DECIMAL value, used in
  filesort cannot guarantee proper ordering.
  Fixed by using the scale of X (as opposed to that of any expression 
  containong ROUND) in the temporary table, and changing
  number of digits only when retreiving sorted values from the temporary 
  table.

A result field is not allowed to change metadata after fix_length_and_dec has
been called.

Note that there is a difference between FLOAT, FLOAT(M), and FLOAT(M, D). The
last one is a non-standard extension. The different types mean:

- FLOAT       - non-specified number of signigicant digits, hardware sets the limit.
- FLOAT(M)    - M number of significant digits.
- FLOAT(M, D) - as above, but D digits after decimal point.

When these fields are used with ROUND( <field>, <non-constant> ), there are
subtle differences if a temporary table is used for query execution:

- FLOAT and FLOAT(M) columns are displayed with different lengths, depending on
  the precision for the current row. ROUND as above will truncate but not
  zero-fill after decimal point and trailing zeroes are always stripped.

- FLOAT(M, D)'s are displayed with M + D digits, they are zero-padded if there
  are not enough decimal digits, and truncated if there are too many integral
  digits. ROUND as above will add or strip zeroes but the display length is
  always the same.

The problem comes when applying variable ROUND to DECIMAL columns. These are
not stored in equal-sized areas and therefore their display length is tied to
the store length. Note that for DECIMAL we need not make a distinction between
DECIMAL(M, D), DECIMAL(M) and DECIMAL. The latter two are syntactic shorthand
for DECIMAL(M, 0) and DECIMAL(10, 0), respectively.

Before the fix for bug#30889, the ROUND(X, D) function changed the length of
DECIMAL fields at will. When looking at the code it is clear that the intent
was DECIMAL columns working just like FLOAT(M) and FLOAT rather than as
FLOAT(M, D).

This is how DECIMAL columns work with ROUND to nonconstant number of
decimal places :

o When actual rounding (truncation) takes place:

  - Without temp table:
    Length of DECIMAL field appears to change, like FLOAT(M) and
    FLOAT. FLOAT(M,D) will zero-pad up to D decimal places.

  - With temp table
    Fixed length, behaves like FLOAT(M, D)

o When no rounding takes place, i.e. zero-padding:

  - Without temp table: Length of field appears to change, unlike any FLOAT*
    type in this case.

  - With temp table
    Fixed length, like FLOAT(M, D)
     
After discussing this with Sergei Golubchik and Timour, my opinion is that the
"normal" behavior is the one without temp table where actual rounding takes
place. This is how Postgres works as well. Hence this should be preserved and
use of temporary tables should be transparent. This is a big fix, but it will
fix three bugs in one go, and probably keep some new ones from appearing.

Previously, the dynamic display length of DECIMAL columns has been
accomplished by the ROUND function changing the number of decimals during
query execution, thus violating the contract for fix_length_and_dec. This has
so far led to the following bug reports:

- Bug #30617: Round() function not working under some circumstances in InnoDB
- Bug #33402: ROUND with decimal and non-constant cannot round to 0 decimal
  places
- Bug #30889: filesort and order by with float/numeric crashes server

The bugs that are not fixed are set as duplicates of this bug.

Ideally, MySQL should behave like Postgres both when rounding to a greater number of decimal digits and when reducing the number of decimal digits. E.g.

SELECT a, ROUND( decimal_field, a ) FROM t1;
1 | 1.0
3 | 1.000
2 | 1.00

and

SELECT a, ROUND( decimal_field, a ) FROM t1;
1 | 1.2
3 | 1.234
2 | 2.23

The behavior with temporary tables should be consistent with this. This is the change that will alter behavior the least and still be consistent.

The problem implementing this is that there is no room to store such information as display length on row level in temporary tables. 

Our options are:

- Have different behavior depending on temporary tables.

- Change the binary DECIMAL representation to include display length, thus violating the contract that it should be comparable with memcmp.

- Add an extra column in temporary tables with display length for dynamically rounded DECIMAL values.

None of these options is very appealing, hence we have to resort to a static display length for ROUND( decimal, nonconstant )

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40695

ChangeSet@1.2546, 2008-01-08 09:44:53+01:00, mhansson@linux-st28.site +4 -0
  Bug#33143: Incorrect ORDER BY for ROUND()/TRUNCATE() result
  
  The ROUND(X, D) function would change the Item::decimals field during
  execution to achieve the effect of a dynamic number of decimal digits.
  This caused a series of bugs as a lot of code is dependent on the 
  mentioned field being stable.
  Fixed by never changing the number of shown digits for DECIMAL when
  used with a nonconstant number of decimal digits.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40697

ChangeSet@1.2546, 2008-01-08 16:25:28+01:00, mhansson@linux-st28.site +4 -0
  Bug#33143: Incorrect ORDER BY for ROUND()/TRUNCATE() result
  
  The ROUND(X, D) function would change the Item::decimals field during
  execution to achieve the effect of a dynamic number of decimal digits.
  This caused a series of bugs as a lot of code is dependent on the 
  mentioned field being stable after fix_fields phase.
  Fixed by never changing the number of shown digits for DECIMAL when
  used with a nonconstant number of decimal digits.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40988

ChangeSet@1.2546, 2008-01-14 16:16:36+01:00, mhansson@linux-st28.site +4 -0
  Bug#33143: Incorrect ORDER BY for ROUND()/TRUNCATE() result
  
  The ROUND(X, D) function would change the Item::decimals field during
  execution to achieve the effect of a dynamic number of decimal digits.
  This caused a series of bugs:
  Bug #30617:Round() function not working under some circumstances in InnoDB
  Bug #33402:ROUND with decimal and non-constant cannot round to 0 decimal places
  Bug #30889:filesort and order by with float/numeric crashes server
  Fixed by never changing the number of shown digits for DECIMAL when
  used with a nonconstant number of decimal digits.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.56

Noted in 5.0.56, 5.1.24, 6.0.5 changelogs.

For DECIMAL columns used with the ROUND(X,D) or TRUNCATE(X,D)
function with a non-constant value of D, adding an ORDER BY for the
function result produced misordered output.

Also documented for 5.1.23-ndb-6.2.14.

Also documented for 5.1.23-ndb-6.3.11.