Merge mhansson@bk-internal:/home/bk/mysql-5.1-opt

into  linux-st28.site:/home/martin/mysql/src/bug33143/my51-bug33143-pushee


sql/item_func.cc:
  Auto merged
mysql-test/r/type_decimal.result:
  Bug#33143: Manual merge
mysql-test/t/type_decimal.test:
  Bug#33143: Manual merge

2 files changed, 104 insertions, 1 deletions

diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result
index a25ffc4c6fc..3e5f6a9b504 100644
--- a/mysql-test/r/type_decimal.result
+++ b/mysql-test/r/type_decimal.result
@@ -794,7 +794,7 @@ dps tinyint(3) unsigned default NULL
 INSERT INTO t1 VALUES (1.1325,3);
 SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1;
 ROUND(qty,3)	dps	ROUND(qty,dps)
-1.133	3	1.133
+1.133	3	1.133000
 DROP TABLE t1;
 SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%';
 %
@@ -885,4 +885,65 @@ c
 1000
 1234567890
 DROP TABLE t1, t2, t3, t4;
+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 ) AS c FROM t1 ORDER BY c;
+a	b	c
+1.000	5	1.000
+2.000	4	2.000
+3.000	3	3.000
+4.000	2	4.000
+5.000	1	5.000
+SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
+a	b	c
+5.000	1	5.000
+4.000	2	4.000
+3.000	3	3.000
+2.000	4	2.000
+1.000	5	1.000
+CREATE TABLE t2 ( a INT, b INT, c DECIMAL(5, 4) );
+INSERT INTO t2 VALUES ( 0, 1, 1.2345 ), ( 1, 2, 1.2345 ),
+( 3, 3, 1.2345 ), ( 2, 4, 1.2345 );
+SELECT a, b, MAX(ROUND(c, a)) 
+FROM t2 
+GROUP BY a, b 
+ORDER BY b;
+a	b	MAX(ROUND(c, a))
+0	1	1.0000
+1	2	1.2000
+3	3	1.2350
+2	4	1.2300
+SELECT a, b, ROUND(c, a) 
+FROM t2;
+a	b	ROUND(c, a)
+0	1	1.0000
+1	2	1.2000
+3	3	1.2350
+2	4	1.2300
+CREATE TABLE t3( a INT, b DECIMAL(6, 3) );
+INSERT INTO t3 VALUES( 0, 1.5 );
+SELECT ROUND( b, a ) FROM t3;
+ROUND( b, a )
+2.000
+CREATE TABLE t4( a INT, b DECIMAL( 12, 0) );
+INSERT INTO t4 VALUES( -9, 1.5e9 );
+SELECT ROUND( b, a ) FROM t4;
+ROUND( b, a )
+2000000000
+CREATE TABLE t5( a INT, b DECIMAL( 13, 12 ) );
+INSERT INTO t5 VALUES( 0, 1.5 );
+INSERT INTO t5 VALUES( 9, 1.5e-9 );
+SELECT ROUND( b, a ) FROM t5;
+ROUND( b, a )
+2.000000000000
+0.000000002000
+CREATE TABLE t6( a INT );
+INSERT INTO t6 VALUES( 6 / 8 );
+SELECT * FROM t6;
+a
+1
+SELECT ROUND(20061108085411.000002);
+ROUND(20061108085411.000002)
+20061108085411
+DROP TABLE t1, t2, t3, t4, t5, t6;
 End of 5.0 tests
diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test
index 807d1e6b01e..2ee6615a5ee 100644
--- a/mysql-test/t/type_decimal.test
+++ b/mysql-test/t/type_decimal.test
@@ -477,5 +477,47 @@ show create table t1;
 select round(a,b) as c from t1 order by c;
 
 DROP TABLE t1, t2, t3, t4;
+#
+# Bug #33143: Incorrect ORDER BY for ROUND()/TRUNCATE() result
+#
+
+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 ) AS c FROM t1 ORDER BY c;
+SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
+
+CREATE TABLE t2 ( a INT, b INT, c DECIMAL(5, 4) );
+
+INSERT INTO t2 VALUES ( 0, 1, 1.2345 ), ( 1, 2, 1.2345 ),
+                      ( 3, 3, 1.2345 ), ( 2, 4, 1.2345 );
+
+SELECT a, b, MAX(ROUND(c, a)) 
+FROM t2 
+GROUP BY a, b 
+ORDER BY b;
+
+SELECT a, b, ROUND(c, a) 
+FROM t2;
+
+CREATE TABLE t3( a INT, b DECIMAL(6, 3) );
+INSERT INTO t3 VALUES( 0, 1.5 );
+SELECT ROUND( b, a ) FROM t3;
+
+CREATE TABLE t4( a INT, b DECIMAL( 12, 0) );
+INSERT INTO t4 VALUES( -9, 1.5e9 );
+SELECT ROUND( b, a ) FROM t4;
+
+CREATE TABLE t5( a INT, b DECIMAL( 13, 12 ) );
+INSERT INTO t5 VALUES( 0, 1.5 );
+INSERT INTO t5 VALUES( 9, 1.5e-9 );
+SELECT ROUND( b, a ) FROM t5;
+
+CREATE TABLE t6( a INT );
+INSERT INTO t6 VALUES( 6 / 8 );
+SELECT * FROM t6;
+
+SELECT ROUND(20061108085411.000002);
+
+DROP TABLE t1, t2, t3, t4, t5, t6;
 
 --echo End of 5.0 tests