path: root/mysql-test/r/type_newdecimal.result

drop table if exists t1;
select 1.1 IN (1.0, 1.2);
1.1 IN (1.0, 1.2)
0
select 1.1 IN (1.0, 1.2, 1.1, 1.4, 0.5);
1.1 IN (1.0, 1.2, 1.1, 1.4, 0.5)
1
select 1.1 IN (1.0, 1.2, NULL, 1.4, 0.5);
1.1 IN (1.0, 1.2, NULL, 1.4, 0.5)
NULL
select 0.5 IN (1.0, 1.2, NULL, 1.4, 0.5);
0.5 IN (1.0, 1.2, NULL, 1.4, 0.5)
1
select 1 IN (1.11, 1.2, 1.1, 1.4, 1, 0.5);
1 IN (1.11, 1.2, 1.1, 1.4, 1, 0.5)
1
select 1 IN (1.11, 1.2, 1.1, 1.4, NULL, 0.5);
1 IN (1.11, 1.2, 1.1, 1.4, NULL, 0.5)
NULL
select case 1.0 when 0.1 then "a" when 1.0 then "b" else "c" END;
case 1.0 when 0.1 then "a" when 1.0 then "b" else "c" END
b
select case 0.1 when 0.1 then "a" when 1.0 then "b" else "c" END;
case 0.1 when 0.1 then "a" when 1.0 then "b" else "c" END
a
select case 1 when 0.1 then "a" when 1.0 then "b" else "c" END;
case 1 when 0.1 then "a" when 1.0 then "b" else "c" END
b
select case 1.0 when 0.1 then "a" when 1 then "b" else "c" END;
case 1.0 when 0.1 then "a" when 1 then "b" else "c" END
b
select case 1.001 when 0.1 then "a" when 1 then "b" else "c" END;
case 1.001 when 0.1 then "a" when 1 then "b" else "c" END
c
create table t1 (a decimal(6,3));
insert into t1 values (1.0), (NULL), (0.1);
select * from t1;
a
1.000
NULL
0.100
select 0.1 in (1.0, 1.2, 1.1, a, 1.4, 0.5) from t1;
0.1 in (1.0, 1.2, 1.1, a, 1.4, 0.5)
0
NULL
1
drop table t1;
create table t1 select if(1, 1.1, 1.2), if(0, 1.1, 1.2), if(0.1, 1.1, 1.2), if(0, 1, 1.1), if(0, NULL, 1.2), if(1, 0.22e1, 1.1), if(1E0, 1.1, 1.2);
select * from t1;
if(1, 1.1, 1.2)	if(0, 1.1, 1.2)	if(0.1, 1.1, 1.2)	if(0, 1, 1.1)	if(0, NULL, 1.2)	if(1, 0.22e1, 1.1)	if(1E0, 1.1, 1.2)
1.1	1.2	1.1	1.1	1.2	2.2	1.1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `if(1, 1.1, 1.2)` decimal(2,1) NOT NULL default '0.0',
  `if(0, 1.1, 1.2)` decimal(2,1) NOT NULL default '0.0',
  `if(0.1, 1.1, 1.2)` decimal(2,1) NOT NULL default '0.0',
  `if(0, 1, 1.1)` decimal(2,1) NOT NULL default '0.0',
  `if(0, NULL, 1.2)` decimal(2,1) default NULL,
  `if(1, 0.22e1, 1.1)` double NOT NULL default '0',
  `if(1E0, 1.1, 1.2)` decimal(2,1) NOT NULL default '0.0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 select nullif(1.1, 1.1), nullif(1.1, 1.2), nullif(1.1, 0.11e1), nullif(1.0, 1), nullif(1, 1.0), nullif(1, 1.1);
