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, `if(0, 1.1, 1.2)` decimal(2,1) NOT NULL, `if(0.1, 1.1, 1.2)` decimal(2,1) NOT NULL, `if(0, 1, 1.1)` decimal(2,1) NOT NULL, `if(0, NULL, 1.2)` decimal(2,1) DEFAULT NULL, `if(1, 0.22e1, 1.1)` double NOT NULL, `if(1E0, 1.1, 1.2)` decimal(2,1) NOT NULL ) 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 ignore 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 ignore 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 ignore 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 ignore 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 ignore into t1 values (18446744073709551615.0); Warnings: Warning 1264 Out of range value for column 'a' at row 1 insert ignore into t1 values (9223372036854775808.0); Warnings: Warning 1264 Out of range value for column 'a' at row 1 insert ignore 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 ignore into t1 values (9999999999999999999999999.000); Warnings: Warning 1264 Out of range value for column 'a' at row 1 insert ignore 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 ignore 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 ignore 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, `truncate(-5678.123451,-3)` decimal(4,0) NOT NULL, `abs(-1.1)` decimal(2,1) NOT NULL, `-(-1.1)` decimal(2,1) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; set session sql_mode='traditional'; select 1e10/0e0; 1e10/0e0 NULL Warnings: Warning 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: Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 select col2/0 from wl1612; col2/0 NULL NULL NULL Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 select col3/0 from wl1612; col3/0 NULL NULL NULL Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 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 'test.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.7777777777777777777000000000000000000 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,49); truncate(99999999999999999999999999999999999999,49) 99999999999999999999999999999999999999.000000000000000000000000000000000000 select truncate(99.999999999999999999999999999999999999,49); truncate(99.999999999999999999999999999999999999,49) 99.99999999999999999999999999999999999900 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 ) 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: Warning 1365 Division by 0 select 1 / 0; 1 / 0 NULL Warnings: Warning 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: Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT INTO Sow6_2f VALUES ('a59b'); ERROR 22007: Incorrect decimal value: 'a59b' for column `test`.`Sow6_2f`.`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: Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 + 1 as x; x 100000000000000000000000000000000000000000000000000000000000000000 Warnings: Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated 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.00000000000123456789024691357814814136 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.546812608597396 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 'sl'. Maximum is 65 create table t1 (sl decimal(32,39)); ERROR 42000: Too big scale 39 specified for 'sl'. Maximum is 38 create table t1 (sl decimal(67,38)); ERROR 42000: Too big precision 67 specified for 'sl'. Maximum is 65 create table t1 (sl decimal(0,50)); ERROR 42000: Too big scale 50 specified for 'sl'. Maximum is 38 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, 38)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `sl` decimal(65,38) 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 0.00000000000000117549 0.000000000000001175494345 1.175494345e-15 0.0000000000000117549 0.00000000000001175494345 1.175494345e-14 0.000000000000117549 0.0000000000001175494345 1.175494345e-13 0.00000000000117549 0.000000000001175494345 1.175494345e-12 0.0000000000117549 0.00000000001175494345 1.175494345e-11 0.000000000117549 0.0000000001175494345 1.175494345e-10 0.00000000117549 0.000000001175494345 1.175494345e-9 0.0000000117549 0.00000001175494345 1.175494345e-8 0.000000117549 0.0000001175494345 1.175494345e-7 0.00000117549 0.000001175494345 1.175494345e-6 0.0000117549 0.00001175494345 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 0.00000000000000117549 0.000000000000011754943671010362 0.0000000000000117549 0.000000000000117549429933840040 0.000000000000117549 0.000000000001175494380653563400 0.00000000000117549 0.000000000011754943372854765000 0.0000000000117549 0.000000000117549428524377220000 0.000000000117549 0.000000001175494368510499000000 0.00000000117549 0.000000011754943685104990000000 0.0000000117549 0.000000117549433298336230000000 0.000000117549 0.000001175494389826781100000000 0.00000117549 0.000011754943443520460000000000 0.0000117549 0.000117549432616215200000000000 0.000117549 0.001175494398921728100000000000 0.00117549 0.011754943057894707000000000000 0.0117549 0.117549434304237370000000000000 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 0.000000000000001175494345 0.000000000000011754943450000000 0.00000000000001175494345 0.000000000000117549434500000000 0.0000000000001175494345 0.000000000001175494345000000000 0.000000000001175494345 0.000000000011754943450000000000 0.00000000001175494345 0.000000000117549434500000000000 0.0000000001175494345 0.000000001175494345000000000000 0.000000001175494345 0.000000011754943450000000000000 0.00000001175494345 0.000000117549434500000000000000 0.0000001175494345 0.000001175494345000000000000000 0.000001175494345 0.000011754943450000000000000000 0.00001175494345 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 0.00000000000000117549 0.000000000000011754943671010362 0.0000000000000117549 0.000000000000117549429933840040 0.000000000000117549 0.000000000001175494380653563400 0.00000000000117549 0.000000000011754943372854765000 0.0000000000117549 0.000000000117549428524377220000 0.000000000117549 0.000000001175494368510499000000 0.00000000117549 0.000000011754943685104990000000 0.0000000117549 0.000000117549433298336230000000 0.000000117549 0.000001175494389826781100000000 0.00000117549 0.000011754943443520460000000000 0.0000117549 0.000117549432616215200000000000 0.000117549 0.001175494398921728100000000000 0.00117549 0.011754943057894707000000000000 0.0117549 0.117549434304237370000000000000 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 0.000000000000001175494345 0.000000000000011754943450000000 0.00000000000001175494345 0.000000000000117549434500000000 0.0000000000001175494345 0.000000000001175494345000000000 0.000000000001175494345 0.000000000011754943450000000000 0.00000000001175494345 0.000000000117549434500000000000 0.0000000001175494345 0.000000001175494345000000000000 0.000000001175494345 0.000000011754943450000000000000 0.00000001175494345 0.000000117549434500000000000000 0.0000001175494345 0.000001175494345000000000000000 0.000001175494345 0.000011754943450000000000000000 0.00001175494345 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: Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated Warning 1264 Out of range value for column 'c1' at row 1 insert into t1 values( 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999); ERROR 22003: DECIMAL value is out of range in '99999999999999999999999999999999999999999999999999999999999999999 * 99999999999999999999999999999999999999999999999999999999999999999' insert into t1 values(1e100); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 select * from t1; c1 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: Warning 1916 Got overflow when converting '19999999999999999999' to UNSIGNED INT. Value truncated 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: Warning 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: Warning 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: Warning 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Warning 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Warning 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: Warning 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Warning 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Warning 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1 Warning 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 '1'. Maximum is 65 SELECT CAST(1 AS decimal(65,38)); CAST(1 AS decimal(65,38)) 1.00000000000000000000000000000000000000 SELECT CAST(1 AS decimal(65,39)); ERROR 42000: Too big scale 39 specified for '1'. Maximum is 38 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,49)) AS aa, SUM(b) FROM t1 GROUP BY aa; ERROR 42000: Too big scale 49 specified for '1'. Maximum is 38 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.1234567890123456789012345678901234567890123456789 AS f1; Warnings: Note 1265 Data truncated for column 'f1' at row 1 DESC t1; Field Type Null Key Default Extra f1 decimal(39,38) NO NULL SELECT f1 FROM t1; f1 0.12345678901234567890123456789012345679 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,36) NO NULL SELECT f1 FROM t1; f1 99999999999999999999999999999.999999999999999999999999999999999999 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.81298807395367312459230693948000000000 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; # # Bug#12563865 # ROUNDED,TMP_BUF,DECIMAL_VALUE STACK CORRUPTION IN ALL VERSIONS >=5.0 # SELECT substring(('M') FROM (999999999999999999999999999999999999999999999999999999999999999999999999999999999)) AS foo; foo Warnings: Warning 1916 Got overflow when converting '999999999999999999999999999999999999999999999999999999999999999999999999999999999' to INT. Value truncated Warning 1916 Got overflow when converting '999999999999999999999999999999999999999999999999999999999999999999999999999999999' to INT. Value truncated SELECT min(999999999999999999999999999999999999999999999999999999999999999999999999999999999) AS foo; foo 999999999999999999999999999999999999999999999999999999999999999999999999999999999 SELECT multipolygonfromtext(('4294967294.1'),(999999999999999999999999999999999999999999999999999999999999999999999999999999999)) AS foo; foo NULL Warnings: Warning 1916 Got overflow when converting '999999999999999999999999999999999999999999999999999999999999999999999999999999999' to INT. Value truncated SELECT convert((999999999999999999999999999999999999999999999999999999999999999999999999999999999), decimal(30,30)) AS foo; foo 0.999999999999999999999999999999 Warnings: Warning 1264 Out of range value for column 'foo' at row 1 SELECT bit_xor(999999999999999999999999999999999999999999999999999999999999999999999999999999999) AS foo; foo 9223372036854775807 Warnings: Warning 1916 Got overflow when converting '999999999999999999999999999999999999999999999999999999999999999999999999999999999' to INT. Value truncated SELECT -(999999999999999999999999999999999999999999999999999999999999999999999999999999999) AS foo; foo -999999999999999999999999999999999999999999999999999999999999999999999999999999999 SELECT date_sub((999999999999999999999999999999999999999999999999999999999999999999999999999999999), interval ((SELECT date_add((0x77500000), interval ('Oml') second))) day_minute) AS foo; foo NULL Warnings: Warning 1292 Incorrect datetime value: '999999999999999999999999999999999999999999999999999999999999999999999999999999999' SELECT truncate(999999999999999999999999999999999999999999999999999999999999999999999999999999999, 28) AS foo; foo 999999999999999999999999999999999999999999999999999999999999999999999999999999999 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: Warning 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: Warning 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: Warning 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(38,38) NO NULL SELECT my_col FROM t1; my_col 0.12345678912345678912345678912345678912 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,38) NO NULL SELECT my_col FROM t1; my_col 1.12345678912345678912345678912345678912 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,38) NO NULL SELECT my_col FROM t1; my_col 0.12345678912345678912345678912345678912 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,38) YES NULL SELECT my_col FROM t1; my_col 0.01234568701234568701234568701234568701 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 NULL 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 NULL 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 NULL SELECT * FROM t1; c1 99999999999999999999999999999999999999999999999999999999999999999 DROP TABLE t1; CREATE TABLE t1 SELECT /* 82 */ 1000000000000000000000000000000000000000000000000000000000000000000000000000000001 AS c1; Warnings: Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated DESC t1; Field Type Null Key Default Extra c1 decimal(65,0) NO NULL 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,36) NO NULL SELECT * FROM t1; c1 99999999999999999999999999999.999999999999999999999999999999999999 DROP TABLE t1; CREATE TABLE t1 SELECT /* 1 */ 1.10000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 80 */ AS c1; DESC t1; Field Type Null Key Default Extra c1 decimal(39,38) NO NULL SELECT * FROM t1; c1 1.10000000000000000000000000000000000000 DROP TABLE t1; CREATE TABLE t1 SELECT /* 1 */ 1.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */ AS c1; DESC t1; Field Type Null Key Default Extra c1 decimal(39,38) NO NULL SELECT * FROM t1; c1 1.10000000000000000000000000000000000000 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(38,38) NO NULL SELECT * FROM t1; c1 0.10000000000000000000000000000000000000 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,36) NO NULL SELECT * FROM t1; c1 99999999999999999999999999999.999999999999999999999999999999999999 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 NULL 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 NULL 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(38,38) NO NULL SELECT * FROM t1; c1 0.12345678901234567890123456789012345679 DROP TABLE t1; CREATE TABLE t1 AS SELECT 123.1234567890123456789012345678901 /* 31 */ AS c1; DESC t1; Field Type Null Key Default Extra c1 decimal(34,31) NO NULL SELECT * FROM t1; c1 123.1234567890123456789012345678901 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 NULL 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; DESC t2; Field Type Null Key Default Extra c1 decimal(33,31) 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.00000000000000000000000000000000000000000000000001, 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(52,38) 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,38) 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,38) YES NULL SELECT * FROM t1; c1 1.10000000000000000000000000000000000000 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; # # BUG#52171: distinct aggregates on unsigned decimal fields trigger assertions # CREATE TABLE t1 (a DECIMAL(4,4) UNSIGNED); INSERT INTO t1 VALUES (0); SELECT AVG(DISTINCT a) FROM t1; AVG(DISTINCT a) 0.00000000 SELECT SUM(DISTINCT a) FROM t1; SUM(DISTINCT a) 0.0000 DROP TABLE t1; # # Bug#55436: buffer overflow in debug binary of dbug_buff in # Field_new_decimal::store_value # SET SQL_MODE=''; CREATE TABLE t1(f1 DECIMAL(44,24)) ENGINE=MYISAM; INSERT INTO t1 SET f1 = -64878E-85; Warnings: Note 1265 Data truncated for column 'f1' at row 1 SELECT f1 FROM t1; f1 0.000000000000000000000000 DROP TABLE IF EXISTS t1; End of 5.1 tests # # BUG#12911710 - VALGRIND FAILURE IN # ROW-DEBUG:PERFSCHEMA.SOCKET_SUMMARY_BY_INSTANCE_FUNC # CREATE TABLE t1(d1 DECIMAL(60,0) NOT NULL, d2 DECIMAL(60,0) NOT NULL); INSERT INTO t1 (d1, d2) VALUES(0.0, 0.0); SELECT d1 * d2 FROM t1; d1 * d2 0 DROP TABLE t1; select 0.000000000000000000000000000000000000000000000000001 mod 1; 0.000000000000000000000000000000000000000000000000001 mod 1 0.00000000000000000000000000000000000000 select 0.0000000001 mod 1; 0.0000000001 mod 1 0.0000000001 select 0.01 mod 1; 0.01 mod 1 0.01 CREATE TABLE t1 ( `FLD1` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD2` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD3` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD4` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD5` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD6` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD7` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD8` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD9` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD10` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD11` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD12` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD13` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD14` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD15` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD16` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD17` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD18` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD19` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD20` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD21` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD22` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000, `FLD23` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000 ); INSERT INTO t1 VALUES (001.0760,000.9500,001.0000,001.0000,001.0000, 001.0000,001.0000,001.0000,001.0000,001.0000,001.0000,000.5949,001.0194, 001.0000,001.0000,001.0000,001.0000,001.0000,001.0000,000.9220,001.1890,001.2130,327.2690); select FLD1*FLD2*FLD3*FLD4*FLD5*FLD6*FLD7*FLD8*FLD9*FLD10*FLD11*FLD12*FLD13*FLD14*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21*FLD22*FLD23 as calc1 from t1; calc1 269.77575757644053032218703200000000000000 select FLD23*FLD2*FLD1*FLD4*FLD5*FLD11*FLD12*FLD13*FLD3*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21*FLD22*FLD14*FLD6*FLD7*FLD8*FLD9*FLD10 as calc2 from t1; calc2 269.77575757644053032218703200000000000000 DROP TABLE t1; CREATE TABLE t1 AS SELECT 1.0 * 2.000; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `1.0 * 2.000` decimal(6,4) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # # MDEV-20495 Assertion `precision > 0' failed in decimal_bin_size upon CREATE .. SELECT with zerofilled decimal # CREATE TABLE t1 (d DECIMAL(1,0) ZEROFILL); CREATE TABLE t2 AS SELECT 0 MOD d AS f FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `f` decimal(1,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1, t2; CREATE TABLE t1 (d DECIMAL(1,0) UNSIGNED); CREATE TABLE t2 AS SELECT 0 MOD d AS f FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `f` decimal(1,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (d DECIMAL(1,0) ZEROFILL); CREATE TABLE t2 AS SELECT CAST(0 AS UNSIGNED) MOD d AS f FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `f` decimal(1,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1, t2; CREATE TABLE t1 (d DECIMAL(1,0) UNSIGNED); CREATE TABLE t2 AS SELECT CAST(0 AS UNSIGNED) MOD d AS f FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `f` decimal(1,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1,t2; # # MDEV-20560 Assertion `precision > 0' failed in decimal_bin_size upon SELECT with MOD short unsigned decimal # CREATE TABLE t1 (a DECIMAL(1,0) UNSIGNED); INSERT INTO t1 VALUES (1.0),(2.0); SELECT DISTINCT 1 MOD a FROM t1; 1 MOD a 0 1 CREATE TABLE t2 AS SELECT DISTINCT 1 MOD a AS f FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `f` decimal(1,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1, t2; CREATE TABLE t1 (a DECIMAL(1,0) UNSIGNED); INSERT INTO t1 VALUES (1.0),(2.0); SELECT DISTINCT 1 MOD a FROM t1; 1 MOD a 0 1 CREATE TABLE t2 AS SELECT DISTINCT CAST(1 AS UNSIGNED) MOD a AS f FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `f` decimal(1,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1, t2; # # End of 5.5 tests # # # MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns # CREATE TABLE t1 (a DATETIME PRIMARY KEY); INSERT INTO t1 VALUES ('1999-01-01 00:00:00'); CREATE TABLE t2 (a DECIMAL(30,1)); INSERT INTO t2 VALUES (19990101000000); INSERT INTO t2 VALUES (990101000000); SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; a 1999-01-01 00:00:00 1999-01-01 00:00:00 SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; a 1999-01-01 00:00:00 1999-01-01 00:00:00 ALTER TABLE t2 ADD PRIMARY KEY(a); SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; a 1999-01-01 00:00:00 1999-01-01 00:00:00 SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; a 1999-01-01 00:00:00 1999-01-01 00:00:00 # t2 should NOT be eliminated EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 1 SIMPLE t2 index PRIMARY PRIMARY 14 NULL 2 Using where; Using index DROP TABLE t1,t2; # # MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns # CREATE TABLE t1 (a TIME(6) PRIMARY KEY); INSERT INTO t1 VALUES ('10:20:30'); CREATE TABLE t2 (a DECIMAL(30,10)); INSERT INTO t2 VALUES (102030),(102030.000000001); SELECT t1.* FROM t1 JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 ALTER TABLE t2 ADD PRIMARY KEY(a); SELECT t1.* FROM t1 JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); a 10:20:30.000000 10:20:30.000000 # t2 should NOT be eliminated EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 1 SIMPLE t2 index PRIMARY PRIMARY 14 NULL 2 Using where; Using index DROP TABLE t1,t2; # # End of 10.0 tests # # # Start of 10.1 tests # # # MDEV-8703 Wrong result for SELECT..WHERE LENGTH(decimal_10_1_column)!=3 AND decimal_10_1_column=1.10 # CREATE TABLE t1 (a DECIMAL(10,1)); INSERT INTO t1 VALUES (1.1),(1.2),(1.3); SELECT * FROM t1 WHERE LENGTH(a)!=3; a SELECT * FROM t1 WHERE LENGTH(a)!=3 AND a=1.10; a EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)!=3 AND a=1.10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 # Notice 1.1 instead of 1.10 in the final WHERE condition EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)!=rand() AND a=1.10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10 and (octet_length(1.1)) <> rand() DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(10,2)); INSERT INTO t1 VALUES (1.1),(1.2),(1.3); SELECT * FROM t1 WHERE LENGTH(a)!=4; a SELECT * FROM t1 WHERE LENGTH(a)!=4 AND a=1.10; a EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)!=4 AND a=1.10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 # Notice 1.10 in the final WHERE condition EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)!=rand() AND a=1.10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10 and (octet_length(1.10)) <> rand() DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(10,3)); INSERT INTO t1 VALUES (1.1),(1.2),(1.3); SELECT * FROM t1 WHERE LENGTH(a)!=5; a SELECT * FROM t1 WHERE LENGTH(a)!=5 AND a=1.10; a EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)!=5 AND a=1.10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 # Notice 1.100 rather than 1.10 in the final WHERE condition EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)!=rand() AND a=1.10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10 and (octet_length(1.100)) <> rand() DROP TABLE t1; # # MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010 # CREATE TABLE t1 (a DECIMAL(10,1) ZEROFILL); INSERT INTO t1 VALUES (2010),(2020); EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010.0 AND a>=2010.0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2010.0 DROP TABLE t1; # # MDEV-8635 Redundant warnings on WHERE decimal_column='ax' # CREATE TABLE t1 (a DECIMAL, KEY(a)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2),(3),(4),(5); EXPLAIN SELECT * FROM t1 WHERE a='ax' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 6 const 1 Using where; Using index; Using filesort DROP TABLE t1; # # MDEV-8502 DECIMAL accepts out of range DEFAULT values # CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000.0); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000e0); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '10000.0'); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '10000.1'); ERROR 42000: Invalid default value for 'a' # # MDEV-10277 Redundant NOTE when inserting '0.00001 ' into a DECIMAL(2,1) column # CREATE TABLE t1 (a DECIMAL(2,1)); INSERT INTO t1 VALUES ('0.00001 '); Warnings: Note 1265 Data truncated for column 'a' at row 1 INSERT INTO t1 VALUES ('1e-10000 '); Warnings: Note 1265 Data truncated for column 'a' at row 1 INSERT INTO t1 VALUES ('0.1 '); Warnings: Note 1265 Data truncated for column 'a' at row 1 INSERT INTO t1 VALUES ('0.111 '); Warnings: Note 1265 Data truncated for column 'a' at row 1 SELECT * FROM t1; a 0.0 0.0 0.1 0.1 DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '1e-10000'); Warnings: Note 1265 Data truncated for column 'a' at row 1 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(2,1) DEFAULT 0.0 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.1 '); Warnings: Note 1265 Data truncated for column 'a' at row 1 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(2,1) DEFAULT 0.1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.10001 '); Warnings: Note 1265 Data truncated for column 'a' at row 1 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(2,1) DEFAULT 0.1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.10001'); Warnings: Note 1265 Data truncated for column 'a' at row 1 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(2,1) DEFAULT 0.1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 0.10001); Warnings: Note 1265 Data truncated for column 'a' at row 1 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(2,1) DEFAULT 0.1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 0.10001e0); Warnings: Note 1265 Data truncated for column 'a' at row 1 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(2,1) DEFAULT 0.1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # # MDEV-23105 Cast number string with many leading zeros to decimal gives unexpected result # SELECT CAST(0000000000000000000000000000000000000000000000000000000000000000000000000000000020.01 AS DECIMAL(15,2)) as val; val 20.01 SET sql_mode=''; CREATE TABLE t1 (a TEXT); INSERT INTO t1 VALUES (CONCAT(REPEAT('0',100),1)); INSERT INTO t1 VALUES (CONCAT(REPEAT('0',100),1,'.0')); INSERT INTO t1 VALUES (CONCAT(REPEAT('0',100),1,'.9')); INSERT INTO t1 VALUES (CONCAT(REPEAT('0',100),1,'.99')); INSERT INTO t1 VALUES (CONCAT(REPEAT('0',100),1,'.994')); INSERT INTO t1 VALUES (CONCAT(REPEAT('0',100),1,'.995')); INSERT INTO t1 VALUES (CONCAT(REPEAT('0',100),1,'.999')); CREATE TABLE t2 (a TEXT, d DECIMAL(15,2)); INSERT IGNORE INTO t2 (a,d) SELECT a, a FROM t1; Warnings: Note 1265 Data truncated for column 'd' at row 5 Note 1265 Data truncated for column 'd' at row 6 Note 1265 Data truncated for column 'd' at row 7 INSERT IGNORE INTO t2 (a,d) SELECT CONCAT('-',a), CONCAT('-',a) FROM t1; Warnings: Note 1265 Data truncated for column 'd' at row 5 Note 1265 Data truncated for column 'd' at row 6 Note 1265 Data truncated for column 'd' at row 7 SELECT d, a FROM t2 ORDER BY d,a; d a -2.00 -00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.995 -2.00 -00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.999 -1.99 -00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.99 -1.99 -00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.994 -1.90 -00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.9 -1.00 -00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 -1.00 -00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.0 1.00 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 1.00 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.0 1.90 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.9 1.99 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.99 1.99 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.994 2.00 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.995 2.00 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001.999 DROP TABLE t1, t2; SET sql_mode=DEFAULT; # # End of 10.1 tests # # # Bug#18408499 UNSIGNED BIGINT HIGH VALUES # WRONG NUMERICAL COMPARISON RESULTS # CREATE TABLE t1(value DECIMAL(24,0) NOT NULL); INSERT INTO t1(value) VALUES('100000000000000000000001'), ('100000000000000000000002'), ('100000000000000000000003'); SELECT * FROM t1 WHERE value = '100000000000000000000002'; value 100000000000000000000002 SELECT * FROM t1 WHERE '100000000000000000000002' = value; value 100000000000000000000002 SELECT * FROM t1 WHERE value + 0 = '100000000000000000000002'; value 100000000000000000000002 SELECT * FROM t1 WHERE value = 100000000000000000000002; value 100000000000000000000002 SELECT * FROM t1 WHERE value + 0 = 100000000000000000000002; value 100000000000000000000002 PREPARE stmt FROM 'SELECT * FROM t1 WHERE value = ?'; set @a="100000000000000000000002"; EXECUTE stmt using @a; value 100000000000000000000002 set @a=100000000000000000000002; EXECUTE stmt using @a; value 100000000000000000000002 DEALLOCATE PREPARE stmt; ALTER TABLE t1 ADD INDEX value (value); SELECT * FROM t1 WHERE value = '100000000000000000000002'; value 100000000000000000000002 DROP TABLE t1; # # End of 10.1 tests # # # Test CREATE .. SELECT # create or replace table t1 as select 1.000000000000000000000000000000000 as a; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(34,33) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 create or replace table t1 as select 1.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 as a; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(39,38) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 create or replace table t1 as select 1.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 as a; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(39,38) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; # # End of 10.2 tests # # # Start of 10.4 tests # # # MDEV-23032 FLOOR()/CEIL() incorrectly calculate the precision of a DECIMAL(M,D) column. # CREATE PROCEDURE p1(prec INT, scale INT, suffix VARCHAR(32)) BEGIN EXECUTE IMMEDIATE CONCAT('CREATE TABLE t1 (a decimal(',prec,',',scale,')',suffix,')'); INSERT IGNORE INTO t1 VALUES (-1e100), (+1e100); CREATE TABLE t2 AS SELECT a, FLOOR(a) AS fa, CEILING(a) AS ca, LENGTH(FLOOR(a)), LENGTH(CEILING(a)) FROM t1 ORDER BY a; SHOW CREATE TABLE t2; SELECT * FROM t2; DROP TABLE t2, t1; END; $$ CALL p1(38,10,''); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,10) DEFAULT NULL, `fa` decimal(29,0) DEFAULT NULL, `ca` decimal(29,0) DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -9999999999999999999999999999.9999999999 fa -10000000000000000000000000000 ca -9999999999999999999999999999 LENGTH(FLOOR(a)) 30 LENGTH(CEILING(a)) 29 a 9999999999999999999999999999.9999999999 fa 9999999999999999999999999999 ca 10000000000000000000000000000 LENGTH(FLOOR(a)) 28 LENGTH(CEILING(a)) 29 CALL p1(28,10,''); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(28,10) DEFAULT NULL, `fa` decimal(19,0) DEFAULT NULL, `ca` decimal(19,0) DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -999999999999999999.9999999999 fa -1000000000000000000 ca -999999999999999999 LENGTH(FLOOR(a)) 20 LENGTH(CEILING(a)) 19 a 999999999999999999.9999999999 fa 999999999999999999 ca 1000000000000000000 LENGTH(FLOOR(a)) 18 LENGTH(CEILING(a)) 19 CALL p1(27,10,''); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(27,10) DEFAULT NULL, `fa` bigint(19) DEFAULT NULL, `ca` bigint(19) DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -99999999999999999.9999999999 fa -100000000000000000 ca -99999999999999999 LENGTH(FLOOR(a)) 19 LENGTH(CEILING(a)) 18 a 99999999999999999.9999999999 fa 99999999999999999 ca 100000000000000000 LENGTH(FLOOR(a)) 17 LENGTH(CEILING(a)) 18 CALL p1(20,10,''); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(20,10) DEFAULT NULL, `fa` bigint(12) DEFAULT NULL, `ca` bigint(12) DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -9999999999.9999999999 fa -10000000000 ca -9999999999 LENGTH(FLOOR(a)) 12 LENGTH(CEILING(a)) 11 a 9999999999.9999999999 fa 9999999999 ca 10000000000 LENGTH(FLOOR(a)) 10 LENGTH(CEILING(a)) 11 CALL p1(19,10,''); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(19,10) DEFAULT NULL, `fa` bigint(11) DEFAULT NULL, `ca` bigint(11) DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -999999999.9999999999 fa -1000000000 ca -999999999 LENGTH(FLOOR(a)) 11 LENGTH(CEILING(a)) 10 a 999999999.9999999999 fa 999999999 ca 1000000000 LENGTH(FLOOR(a)) 9 LENGTH(CEILING(a)) 10 CALL p1(18,10,''); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(18,10) DEFAULT NULL, `fa` int(10) DEFAULT NULL, `ca` int(10) DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -99999999.9999999999 fa -100000000 ca -99999999 LENGTH(FLOOR(a)) 10 LENGTH(CEILING(a)) 9 a 99999999.9999999999 fa 99999999 ca 100000000 LENGTH(FLOOR(a)) 8 LENGTH(CEILING(a)) 9 CALL p1(10,10,''); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(10,10) DEFAULT NULL, `fa` int(2) DEFAULT NULL, `ca` int(2) DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -0.9999999999 fa -1 ca 0 LENGTH(FLOOR(a)) 2 LENGTH(CEILING(a)) 1 a 0.9999999999 fa 0 ca 1 LENGTH(FLOOR(a)) 1 LENGTH(CEILING(a)) 1 CALL p1(38,10,' UNSIGNED'); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,10) unsigned DEFAULT NULL, `fa` decimal(28,0) unsigned DEFAULT NULL, `ca` decimal(29,0) unsigned DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0.0000000000 fa 0 ca 0 LENGTH(FLOOR(a)) 1 LENGTH(CEILING(a)) 1 a 9999999999999999999999999999.9999999999 fa 9999999999999999999999999999 ca 10000000000000000000000000000 LENGTH(FLOOR(a)) 28 LENGTH(CEILING(a)) 29 CALL p1(28,10,' UNSIGNED'); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(28,10) unsigned DEFAULT NULL, `fa` bigint(18) unsigned DEFAULT NULL, `ca` decimal(19,0) unsigned DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0.0000000000 fa 0 ca 0 LENGTH(FLOOR(a)) 1 LENGTH(CEILING(a)) 1 a 999999999999999999.9999999999 fa 999999999999999999 ca 1000000000000000000 LENGTH(FLOOR(a)) 18 LENGTH(CEILING(a)) 19 CALL p1(27,10,' UNSIGNED'); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(27,10) unsigned DEFAULT NULL, `fa` bigint(17) unsigned DEFAULT NULL, `ca` bigint(18) unsigned DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0.0000000000 fa 0 ca 0 LENGTH(FLOOR(a)) 1 LENGTH(CEILING(a)) 1 a 99999999999999999.9999999999 fa 99999999999999999 ca 100000000000000000 LENGTH(FLOOR(a)) 17 LENGTH(CEILING(a)) 18 CALL p1(20,10,' UNSIGNED'); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(20,10) unsigned DEFAULT NULL, `fa` bigint(10) unsigned DEFAULT NULL, `ca` bigint(11) unsigned DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0.0000000000 fa 0 ca 0 LENGTH(FLOOR(a)) 1 LENGTH(CEILING(a)) 1 a 9999999999.9999999999 fa 9999999999 ca 10000000000 LENGTH(FLOOR(a)) 10 LENGTH(CEILING(a)) 11 CALL p1(19,10,' UNSIGNED'); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(19,10) unsigned DEFAULT NULL, `fa` int(9) unsigned DEFAULT NULL, `ca` bigint(10) unsigned DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0.0000000000 fa 0 ca 0 LENGTH(FLOOR(a)) 1 LENGTH(CEILING(a)) 1 a 999999999.9999999999 fa 999999999 ca 1000000000 LENGTH(FLOOR(a)) 9 LENGTH(CEILING(a)) 10 CALL p1(18,10,' UNSIGNED'); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(18,10) unsigned DEFAULT NULL, `fa` int(8) unsigned DEFAULT NULL, `ca` int(9) unsigned DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0.0000000000 fa 0 ca 0 LENGTH(FLOOR(a)) 1 LENGTH(CEILING(a)) 1 a 99999999.9999999999 fa 99999999 ca 100000000 LENGTH(FLOOR(a)) 8 LENGTH(CEILING(a)) 9 CALL p1(10,10,' UNSIGNED'); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(10,10) unsigned DEFAULT NULL, `fa` int(1) unsigned DEFAULT NULL, `ca` int(1) unsigned DEFAULT NULL, `LENGTH(FLOOR(a))` int(10) DEFAULT NULL, `LENGTH(CEILING(a))` int(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0.0000000000 fa 0 ca 0 LENGTH(FLOOR(a)) 1 LENGTH(CEILING(a)) 1 a 0.9999999999 fa 0 ca 1 LENGTH(FLOOR(a)) 1 LENGTH(CEILING(a)) 1 DROP PROCEDURE p1; # # MDEV-23118 FORMAT(d1,dec) where dec=0/38 and d1 is DECIMAL(38,38) gives incorrect results # CREATE OR REPLACE TABLE t1 (a DECIMAL(38,38)); INSERT INTO t1 VALUES (-0.9999999999999999999999999999999999999), (0.9999999999999999999999999999999999999); SELECT a, FORMAT(a,0), FORMAT(a,38) FROM t1; a -0.99999999999999999999999999999999999990 FORMAT(a,0) -1 FORMAT(a,38) -0.99999999999999999999999999999999999990 a 0.99999999999999999999999999999999999990 FORMAT(a,0) 1 FORMAT(a,38) 0.99999999999999999999999999999999999990 CREATE OR REPLACE TABLE t2 AS SELECT a, FORMAT(a,0), FORMAT(a,38) FROM t1; SELECT * FROM t2; a -0.99999999999999999999999999999999999990 FORMAT(a,0) -1 FORMAT(a,38) -0.99999999999999999999999999999999999990 a 0.99999999999999999999999999999999999990 FORMAT(a,0) 1 FORMAT(a,38) 0.99999999999999999999999999999999999990 SHOW CREATE TABLE t2; Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,38) DEFAULT NULL, `FORMAT(a,0)` varchar(2) DEFAULT NULL, `FORMAT(a,38)` varchar(41) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2,t1; # # End of 10.4 tests #