1# bug in decimal() with negative numbers by kaido@tradenet.ee 2 3--disable_warnings 4DROP TABLE IF EXISTS t1, t2; 5--enable_warnings 6SET SQL_WARNINGS=1; 7 8CREATE TABLE t1 ( 9 id int(11) NOT NULL auto_increment, 10 datatype_id int(11) DEFAULT '0' NOT NULL, 11 min_value decimal(20,10) DEFAULT '0.0000000000' NOT NULL, 12 max_value decimal(20,10) DEFAULT '0.0000000000' NOT NULL, 13 valuename varchar(20), 14 forecolor int(11), 15 backcolor int(11), 16 PRIMARY KEY (id), 17 UNIQUE datatype_id (datatype_id, min_value, max_value) 18); 19INSERT INTO t1 VALUES ( '1', '4', '0.0000000000', '0.0000000000', 'Ei saja', '0', '16776960'); 20INSERT INTO t1 VALUES ( '2', '4', '1.0000000000', '1.0000000000', 'Sajab', '16777215', '255'); 21INSERT INTO t1 VALUES ( '3', '1', '2.0000000000', '49.0000000000', '', '0', '16777215'); 22INSERT INTO t1 VALUES ( '60', '11', '0.0000000000', '0.0000000000', 'Rikkis', '16777215', '16711680'); 23INSERT INTO t1 VALUES ( '4', '12', '1.0000000000', '1.0000000000', 'nork sadu', '65280', '14474460'); 24INSERT INTO t1 VALUES ( '5', '12', '2.0000000000', '2.0000000000', 'keskmine sadu', '255', '14474460'); 25INSERT INTO t1 VALUES ( '6', '12', '3.0000000000', '3.0000000000', 'tugev sadu', '127', '14474460'); 26INSERT INTO t1 VALUES ( '43', '39', '6.0000000000', '6.0000000000', 'lobjakas', '13107327', '16763080'); 27INSERT INTO t1 VALUES ( '40', '39', '2.0000000000', '2.0000000000', 'vihm', '8355839', '16777215'); 28INSERT INTO t1 VALUES ( '53', '1', '-35.0000000000', '-5.0000000000', '', '0', '16777215'); 29INSERT INTO t1 VALUES ( '41', '39', '3.0000000000', '3.0000000000', 'k�lm vihm', '120', '16763080'); 30INSERT INTO t1 VALUES ( '12', '21', '21.0000000000', '21.0000000000', 'Kuiv', '13158600', '16777215'); 31INSERT INTO t1 VALUES ( '13', '21', '13.0000000000', '13.0000000000', 'M�rg', '5263615', '16777215'); 32INSERT INTO t1 VALUES ( '14', '21', '22.0000000000', '22.0000000000', 'Niiske', '9869055', '16777215'); 33INSERT INTO t1 VALUES ( '19', '21', '33.0000000000', '33.0000000000', 'M�rg', '5263615', '16777215'); 34INSERT INTO t1 VALUES ( '15', '21', '23.0000000000', '23.0000000000', 'M�rg', '5263615', '16777215'); 35INSERT INTO t1 VALUES ( '16', '21', '31.0000000000', '31.0000000000', 'Kuiv', '13158600', '16777215'); 36INSERT INTO t1 VALUES ( '17', '21', '12.0000000000', '12.0000000000', 'Niiske', '9869055', '16777215'); 37INSERT INTO t1 VALUES ( '18', '21', '32.0000000000', '32.0000000000', 'Niiske', '9869055', '16777215'); 38INSERT INTO t1 VALUES ( '20', '21', '331.0000000000', '331.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600'); 39INSERT INTO t1 VALUES ( '21', '21', '11.0000000000', '11.0000000000', 'Kuiv', '13158600', '16777215'); 40INSERT INTO t1 VALUES ( '22', '33', '21.0000000000', '21.0000000000', 'Pilves, kuiv', '8355711', '12632256'); 41INSERT INTO t1 VALUES ( '23', '33', '13.0000000000', '13.0000000000', 'Sajab, m�rg', '0', '8355839'); 42INSERT INTO t1 VALUES ( '24', '33', '22.0000000000', '22.0000000000', 'Pilves, niiske', '8355711', '12632319'); 43INSERT INTO t1 VALUES ( '29', '33', '33.0000000000', '33.0000000000', 'Selge, m�rg', '16777215', '8355839'); 44INSERT INTO t1 VALUES ( '25', '33', '23.0000000000', '23.0000000000', 'Pilves, m�rg', '8355711', '8355839'); 45INSERT INTO t1 VALUES ( '26', '33', '31.0000000000', '31.0000000000', 'Selge, kuiv', '16777215', '12632256'); 46INSERT INTO t1 VALUES ( '27', '33', '12.0000000000', '12.0000000000', 'Sajab, niiske', '0', '12632319'); 47INSERT INTO t1 VALUES ( '28', '33', '32.0000000000', '32.0000000000', 'Selge, niiske', '16777215', '12632319'); 48INSERT INTO t1 VALUES ( '30', '33', '331.0000000000', '331.0000000000', 'H�rmatis! selge,kuiv', '16711680', '12632256'); 49INSERT INTO t1 VALUES ( '31', '33', '11.0000000000', '11.0000000000', 'Sajab, kuiv', '0', '12632256'); 50INSERT INTO t1 VALUES ( '32', '11', '1.0000000000', '1.0000000000', 'Korras', '16777215', '49152'); 51INSERT INTO t1 VALUES ( '33', '21', '335.0000000000', '335.0000000000', 'H�rmatis!', '14448840', '11842740'); 52INSERT INTO t1 VALUES ( '34', '21', '134.0000000000', '134.0000000000', 'Hoiatus, M+S!', '255', '13158600'); 53INSERT INTO t1 VALUES ( '35', '21', '133.0000000000', '133.0000000000', 'Hoiatus, m�rg!', '5263615', '13158600'); 54INSERT INTO t1 VALUES ( '36', '21', '135.0000000000', '135.0000000000', 'H�rmatis!', '14448840', '11842740'); 55INSERT INTO t1 VALUES ( '37', '21', '334.0000000000', '334.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600'); 56INSERT INTO t1 VALUES ( '38', '21', '132.0000000000', '132.0000000000', 'Hoiatus, niiske!', '9869055', '13158600'); 57INSERT INTO t1 VALUES ( '39', '39', '1.0000000000', '1.0000000000', 'ei saja', '11206570', '16777215'); 58INSERT INTO t1 VALUES ( '44', '39', '4.0000000000', '5.0000000000', 'lumi', '16711680', '16763080'); 59INSERT INTO t1 VALUES ( '45', '12', '0.0000000000', '0.0000000000', '', '16777215', '14474460'); 60INSERT INTO t1 VALUES ( '46', '39', '8.0000000000', '8.0000000000', 'rahe', '9830400', '16763080'); 61INSERT INTO t1 VALUES ( '47', '39', '9.0000000000', '9.0000000000', 't��p ebaselge', '12582912', '16777215'); 62INSERT INTO t1 VALUES ( '48', '39', '7.0000000000', '7.0000000000', 'lumetuisk', '7209070', '16763080'); 63INSERT INTO t1 VALUES ( '142', '15', '2.0000000000', '49.0000000000', '', '0', '16777215'); 64INSERT INTO t1 VALUES ( '52', '1', '-4.9000000000', '-0.1000000000', '', '0', '15774720'); 65INSERT INTO t1 VALUES ( '141', '15', '-4.9000000000', '-0.1000000000', '', '0', '15774720'); 66INSERT INTO t1 VALUES ( '55', '8', '0.0000000000', '0.0000000000', '', '0', '16777215'); 67INSERT INTO t1 VALUES ( '56', '8', '0.0100000000', '0.1000000000', '', '0', '16770560'); 68INSERT INTO t1 VALUES ( '57', '8', '0.1100000000', '25.0000000000', '', '0', '15774720'); 69INSERT INTO t1 VALUES ( '58', '2', '90.0000000000', '94.9000000000', '', NULL, '16770560'); 70INSERT INTO t1 VALUES ( '59', '6', '0.0000000000', '360.0000000000', '', NULL, '16777215'); 71INSERT INTO t1 VALUES ( '61', '21', '38.0000000000', '38.0000000000', 'Niiske', '9869055', '16777215'); 72INSERT INTO t1 VALUES ( '62', '38', '500.0000000000', '999.0000000000', '', '0', '16770560'); 73INSERT INTO t1 VALUES ( '63', '38', '1000.0000000000', '2000.0000000000', '', '0', '16777215'); 74INSERT INTO t1 VALUES ( '64', '17', '0.0000000000', '0.0000000000', '', NULL, '16777215'); 75INSERT INTO t1 VALUES ( '65', '17', '0.1000000000', '10.0000000000', '', NULL, '16770560'); 76INSERT INTO t1 VALUES ( '67', '21', '412.0000000000', '412.0000000000', 'Niiske', '9869055', '16777215'); 77INSERT INTO t1 VALUES ( '68', '21', '413.0000000000', '413.0000000000', 'M�rg', '5263615', '16777215'); 78INSERT INTO t1 VALUES ( '69', '21', '113.0000000000', '113.0000000000', 'M�rg', '5263615', '16777215'); 79INSERT INTO t1 VALUES ( '70', '21', '416.0000000000', '416.0000000000', 'Lumine!', '16711680', '11842740'); 80INSERT INTO t1 VALUES ( '71', '38', '0.0000000000', '499.0000000000', '', NULL, '16711680'); 81INSERT INTO t1 VALUES ( '72', '22', '-49.0000000000', '49.0000000000', '', NULL, '16777215'); 82INSERT INTO t1 VALUES ( '73', '13', '0.0000000000', '9.9000000000', '', NULL, '16777215'); 83INSERT INTO t1 VALUES ( '74', '13', '10.0000000000', '14.9000000000', '', NULL, '16770560'); 84INSERT INTO t1 VALUES ( '75', '7', '0.0000000000', '50.0000000000', '', NULL, '16777215'); 85INSERT INTO t1 VALUES ( '76', '18', '0.0000000000', '0.0000000000', '', NULL, '16777215'); 86INSERT INTO t1 VALUES ( '77', '18', '0.1000000000', '10.0000000000', '', NULL, '16770560'); 87INSERT INTO t1 VALUES ( '78', '19', '300.0000000000', '400.0000000000', '', NULL, '16777215'); 88INSERT INTO t1 VALUES ( '79', '19', '0.0000000000', '299.0000000000', '', NULL, '16770560'); 89INSERT INTO t1 VALUES ( '80', '23', '0.0000000000', '100.0000000000', '', NULL, '16777215'); 90INSERT INTO t1 VALUES ( '81', '24', '0.0000000000', '200.0000000000', '', NULL, '16777215'); 91INSERT INTO t1 VALUES ( '82', '26', '0.0000000000', '0.0000000000', '', NULL, '16777215'); 92INSERT INTO t1 VALUES ( '83', '26', '0.1000000000', '5.0000000000', '', NULL, '16776960'); 93INSERT INTO t1 VALUES ( '84', '21', '422.0000000000', '422.0000000000', 'Niiske', '9869055', '16777215'); 94INSERT INTO t1 VALUES ( '85', '21', '411.0000000000', '411.0000000000', 'Saju hoiat.,kuiv!', '16777215', '13158600'); 95INSERT INTO t1 VALUES ( '86', '21', '423.0000000000', '423.0000000000', 'M�rg', '5263615', '16777215'); 96INSERT INTO t1 VALUES ( '144', '16', '-49.0000000000', '-5.0000000000', '', NULL, '16777215'); 97INSERT INTO t1 VALUES ( '88', '16', '2.0000000000', '49.0000000000', '', NULL, '16777215'); 98INSERT INTO t1 VALUES ( '89', '21', '338.0000000000', '338.0000000000', 'H�rm.hoiatus, N+S!', '16744319', '13158600'); 99INSERT INTO t1 VALUES ( '90', '21', '332.0000000000', '332.0000000000', 'H�rm.hoiat., niiske!', '16744319', '13158600'); 100INSERT INTO t1 VALUES ( '91', '21', '114.0000000000', '114.0000000000', 'Hoiatus, M+S!', '255', '13158600'); 101INSERT INTO t1 VALUES ( '92', '21', '117.0000000000', '117.0000000000', 'Hoiatus, J��!', '14448840', '16711680'); 102INSERT INTO t1 VALUES ( '93', '21', '116.0000000000', '116.0000000000', 'Lumine!', '16711680', '11842740'); 103INSERT INTO t1 VALUES ( '94', '21', '414.0000000000', '414.0000000000', 'Hoiatus, M+S!', '255', '13158600'); 104INSERT INTO t1 VALUES ( '95', '21', '325.0000000000', '325.0000000000', 'H�rmatis!', '14448840', '11842740'); 105INSERT INTO t1 VALUES ( '96', '21', '321.0000000000', '321.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600'); 106INSERT INTO t1 VALUES ( '97', '21', '328.0000000000', '328.0000000000', 'H�rm.hoiatus, N+S!', '16744319', '13158600'); 107INSERT INTO t1 VALUES ( '98', '21', '28.0000000000', '28.0000000000', 'Niiske ja sool', '9869055', '16777215'); 108INSERT INTO t1 VALUES ( '99', '21', '118.0000000000', '118.0000000000', 'Hoiatus, N+S!', '9869055', '13158600'); 109INSERT INTO t1 VALUES ( '100', '21', '418.0000000000', '418.0000000000', 'Hoiatus, N+S!', '9869055', '13158600'); 110INSERT INTO t1 VALUES ( '101', '21', '322.0000000000', '322.0000000000', 'H�rm.hoiat., niiske!', '16744319', '13158600'); 111INSERT INTO t1 VALUES ( '102', '21', '428.0000000000', '428.0000000000', 'Hoiatus, N+S!', '9869055', '13158600'); 112INSERT INTO t1 VALUES ( '103', '21', '432.0000000000', '432.0000000000', 'Hoiatus, niiske!', '7895240', '13158600'); 113INSERT INTO t1 VALUES ( '104', '21', '421.0000000000', '421.0000000000', 'Saju hoiat.,kuiv!', '16777215', '13158600'); 114INSERT INTO t1 VALUES ( '105', '21', '24.0000000000', '24.0000000000', 'M�rg ja sool', '255', '16777215'); 115INSERT INTO t1 VALUES ( '106', '21', '438.0000000000', '438.0000000000', 'Hoiatus, N+S!', '9869055', '13158600'); 116INSERT INTO t1 VALUES ( '107', '21', '112.0000000000', '112.0000000000', 'Hoiatus, niiske!', '9869055', '13158600'); 117INSERT INTO t1 VALUES ( '108', '21', '34.0000000000', '34.0000000000', 'M�rg ja sool', '255', '16777215'); 118INSERT INTO t1 VALUES ( '109', '21', '434.0000000000', '434.0000000000', 'Hoiatus, M+S!', '255', '13158600'); 119INSERT INTO t1 VALUES ( '110', '21', '124.0000000000', '124.0000000000', 'Hoiatus, M+S!', '255', '13158600'); 120INSERT INTO t1 VALUES ( '111', '21', '424.0000000000', '424.0000000000', 'Hoiatus, M+S!', '255', '13158600'); 121INSERT INTO t1 VALUES ( '112', '21', '123.0000000000', '123.0000000000', 'Hoiatus, m�rg!', '5263615', '13158600'); 122INSERT INTO t1 VALUES ( '140', '15', '-49.0000000000', '-5.0000000000', '', '0', '16777215'); 123INSERT INTO t1 VALUES ( '114', '21', '18.0000000000', '18.0000000000', 'Niiske ja sool', '9869055', '16777215'); 124INSERT INTO t1 VALUES ( '115', '21', '122.0000000000', '122.0000000000', 'Hoiatus, niiske!', '9869055', '13158600'); 125INSERT INTO t1 VALUES ( '116', '21', '14.0000000000', '14.0000000000', 'M�rg ja sool', '255', '16777215'); 126INSERT INTO t1 VALUES ( '117', '21', '311.0000000000', '311.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600'); 127INSERT INTO t1 VALUES ( '121', '2', '95.0000000000', '100.0000000000', '', NULL, '15774720'); 128INSERT INTO t1 VALUES ( '118', '2', '0.0000000000', '89.9000000000', '', NULL, '16777215'); 129INSERT INTO t1 VALUES ( '119', '21', '16.0000000000', '16.0000000000', 'Lumine!', '16711680', '11842740'); 130INSERT INTO t1 VALUES ( '120', '21', '26.0000000000', '26.0000000000', 'Lumine!', '16711680', '11842740'); 131INSERT INTO t1 VALUES ( '122', '13', '15.0000000000', '50.0000000000', '', NULL, '15774720'); 132INSERT INTO t1 VALUES ( '123', '5', '0.0000000000', '9.9000000000', '', NULL, '16777215'); 133INSERT INTO t1 VALUES ( '124', '5', '10.0000000000', '14.9000000000', '', NULL, '16770560'); 134INSERT INTO t1 VALUES ( '125', '5', '15.0000000000', '50.0000000000', '', NULL, '15774720'); 135INSERT INTO t1 VALUES ( '126', '21', '128.0000000000', '128.0000000000', 'Hoiatus, N+S!', '9869055', '13158600'); 136INSERT INTO t1 VALUES ( '127', '21', '318.0000000000', '318.0000000000', 'H�rm.hoiatus, N+S!', '16744319', '13158600'); 137INSERT INTO t1 VALUES ( '128', '21', '312.0000000000', '312.0000000000', 'H�rm.hoiat., niiske!', '16744319', '13158600'); 138INSERT INTO t1 VALUES ( '129', '21', '126.0000000000', '126.0000000000', 'Lumine!', '16711680', '11842740'); 139INSERT INTO t1 VALUES ( '130', '21', '324.0000000000', '324.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600'); 140INSERT INTO t1 VALUES ( '131', '21', '316.0000000000', '316.0000000000', 'Lumine!', '16711680', '11842740'); 141INSERT INTO t1 VALUES ( '132', '1', '0.0000000000', '1.9000000000', '', NULL, '16769024'); 142INSERT INTO t1 VALUES ( '134', '3', '-50.0000000000', '50.0000000000', '', NULL, '16777215'); 143INSERT INTO t1 VALUES ( '135', '8', '26.0000000000', '2000.0000000000', '', '9868950', '15774720'); 144INSERT INTO t1 VALUES ( '136', '21', '426.0000000000', '426.0000000000', 'Lumine!', '16711680', '11842740'); 145INSERT INTO t1 VALUES ( '137', '21', '127.0000000000', '127.0000000000', 'Hoiatus, J��!', '14448840', '16711680'); 146INSERT INTO t1 VALUES ( '138', '21', '121.0000000000', '121.0000000000', 'Kuiv', '13158600', '16777215'); 147INSERT INTO t1 VALUES ( '139', '21', '326.0000000000', '326.0000000000', 'Lumine!', '16711680', '11842740'); 148INSERT INTO t1 VALUES ( '143', '16', '-4.9000000000', '-0.1000000000', '', NULL, '15774720'); 149INSERT INTO t1 VALUES ( '145', '15', '0.0000000000', '1.9000000000', '', '0', '16769024'); 150INSERT INTO t1 VALUES ( '146', '16', '0.0000000000', '1.9000000000', '', '0', '16769024'); 151select * from t1 where min_value<=1 and max_value>=-1 and datatype_id=16; 152select * from t1 where min_value<=-1 and max_value>=-1 and datatype_id=16; 153drop table t1; 154 155# 156# Test of correct handling leading zero and +/- signs 157# then values are passed as strings 158# Also test overflow handling in this case 159# 160 161create table t1 (a decimal(10,2)); 162insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); 163insert into t1 values ("-.1"),("+.1"),(".1"); 164insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); 165insert ignore into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); 166insert ignore into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); 167insert ignore into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); 168insert ignore into t1 values ("1e+4294967296"),("1e-4294967296"); 169insert ignore into t1 values ("1e+18446744073709551615"),("1e+18446744073709551616"),("1e-9223372036854775807"),("1e-9223372036854775809"); 170insert ignore into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); 171select * from t1; 172drop table t1; 173 174create table t1 (a decimal(10,2) unsigned); 175insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); 176insert ignore into t1 values ("-.1"),("+.1"),(".1"); 177insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); 178insert ignore into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); 179insert ignore into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); 180insert ignore into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); 181insert ignore into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); 182select * from t1; 183drop table t1; 184 185create table t1 (a decimal(10,2) zerofill); 186insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); 187insert ignore into t1 values ("-.1"),("+.1"),(".1"); 188insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); 189insert ignore into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); 190insert ignore into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); 191insert ignore into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); 192insert ignore into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); 193select * from t1; 194drop table t1; 195 196 197create table t1 (a decimal(10,2)); 198# The -0.0 needs to be quoted as not all platforms supports this 199insert into t1 values (0.0),("-0.0"),(+0.0),(01.0),(+01.0),(-01.0); 200insert into t1 values (-.1),(+.1),(.1); 201insert into t1 values (00000000000001),(+0000000000001),(-0000000000001); 202insert ignore into t1 values (+111111111.11),(111111111.11),(-11111111.11); 203insert ignore into t1 values (-111111111.11),(+1111111111.11),(1111111111.11); 204insert ignore into t1 values (1e+100),(1e-100),(-1e+100); 205insert into t1 values (123.4e0),(123.4e+2),(123.4e-2),(123e1),(123e+0); 206insert into t1 values (MID("987",1,2)),("987 "),("987.6e+2 "); 207select * from t1; 208drop table t1; 209 210# 211# Test correct handling of overflowed decimal values 212# 213 214create table t1 (a decimal); 215insert ignore into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+12345678901'),(99999999999999); 216select * from t1; 217drop table t1; 218create table t1 (a decimal unsigned); 219insert ignore into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); 220select * from t1; 221drop table t1; 222create table t1 (a decimal zerofill); 223insert ignore into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); 224select * from t1; 225drop table t1; 226create table t1 (a decimal unsigned zerofill); 227insert ignore into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); 228select * from t1; 229drop table t1; 230 231# Exponent overflow bug 232create table t1(a decimal(10,0)); 233insert ignore into t1 values ("1e4294967295"); 234select * from t1; 235delete from t1; 236insert ignore into t1 values("1e4294967297"); 237select * from t1; 238drop table t1; 239 240# 241# Test of wrong decimal type 242# 243 244--error 1064 245CREATE TABLE t1 (a_dec DECIMAL(-1,0)); 246--error 1064 247CREATE TABLE t1 (a_dec DECIMAL(-2,1)); 248--error 1064 249CREATE TABLE t1 (a_dec DECIMAL(-1,1)); 250--error 1427 251CREATE TABLE t1 (a_dec DECIMAL(0,11)); 252 253# 254# Zero prepend overflow bug 255# 256--disable_warnings 257create table t1(a decimal(7,3)); 258insert into t1 values ('1'),('+1'),('-1'),('0000000001'),('+0000000001'),('-0000000001'),('10'),('+10'),('-10'),('0000000010'),('+0000000010'),('-0000000010'),('100'),('+100'),('-100'),('0000000100'),('+0000000100'),('-0000000100'),('1000'),('+1000'),('-1000'),('0000001000'),('+0000001000'),('-0000001000'),('10000'),('+10000'),('-10000'),('0000010000'),('+0000010000'),('-0000010000'),('100000'),('+100000'),('-100000'),('0000100000'),('+0000100000'),('-0000100000'),('1000000'),('+1000000'),('-1000000'),('0001000000'),('+0001000000'),('-0001000000'),('10000000'),('+10000000'),('-10000000'),('0010000000'),('+0010000000'),('-0010000000'),('100000000'),('+100000000'),('-100000000'),('0100000000'),('+0100000000'),('-0100000000'),('1000000000'),('+1000000000'),('-1000000000'),('1000000000'),('+1000000000'),('-1000000000'); 259select * from t1; 260drop table t1; 261create table t1(a decimal(7,3) unsigned); 262insert into t1 values ('1'),('+1'),('-1'),('0000000001'),('+0000000001'),('-0000000001'),('10'),('+10'),('-10'),('0000000010'),('+0000000010'),('-0000000010'),('100'),('+100'),('-100'),('0000000100'),('+0000000100'),('-0000000100'),('1000'),('+1000'),('-1000'),('0000001000'),('+0000001000'),('-0000001000'),('10000'),('+10000'),('-10000'),('0000010000'),('+0000010000'),('-0000010000'),('100000'),('+100000'),('-100000'),('0000100000'),('+0000100000'),('-0000100000'),('1000000'),('+1000000'),('-1000000'),('0001000000'),('+0001000000'),('-0001000000'),('10000000'),('+10000000'),('-10000000'),('0010000000'),('+0010000000'),('-0010000000'),('100000000'),('+100000000'),('-100000000'),('0100000000'),('+0100000000'),('-0100000000'),('1000000000'),('+1000000000'),('-1000000000'),('1000000000'),('+1000000000'),('-1000000000'); 263select * from t1; 264drop table t1; 265create table t1(a decimal(7,3) zerofill); 266insert into t1 values ('1'),('+1'),('-1'),('0000000001'),('+0000000001'),('-0000000001'),('10'),('+10'),('-10'),('0000000010'),('+0000000010'),('-0000000010'),('100'),('+100'),('-100'),('0000000100'),('+0000000100'),('-0000000100'),('1000'),('+1000'),('-1000'),('0000001000'),('+0000001000'),('-0000001000'),('10000'),('+10000'),('-10000'),('0000010000'),('+0000010000'),('-0000010000'),('100000'),('+100000'),('-100000'),('0000100000'),('+0000100000'),('-0000100000'),('1000000'),('+1000000'),('-1000000'),('0001000000'),('+0001000000'),('-0001000000'),('10000000'),('+10000000'),('-10000000'),('0010000000'),('+0010000000'),('-0010000000'),('100000000'),('+100000000'),('-100000000'),('0100000000'),('+0100000000'),('-0100000000'),('1000000000'),('+1000000000'),('-1000000000'),('1000000000'),('+1000000000'),('-1000000000'); 267--enable_warnings 268select * from t1; 269drop table t1; 270 271# 272# Bug #7589: a problem with update from column 273# 274 275create table t1(a decimal(10,5), b decimal(10,1)); 276insert into t1 values(123.12345, 123.12345); 277update t1 set b=a; 278select * from t1; 279drop table t1; 280 281--echo End of 4.1 tests 282 283# 284# Test for BUG#8397: decimal type in subselects (Item_cache_decimal) 285# 286CREATE TABLE t1 287(EMPNUM CHAR(3) NOT NULL, 288HOURS DECIMAL(5)); 289CREATE TABLE t2 290(EMPNUM CHAR(3) NOT NULL, 291HOURS BIGINT); 292 293INSERT INTO t1 VALUES ('E1',40); 294INSERT INTO t1 VALUES ('E8',NULL); 295INSERT INTO t2 VALUES ('E1',40); 296 297SELECT EMPNUM FROM t1 WHERE HOURS IN (SELECT HOURS FROM t2); 298SELECT EMPNUM FROM t1 WHERE HOURS IN (SELECT HOURS FROM t1); 299 300DROP TABLE t1,t2; 301 302# 303# Test limits of decimal 304# 305create table t1 (d decimal(64,0)); 306insert into t1 values (1); 307select * from t1; 308drop table t1; 309create table t1 (d decimal(5)); 310show create table t1; 311drop table t1; 312create table t1 (d decimal); 313show create table t1; 314drop table t1; 315--error 1426 316create table t1 (d decimal(66,0)); 317 318# 319# Test example from manual 320# 321 322CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2)); 323INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00), 324(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40), 325(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00), 326(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00), 327(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20), 328(6, 0.00, 0.00), (6, -51.40, 0.00); 329 330SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 GROUP BY i HAVING a <> b; 331SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i 332HAVING a <> b; 333drop table t1; 334 335# 336# A test case for Bug#4956 "strange result, insert into longtext, parameter 337# with numeric value": ensure that conversion is done identically no matter 338# where the input data comes from. 339# 340create table t1 (c1 varchar(100), c2 longtext); 341insert into t1 set c1= 'non PS, 1.0 as constant', c2=1.0; 342prepare stmt from "insert into t1 set c1='PS, 1.0 as constant ', c2=1.0"; 343execute stmt; 344set @a=1.0; 345insert into t1 set c1='non PS, 1.0 in parameter', c2=@a; 346prepare stmt from "insert into t1 set c1='PS, 1.0 in parameter ', c2=?"; 347execute stmt using @a; 348select * from t1; 349deallocate prepare stmt; 350drop table t1; 351 352# 353# A test case for Bug#5673 "Rounding problem in 4.0.21 inserting decimal 354# value into a char field": this is a regression bug in 4.0 tree caused by 355# a fix for some other decimal conversion issue. The patch never was 356# approved to get into 4.0 (maybe because it was considered too intrusive) 357# 358 359create table t1 ( 360 strippedproductid char(15) not null default '', 361 zlevelprice decimal(10,2) default null, 362 primary key (strippedproductid) 363); 364 365create table t2 ( 366 productid char(15) not null default '', 367 zlevelprice char(21) default null, 368 primary key (productid) 369); 370 371insert into t1 values ('002trans','49.99'); 372insert into t1 values ('003trans','39.98'); 373insert into t1 values ('004trans','31.18'); 374 375insert INTO t2 SELECT * FROM t1; 376 377select * from t2; 378drop table t1, t2; 379 380# 381# Bug #17826 'type_decimal' fails with ps-protocol 382# 383create table t1 (f1 decimal(5)); 384insert into t1 values (40); 385flush tables; 386select f1 from t1 where f1 in (select f1 from t1); 387drop table t1; 388 389# 390# Bug#22183: Unhandled NULL caused server crash 391# 392create table t1 as 393 select from_days(s) as date,t 394 from (select 1 as s,'t' as t union select null, null ) as sub1; 395select group_concat(t) from t1 group by week(date)/10; 396drop table t1; 397 398# 399# Bug#28980: ROUND(<dec expr>, <int col>) returned double values 400# 401 402CREATE TABLE t1 ( 403 qty decimal(16,6) default NULL, 404 dps tinyint(3) unsigned default NULL 405); 406INSERT INTO t1 VALUES (1.1325,3); 407 408SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1; 409 410DROP TABLE t1; 411 412# 413# Bug #24541: "Data truncated..." on decimal type columns without any good reason 414# 415 416create table t1 (c1 decimal(10,6)); 417insert into t1 (c1) values (9.99e-4); 418insert into t1 (c1) values (9.98e-4); 419insert into t1 (c1) values (0.000999); 420insert into t1 (c1) values (cast(9.99e-4 as decimal(10,6))); 421select * from t1; 422drop table t1; 423 424# 425# Bug#31019: MOD() function and operator crashes MySQL when 426# divisor is very long and < 1 427# 428 429SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%'; 430SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()'; 431 432# Bug #31227: memory overrun with decimal (6,6) and zerofill and group_concat 433# valgrind will complain about this (the group_concat(f2)) on unpatched mysqld. 434# 435create table t1 (f1 decimal(6,6),f2 decimal(6,6) zerofill); 436insert into t1 values (-0.123456,0.123456); 437select group_concat(f1),group_concat(f2) from t1; 438drop table t1; 439 440# 441# BUG#31450 "Query causes error 1048" 442# 443create table t1 ( 444 ua_id decimal(22,0) not null, 445 ua_invited_by_id decimal(22,0) default NULL, 446 primary key(ua_id) 447); 448insert into t1 values (123, NULL), (456, NULL); 449 450--echo this must not produce error 1048: 451select * from t1 where ua_invited_by_id not in (select ua_id from t1); 452 453drop table t1; 454 455# 456# Bug #30889: filesort and order by with float/numeric crashes server 457# 458--disable_warnings 459DROP TABLE IF EXISTS t3; 460DROP TABLE IF EXISTS t4; 461--enable_warnings 462CREATE TABLE t1( a NUMERIC, b INT ); 463INSERT INTO t1 VALUES (123456, 40), (123456, 40); 464SELECT TRUNCATE( a, b ) AS c FROM t1 ORDER BY c; 465SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c; 466SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c; 467 468CREATE TABLE t2( a NUMERIC, b INT ); 469INSERT INTO t2 VALUES (123456, 100); 470SELECT TRUNCATE( a, b ) AS c FROM t2 ORDER BY c; 471SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c; 472 473CREATE TABLE t3( a DECIMAL, b INT ); 474INSERT INTO t3 VALUES (123456, 40), (123456, 40); 475SELECT TRUNCATE( a, b ) AS c FROM t3 ORDER BY c; 476SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c; 477SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c; 478 479CREATE TABLE t4( a DECIMAL, b INT ); 480INSERT INTO t4 VALUES (123456, 40), (123456, 40); 481SELECT TRUNCATE( a, b ) AS c FROM t4 ORDER BY c; 482SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c; 483SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c; 484 485delete from t1; 486INSERT INTO t1 VALUES (1234567890, 20), (999.99, 5); 487show create table t1; 488 489select round(a,b) as c from t1 order by c; 490 491DROP TABLE t1, t2, t3, t4; 492 493# 494# Bug #33143: Incorrect ORDER BY for ROUND()/TRUNCATE() result 495# 496 497CREATE TABLE t1( a DECIMAL(4, 3), b INT ); 498INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 4 ), ( 3, 3 ), ( 4, 2 ), ( 5, 1 ); 499SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c; 500SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC; 501 502CREATE TABLE t2 ( a INT, b INT, c DECIMAL(5, 4) ); 503 504INSERT INTO t2 VALUES ( 0, 1, 1.2345 ), ( 1, 2, 1.2345 ), 505 ( 3, 3, 1.2345 ), ( 2, 4, 1.2345 ); 506 507SELECT a, b, MAX(ROUND(c, a)) 508FROM t2 509GROUP BY a, b 510ORDER BY b; 511 512SELECT a, b, ROUND(c, a) 513FROM t2; 514 515CREATE TABLE t3( a INT, b DECIMAL(6, 3) ); 516INSERT INTO t3 VALUES( 0, 1.5 ); 517SELECT ROUND( b, a ) FROM t3; 518 519CREATE TABLE t4( a INT, b DECIMAL( 12, 0) ); 520INSERT INTO t4 VALUES( -9, 1.5e9 ); 521SELECT ROUND( b, a ) FROM t4; 522 523CREATE TABLE t5( a INT, b DECIMAL( 13, 12 ) ); 524INSERT INTO t5 VALUES( 0, 1.5 ); 525INSERT INTO t5 VALUES( 9, 1.5e-9 ); 526SELECT ROUND( b, a ) FROM t5; 527 528CREATE TABLE t6( a INT ); 529INSERT INTO t6 VALUES( 6 / 8 ); 530SELECT * FROM t6; 531 532SELECT ROUND(20061108085411.000002); 533 534DROP TABLE t1, t2, t3, t4, t5, t6; 535 536# 537# Bug#36023: Incorrect handling of zero length caused an assertion to fail. 538# 539create table t1(`c` decimal(9,2)); 540insert into t1 values (300),(201.11); 541select max(case 1 when 1 then c else null end) from t1 group by c; 542drop table t1; 543 544--echo End of 5.0 tests 545 546# 547# Bug#52168 decimal casting catastrophes: 548# crashes and valgrind errors on simple casts 549# 550 551# Uninitialized read when calling Item_cache_decimal::val_int() 552CREATE TABLE t1 (a INTEGER); 553INSERT INTO t1 VALUES (NULL); 554CREATE TABLE t2 (b INTEGER); 555INSERT INTO t2 VALUES (NULL), (NULL); 556SELECT b FROM t1 JOIN t2 WHERE CONVERT(a, DECIMAL)|CONVERT(b, DECIMAL); 557DROP TABLE t1, t2; 558 559# 560# Bug#52122 crash when converting derived table column to decimal 561# 562CREATE TABLE t1 (col0 INTEGER, col1 REAL); 563CREATE TABLE t2 (col0 INTEGER); 564INSERT INTO t1 VALUES (0, 0.0), (NULL, NULL); 565INSERT INTO t2 VALUES (1); 566 567# Uninitialized read when calling Item_cache_decimal::val_real() 568SELECT 1 FROM t1 569JOIN 570( 571 SELECT t2.col0 FROM t2 RIGHT JOIN t1 USING(col0) 572 GROUP BY t2.col0 573) AS subq 574WHERE t1.col1 + CAST(subq.col0 AS DECIMAL); 575 576# Uninitialized read when calling Item_cache_decimal::val_str() 577SELECT 1 FROM t1 578JOIN 579( 580 SELECT t2.col0 FROM t2 RIGHT JOIN t1 USING(col0) 581 GROUP BY t2.col0 582) AS subq 583WHERE CONCAT(t1.col1, CAST(subq.col0 AS DECIMAL)); 584 585DROP TABLE t1, t2; 586 587 588--echo # 589--echo # Start of 5.5 tests 590--echo # 591 592--echo # 593--echo # MDEV-8267 Add /*old*/ comment into I_S.COLUMN_TYPE for old DECIMAL 594--echo # 595 596let $MYSQLD_DATADIR= `select @@datadir`; 597 598--copy_file std_data/old_decimal/t1dec102.frm $MYSQLD_DATADIR/test/t1dec102.frm 599--copy_file std_data/old_decimal/t1dec102.MYD $MYSQLD_DATADIR/test/t1dec102.MYD 600--copy_file std_data/old_decimal/t1dec102.MYI $MYSQLD_DATADIR/test/t1dec102.MYI 601 602SHOW CREATE TABLE t1dec102; 603SHOW COLUMNS FROM t1dec102; 604SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='t1dec102'; 605DROP TABLE t1dec102; 606 607# 608# MDEV-10552 equality operation on cast of the value "-0.0" to decimal not working 609# 610select cast('-0.0' as decimal(5,1)) < 0; 611 612--echo # 613--echo # End of 5.5 tests 614--echo # 615 616 617--echo # 618--echo # Start of 10.1 tests 619--echo # 620 621--echo # 622--echo # MDEV-18968 Both (WHERE 0.1) and (WHERE NOT 0.1) return empty set 623--echo # 624 625CREATE TABLE t1 (a INT); 626INSERT INTO t1 VALUES (10); 627SELECT CASE WHEN 0.1 THEN 'TRUE' ELSE 'FALSE' END FROM t1; 628SELECT * FROM t1 WHERE 0.1; 629SELECT * FROM t1 WHERE NOT 0.1; 630DROP TABLE t1; 631 632 633--echo # 634--echo # End of 10.1 tests 635--echo # 636 637--echo # 638--echo # Start of 10.2 tests 639--echo # 640 641--echo # 642--echo # MDEV-15420 Wrong result for CAST from TIME or DATETIME with zero integer part and non-zero microseconds to DECIMAL(X,Y) 643--echo # 644 645SET sql_mode=''; # Allow zero date 646SELECT 647 CAST(TIMESTAMP'0000-00-00 00:00:00.123456' AS DECIMAL(10,6)) AS c1, 648 CAST(TIME'00:00:00.123456' AS DECIMAL(10,6)) AS c2; 649SET sql_mode=DEFAULT; 650 651--echo # 652--echo # MDEV-12574 MAX(old_decimal) produces a column of the old DECIMAL type 653--echo # 654 655let $MYSQLD_DATADIR= `select @@datadir`; 656--copy_file std_data/old_decimal/t1dec102.frm $MYSQLD_DATADIR/test/t1dec102.frm 657--copy_file std_data/old_decimal/t1dec102.MYD $MYSQLD_DATADIR/test/t1dec102.MYD 658--copy_file std_data/old_decimal/t1dec102.MYI $MYSQLD_DATADIR/test/t1dec102.MYI 659 660SHOW CREATE TABLE t1dec102; 661 662CREATE TABLE t1 AS SELECT a, MAX(a), COALESCE(a) FROM t1dec102; 663SHOW CREATE TABLE t1; 664DROP TABLE t1; 665 666CREATE TABLE t1 AS SELECT a FROM t1dec102 UNION SELECT a FROM t1dec102; 667SHOW CREATE TABLE t1; 668DROP TABLE t1; 669 670DROP TABLE t1dec102; 671 672--echo # 673--echo # MDEV-24790 CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result 674--echo # 675 676SELECT CAST('0e111111111' AS DECIMAL(38,0)) AS a; 677SELECT CAST('0e1111111111' AS DECIMAL(38,0)) AS a; 678SELECT CAST('.00000000000000000000000000000000000001e111111111111111111111' AS DECIMAL(38,0)) AS a; 679 680CREATE TABLE t1 (str VARCHAR(128), comment VARCHAR(128)); 681INSERT INTO t1 VALUES 682('0e111111111111111111111', 'Zero mantissa and a huge positive exponent'), 683('1e111111111111111111111', 'Non-zero mantissa, huge positive exponent'), 684('0e-111111111111111111111', 'Zero mantissa and a huge negative exponent'), 685('1e-111111111111111111111', 'Non-zero mantissa and a huge negative exponent'); 686 687# The loop below issues SHOW WARNINGS manually, disable automatic warnings 688--disable_warnings 689DELIMITER $$; 690BEGIN NOT ATOMIC 691 DECLARE done INT DEFAULT FALSE; 692 DECLARE vstr, vcomment VARCHAR(128); 693 DECLARE cur1 CURSOR FOR SELECT str, comment FROM t1 ORDER BY str; 694 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 695 OPEN cur1; 696read_loop: 697 LOOP 698 FETCH cur1 INTO vstr, vcomment; 699 IF done THEN 700 LEAVE read_loop; 701 END IF; 702 SELECT vstr AS `--------`, vcomment AS `--------`; 703 SELECT CAST(str AS DECIMAL(38,0)) FROM t1 WHERE str=vstr; 704 SHOW WARNINGS; 705 SELECT CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) FROM t1 WHERE str=vstr; 706 SHOW WARNINGS; 707 END LOOP; 708END; 709$$ 710DELIMITER ;$$ 711--enable_warnings 712 713 714DROP TABLE t1; 715 716--echo # 717--echo # End of 10.2 tests 718--echo # 719 720--echo # 721--echo # Start of 10.3 tests 722--echo # 723 724--echo # 725--echo # MDEV-9217 Split Item::tmp_table_field_from_field_type() into virtual methods in Type_handler 726--echo # 727 728let $MYSQLD_DATADIR= `select @@datadir`; 729 730--copy_file std_data/old_decimal/t1dec102.frm $MYSQLD_DATADIR/test/t1dec102.frm 731--copy_file std_data/old_decimal/t1dec102.MYD $MYSQLD_DATADIR/test/t1dec102.MYD 732--copy_file std_data/old_decimal/t1dec102.MYI $MYSQLD_DATADIR/test/t1dec102.MYI 733 734--echo # This creates the old DECIMAL. Will be fixed in MDEV-12574. 735CREATE TABLE t1 AS SELECT MAX(a) FROM t1dec102; 736SHOW CREATE TABLE t1; 737DROP TABLE t1; 738 739CREATE TABLE t1 AS SELECT COALESCE(a) FROM t1dec102; 740SHOW CREATE TABLE t1; 741DROP TABLE t1; 742 743CREATE TABLE t1 (a BIGINT); 744CREATE TABLE t2 AS SELECT a FROM t1dec102 UNION SELECT a FROM t1; 745SHOW CREATE TABLE t2; 746DROP TABLE t2; 747DROP TABLE t1; 748 749CREATE TABLE t1 (a MEDIUMINT); 750CREATE TABLE t2 AS SELECT a FROM t1dec102 UNION SELECT a FROM t1; 751SHOW CREATE TABLE t2; 752DROP TABLE t2; 753DROP TABLE t1; 754 755CREATE TABLE t1 (a YEAR); 756CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1dec102; 757SHOW CREATE TABLE t2; 758DROP TABLE t2; 759DROP TABLE t1; 760 761DROP TABLE t1dec102; 762 763--echo # 764--echo # End of 10.3 tests 765--echo # 766 767 768--echo # 769--echo # Start of 10.4 tests 770--echo # 771 772--echo # 773--echo # MDEV-11362 True condition elimination does not work for DECIMAL dynamic SQL parameters 774--echo # 775 776CREATE TABLE t1 (a DECIMAL(10,1)); 777INSERT INTO t1 VALUES (1),(2),(3); 778--echo # Equal values 779EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>1.0+a; 780EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1.0,1.0; 781EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>?+a' USING 1.0; 782EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1.0+a' USING 1.0; 783--echo # Not equal values 784EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>1.1+a; 785EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1.0,1.1; 786EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>?+a' USING 1.1; 787EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1.0+a' USING 1.1; 788DROP TABLE t1; 789 790--echo # 791--echo # MDEV-16426 Optimizer erroneously treats equal constants of different formats as same 792--echo # 793 794CREATE TABLE t1 (a DECIMAL(10,3)); 795INSERT INTO t1 VALUES (10.0),(10.1); 796 797--echo Equal values 798SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.0)+a; 799EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.0)+a; 800EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.0; 801EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.0; 802 803--echo Values with different formats 804SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.00)+a; 805EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(10.00)+a; 806EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.00; 807EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(10.0)+a<=>LENGTH(?)+a' USING 10.00; 808 809DROP TABLE t1; 810 811 812--echo # 813--echo # MDEV-16984 Assertion `dec' failed in Dec_ptr::cmp 814--echo # 815SET sql_mode=''; 816CREATE TABLE t1 (dc decimal(10)); 817INSERT INTO t1 VALUES (0000000),(NULL); 818SELECT 1 FROM t1 GROUP BY 'm' <=> dc; 819DROP TABLE t1; 820SET sql_mode=DEFAULT; 821 822 823--echo # 824--echo # End of 10.4 tests 825--echo # 826