select * from t1;
nullif(1.1, 1.1)	nullif(1.1, 1.2)	nullif(1.1, 0.11e1)	nullif(1.0, 1)	nullif(1, 1.0)	nullif(1, 1.1)
NULL	1.1	NULL	NULL	NULL	1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `nullif(1.1, 1.1)` decimal(2,1) default NULL,
  `nullif(1.1, 1.2)` decimal(2,1) default NULL,
  `nullif(1.1, 0.11e1)` decimal(2,1) default NULL,
  `nullif(1.0, 1)` decimal(2,1) default NULL,
  `nullif(1, 1.0)` int(1) default NULL,
  `nullif(1, 1.1)` int(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 (a decimal(4,2));
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 2
Warning	1264	Out of range value adjusted for column 'a' at row 3
Warning	1264	Out of range value adjusted for column 'a' at row 4
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 2
Warning	1264	Out of range value adjusted for column 'a' at row 3
Warning	1264	Out of range value adjusted for column 'a' at row 4
select a from t1;
a
99.99
99.99
99.99
99.99
-99.99
-99.99
-99.99
-99.99
drop table t1;
create table t1 (a decimal(4,2) unsigned);
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 2
Warning	1264	Out of range value adjusted for column 'a' at row 3
Warning	1264	Out of range value adjusted for column 'a' at row 4
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 2
Warning	1264	Out of range value adjusted for column 'a' at row 3
Warning	1264	Out of range value adjusted for column 'a' at row 4
select a from t1;
a
99.99
99.99
99.99
99.99
99.99
0.00
0.00
0.00
drop table t1;
create table t1 (a bigint);
insert into t1 values (18446744073709551615.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
insert into t1 values (9223372036854775808.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
insert into t1 values (-18446744073709551615.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
select * from t1;
a
9223372036854775807
9223372036854775807
-9223372036854775808
drop table t1;
create table t1 (a bigint unsigned);
insert into t1 values (18446744073709551615.0);
insert into t1 values (9223372036854775808.0);
insert into t1 values (9999999999999999999999999.000);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
insert into t1 values (-1.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
select * from t1;
a
18446744073709551615
9223372036854775808
18446744073709551615
0
drop table t1;
create table t1 (a tinyint);
insert into t1 values (18446744073709551615.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 1
insert into t1 values (9223372036854775808.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 1
select * from t1;
a
127
127
drop table t1;
create table t1 select round(15.4,-1), truncate(-5678.123451,-3), abs(-1.1), -(-1.1);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `round(15.4,-1)` decimal(3,0) NOT NULL default '0',
  `truncate(-5678.123451,-3)` decimal(4,0) NOT NULL default '0',
  `abs(-1.1)` decimal(3,1) NOT NULL default '0.0',
  `-(-1.1)` decimal(2,1) NOT NULL default '0.0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set session sql_mode='traditional';
select 1e10/0e0;
1e10/0e0
NULL
Warnings:
Error	1365	Division by 0
create table wl1612 (col1 int, col2 decimal(38,10), col3 numeric(38,10));
insert into wl1612 values(1,12345678901234567890.1234567890,12345678901234567890.1234567890);
select * from wl1612;
col1	col2	col3
1	12345678901234567890.1234567890	12345678901234567890.1234567890
insert into wl1612 values(2,01234567890123456789.0123456789,01234567890123456789.0123456789);
select * from wl1612 where col1=2;
col1	col2	col3
2	1234567890123456789.0123456789	1234567890123456789.0123456789
insert into wl1612 values(3,1234567890123456789012345678.0123456789,1234567890123456789012345678.0123456789);
select * from wl1612 where col1=3;
col1	col2	col3
3	1234567890123456789012345678.0123456789	1234567890123456789012345678.0123456789
select col1/0 from wl1612;
col1/0
NULL
NULL
NULL
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
Error	1365	Division by 0
select col2/0 from wl1612;
col2/0
NULL
NULL
NULL
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
Error	1365	Division by 0
select col3/0 from wl1612;
col3/0
NULL
NULL
NULL
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
Error	1365	Division by 0
insert into wl1612 values(5,5000.0005,5000.0005);
insert into wl1612 values(6,5000.0005,5000.0005);
select sum(col2),sum(col3) from wl1612;
sum(col2)	sum(col3)
1234567903703703580370380357.1491481468	1234567903703703580370380357.1491481468
insert into wl1612 values(7,500000.000005,500000.000005);
insert into wl1612 values(8,500000.000005,500000.000005);
select sum(col2),sum(col3) from wl1612 where col1>4;
sum(col2)	sum(col3)
1010000.0010100000	1010000.0010100000
insert into wl1612 (col1, col2) values(9,1.01234567891);
Warnings:
Note	1265	Data truncated for column 'col2' at row 1
insert into wl1612 (col1, col2) values(10,1.01234567894);
Warnings:
Note	1265	Data truncated for column 'col2' at row 1
insert into wl1612 (col1, col2) values(11,1.01234567895);
Warnings:
Note	1265	Data truncated for column 'col2' at row 1
insert into wl1612 (col1, col2) values(12,1.01234567896);
Warnings:
Note	1265	Data truncated for column 'col2' at row 1
select col1,col2 from wl1612 where col1>8;
col1	col2
9	1.0123456789
10	1.0123456789
11	1.0123456790
12	1.0123456790
insert into wl1612 (col1, col3) values(13,1.01234567891);
Warnings:
Note	1265	Data truncated for column 'col3' at row 1
insert into wl1612 (col1, col3) values(14,1.01234567894);
Warnings:
Note	1265	Data truncated for column 'col3' at row 1
insert into wl1612 (col1, col3) values(15,1.01234567895);
Warnings:
Note	1265	Data truncated for column 'col3' at row 1
insert into wl1612 (col1, col3) values(16,1.01234567896);
Warnings:
Note	1265	Data truncated for column 'col3' at row 1
select col1,col3 from wl1612 where col1>12;
col1	col3
13	1.0123456789
14	1.0123456789
15	1.0123456790
16	1.0123456790
select col1 from wl1612 where col1>4 and col2=1.01234567891;
col1
select col1 from wl1612 where col1>4 and col2=1.0123456789;
col1
9
10
select col1 from wl1612 where col1>4 and col2<>1.0123456789;
col1
5
6
7
8
11
12
select col1 from wl1612 where col1>4 and col2<1.0123456789;
col1
select col1 from wl1612 where col1>4 and col2<=1.0123456789;
col1
9
10
select col1 from wl1612 where col1>4 and col2>1.0123456789;
col1
5
6
7
8
11
12
select col1 from wl1612 where col1>4 and col2>=1.0123456789;
col1
5
6
7
8
9
10
11
12
select col1 from wl1612 where col1>4 and col2=1.012345679;
col1
11
12
select col1 from wl1612 where col1>4 and col2<>1.012345679;
col1
5
6
7
8
9
10
select col1 from wl1612 where col1>4 and col3=1.01234567891;
col1
select col1 from wl1612 where col1>4 and col3=1.0123456789;
col1
13
14
select col1 from wl1612 where col1>4 and col3<>1.0123456789;
col1
5
6
7
8
15
16
select col1 from wl1612 where col1>4 and col3<1.0123456789;
col1
select col1 from wl1612 where col1>4 and col3<=1.0123456789;
col1
13
14
select col1 from wl1612 where col1>4 and col3>1.0123456789;
col1
5
6
7
8
15
16
select col1 from wl1612 where col1>4 and col3>=1.0123456789;
col1
5
6
7
8
13
14
15
16
select col1 from wl1612 where col1>4 and col3=1.012345679;
col1
15
16
select col1 from wl1612 where col1>4 and col3<>1.012345679;
col1
5
6
7
8
13
14
drop table wl1612;
select 1/3;
1/3
0.3333
select 0.8=0.7+0.1;
0.8=0.7+0.1
1
select 0.7+0.1;
0.7+0.1
0.8
create table wl1612_1 (col1 int);
insert into wl1612_1 values(10);
select * from wl1612_1 where 0.8=0.7+0.1;
col1
10
select 0.07+0.07 from wl1612_1;
0.07+0.07
0.14
select 0.07-0.07 from wl1612_1;
0.07-0.07
0.00
select 0.07*0.07 from wl1612_1;
0.07*0.07
0.0049
select 0.07/0.07 from wl1612_1;
0.07/0.07
1.000000
drop table wl1612_1;
create table wl1612_2 (col1 decimal(10,2), col2 numeric(10,2));
insert into wl1612_2 values(1,1);
insert into wl1612_2 values(+1,+1);
insert into wl1612_2 values(+01,+01);
insert into wl1612_2 values(+001,+001);
select col1,count(*) from wl1612_2 group by col1;
col1	count(*)
1.00	4
select col2,count(*) from wl1612_2 group by col2;
col2	count(*)
1.00	4
drop table wl1612_2;
create table wl1612_3 (col1 decimal(10,2), col2 numeric(10,2));
insert into wl1612_3 values('1','1');
insert into wl1612_3 values('+1','+1');
insert into wl1612_3 values('+01','+01');
insert into wl1612_3 values('+001','+001');
select col1,count(*) from wl1612_3 group by col1;
col1	count(*)
1.00	4
select col2,count(*) from wl1612_3 group by col2;
col2	count(*)
1.00	4
drop table wl1612_3;
select mod(234,10) ;
mod(234,10)
4
select mod(234.567,10.555);
mod(234.567,10.555)
2.357
select mod(-234.567,10.555);
mod(-234.567,10.555)
-2.357
select mod(234.567,-10.555);
mod(234.567,-10.555)
2.357
select round(15.1);
round(15.1)
15
select round(15.4);
round(15.4)
15
select round(15.5);
round(15.5)
16
select round(15.6);
round(15.6)
16
select round(15.9);
round(15.9)
16
select round(-15.1);
round(-15.1)
-15
select round(-15.4);
round(-15.4)
-15
select round(-15.5);
round(-15.5)
-16
select round(-15.6);
round(-15.6)
-16
select round(-15.9);
round(-15.9)
-16
select round(15.1,1);
round(15.1,1)
15.1
select round(15.4,1);
round(15.4,1)
15.4
select round(15.5,1);
round(15.5,1)
15.5
select round(15.6,1);
round(15.6,1)
15.6
select round(15.9,1);
round(15.9,1)
15.9
select round(-15.1,1);
round(-15.1,1)
-15.1
select round(-15.4,1);
round(-15.4,1)
-15.4
select round(-15.5,1);
round(-15.5,1)
-15.5
select round(-15.6,1);
round(-15.6,1)
-15.6
select round(-15.9,1);
round(-15.9,1)
-15.9
select round(15.1,0);
round(15.1,0)
15
select round(15.4,0);
round(15.4,0)
15
select round(15.5,0);
round(15.5,0)
16
select round(15.6,0);
round(15.6,0)
16
select round(15.9,0);
round(15.9,0)
16
select round(-15.1,0);
round(-15.1,0)
-15
select round(-15.4,0);
round(-15.4,0)
-15
select round(-15.5,0);
round(-15.5,0)
-16
select round(-15.6,0);
round(-15.6,0)
-16
select round(-15.9,0);
round(-15.9,0)
-16
select round(15.1,-1);
round(15.1,-1)
20
select round(15.4,-1);
round(15.4,-1)
20
select round(15.5,-1);
round(15.5,-1)
20
select round(15.6,-1);
round(15.6,-1)
20
select round(15.9,-1);
round(15.9,-1)
20
select round(-15.1,-1);
round(-15.1,-1)
-20
select round(-15.4,-1);
round(-15.4,-1)
-20
select round(-15.5,-1);
round(-15.5,-1)
-20
select round(-15.6,-1);
round(-15.6,-1)
-20
select round(-15.91,-1);
round(-15.91,-1)
-20
select truncate(5678.123451,0);
truncate(5678.123451,0)
5678
select truncate(5678.123451,1);
truncate(5678.123451,1)
5678.1
select truncate(5678.123451,2);
truncate(5678.123451,2)
5678.12
select truncate(5678.123451,3);
truncate(5678.123451,3)
5678.123
select truncate(5678.123451,4);
truncate(5678.123451,4)
5678.1234
select truncate(5678.123451,5);
truncate(5678.123451,5)
5678.12345
select truncate(5678.123451,6);
truncate(5678.123451,6)
5678.123451
select truncate(5678.123451,-1);
truncate(5678.123451,-1)
5670
select truncate(5678.123451,-2);
truncate(5678.123451,-2)
5600
select truncate(5678.123451,-3);
truncate(5678.123451,-3)
5000
select truncate(5678.123451,-4);
truncate(5678.123451,-4)
0
select truncate(-5678.123451,0);
truncate(-5678.123451,0)
-5678
select truncate(-5678.123451,1);
truncate(-5678.123451,1)
-5678.1
select truncate(-5678.123451,2);
truncate(-5678.123451,2)
-5678.12
select truncate(-5678.123451,3);
truncate(-5678.123451,3)
-5678.123
select truncate(-5678.123451,4);
truncate(-5678.123451,4)
-5678.1234
select truncate(-5678.123451,5);
truncate(-5678.123451,5)
-5678.12345
select truncate(-5678.123451,6);
truncate(-5678.123451,6)
-5678.123451
select truncate(-5678.123451,-1);
truncate(-5678.123451,-1)
-5670
select truncate(-5678.123451,-2);
truncate(-5678.123451,-2)
-5600
select truncate(-5678.123451,-3);
truncate(-5678.123451,-3)
-5000
select truncate(-5678.123451,-4);
truncate(-5678.123451,-4)
0
create table wl1612_4 (col1 int, col2 decimal(30,25), col3 numeric(30,25));
insert into wl1612_4 values(1,0.0123456789012345678912345,0.0123456789012345678912345);
select col2/9999999999 from wl1612_4 where col1=1;
col2/9999999999
0.00000000000123456789024691358
select col3/9999999999 from wl1612_4 where col1=1;
col3/9999999999
0.00000000000123456789024691358
select 9999999999/col2 from wl1612_4 where col1=1;
9999999999/col2
810000007209.0001
select 9999999999/col3 from wl1612_4 where col1=1;
9999999999/col3
810000007209.0001
select col2*9999999999 from wl1612_4 where col1=1;
col2*9999999999
123456789.0000000000111104321087655
select col3*9999999999 from wl1612_4 where col1=1;
col3*9999999999
123456789.0000000000111104321087655
insert into wl1612_4 values(2,55555.0123456789012345678912345,55555.0123456789012345678912345);
select col2/9999999999 from wl1612_4 where col1=2;
col2/9999999999
0.00000555550123512344024696913
select col3/9999999999 from wl1612_4 where col1=2;
col3/9999999999
0.00000555550123512344024696913
select 9999999999/col2 from wl1612_4 where col1=2;
9999999999/col2
180001.7600
select 9999999999/col3 from wl1612_4 where col1=2;
9999999999/col3
180001.7600
select col2*9999999999 from wl1612_4 where col1=2;
col2*9999999999
555550123401234.0000000000111104321087655
select col3*9999999999 from wl1612_4 where col1=2;
col3*9999999999
555550123401234.0000000000111104321087655
drop table wl1612_4;
set sql_mode='';
select 23.4 + (-41.7), 23.4 - (41.7) = -18.3;
23.4 + (-41.7)	23.4 - (41.7) = -18.3
-18.3	1
select -18.3=-18.3;
-18.3=-18.3
1
select 18.3=18.3;
18.3=18.3
1
select -18.3=18.3;
-18.3=18.3
0
select 0.8 = 0.7 + 0.1;
0.8 = 0.7 + 0.1
1
create procedure p1 () begin 
declare v1, v2, v3, v4 decimal(16,12); declare v5 int; 
set v1 = 1; set v2 = 2; set v3 = 1000000000000; set v4 = 2000000000000; set v5 = 0; 
while v5 < 100000 do
set v1 = v1 + 0.000000000001; set v2 = v2 - 0.000000000001; set v3 = v3 + 1; set v4 = v4 - 1; set v5 = v5 + 1; 
end while; select v1, v2, v3 * 0.000000000001, v4 * 0.000000000001; end;//
#
call p1()//
#
v1	v2	v3 * 0.000000000001	v4 * 0.000000000001
1.000000100000	1.999999900000	1.000000100000	1.999999900000
drop procedure p1;
drop table if exists t1;
Warnings:
Note	1051	Unknown table 't1'
create table t1 (col1 decimal(38));
insert into t1 values (12345678901234567890123456789012345678);
select * from t1;
col1
12345678901234567890123456789012345678
drop table t1;
create table t1 (col1 decimal(31,30));
insert into t1 values (0.00000000001);
select * from t1;
col1
0.000000000010000000000000000000
drop table t1;
select 7777777777777777777777777777777777777 * 10;
7777777777777777777777777777777777777 * 10
77777777777777777777777777777777777770
select .7777777777777777777777777777777777777 *
1000000000000000000;
.7777777777777777777777777777777777777 *
1000000000000000000
777777777777777777.777777777777777777700000000000
select .7777777777777777777777777777777777777 - 0.1;
.7777777777777777777777777777777777777 - 0.1
0.6777777777777777777777777777777777777
select .343434343434343434 + .343434343434343434;
.343434343434343434 + .343434343434343434
0.686868686868686868
select abs(9999999999999999999999);
abs(9999999999999999999999)
9999999999999999999999
select abs(-9999999999999999999999);
abs(-9999999999999999999999)
9999999999999999999999
select ceiling(999999999999999999);
ceiling(999999999999999999)
999999999999999999
select ceiling(99999999999999999999);
ceiling(99999999999999999999)
99999999999999999999
select ceiling(9.9999999999999999999);
ceiling(9.9999999999999999999)
10
select ceiling(-9.9999999999999999999);
ceiling(-9.9999999999999999999)
-9
select floor(999999999999999999);
floor(999999999999999999)
999999999999999999
select floor(9999999999999999999999);
floor(9999999999999999999999)
9999999999999999999999
select floor(9.999999999999999999999);
floor(9.999999999999999999999)
9
select floor(-9.999999999999999999999);
floor(-9.999999999999999999999)
-10
select floor(-999999999999999999999.999);
floor(-999999999999999999999.999)
-1000000000000000000000
select ceiling(999999999999999999999.999);
ceiling(999999999999999999999.999)
1000000000000000000000
select 99999999999999999999999999999999999999 mod 3;
99999999999999999999999999999999999999 mod 3
0
select round(99999999999999999.999);
round(99999999999999999.999)
100000000000000000
select round(-99999999999999999.999);
round(-99999999999999999.999)
-100000000000000000
select round(99999999999999999.999,3);
round(99999999999999999.999,3)
99999999999999999.999
select round(-99999999999999999.999,3);
round(-99999999999999999.999,3)
-99999999999999999.999
select truncate(99999999999999999999999999999999999999,31);
truncate(99999999999999999999999999999999999999,31)
99999999999999999999999999999999999999.000000000000000000000000000000
select truncate(99.999999999999999999999999999999999999,31);
truncate(99.999999999999999999999999999999999999,31)
100.000000000000000000000000000000
select truncate(99999999999999999999999999999999999999,-31);
truncate(99999999999999999999999999999999999999,-31)
99999990000000000000000000000000000000
create table t1 as select 0.5;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `0.5` decimal(2,1) NOT NULL default '0.0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select round(1.5),round(2.5);
round(1.5)	round(2.5)
2	3
select 0.07 * 0.07;
0.07 * 0.07
0.0049
set sql_mode='traditional';
select 1E-500 = 0;
1E-500 = 0
1
select 1 / 1E-500;
1 / 1E-500
NULL
Warnings:
Error	1365	Division by 0
select 1 / 0;
1 / 0
NULL
Warnings:
Error	1365	Division by 0
set sql_mode='ansi,traditional';
CREATE TABLE Sow6_2f (col1 NUMERIC(4,2));
INSERT INTO Sow6_2f VALUES (10.55);
INSERT INTO Sow6_2f VALUES (10.5555);
Warnings:
Note	1265	Data truncated for column 'col1' at row 1
INSERT INTO Sow6_2f VALUES (-10.55);
INSERT INTO Sow6_2f VALUES (-10.5555);
Warnings:
Note	1265	Data truncated for column 'col1' at row 1
INSERT INTO Sow6_2f VALUES (11);
INSERT INTO Sow6_2f VALUES (101.55);
ERROR 22003: Out of range value adjusted for column 'col1' at row 1
UPDATE Sow6_2f SET col1 = col1 * 50 WHERE col1 = 11;
ERROR 22003: Out of range value adjusted for column 'col1' at row 5
UPDATE Sow6_2f SET col1 = col1 / 0 WHERE col1 > 0;
ERROR 22012: Division by 0
SELECT MOD(col1,0) FROM Sow6_2f;
MOD(col1,0)
NULL
NULL
NULL
NULL
NULL
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
Error	1365	Division by 0
Error	1365	Division by 0
Error	1365	Division by 0
INSERT INTO Sow6_2f VALUES ('a59b');
ERROR HY000: Incorrect decimal value: 'a59b' for column 'col1' at row 1
drop table Sow6_2f;
select 10.3330000000000/12.34500000;
10.3330000000000/12.34500000
0.83701903604698258
set sql_mode='';
select 0/0;
0/0
NULL
select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 as x;
x
999999999999999999999999999999999999999999999999999999999999999999999999999999999
Warnings:
Error	1292	Truncated incorrect DECIMAL value: ''
select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 + 1 as x;
x
NULL
Warnings:
Error	1292	Truncated incorrect DECIMAL value: ''
Error	1292	Truncated incorrect DECIMAL value: ''
select 0.190287977636363637 + 0.040372670 * 0 -  0;
0.190287977636363637 + 0.040372670 * 0 -  0
0.190287977636363637
select -0.123 * 0;
-0.123 * 0
0.000
CREATE TABLE t1 (f1 DECIMAL (12,9), f2 DECIMAL(2,2));
INSERT INTO t1 VALUES (10.5, 0);
UPDATE t1 SET f1 = 4.5;
SELECT * FROM t1;
f1	f2
4.500000000	0.00
DROP TABLE t1;
CREATE TABLE t1 (f1 DECIMAL (64,20), f2 DECIMAL(2,2));
INSERT INTO t1 VALUES (9999999999999999999999999999999999, 0);
SELECT * FROM t1;
f1	f2
9999999999999999999999999999999999.00000000000000000000	0.00
DROP TABLE t1;
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
SELECT GRADE  FROM t1 WHERE GRADE > 160 AND GRADE < 300;
GRADE
252
SELECT GRADE  FROM t1 WHERE GRADE= 151;
GRADE
151
DROP TABLE t1;
select abs(10/0);
abs(10/0)
NULL
select abs(NULL);
abs(NULL)
NULL