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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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)) as exp;
exp
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 specified for 'sl'. Maximum is 65
create table t1 (sl decimal(32,39));
ERROR 42000: Too big scale specified for 'sl'. Maximum is 38
create table t1 (sl decimal(67,38));
ERROR 42000: Too big precision specified for 'sl'. Maximum is 65
create table t1 (sl decimal(0,50));
ERROR 42000: Too big scale 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 0
Note	1265	Data truncated for column 'f2' at row 0
Note	1265	Data truncated for column 'f3' at row 0
Note	1265	Data truncated for column 'f4' at row 0
Note	1265	Data truncated for column 'f5' at row 0
Note	1265	Data truncated for column 'f6' at row 0
Note	1265	Data truncated for column 'f7' at row 0
Note	1265	Data truncated for column 'f8' at row 0
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 2
Warning	1264	Out of range value for column 'cast(a as DECIMAL(3,2))' at row 3
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 2
Warning	1264	Out of range value for column 'cast(a as DECIMAL(3,2))' at row 3
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 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 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 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) as exp;
exp
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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
Warnings:
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`a` of type `decimal` = "`t1`.`a`" of type `datetime`
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
Warnings:
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`a` of type `decimal` = "`t1`.`a`" of type `time`
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 0
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` decimal(2,1) DEFAULT 0.0
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.1 ');
Warnings:
Note	1265	Data truncated for column 'a' at row 0
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` decimal(2,1) DEFAULT 0.1
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.10001 ');
Warnings:
Note	1265	Data truncated for column 'a' at row 0
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` decimal(2,1) DEFAULT 0.1
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.10001');
Warnings:
Note	1265	Data truncated for column 'a' at row 0
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` decimal(2,1) DEFAULT 0.1
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 0.10001);
Warnings:
Note	1265	Data truncated for column 'a' at row 0
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` decimal(2,1) DEFAULT 0.1
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 0.10001e0);
Warnings:
Note	1265	Data truncated for column 'a' at row 0
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` decimal(2,1) DEFAULT 0.1
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
#
# MDEV-25317 Assertion `scale <= precision' failed in
# decimal_bin_size And Assertion `scale >= 0 && precision > 0 && scale <= precision'
# failed in decimal_bin_size_inline/decimal_bin_size.
#
SELECT AVG(DISTINCT 0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001) as exp;
exp
0.00000000000000000000000000000000000000
CREATE TABLE t1 AS SELECT NULL AS v1;
SELECT 1 FROM t1 GROUP BY v1 ORDER BY AVG ( from_unixtime ( '' ) ) ;
1
1
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: ''
Warning	1292	Truncated incorrect unixtime value: '0.0'
DROP TABLE t1;
SELECT SUM(DISTINCT 0.000000000000000000000000000000000000001);
SUM(DISTINCT 0.000000000000000000000000000000000000001)
0.00000000000000000000000000000000000000
CREATE TABLE t1 AS SELECT 1.000000000000000000000000000000000 AS a;
ALTER TABLE t1 ADD COLUMN b INT;
SELECT ROUND (a,b) AS c FROM t1 ORDER BY c;
c
NULL
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `FORMAT(a,38)` varchar(41) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t2,t1;
#
# End of 10.4 tests
#
#
# Start of 10.5 tests
#
#
# MDEV-20548 Unexpected error on CREATE..SELECT HEX(num)
#
SET sql_mode=STRICT_ALL_TABLES;
CREATE TABLE t1 AS SELECT HEX(-2) AS h;
SELECT * FROM t1;
h
FFFFFFFFFFFFFFFE
SHOW COLUMNS IN t1;
Field	Type	Null	Key	Default	Extra
h	varchar(16)	YES		NULL	
DROP TABLE t1;
CREATE TABLE t1 AS SELECT HEX(-1) AS h;
SELECT * FROM t1;
h
FFFFFFFFFFFFFFFF
SHOW COLUMNS IN t1;
Field	Type	Null	Key	Default	Extra
h	varchar(16)	YES		NULL	
DROP TABLE t1;
CREATE TABLE t1 AS SELECT HEX(+1) AS h;
SELECT * FROM t1;
h
1
SHOW COLUMNS IN t1;
Field	Type	Null	Key	Default	Extra
h	varchar(16)	YES		NULL	
DROP TABLE t1;
CREATE TABLE t1 AS SELECT HEX(+2) AS h;
SELECT * FROM t1;
h
2
SHOW COLUMNS IN t1;
Field	Type	Null	Key	Default	Extra
h	varchar(16)	YES		NULL	
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(41,1));
INSERT INTO t1 VALUES (-1000000000000000000000000000000000000000);
INSERT INTO t1 VALUES (-0x8000000000);
INSERT INTO t1 VALUES (-0x80000000),(-0x800000),(-0x8000),(-0x80),(-2),(-1);
INSERT INTO t1 VALUES (1),(2),(128),(0x7fff),(0x7fffff),(0x7fffffff);
INSERT INTO t1 VALUES (+0x7fffffffff);
INSERT INTO t1 VALUES (+1000000000000000000000000000000000000000);
CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1;
SELECT * FROM t2 ORDER BY a;
a	h
-1000000000000000000000000000000000000000.0	FFFFFFFFFFFFFFFF
-549755813888.0	FFFFFF8000000000
-2147483648.0	FFFFFFFF80000000
-8388608.0	FFFFFFFFFF800000
-32768.0	FFFFFFFFFFFF8000
-128.0	FFFFFFFFFFFFFF80
-2.0	FFFFFFFFFFFFFFFE
-1.0	FFFFFFFFFFFFFFFF
1.0	1
2.0	2
128.0	80
32767.0	7FFF
8388607.0	7FFFFF
2147483647.0	7FFFFFFF
549755813887.0	7FFFFFFFFF
1000000000000000000000000000000000000000.0	FFFFFFFFFFFFFFFF
SHOW COLUMNS IN t2;
Field	Type	Null	Key	Default	Extra
a	decimal(41,1)	YES		NULL	
h	varchar(16)	YES		NULL	
DROP TABLE t1, t2;
SET sql_mode=DEFAULT;
#
# MDEV-25174 DOUBLE columns do not accept large hex hybrids
#
CREATE TABLE t1 (a DECIMAL(30,0));
INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFF);
INSERT INTO t1 VALUES (0x8000000000000000);
INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
SELECT * FROM t1 ORDER BY a;
a
9223372036854775807
9223372036854775808
18446744073709551615
DROP TABLE t1;
#
# End of 10.5 tests
#
#
# MDEV-32203 Raise notes when an index cannot be used on data type mismatch
#
SET note_verbosity=unusable_keys;
CREATE TABLE t1 (indexed_col DECIMAL(30,6), KEY(indexed_col));
FOR i IN 1..31
DO
INSERT INTO t1 VALUES (MAKEDATE(2023, i));
END FOR;
$$
SELECT * FROM t1 WHERE indexed_col=20230101;
indexed_col
20230101.000000
SELECT * FROM t1 WHERE indexed_col=20230101102030;
indexed_col
SELECT * FROM t1 WHERE indexed_col=20230101102030.1;
indexed_col
SELECT * FROM t1 WHERE indexed_col=20230101102030.1e0;
indexed_col
SELECT * FROM t1 WHERE indexed_col='10:20:30';
indexed_col
SELECT * FROM t1 WHERE indexed_col='2001-01-01';
indexed_col
SELECT * FROM t1 WHERE indexed_col='2001-01-01 10:20:30';
indexed_col
SELECT * FROM t1 WHERE indexed_col=DATE'2001-01-01';
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `decimal` = "DATE'2001-01-01'" of type `date`
SELECT * FROM t1 WHERE indexed_col=TIME'10:20:30';
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `decimal` = "TIME'10:20:30'" of type `time`
SELECT * FROM t1 WHERE indexed_col=TIMESTAMP'2001-01-01 10:20:30';
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `decimal` = "TIMESTAMP'2001-01-01 10:20:30'" of type `datetime`
SELECT * FROM t1 WHERE indexed_col=0x00;
indexed_col
SELECT * FROM t1 WHERE indexed_col=_utf8mb3'0' COLLATE utf8mb3_bin;
indexed_col
CREATE TABLE t2 (not_indexed_col INT);
INSERT INTO t2 VALUES (20230101),(20230102);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101.000000	20230101
20230102.000000	20230102
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col INT UNSIGNED);
INSERT INTO t2 VALUES (20230101),(20230102);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101.000000	20230101
20230102.000000	20230102
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col BIGINT);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col BIGINT UNSIGNED);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DECIMAL(30,6));
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col FLOAT);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DOUBLE);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DATE);
INSERT INTO t2 VALUES ('2023-01-01'),('2023-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101.000000	2023-01-01
20230102.000000	2023-01-02
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `decimal` = "`t2`.`not_indexed_col`" of type `date`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DATETIME);
INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101.000000	2023-01-01 00:00:00
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `decimal` = "`t2`.`not_indexed_col`" of type `datetime`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col TIMESTAMP);
INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101.000000	2023-01-01 00:00:00
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `decimal` = "`t2`.`not_indexed_col`" of type `timestamp`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARBINARY(32));
INSERT INTO t2 VALUES (0x30),(0x31);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARCHAR(32)) CHARACTER SET latin1;
INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: '2001-01-01'
Warning	1292	Truncated incorrect DECIMAL value: '2001-01-02'
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARCHAR(32) CHARACTER SET utf8mb3);
INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: '2001-01-01'
Warning	1292	Truncated incorrect DECIMAL value: '2001-01-02'
DROP TABLE t2;
DROP TABLE t1;
SET note_verbosity=DEFAULT;