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 for column 'a' at row 1 Warning 1264 Out of range value for column 'a' at row 2 Warning 1264 Out of range value for column 'a' at row 3 Warning 1264 Out of range value for column 'a' at row 4 insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1); Warnings: Warning 1264 Out of range value for column 'a' at row 1 Warning 1264 Out of range value for column 'a' at row 2 Warning 1264 Out of range value for column 'a' at row 3 Warning 1264 Out of range value 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 for column 'a' at row 1 Warning 1264 Out of range value for column 'a' at row 2 Warning 1264 Out of range value for column 'a' at row 3 Warning 1264 Out of range value for column 'a' at row 4 insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1); Warnings: Warning 1264 Out of range value for column 'a' at row 1 Warning 1264 Out of range value for column 'a' at row 2 Warning 1264 Out of range value for column 'a' at row 3 Warning 1264 Out of range value for column 'a' at row 4 select a from t1; a 99.99 99.99 99.99 99.99 0.00 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 for column 'a' at row 1 insert into t1 values (9223372036854775808.0); Warnings: Warning 1264 Out of range value for column 'a' at row 1 insert into t1 values (-18446744073709551615.0); Warnings: Warning 1264 Out of range value 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 for column 'a' at row 1 insert into t1 values (-1.0); Warnings: Warning 1264 Out of range value 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 for column 'a' at row 1 Warning 1264 Out of range value for column 'a' at row 1 insert into t1 values (9223372036854775808.0); Warnings: Warning 1264 Out of range value for column 'a' at row 1 Warning 1264 Out of range value 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 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) 99.999999999999999999999999999999 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 for column 'col1' at row 1 UPDATE Sow6_2f SET col1 = col1 * 50 WHERE col1 = 11; ERROR 22003: Out of range value 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 99999999999999999999999999999999999999999999999999999999999999999 Warnings: Error 1292 Truncated incorrect DECIMAL value: '' select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 + 1 as x; x 100000000000000000000000000000000000000000000000000000000000000000 Warnings: 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; select abs(10/0); abs(10/0) NULL select abs(NULL); abs(NULL) NULL set @@sql_mode='traditional'; create table t1( d1 decimal(18) unsigned, d2 decimal(20) unsigned, d3 decimal (22) unsigned); insert into t1 values(1,-1,-1); ERROR 22003: Out of range value for column 'd2' at row 1 drop table t1; create table t1 (col1 decimal(5,2), col2 numeric(5,2)); insert into t1 values (999.999,999.999); ERROR 22003: Out of range value for column 'col1' at row 1 insert into t1 values (-999.999,-999.999); ERROR 22003: Out of range value for column 'col1' at row 1 select * from t1; col1 col2 drop table t1; set sql_mode=''; set @sav_dpi= @@div_precision_increment; set @@div_precision_increment=15; create table t1 (col1 int, col2 decimal(30,25), col3 numeric(30,25)); insert into t1 values (1,0.0123456789012345678912345,0.0123456789012345678912345); select col2/9999999999 from t1 where col1=1; col2/9999999999 0.000000000001234567890246913578 select 9999999999/col2 from t1 where col1=1; 9999999999/col2 810000007209.000065537105051 select 77777777/7777777; 77777777/7777777 10.000000900000090 drop table t1; set div_precision_increment= @sav_dpi; create table t1 (a decimal(4,2)); insert into t1 values (0.00); select * from t1 where a > -0.00; a select * from t1 where a = -0.00; a 0.00 drop table t1; create table t1 (col1 bigint default -9223372036854775808); insert into t1 values (default); select * from t1; col1 -9223372036854775808 drop table t1; select cast('1.00000001335143196001808973960578441619873046875E-10' as decimal(30,15)); cast('1.00000001335143196001808973960578441619873046875E-10' as decimal(30,15)) 0.000000000100000 select ln(14000) c1, convert(ln(14000),decimal(5,3)) c2, cast(ln(14000) as decimal(5,3)) c3; c1 c2 c3 9.5468126085974 9.547 9.547 select convert(ln(14000),decimal(2,3)) c1; ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column ''). select cast(ln(14000) as decimal(2,3)) c1; ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column ''). create table t1 (sl decimal(70,30)); ERROR 42000: Too big precision 70 specified for column 'sl'. Maximum is 65. create table t1 (sl decimal(32,31)); ERROR 42000: Too big scale 31 specified for column 'sl'. Maximum is 30. create table t1 (sl decimal(0,38)); ERROR 42000: Too big scale 38 specified for column 'sl'. Maximum is 30. create table t1 (sl decimal(0,30)); ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'sl'). create table t1 (sl decimal(5, 5)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `sl` decimal(5,5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (sl decimal(65, 30)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `sl` decimal(65,30) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 ( f1 decimal unsigned not null default 17.49, f2 decimal unsigned not null default 17.68, f3 decimal unsigned not null default 99.2, f4 decimal unsigned not null default 99.7, f5 decimal unsigned not null default 104.49, f6 decimal unsigned not null default 199.91, f7 decimal unsigned not null default 999.9, f8 decimal unsigned not null default 9999.99); Warnings: Note 1265 Data truncated for column 'f1' at row 1 Note 1265 Data truncated for column 'f2' at row 1 Note 1265 Data truncated for column 'f3' at row 1 Note 1265 Data truncated for column 'f4' at row 1 Note 1265 Data truncated for column 'f5' at row 1 Note 1265 Data truncated for column 'f6' at row 1 Note 1265 Data truncated for column 'f7' at row 1 Note 1265 Data truncated for column 'f8' at row 1 insert into t1 (f1) values (1); select * from t1; f1 f2 f3 f4 f5 f6 f7 f8 1 18 99 100 104 200 1000 10000 drop table t1; create table t1 ( f0 decimal (30,30) zerofill not null DEFAULT 0, f1 decimal (0,0) zerofill not null default 0); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `f0` decimal(30,30) unsigned zerofill NOT NULL DEFAULT '0.000000000000000000000000000000', `f1` decimal(10,0) unsigned zerofill NOT NULL DEFAULT '0000000000' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; drop procedure if exists wg2; create procedure wg2() begin declare v int default 1; declare tdec decimal(5) default 0; while v <= 9 do set tdec =tdec * 10; select v, tdec; set v = v + 1; end while; end// call wg2()// v tdec 1 0 v tdec 2 0 v tdec 3 0 v tdec 4 0 v tdec 5 0 v tdec 6 0 v tdec 7 0 v tdec 8 0 v tdec 9 0 drop procedure wg2; select cast(@non_existing_user_var/2 as DECIMAL); cast(@non_existing_user_var/2 as DECIMAL) NULL create table t (d decimal(0,10)); ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'd'). CREATE TABLE t1 ( my_float FLOAT, my_double DOUBLE, my_varchar VARCHAR(50), my_decimal DECIMAL(65,30) ); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `my_float` float DEFAULT NULL, `my_double` double DEFAULT NULL, `my_varchar` varchar(50) DEFAULT NULL, `my_decimal` decimal(65,30) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 SET my_float = 1.175494345e-32, my_double = 1.175494345e-32, my_varchar = '1.175494345e-32'; INSERT INTO t1 SET my_float = 1.175494345e-31, my_double = 1.175494345e-31, my_varchar = '1.175494345e-31'; INSERT INTO t1 SET my_float = 1.175494345e-30, my_double = 1.175494345e-30, my_varchar = '1.175494345e-30'; INSERT INTO t1 SET my_float = 1.175494345e-29, my_double = 1.175494345e-29, my_varchar = '1.175494345e-29'; INSERT INTO t1 SET my_float = 1.175494345e-28, my_double = 1.175494345e-28, my_varchar = '1.175494345e-28'; INSERT INTO t1 SET my_float = 1.175494345e-27, my_double = 1.175494345e-27, my_varchar = '1.175494345e-27'; INSERT INTO t1 SET my_float = 1.175494345e-26, my_double = 1.175494345e-26, my_varchar = '1.175494345e-26'; INSERT INTO t1 SET my_float = 1.175494345e-25, my_double = 1.175494345e-25, my_varchar = '1.175494345e-25'; INSERT INTO t1 SET my_float = 1.175494345e-24, my_double = 1.175494345e-24, my_varchar = '1.175494345e-24'; INSERT INTO t1 SET my_float = 1.175494345e-23, my_double = 1.175494345e-23, my_varchar = '1.175494345e-23'; INSERT INTO t1 SET my_float = 1.175494345e-22, my_double = 1.175494345e-22, my_varchar = '1.175494345e-22'; INSERT INTO t1 SET my_float = 1.175494345e-21, my_double = 1.175494345e-21, my_varchar = '1.175494345e-21'; INSERT INTO t1 SET my_float = 1.175494345e-20, my_double = 1.175494345e-20, my_varchar = '1.175494345e-20'; INSERT INTO t1 SET my_float = 1.175494345e-19, my_double = 1.175494345e-19, my_varchar = '1.175494345e-19'; INSERT INTO t1 SET my_float = 1.175494345e-18, my_double = 1.175494345e-18, my_varchar = '1.175494345e-18'; INSERT INTO t1 SET my_float = 1.175494345e-17, my_double = 1.175494345e-17, my_varchar = '1.175494345e-17'; INSERT INTO t1 SET my_float = 1.175494345e-16, my_double = 1.175494345e-16, my_varchar = '1.175494345e-16'; INSERT INTO t1 SET my_float = 1.175494345e-15, my_double = 1.175494345e-15, my_varchar = '1.175494345e-15'; INSERT INTO t1 SET my_float = 1.175494345e-14, my_double = 1.175494345e-14, my_varchar = '1.175494345e-14'; INSERT INTO t1 SET my_float = 1.175494345e-13, my_double = 1.175494345e-13, my_varchar = '1.175494345e-13'; INSERT INTO t1 SET my_float = 1.175494345e-12, my_double = 1.175494345e-12, my_varchar = '1.175494345e-12'; INSERT INTO t1 SET my_float = 1.175494345e-11, my_double = 1.175494345e-11, my_varchar = '1.175494345e-11'; INSERT INTO t1 SET my_float = 1.175494345e-10, my_double = 1.175494345e-10, my_varchar = '1.175494345e-10'; INSERT INTO t1 SET my_float = 1.175494345e-9, my_double = 1.175494345e-9, my_varchar = '1.175494345e-9'; INSERT INTO t1 SET my_float = 1.175494345e-8, my_double = 1.175494345e-8, my_varchar = '1.175494345e-8'; INSERT INTO t1 SET my_float = 1.175494345e-7, my_double = 1.175494345e-7, my_varchar = '1.175494345e-7'; INSERT INTO t1 SET my_float = 1.175494345e-6, my_double = 1.175494345e-6, my_varchar = '1.175494345e-6'; INSERT INTO t1 SET my_float = 1.175494345e-5, my_double = 1.175494345e-5, my_varchar = '1.175494345e-5'; INSERT INTO t1 SET my_float = 1.175494345e-4, my_double = 1.175494345e-4, my_varchar = '1.175494345e-4'; INSERT INTO t1 SET my_float = 1.175494345e-3, my_double = 1.175494345e-3, my_varchar = '1.175494345e-3'; INSERT INTO t1 SET my_float = 1.175494345e-2, my_double = 1.175494345e-2, my_varchar = '1.175494345e-2'; INSERT INTO t1 SET my_float = 1.175494345e-1, my_double = 1.175494345e-1, my_varchar = '1.175494345e-1'; SELECT my_float, my_double, my_varchar FROM t1; my_float my_double my_varchar 1.17549e-32 1.175494345e-32 1.175494345e-32 1.17549e-31 1.175494345e-31 1.175494345e-31 1.17549e-30 1.175494345e-30 1.175494345e-30 1.17549e-29 1.175494345e-29 1.175494345e-29 1.17549e-28 1.175494345e-28 1.175494345e-28 1.17549e-27 1.175494345e-27 1.175494345e-27 1.17549e-26 1.175494345e-26 1.175494345e-26 1.17549e-25 1.175494345e-25 1.175494345e-25 1.17549e-24 1.175494345e-24 1.175494345e-24 1.17549e-23 1.175494345e-23 1.175494345e-23 1.17549e-22 1.175494345e-22 1.175494345e-22 1.17549e-21 1.175494345e-21 1.175494345e-21 1.17549e-20 1.175494345e-20 1.175494345e-20 1.17549e-19 1.175494345e-19 1.175494345e-19 1.17549e-18 1.175494345e-18 1.175494345e-18 1.17549e-17 1.175494345e-17 1.175494345e-17 1.17549e-16 1.175494345e-16 1.175494345e-16 1.17549e-15 1.175494345e-15 1.175494345e-15 1.17549e-14 1.175494345e-14 1.175494345e-14 1.17549e-13 1.175494345e-13 1.175494345e-13 1.17549e-12 1.175494345e-12 1.175494345e-12 1.17549e-11 1.175494345e-11 1.175494345e-11 1.17549e-10 1.175494345e-10 1.175494345e-10 1.17549e-09 1.175494345e-09 1.175494345e-9 1.17549e-08 1.175494345e-08 1.175494345e-8 1.17549e-07 1.175494345e-07 1.175494345e-7 1.17549e-06 1.175494345e-06 1.175494345e-6 1.17549e-05 1.175494345e-05 1.175494345e-5 0.000117549 0.0001175494345 1.175494345e-4 0.00117549 0.001175494345 1.175494345e-3 0.0117549 0.01175494345 1.175494345e-2 0.117549 0.1175494345 1.175494345e-1 SELECT CAST(my_float AS DECIMAL(65,30)), my_float FROM t1; CAST(my_float AS DECIMAL(65,30)) my_float 0.000000000000000000000000000000 1.17549e-32 0.000000000000000000000000000000 1.17549e-31 0.000000000000000000000000000001 1.17549e-30 0.000000000000000000000000000012 1.17549e-29 0.000000000000000000000000000118 1.17549e-28 0.000000000000000000000000001175 1.17549e-27 0.000000000000000000000000011755 1.17549e-26 0.000000000000000000000000117549 1.17549e-25 0.000000000000000000000001175494 1.17549e-24 0.000000000000000000000011754943 1.17549e-23 0.000000000000000000000117549438 1.17549e-22 0.000000000000000000001175494332 1.17549e-21 0.000000000000000000011754943324 1.17549e-20 0.000000000000000000117549434853 1.17549e-19 0.000000000000000001175494374380 1.17549e-18 0.000000000000000011754943743802 1.17549e-17 0.000000000000000117549432474939 1.17549e-16 0.000000000000001175494324749389 1.17549e-15 0.000000000000011754943671010360 1.17549e-14 0.000000000000117549429933840000 1.17549e-13 0.000000000001175494380653563000 1.17549e-12 0.000000000011754943372854760000 1.17549e-11 0.000000000117549428524377200000 1.17549e-10 0.000000001175494368510499000000 1.17549e-09 0.000000011754943685104990000000 1.17549e-08 0.000000117549433298336200000000 1.17549e-07 0.000001175494389826781000000000 1.17549e-06 0.000011754943443520460000000000 1.17549e-05 0.000117549432616215200000000000 0.000117549 0.001175494398921728000000000000 0.00117549 0.011754943057894710000000000000 0.0117549 0.117549434304237400000000000000 0.117549 SELECT CAST(my_double AS DECIMAL(65,30)), my_double FROM t1; CAST(my_double AS DECIMAL(65,30)) my_double 0.000000000000000000000000000000 1.175494345e-32 0.000000000000000000000000000000 1.175494345e-31 0.000000000000000000000000000001 1.175494345e-30 0.000000000000000000000000000012 1.175494345e-29 0.000000000000000000000000000118 1.175494345e-28 0.000000000000000000000000001175 1.175494345e-27 0.000000000000000000000000011755 1.175494345e-26 0.000000000000000000000000117549 1.175494345e-25 0.000000000000000000000001175494 1.175494345e-24 0.000000000000000000000011754943 1.175494345e-23 0.000000000000000000000117549435 1.175494345e-22 0.000000000000000000001175494345 1.175494345e-21 0.000000000000000000011754943450 1.175494345e-20 0.000000000000000000117549434500 1.175494345e-19 0.000000000000000001175494345000 1.175494345e-18 0.000000000000000011754943450000 1.175494345e-17 0.000000000000000117549434500000 1.175494345e-16 0.000000000000001175494345000000 1.175494345e-15 0.000000000000011754943450000000 1.175494345e-14 0.000000000000117549434500000000 1.175494345e-13 0.000000000001175494345000000000 1.175494345e-12 0.000000000011754943450000000000 1.175494345e-11 0.000000000117549434500000000000 1.175494345e-10 0.000000001175494345000000000000 1.175494345e-09 0.000000011754943450000000000000 1.175494345e-08 0.000000117549434500000000000000 1.175494345e-07 0.000001175494345000000000000000 1.175494345e-06 0.000011754943450000000000000000 1.175494345e-05 0.000117549434500000000000000000 0.0001175494345 0.001175494345000000000000000000 0.001175494345 0.011754943450000000000000000000 0.01175494345 0.117549434500000000000000000000 0.1175494345 SELECT CAST(my_varchar AS DECIMAL(65,30)), my_varchar FROM t1; CAST(my_varchar AS DECIMAL(65,30)) my_varchar 0.000000000000000000000000000000 1.175494345e-32 0.000000000000000000000000000000 1.175494345e-31 0.000000000000000000000000000001 1.175494345e-30 0.000000000000000000000000000012 1.175494345e-29 0.000000000000000000000000000118 1.175494345e-28 0.000000000000000000000000001175 1.175494345e-27 0.000000000000000000000000011755 1.175494345e-26 0.000000000000000000000000117549 1.175494345e-25 0.000000000000000000000001175494 1.175494345e-24 0.000000000000000000000011754943 1.175494345e-23 0.000000000000000000000117549435 1.175494345e-22 0.000000000000000000001175494345 1.175494345e-21 0.000000000000000000011754943450 1.175494345e-20 0.000000000000000000117549434500 1.175494345e-19 0.000000000000000001175494345000 1.175494345e-18 0.000000000000000011754943450000 1.175494345e-17 0.000000000000000117549434500000 1.175494345e-16 0.000000000000001175494345000000 1.175494345e-15 0.000000000000011754943450000000 1.175494345e-14 0.000000000000117549434500000000 1.175494345e-13 0.000000000001175494345000000000 1.175494345e-12 0.000000000011754943450000000000 1.175494345e-11 0.000000000117549434500000000000 1.175494345e-10 0.000000001175494345000000000000 1.175494345e-9 0.000000011754943450000000000000 1.175494345e-8 0.000000117549434500000000000000 1.175494345e-7 0.000001175494345000000000000000 1.175494345e-6 0.000011754943450000000000000000 1.175494345e-5 0.000117549434500000000000000000 1.175494345e-4 0.001175494345000000000000000000 1.175494345e-3 0.011754943450000000000000000000 1.175494345e-2 0.117549434500000000000000000000 1.175494345e-1 UPDATE t1 SET my_decimal = my_float; SELECT my_decimal, my_float FROM t1; my_decimal my_float 0.000000000000000000000000000000 1.17549e-32 0.000000000000000000000000000000 1.17549e-31 0.000000000000000000000000000001 1.17549e-30 0.000000000000000000000000000012 1.17549e-29 0.000000000000000000000000000118 1.17549e-28 0.000000000000000000000000001175 1.17549e-27 0.000000000000000000000000011755 1.17549e-26 0.000000000000000000000000117549 1.17549e-25 0.000000000000000000000001175494 1.17549e-24 0.000000000000000000000011754943 1.17549e-23 0.000000000000000000000117549438 1.17549e-22 0.000000000000000000001175494332 1.17549e-21 0.000000000000000000011754943324 1.17549e-20 0.000000000000000000117549434853 1.17549e-19 0.000000000000000001175494374380 1.17549e-18 0.000000000000000011754943743802 1.17549e-17 0.000000000000000117549432474939 1.17549e-16 0.000000000000001175494324749389 1.17549e-15 0.000000000000011754943671010360 1.17549e-14 0.000000000000117549429933840000 1.17549e-13 0.000000000001175494380653563000 1.17549e-12 0.000000000011754943372854760000 1.17549e-11 0.000000000117549428524377200000 1.17549e-10 0.000000001175494368510499000000 1.17549e-09 0.000000011754943685104990000000 1.17549e-08 0.000000117549433298336200000000 1.17549e-07 0.000001175494389826781000000000 1.17549e-06 0.000011754943443520460000000000 1.17549e-05 0.000117549432616215200000000000 0.000117549 0.001175494398921728000000000000 0.00117549 0.011754943057894710000000000000 0.0117549 0.117549434304237400000000000000 0.117549 UPDATE t1 SET my_decimal = my_double; SELECT my_decimal, my_double FROM t1; my_decimal my_double 0.000000000000000000000000000000 1.175494345e-32 0.000000000000000000000000000000 1.175494345e-31 0.000000000000000000000000000001 1.175494345e-30 0.000000000000000000000000000012 1.175494345e-29 0.000000000000000000000000000118 1.175494345e-28 0.000000000000000000000000001175 1.175494345e-27 0.000000000000000000000000011755 1.175494345e-26 0.000000000000000000000000117549 1.175494345e-25 0.000000000000000000000001175494 1.175494345e-24 0.000000000000000000000011754943 1.175494345e-23 0.000000000000000000000117549435 1.175494345e-22 0.000000000000000000001175494345 1.175494345e-21 0.000000000000000000011754943450 1.175494345e-20 0.000000000000000000117549434500 1.175494345e-19 0.000000000000000001175494345000 1.175494345e-18 0.000000000000000011754943450000 1.175494345e-17 0.000000000000000117549434500000 1.175494345e-16 0.000000000000001175494345000000 1.175494345e-15 0.000000000000011754943450000000 1.175494345e-14 0.000000000000117549434500000000 1.175494345e-13 0.000000000001175494345000000000 1.175494345e-12 0.000000000011754943450000000000 1.175494345e-11 0.000000000117549434500000000000 1.175494345e-10 0.000000001175494345000000000000 1.175494345e-09 0.000000011754943450000000000000 1.175494345e-08 0.000000117549434500000000000000 1.175494345e-07 0.000001175494345000000000000000 1.175494345e-06 0.000011754943450000000000000000 1.175494345e-05 0.000117549434500000000000000000 0.0001175494345 0.001175494345000000000000000000 0.001175494345 0.011754943450000000000000000000 0.01175494345 0.117549434500000000000000000000 0.1175494345 UPDATE t1 SET my_decimal = my_varchar; Warnings: Note 1265 Data truncated for column 'my_decimal' at row 1 Note 1265 Data truncated for column 'my_decimal' at row 2 Note 1265 Data truncated for column 'my_decimal' at row 3 Note 1265 Data truncated for column 'my_decimal' at row 4 Note 1265 Data truncated for column 'my_decimal' at row 5 Note 1265 Data truncated for column 'my_decimal' at row 6 Note 1265 Data truncated for column 'my_decimal' at row 7 Note 1265 Data truncated for column 'my_decimal' at row 8 Note 1265 Data truncated for column 'my_decimal' at row 9 Note 1265 Data truncated for column 'my_decimal' at row 10 Note 1265 Data truncated for column 'my_decimal' at row 11 SELECT my_decimal, my_varchar FROM t1; my_decimal my_varchar 0.000000000000000000000000000000 1.175494345e-32 0.000000000000000000000000000000 1.175494345e-31 0.000000000000000000000000000001 1.175494345e-30 0.000000000000000000000000000012 1.175494345e-29 0.000000000000000000000000000118 1.175494345e-28 0.000000000000000000000000001175 1.175494345e-27 0.000000000000000000000000011755 1.175494345e-26 0.000000000000000000000000117549 1.175494345e-25 0.000000000000000000000001175494 1.175494345e-24 0.000000000000000000000011754943 1.175494345e-23 0.000000000000000000000117549435 1.175494345e-22 0.000000000000000000001175494345 1.175494345e-21 0.000000000000000000011754943450 1.175494345e-20 0.000000000000000000117549434500 1.175494345e-19 0.000000000000000001175494345000 1.175494345e-18 0.000000000000000011754943450000 1.175494345e-17 0.000000000000000117549434500000 1.175494345e-16 0.000000000000001175494345000000 1.175494345e-15 0.000000000000011754943450000000 1.175494345e-14 0.000000000000117549434500000000 1.175494345e-13 0.000000000001175494345000000000 1.175494345e-12 0.000000000011754943450000000000 1.175494345e-11 0.000000000117549434500000000000 1.175494345e-10 0.000000001175494345000000000000 1.175494345e-9 0.000000011754943450000000000000 1.175494345e-8 0.000000117549434500000000000000 1.175494345e-7 0.000001175494345000000000000000 1.175494345e-6 0.000011754943450000000000000000 1.175494345e-5 0.000117549434500000000000000000 1.175494345e-4 0.001175494345000000000000000000 1.175494345e-3 0.011754943450000000000000000000 1.175494345e-2 0.117549434500000000000000000000 1.175494345e-1 DROP TABLE t1; create table t1 (c1 decimal(64)); insert into t1 values( 89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000); Warnings: Error 1292 Truncated incorrect DECIMAL value: '' Warning 1264 Out of range value for column 'c1' at row 1 insert into t1 values( 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999); Warnings: Error 1292 Truncated incorrect DECIMAL value: '' Error 1292 Truncated incorrect DECIMAL value: '' Error 1292 Truncated incorrect DECIMAL value: '' Warning 1264 Out of range value for column 'c1' at row 1 insert into t1 values(1e100); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 select * from t1; c1 9999999999999999999999999999999999999999999999999999999999999999 9999999999999999999999999999999999999999999999999999999999999999 9999999999999999999999999999999999999999999999999999999999999999 drop table t1; create table t1(a decimal(7,2)); insert into t1 values(123.12); select * from t1; a 123.12 alter table t1 modify a decimal(10,2); select * from t1; a 123.12 drop table t1; create table t1 (i int, j int); insert into t1 values (1,1), (1,2), (2,3), (2,4); select i, count(distinct j) from t1 group by i; i count(distinct j) 1 2 2 2 select i+0.0 as i2, count(distinct j) from t1 group by i2; i2 count(distinct j) 1.0 2 2.0 2 drop table t1; create table t1(f1 decimal(20,6)); insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond); insert into t1 values (CAST('10:11:12' AS time)); select * from t1; f1 20101112000000.000014 101112.000000 drop table t1; select cast(19999999999999999999 as unsigned); cast(19999999999999999999 as unsigned) 18446744073709551615 Warnings: Error 1292 Truncated incorrect DECIMAL value: '' create table t1(a decimal(18)); insert into t1 values(123456789012345678); alter table t1 modify column a decimal(19); select * from t1; a 123456789012345678 drop table t1; select cast(11.1234 as DECIMAL(3,2)); cast(11.1234 as DECIMAL(3,2)) 9.99 Warnings: Error 1264 Out of range value for column 'cast(11.1234 as DECIMAL(3,2))' at row 1 select * from (select cast(11.1234 as DECIMAL(3,2))) t; cast(11.1234 as DECIMAL(3,2)) 9.99 Warnings: Error 1264 Out of range value for column 'cast(11.1234 as DECIMAL(3,2))' at row 1 select cast(a as DECIMAL(3,2)) from (select 11.1233 as a UNION select 11.1234 UNION select 12.1234 ) t; cast(a as DECIMAL(3,2)) 9.99 9.99 9.99 Warnings: Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 select cast(a as DECIMAL(3,2)), count(*) from (select 11.1233 as a UNION select 11.1234 UNION select 12.1234 ) t group by 1; cast(a as DECIMAL(3,2)) count(*) 9.99 3 Warnings: Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 create table t1 (s varchar(100)); insert into t1 values (0.00000000010000000000000000364321973154977415791655470655996396089904010295867919921875); drop table t1; SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 * (99.999999999998 / 100) b; a b 0.9999999999999800000000000000 0.9999999999999800000000000000 SELECT CAST(1 AS decimal(65,10)); CAST(1 AS decimal(65,10)) 1.0000000000 SELECT CAST(1 AS decimal(66,10)); ERROR 42000: Too big precision 66 specified for column '1'. Maximum is 65. SELECT CAST(1 AS decimal(65,30)); CAST(1 AS decimal(65,30)) 1.000000000000000000000000000000 SELECT CAST(1 AS decimal(65,31)); ERROR 42000: Too big scale 31 specified for column '1'. Maximum is 30. CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL); INSERT INTO t1 VALUES (3,30), (1,10), (2,10); SELECT a+CAST(1 AS decimal(65,30)) AS aa, SUM(b) FROM t1 GROUP BY aa; aa SUM(b) 2.000000000000000000000000000000 10 3.000000000000000000000000000000 10 4.000000000000000000000000000000 30 SELECT a+CAST(1 AS decimal(65,31)) AS aa, SUM(b) FROM t1 GROUP BY aa; ERROR 42000: Too big scale 31 specified for column '1'. Maximum is 30. DROP TABLE t1; CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL); INSERT INTO t1 VALUES (3,30), (1,10), (2,10); SET @a= CAST(1 AS decimal); SELECT 1 FROM t1 GROUP BY @b := @a, @b; 1 1 1 DROP TABLE t1; CREATE TABLE t1 SELECT 0.123456789012345678901234567890123456 AS f1; Warnings: Note 1265 Data truncated for column 'f1' at row 1 DESC t1; Field Type Null Key Default Extra f1 decimal(31,30) NO 0.000000000000000000000000000000 SELECT f1 FROM t1; f1 0.123456789012345678901234567890 DROP TABLE t1; CREATE TABLE t1 SELECT 123451234512345123451234512345123451234512345.678906789067890678906789067890678906789067890 AS f1; Warnings: Warning 1264 Out of range value for column 'f1' at row 1 DESC t1; Field Type Null Key Default Extra f1 decimal(65,30) NO 0.000000000000000000000000000000 SELECT f1 FROM t1; f1 99999999999999999999999999999999999.999999999999999999999999999999 DROP TABLE t1; select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 * 1.01500000 * 0.99500000); (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 * 1.01500000 * 0.99500000) 0.812988073953673124592306939480 create table t1 as select 5.05 / 0.014; Warnings: Note 1265 Data truncated for column '5.05 / 0.014' at row 1 show warnings; Level Code Message Note 1265 Data truncated for column '5.05 / 0.014' at row 1 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `5.05 / 0.014` decimal(10,6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t1; 5.05 / 0.014 360.714286 DROP TABLE t1; End of 5.0 tests select cast(143.481 as decimal(4,1)); cast(143.481 as decimal(4,1)) 143.5 select cast(143.481 as decimal(4,0)); cast(143.481 as decimal(4,0)) 143 select cast(143.481 as decimal(2,1)); cast(143.481 as decimal(2,1)) 9.9 Warnings: Error 1264 Out of range value for column 'cast(143.481 as decimal(2,1))' at row 1 select cast(-3.4 as decimal(2,1)); cast(-3.4 as decimal(2,1)) -3.4 select cast(99.6 as decimal(2,0)); cast(99.6 as decimal(2,0)) 99 Warnings: Error 1264 Out of range value for column 'cast(99.6 as decimal(2,0))' at row 1 select cast(-13.4 as decimal(2,1)); cast(-13.4 as decimal(2,1)) -9.9 Warnings: Error 1264 Out of range value for column 'cast(-13.4 as decimal(2,1))' at row 1 select cast(98.6 as decimal(2,0)); cast(98.6 as decimal(2,0)) 99 # # Bug #45262: Bad effects with CREATE TABLE and DECIMAL # CREATE TABLE t1 SELECT .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; Warnings: Note 1265 Data truncated for column 'my_col' at row 1 DESCRIBE t1; Field Type Null Key Default Extra my_col decimal(30,30) NO 0.000000000000000000000000000000 SELECT my_col FROM t1; my_col 0.123456789123456789123456789123 DROP TABLE t1; CREATE TABLE t1 SELECT 1 + .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; Warnings: Note 1265 Data truncated for column 'my_col' at row 1 DESCRIBE t1; Field Type Null Key Default Extra my_col decimal(65,30) NO 0.000000000000000000000000000000 SELECT my_col FROM t1; my_col 1.123456789123456789123456789123 DROP TABLE t1; CREATE TABLE t1 SELECT 1 * .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; Warnings: Note 1265 Data truncated for column 'my_col' at row 1 DESCRIBE t1; Field Type Null Key Default Extra my_col decimal(65,30) NO 0.000000000000000000000000000000 SELECT my_col FROM t1; my_col 0.123456789123456789123456789123 DROP TABLE t1; CREATE TABLE t1 SELECT 1 / .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; Warnings: Note 1265 Data truncated for column 'my_col' at row 1 DESCRIBE t1; Field Type Null Key Default Extra my_col decimal(65,4) YES NULL SELECT my_col FROM t1; my_col 8.1000 DROP TABLE t1; CREATE TABLE t1 SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; Warnings: Note 1265 Data truncated for column 'my_col' at row 1 DESCRIBE t1; Field Type Null Key Default Extra my_col decimal(65,30) YES NULL SELECT my_col FROM t1; my_col 0.012345687012345687012345687012 DROP TABLE t1; # # Bug#45261: Crash, stored procedure + decimal # DROP TABLE IF EXISTS t1; CREATE TABLE t1 SELECT /* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001 AS c1; Warnings: Warning 1264 Out of range value for column 'c1' at row 1 DESC t1; Field Type Null Key Default Extra c1 decimal(65,0) NO 0 SELECT * FROM t1; c1 99999999999999999999999999999999999999999999999999999999999999999 DROP TABLE t1; CREATE TABLE t1 SELECT /* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001. AS c1; Warnings: Warning 1264 Out of range value for column 'c1' at row 1 DESC t1; Field Type Null Key Default Extra c1 decimal(65,0) NO 0 SELECT * FROM t1; c1 99999999999999999999999999999999999999999999999999999999999999999 DROP TABLE t1; CREATE TABLE t1 SELECT /* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001.1 /* 1 */ AS c1; Warnings: Warning 1264 Out of range value for column 'c1' at row 1 DESC t1; Field Type Null Key Default Extra c1 decimal(65,0) NO 0 SELECT * FROM t1; c1 99999999999999999999999999999999999999999999999999999999999999999 DROP TABLE t1; CREATE TABLE t1 SELECT /* 82 */ 1000000000000000000000000000000000000000000000000000000000000000000000000000000001 AS c1; Warnings: Error 1292 Truncated incorrect DECIMAL value: '' DESC t1; Field Type Null Key Default Extra c1 decimal(65,0) NO 0 SELECT * FROM t1; c1 99999999999999999999999999999999999999999999999999999999999999999 DROP TABLE t1; CREATE TABLE t1 SELECT /* 40 */ 1000000000000000000000000000000000000001.1000000000000000000000000000000000000001 /* 40 */ AS c1; Warnings: Warning 1264 Out of range value for column 'c1' at row 1 DESC t1; Field Type Null Key Default Extra c1 decimal(65,30) NO 0.000000000000000000000000000000 SELECT * FROM t1; c1 99999999999999999999999999999999999.999999999999999999999999999999 DROP TABLE t1; CREATE TABLE t1 SELECT /* 1 */ 1.10000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 80 */ AS c1; DESC t1; Field Type Null Key Default Extra c1 decimal(31,30) NO 0.000000000000000000000000000000 SELECT * FROM t1; c1 1.100000000000000000000000000000 DROP TABLE t1; CREATE TABLE t1 SELECT /* 1 */ 1.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */ AS c1; DESC t1; Field Type Null Key Default Extra c1 decimal(31,30) NO 0.000000000000000000000000000000 SELECT * FROM t1; c1 1.100000000000000000000000000000 DROP TABLE t1; CREATE TABLE t1 SELECT .100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */ AS c1; Warnings: Note 1265 Data truncated for column 'c1' at row 1 DESC t1; Field Type Null Key Default Extra c1 decimal(30,30) NO 0.000000000000000000000000000000 SELECT * FROM t1; c1 0.100000000000000000000000000000 DROP TABLE t1; CREATE TABLE t1 SELECT /* 45 */ 123456789012345678901234567890123456789012345.123456789012345678901234567890123456789012345 /* 45 */ AS c1; Warnings: Warning 1264 Out of range value for column 'c1' at row 1 DESC t1; Field Type Null Key Default Extra c1 decimal(65,30) NO 0.000000000000000000000000000000 SELECT * FROM t1; c1 99999999999999999999999999999999999.999999999999999999999999999999 DROP TABLE t1; CREATE TABLE t1 SELECT /* 65 */ 12345678901234567890123456789012345678901234567890123456789012345.1 /* 1 */ AS c1; Warnings: Warning 1264 Out of range value for column 'c1' at row 1 DESC t1; Field Type Null Key Default Extra c1 decimal(65,1) NO 0.0 SELECT * FROM t1; c1 9999999999999999999999999999999999999999999999999999999999999999.9 DROP TABLE t1; CREATE TABLE t1 SELECT /* 66 */ 123456789012345678901234567890123456789012345678901234567890123456.1 /* 1 */ AS c1; Warnings: Warning 1264 Out of range value for column 'c1' at row 1 DESC t1; Field Type Null Key Default Extra c1 decimal(65,1) NO 0.0 SELECT * FROM t1; c1 9999999999999999999999999999999999999999999999999999999999999999.9 DROP TABLE t1; CREATE TABLE t1 SELECT .123456789012345678901234567890123456789012345678901234567890123456 /* 66 */ AS c1; Warnings: Note 1265 Data truncated for column 'c1' at row 1 DESC t1; Field Type Null Key Default Extra c1 decimal(30,30) NO 0.000000000000000000000000000000 SELECT * FROM t1; c1 0.123456789012345678901234567890 DROP TABLE t1; CREATE TABLE t1 AS SELECT 123.1234567890123456789012345678901 /* 31 */ AS c1; Warnings: Note 1265 Data truncated for column 'c1' at row 1 DESC t1; Field Type Null Key Default Extra c1 decimal(33,30) NO 0.000000000000000000000000000000 SELECT * FROM t1; c1 123.123456789012345678901234567890 DROP TABLE t1; CREATE TABLE t1 SELECT 1.1 + CAST(1 AS DECIMAL(65,30)) AS c1; DESC t1; Field Type Null Key Default Extra c1 decimal(65,30) NO 0.000000000000000000000000000000 SELECT * FROM t1; c1 2.100000000000000000000000000000 DROP TABLE t1; # # Test that the integer and decimal parts are properly calculated. # CREATE TABLE t1 (a DECIMAL(30,30)); INSERT INTO t1 VALUES (0.1),(0.2),(0.3); CREATE TABLE t2 SELECT MIN(a + 0.0000000000000000000000000000001) AS c1 FROM t1; Warnings: Note 1265 Data truncated for column 'c1' at row 3 DESC t2; Field Type Null Key Default Extra c1 decimal(32,30) YES NULL DROP TABLE t1,t2; CREATE TABLE t1 (a DECIMAL(30,30)); INSERT INTO t1 VALUES (0.1),(0.2),(0.3); CREATE TABLE t2 SELECT IFNULL(a + 0.0000000000000000000000000000001, NULL) AS c1 FROM t1; Warnings: Note 1265 Data truncated for column 'c1' at row 1 Note 1265 Data truncated for column 'c1' at row 2 Note 1265 Data truncated for column 'c1' at row 3 DESC t2; Field Type Null Key Default Extra c1 decimal(34,0) YES NULL DROP TABLE t1,t2; CREATE TABLE t1 (a DECIMAL(30,30)); INSERT INTO t1 VALUES (0.1),(0.2),(0.3); CREATE TABLE t2 SELECT CASE a WHEN 0.1 THEN 0.0000000000000000000000000000000000000000000000000000000000000000001 END AS c1 FROM t1; Warnings: Note 1265 Data truncated for column 'c1' at row 1 DESC t2; Field Type Null Key Default Extra c1 decimal(65,30) YES NULL DROP TABLE t1,t2; # # Test that variables get maximum precision. # SET @decimal= 1.1; CREATE TABLE t1 SELECT @decimal AS c1; DESC t1; Field Type Null Key Default Extra c1 decimal(65,30) YES NULL SELECT * FROM t1; c1 1.100000000000000000000000000000 DROP TABLE t1; # # Bug #45261 : Crash, stored procedure + decimal # Original test by the reporter. # # should not crash CREATE TABLE t1 SELECT .123456789012345678901234567890123456789012345678901234567890123456 AS a; Warnings: Note 1265 Data truncated for column 'a' at row 1 DROP TABLE t1; CREATE PROCEDURE test_proc() BEGIN # The las non critical CUSER definition is: # DECLARE mycursor CURSOR FOR SELECT 1 % # .12345678912345678912345678912345678912345678912345678912345678912 AS my_col; DECLARE mycursor CURSOR FOR SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; OPEN mycursor; CLOSE mycursor; END| # should not crash CALL test_proc(); DROP PROCEDURE test_proc; # # Bug #48370 Absolutely wrong calculations with GROUP BY and # decimal fields when using IF # CREATE TABLE currencies (id int, rate decimal(16,4), PRIMARY KEY (id), KEY (rate)); INSERT INTO currencies VALUES (11,0.7028); INSERT INTO currencies VALUES (1,1); CREATE TABLE payments ( id int, supplier_id int, status int, currency_id int, vat decimal(7,4), PRIMARY KEY (id), KEY currency_id (currency_id), KEY supplier_id (supplier_id) ); INSERT INTO payments (id,status,vat,supplier_id,currency_id) VALUES (3001,2,0.0000,344,11), (1,2,0.0000,1,1); CREATE TABLE sub_tasks ( id int, currency_id int, price decimal(16,4), discount decimal(10,4), payment_id int, PRIMARY KEY (id), KEY currency_id (currency_id), KEY payment_id (payment_id) ) ; INSERT INTO sub_tasks (id, price, discount, payment_id, currency_id) VALUES (52, 12.60, 0, 3001, 11), (56, 14.58, 0, 3001, 11); # should return 1 and the same values in col 2 and 3 select STRAIGHT_JOIN (1 + PAY.vat) AS mult, SUM(ROUND((SUB.price - ROUND(ROUND(SUB.price, 2) * SUB.discount, 2)) * CUR.rate / CUR.rate, 2) ) v_net_with_discount, SUM(ROUND((SUB.price - ROUND(ROUND(SUB.price, 2) * SUB.discount, 1)) * CUR.rate / CUR.rate , 2) * (1 + PAY.vat) ) v_total from currencies CUR, payments PAY, sub_tasks SUB where SUB.payment_id = PAY.id and PAY.currency_id = CUR.id and PAY.id > 2 group by PAY.id + 1; mult v_net_with_discount v_total 1.0000 27.18 27.180000 DROP TABLE currencies, payments, sub_tasks; End of 5.1 tests