diff options
| -rw-r--r-- | mysql-test/r/analyse.result | 7 | ||||
| -rw-r--r-- | mysql-test/r/cast.result | 16 | ||||
| -rw-r--r-- | mysql-test/r/func_group.result | 35 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 36 | ||||
| -rw-r--r-- | mysql-test/t/analyse.test | 8 | ||||
| -rw-r--r-- | mysql-test/t/cast.test | 8 | ||||
| -rw-r--r-- | mysql-test/t/func_group.test | 22 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 23 |
8 files changed, 155 insertions, 0 deletions
diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result index 83f70a69622..a56902e8ae7 100644 --- a/mysql-test/r/analyse.result +++ b/mysql-test/r/analyse.result @@ -102,3 +102,10 @@ select * from t1 procedure analyse(); Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype test.t1.v " \\ 1 19 0 0 3.7619 NULL ENUM('"','""','"c','\'\0\\"','\'','\'\'','\'b','a\0\0\0b','a\0','a""""b','a\'\'\'\'b','abc','abc\'def\\hij"klm\0opq','a\\\\\\\\b','b\'','c"','d\\','The\ZEnd','\\','\\d','\\\\') NOT NULL drop table t1; +create table t1 (df decimal(5,1)); +insert into t1 values(1.1); +insert into t1 values(2.2); +select * from t1 procedure analyse(); +Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype +test.t1.df 1.1 2.2 8 8 0 0 1.650000000 0.302500000 ENUM('1.1','2.2') NOT NULL +drop table t1; diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 10eb597fa68..57821699f68 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -187,3 +187,19 @@ timediff(cast('2004-12-30 12:00:00' as time), '12:00:00') select timediff(cast('1 12:00:00' as time), '12:00:00'); timediff(cast('1 12:00:00' as time), '12:00:00') 24:00:00 +select cast('1.2' as decimal(3,2)); +cast('1.2' as decimal(3,2)) +1.20 +select 1e18 * cast('1.2' as decimal(3,2)); +1e18 * cast('1.2' as decimal(3,2)) +1.2e+18 +select cast(cast('1.2' as decimal(3,2)) as signed); +cast(cast('1.2' as decimal(3,2)) as signed) +1 +set @v1=1e18; +select cast(@v1 as decimal(22, 2)); +cast(@v1 as decimal(22, 2)) +1000000000000000000.00 +select cast(-1e18 as decimal(22,2)); +cast(-1e18 as decimal(22,2)) +-1000000000000000000.00 diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 0ad992b87e5..b3eee950656 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -769,3 +769,38 @@ show columns from t2; Field Type Null Key Default Extra f2 datetime NO 0000-00-00 00:00:00 drop table t2, t1; +create table t2 (ff double); +insert into t2 values (2.2); +select cast(sum(distinct ff) as decimal(5,2)) from t2; +cast(sum(distinct ff) as decimal(5,2)) +2.20 +select cast(sum(distinct ff) as signed) from t2; +cast(sum(distinct ff) as signed) +2 +select cast(variance(ff) as decimal(10,3)) from t2; +cast(variance(ff) as decimal(10,3)) +0.000 +select cast(min(ff) as decimal(5,2)) from t2; +cast(min(ff) as decimal(5,2)) +2.20 +create table t1 (df decimal(5,1)); +insert into t1 values(1.1); +insert into t1 values(2.2); +select cast(sum(distinct df) as signed) from t1; +cast(sum(distinct df) as signed) +3 +select cast(min(df) as signed) from t1; +cast(min(df) as signed) +0 +select 1e8 * sum(distinct df) from t1; +1e8 * sum(distinct df) +330000000 +select 1e8 * min(df) from t1; +1e8 * min(df) +110000000 +create table t3 (ifl int); +insert into t3 values(1), (2); +select cast(min(ifl) as decimal(5,2)) from t3; +cast(min(ifl) as decimal(5,2)) +1.00 +drop table t1, t2, t3; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index bb9ac2ff4eb..49006a6fecf 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2276,3 +2276,39 @@ pass userid parentid parentgroup childid groupname grouptypeid crse categoryid c 1 5141 12 group2 12 group2 5 1 2 88 Oct04 1 5141 12 group2 12 group2 5 1 2 89 Oct04 drop table if exists t1, t2, t3, t4, t5; +create table t1 (df decimal(5,1)); +insert into t1 values(1.1); +insert into t1 values(2.2); +select * from t1 where df <= all (select avg(df) from t1 group by df); +df +1.1 +select * from t1 where df >= all (select avg(df) from t1 group by df); +df +2.2 +drop table t1; +create table t1 (df decimal(5,1)); +insert into t1 values(1.1); +select 1.1 * exists(select * from t1); +1.1 * exists(select * from t1) +1.1 +drop table t1; +CREATE TABLE t1 ( +grp int(11) default NULL, +a decimal(10,2) default NULL); +insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL); +select * from t1; +grp a +1 1.00 +2 2.00 +2 3.00 +3 4.00 +3 5.00 +3 6.00 +NULL NULL +select min(a) from t1 group by grp; +min(a) +NULL +1.00 +2.00 +4.00 +drop table t1; diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test index 52e367769a2..f5523f83226 100644 --- a/mysql-test/t/analyse.test +++ b/mysql-test/t/analyse.test @@ -47,3 +47,11 @@ create table t1 (v varchar(128)); insert into t1 values ('abc'),('abc\'def\\hij\"klm\0opq'),('\''),('\"'),('\\'),('a\0'),('b\''),('c\"'),('d\\'),('\'b'),('\"c'),('\\d'),('a\0\0\0b'),('a\'\'\'\'b'),('a\"\"\"\"b'),('a\\\\\\\\b'),('\'\0\\\"'),('\'\''),('\"\"'),('\\\\'),('The\ZEnd'); select * from t1 procedure analyse(); drop table t1; + +#decimal-related test + +create table t1 (df decimal(5,1)); +insert into t1 values(1.1); +insert into t1 values(2.2); +select * from t1 procedure analyse(); +drop table t1; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 23bba7d5aff..5866431e687 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -118,3 +118,11 @@ select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour); select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00'); # Still we should not throw away "days" part of time value select timediff(cast('1 12:00:00' as time), '12:00:00'); + +#decimal-related additions +select cast('1.2' as decimal(3,2)); +select 1e18 * cast('1.2' as decimal(3,2)); +select cast(cast('1.2' as decimal(3,2)) as signed); +set @v1=1e18; +select cast(@v1 as decimal(22, 2)); +select cast(-1e18 as decimal(22,2)); diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 60eefe2a104..b6e6664d1b8 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -492,3 +492,25 @@ drop table t2; create table t2 select f2 from (select now() f2 from t1) a; show columns from t2; drop table t2, t1; + +# decimal-related tests +create table t2 (ff double); +insert into t2 values (2.2); +select cast(sum(distinct ff) as decimal(5,2)) from t2; +select cast(sum(distinct ff) as signed) from t2; +select cast(variance(ff) as decimal(10,3)) from t2; +select cast(min(ff) as decimal(5,2)) from t2; + +create table t1 (df decimal(5,1)); +insert into t1 values(1.1); +insert into t1 values(2.2); +select cast(sum(distinct df) as signed) from t1; +select cast(min(df) as signed) from t1; +select 1e8 * sum(distinct df) from t1; +select 1e8 * min(df) from t1; + +create table t3 (ifl int); +insert into t3 values(1), (2); +select cast(min(ifl) as decimal(5,2)) from t3; + +drop table t1, t2, t3; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 429012e8a36..cfcb32f37c8 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1543,3 +1543,26 @@ group by drop table if exists t1, t2, t3, t4, t5; + +#decimal-related tests +create table t1 (df decimal(5,1)); +insert into t1 values(1.1); +insert into t1 values(2.2); + +select * from t1 where df <= all (select avg(df) from t1 group by df); +select * from t1 where df >= all (select avg(df) from t1 group by df); +drop table t1; + +create table t1 (df decimal(5,1)); +insert into t1 values(1.1); +select 1.1 * exists(select * from t1); +drop table t1; + +CREATE TABLE t1 ( + grp int(11) default NULL, + a decimal(10,2) default NULL); + +insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL); +select * from t1; +select min(a) from t1 group by grp; +drop table t1; |