diff options
Diffstat (limited to 'mysql-test/main/type_newdecimal.result')
| -rw-r--r-- | mysql-test/main/type_newdecimal.result | 2307 |
1 files changed, 2307 insertions, 0 deletions
diff --git a/mysql-test/main/type_newdecimal.result b/mysql-test/main/type_newdecimal.result new file mode 100644 index 00000000000..ba84ff50504 --- /dev/null +++ b/mysql-test/main/type_newdecimal.result @@ -0,0 +1,2307 @@ +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 '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 +# +# Start of 10.0 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 <cache>(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 <cache>(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 <cache>(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; +# +# 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 +# |