# bug in decimal() with negative numbers by kaido@tradenet.ee --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, datatype_id int(11) DEFAULT '0' NOT NULL, minvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL, maxvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL, valuename varchar(20), forecolor int(11), backcolor int(11), PRIMARY KEY (id), UNIQUE datatype_id (datatype_id, minvalue, maxvalue) ); INSERT INTO t1 VALUES ( '1', '4', '0.0000000000', '0.0000000000', 'Ei saja', '0', '16776960'); INSERT INTO t1 VALUES ( '2', '4', '1.0000000000', '1.0000000000', 'Sajab', '16777215', '255'); INSERT INTO t1 VALUES ( '3', '1', '2.0000000000', '49.0000000000', '', '0', '16777215'); INSERT INTO t1 VALUES ( '60', '11', '0.0000000000', '0.0000000000', 'Rikkis', '16777215', '16711680'); INSERT INTO t1 VALUES ( '4', '12', '1.0000000000', '1.0000000000', 'nork sadu', '65280', '14474460'); INSERT INTO t1 VALUES ( '5', '12', '2.0000000000', '2.0000000000', 'keskmine sadu', '255', '14474460'); INSERT INTO t1 VALUES ( '6', '12', '3.0000000000', '3.0000000000', 'tugev sadu', '127', '14474460'); INSERT INTO t1 VALUES ( '43', '39', '6.0000000000', '6.0000000000', 'lobjakas', '13107327', '16763080'); INSERT INTO t1 VALUES ( '40', '39', '2.0000000000', '2.0000000000', 'vihm', '8355839', '16777215'); INSERT INTO t1 VALUES ( '53', '1', '-35.0000000000', '-5.0000000000', '', '0', '16777215'); INSERT INTO t1 VALUES ( '41', '39', '3.0000000000', '3.0000000000', 'külm vihm', '120', '16763080'); INSERT INTO t1 VALUES ( '12', '21', '21.0000000000', '21.0000000000', 'Kuiv', '13158600', '16777215'); INSERT INTO t1 VALUES ( '13', '21', '13.0000000000', '13.0000000000', 'Märg', '5263615', '16777215'); INSERT INTO t1 VALUES ( '14', '21', '22.0000000000', '22.0000000000', 'Niiske', '9869055', '16777215'); INSERT INTO t1 VALUES ( '19', '21', '33.0000000000', '33.0000000000', 'Märg', '5263615', '16777215'); INSERT INTO t1 VALUES ( '15', '21', '23.0000000000', '23.0000000000', 'Märg', '5263615', '16777215'); INSERT INTO t1 VALUES ( '16', '21', '31.0000000000', '31.0000000000', 'Kuiv', '13158600', '16777215'); INSERT INTO t1 VALUES ( '17', '21', '12.0000000000', '12.0000000000', 'Niiske', '9869055', '16777215'); INSERT INTO t1 VALUES ( '18', '21', '32.0000000000', '32.0000000000', 'Niiske', '9869055', '16777215'); INSERT INTO t1 VALUES ( '20', '21', '331.0000000000', '331.0000000000', 'Härmatise hoiatus!', '14448840', '13158600'); INSERT INTO t1 VALUES ( '21', '21', '11.0000000000', '11.0000000000', 'Kuiv', '13158600', '16777215'); INSERT INTO t1 VALUES ( '22', '33', '21.0000000000', '21.0000000000', 'Pilves, kuiv', '8355711', '12632256'); INSERT INTO t1 VALUES ( '23', '33', '13.0000000000', '13.0000000000', 'Sajab, märg', '0', '8355839'); INSERT INTO t1 VALUES ( '24', '33', '22.0000000000', '22.0000000000', 'Pilves, niiske', '8355711', '12632319'); INSERT INTO t1 VALUES ( '29', '33', '33.0000000000', '33.0000000000', 'Selge, märg', '16777215', '8355839'); INSERT INTO t1 VALUES ( '25', '33', '23.0000000000', '23.0000000000', 'Pilves, märg', '8355711', '8355839'); INSERT INTO t1 VALUES ( '26', '33', '31.0000000000', '31.0000000000', 'Selge, kuiv', '16777215', '12632256'); INSERT INTO t1 VALUES ( '27', '33', '12.0000000000', '12.0000000000', 'Sajab, niiske', '0', '12632319'); INSERT INTO t1 VALUES ( '28', '33', '32.0000000000', '32.0000000000', 'Selge, niiske', '16777215', '12632319'); INSERT INTO t1 VALUES ( '30', '33', '331.0000000000', '331.0000000000', 'Härmatis! selge,kuiv', '16711680', '12632256'); INSERT INTO t1 VALUES ( '31', '33', '11.0000000000', '11.0000000000', 'Sajab, kuiv', '0', '12632256'); INSERT INTO t1 VALUES ( '32', '11', '1.0000000000', '1.0000000000', 'Korras', '16777215', '49152'); INSERT INTO t1 VALUES ( '33', '21', '335.0000000000', '335.0000000000', 'Härmatis!', '14448840', '11842740'); INSERT INTO t1 VALUES ( '34', '21', '134.0000000000', '134.0000000000', 'Hoiatus, M+S!', '255', '13158600'); INSERT INTO t1 VALUES ( '35', '21', '133.0000000000', '133.0000000000', 'Hoiatus, märg!', '5263615', '13158600'); INSERT INTO t1 VALUES ( '36', '21', '135.0000000000', '135.0000000000', 'Härmatis!', '14448840', '11842740'); INSERT INTO t1 VALUES ( '37', '21', '334.0000000000', '334.0000000000', 'Härmatise hoiatus!', '14448840', '13158600'); INSERT INTO t1 VALUES ( '38', '21', '132.0000000000', '132.0000000000', 'Hoiatus, niiske!', '9869055', '13158600'); INSERT INTO t1 VALUES ( '39', '39', '1.0000000000', '1.0000000000', 'ei saja', '11206570', '16777215'); INSERT INTO t1 VALUES ( '44', '39', '4.0000000000', '5.0000000000', 'lumi', '16711680', '16763080'); INSERT INTO t1 VALUES ( '45', '12', '0.0000000000', '0.0000000000', '', '16777215', '14474460'); INSERT INTO t1 VALUES ( '46', '39', '8.0000000000', '8.0000000000', 'rahe', '9830400', '16763080'); INSERT INTO t1 VALUES ( '47', '39', '9.0000000000', '9.0000000000', 'tüüp ebaselge', '12582912', '16777215'); INSERT INTO t1 VALUES ( '48', '39', '7.0000000000', '7.0000000000', 'lumetuisk', '7209070', '16763080'); INSERT INTO t1 VALUES ( '142', '15', '2.0000000000', '49.0000000000', '', '0', '16777215'); INSERT INTO t1 VALUES ( '52', '1', '-4.9000000000', '-0.1000000000', '', '0', '15774720'); INSERT INTO t1 VALUES ( '141', '15', '-4.9000000000', '-0.1000000000', '', '0', '15774720'); INSERT INTO t1 VALUES ( '55', '8', '0.0000000000', '0.0000000000', '', '0', '16777215'); INSERT INTO t1 VALUES ( '56', '8', '0.0100000000', '0.1000000000', '', '0', '16770560'); INSERT INTO t1 VALUES ( '57', '8', '0.1100000000', '25.0000000000', '', '0', '15774720'); INSERT INTO t1 VALUES ( '58', '2', '90.0000000000', '94.9000000000', '', NULL, '16770560'); INSERT INTO t1 VALUES ( '59', '6', '0.0000000000', '360.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '61', '21', '38.0000000000', '38.0000000000', 'Niiske', '9869055', '16777215'); INSERT INTO t1 VALUES ( '62', '38', '500.0000000000', '999.0000000000', '', '0', '16770560'); INSERT INTO t1 VALUES ( '63', '38', '1000.0000000000', '2000.0000000000', '', '0', '16777215'); INSERT INTO t1 VALUES ( '64', '17', '0.0000000000', '0.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '65', '17', '0.1000000000', '10.0000000000', '', NULL, '16770560'); INSERT INTO t1 VALUES ( '67', '21', '412.0000000000', '412.0000000000', 'Niiske', '9869055', '16777215'); INSERT INTO t1 VALUES ( '68', '21', '413.0000000000', '413.0000000000', 'Märg', '5263615', '16777215'); INSERT INTO t1 VALUES ( '69', '21', '113.0000000000', '113.0000000000', 'Märg', '5263615', '16777215'); INSERT INTO t1 VALUES ( '70', '21', '416.0000000000', '416.0000000000', 'Lumine!', '16711680', '11842740'); INSERT INTO t1 VALUES ( '71', '38', '0.0000000000', '499.0000000000', '', NULL, '16711680'); INSERT INTO t1 VALUES ( '72', '22', '-49.0000000000', '49.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '73', '13', '0.0000000000', '9.9000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '74', '13', '10.0000000000', '14.9000000000', '', NULL, '16770560'); INSERT INTO t1 VALUES ( '75', '7', '0.0000000000', '50.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '76', '18', '0.0000000000', '0.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '77', '18', '0.1000000000', '10.0000000000', '', NULL, '16770560'); INSERT INTO t1 VALUES ( '78', '19', '300.0000000000', '400.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '79', '19', '0.0000000000', '299.0000000000', '', NULL, '16770560'); INSERT INTO t1 VALUES ( '80', '23', '0.0000000000', '100.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '81', '24', '0.0000000000', '200.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '82', '26', '0.0000000000', '0.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '83', '26', '0.1000000000', '5.0000000000', '', NULL, '16776960'); INSERT INTO t1 VALUES ( '84', '21', '422.0000000000', '422.0000000000', 'Niiske', '9869055', '16777215'); INSERT INTO t1 VALUES ( '85', '21', '411.0000000000', '411.0000000000', 'Saju hoiat.,kuiv!', '16777215', '13158600'); INSERT INTO t1 VALUES ( '86', '21', '423.0000000000', '423.0000000000', 'Märg', '5263615', '16777215'); INSERT INTO t1 VALUES ( '144', '16', '-49.0000000000', '-5.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '88', '16', '2.0000000000', '49.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '89', '21', '338.0000000000', '338.0000000000', 'Härm.hoiatus, N+S!', '16744319', '13158600'); INSERT INTO t1 VALUES ( '90', '21', '332.0000000000', '332.0000000000', 'Härm.hoiat., niiske!', '16744319', '13158600'); INSERT INTO t1 VALUES ( '91', '21', '114.0000000000', '114.0000000000', 'Hoiatus, M+S!', '255', '13158600'); INSERT INTO t1 VALUES ( '92', '21', '117.0000000000', '117.0000000000', 'Hoiatus, JÄÄ!', '14448840', '16711680'); INSERT INTO t1 VALUES ( '93', '21', '116.0000000000', '116.0000000000', 'Lumine!', '16711680', '11842740'); INSERT INTO t1 VALUES ( '94', '21', '414.0000000000', '414.0000000000', 'Hoiatus, M+S!', '255', '13158600'); INSERT INTO t1 VALUES ( '95', '21', '325.0000000000', '325.0000000000', 'Härmatis!', '14448840', '11842740'); INSERT INTO t1 VALUES ( '96', '21', '321.0000000000', '321.0000000000', 'Härmatise hoiatus!', '14448840', '13158600'); INSERT INTO t1 VALUES ( '97', '21', '328.0000000000', '328.0000000000', 'Härm.hoiatus, N+S!', '16744319', '13158600'); INSERT INTO t1 VALUES ( '98', '21', '28.0000000000', '28.0000000000', 'Niiske ja sool', '9869055', '16777215'); INSERT INTO t1 VALUES ( '99', '21', '118.0000000000', '118.0000000000', 'Hoiatus, N+S!', '9869055', '13158600'); INSERT INTO t1 VALUES ( '100', '21', '418.0000000000', '418.0000000000', 'Hoiatus, N+S!', '9869055', '13158600'); INSERT INTO t1 VALUES ( '101', '21', '322.0000000000', '322.0000000000', 'Härm.hoiat., niiske!', '16744319', '13158600'); INSERT INTO t1 VALUES ( '102', '21', '428.0000000000', '428.0000000000', 'Hoiatus, N+S!', '9869055', '13158600'); INSERT INTO t1 VALUES ( '103', '21', '432.0000000000', '432.0000000000', 'Hoiatus, niiske!', '7895240', '13158600'); INSERT INTO t1 VALUES ( '104', '21', '421.0000000000', '421.0000000000', 'Saju hoiat.,kuiv!', '16777215', '13158600'); INSERT INTO t1 VALUES ( '105', '21', '24.0000000000', '24.0000000000', 'Märg ja sool', '255', '16777215'); INSERT INTO t1 VALUES ( '106', '21', '438.0000000000', '438.0000000000', 'Hoiatus, N+S!', '9869055', '13158600'); INSERT INTO t1 VALUES ( '107', '21', '112.0000000000', '112.0000000000', 'Hoiatus, niiske!', '9869055', '13158600'); INSERT INTO t1 VALUES ( '108', '21', '34.0000000000', '34.0000000000', 'Märg ja sool', '255', '16777215'); INSERT INTO t1 VALUES ( '109', '21', '434.0000000000', '434.0000000000', 'Hoiatus, M+S!', '255', '13158600'); INSERT INTO t1 VALUES ( '110', '21', '124.0000000000', '124.0000000000', 'Hoiatus, M+S!', '255', '13158600'); INSERT INTO t1 VALUES ( '111', '21', '424.0000000000', '424.0000000000', 'Hoiatus, M+S!', '255', '13158600'); INSERT INTO t1 VALUES ( '112', '21', '123.0000000000', '123.0000000000', 'Hoiatus, märg!', '5263615', '13158600'); INSERT INTO t1 VALUES ( '140', '15', '-49.0000000000', '-5.0000000000', '', '0', '16777215'); INSERT INTO t1 VALUES ( '114', '21', '18.0000000000', '18.0000000000', 'Niiske ja sool', '9869055', '16777215'); INSERT INTO t1 VALUES ( '115', '21', '122.0000000000', '122.0000000000', 'Hoiatus, niiske!', '9869055', '13158600'); INSERT INTO t1 VALUES ( '116', '21', '14.0000000000', '14.0000000000', 'Märg ja sool', '255', '16777215'); INSERT INTO t1 VALUES ( '117', '21', '311.0000000000', '311.0000000000', 'Härmatise hoiatus!', '14448840', '13158600'); INSERT INTO t1 VALUES ( '121', '2', '95.0000000000', '100.0000000000', '', NULL, '15774720'); INSERT INTO t1 VALUES ( '118', '2', '0.0000000000', '89.9000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '119', '21', '16.0000000000', '16.0000000000', 'Lumine!', '16711680', '11842740'); INSERT INTO t1 VALUES ( '120', '21', '26.0000000000', '26.0000000000', 'Lumine!', '16711680', '11842740'); INSERT INTO t1 VALUES ( '122', '13', '15.0000000000', '50.0000000000', '', NULL, '15774720'); INSERT INTO t1 VALUES ( '123', '5', '0.0000000000', '9.9000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '124', '5', '10.0000000000', '14.9000000000', '', NULL, '16770560'); INSERT INTO t1 VALUES ( '125', '5', '15.0000000000', '50.0000000000', '', NULL, '15774720'); INSERT INTO t1 VALUES ( '126', '21', '128.0000000000', '128.0000000000', 'Hoiatus, N+S!', '9869055', '13158600'); INSERT INTO t1 VALUES ( '127', '21', '318.0000000000', '318.0000000000', 'Härm.hoiatus, N+S!', '16744319', '13158600'); INSERT INTO t1 VALUES ( '128', '21', '312.0000000000', '312.0000000000', 'Härm.hoiat., niiske!', '16744319', '13158600'); INSERT INTO t1 VALUES ( '129', '21', '126.0000000000', '126.0000000000', 'Lumine!', '16711680', '11842740'); INSERT INTO t1 VALUES ( '130', '21', '324.0000000000', '324.0000000000', 'Härmatise hoiatus!', '14448840', '13158600'); INSERT INTO t1 VALUES ( '131', '21', '316.0000000000', '316.0000000000', 'Lumine!', '16711680', '11842740'); INSERT INTO t1 VALUES ( '132', '1', '0.0000000000', '1.9000000000', '', NULL, '16769024'); INSERT INTO t1 VALUES ( '134', '3', '-50.0000000000', '50.0000000000', '', NULL, '16777215'); INSERT INTO t1 VALUES ( '135', '8', '26.0000000000', '2000.0000000000', '', '9868950', '15774720'); INSERT INTO t1 VALUES ( '136', '21', '426.0000000000', '426.0000000000', 'Lumine!', '16711680', '11842740'); INSERT INTO t1 VALUES ( '137', '21', '127.0000000000', '127.0000000000', 'Hoiatus, JÄÄ!', '14448840', '16711680'); INSERT INTO t1 VALUES ( '138', '21', '121.0000000000', '121.0000000000', 'Kuiv', '13158600', '16777215'); INSERT INTO t1 VALUES ( '139', '21', '326.0000000000', '326.0000000000', 'Lumine!', '16711680', '11842740'); INSERT INTO t1 VALUES ( '143', '16', '-4.9000000000', '-0.1000000000', '', NULL, '15774720'); INSERT INTO t1 VALUES ( '145', '15', '0.0000000000', '1.9000000000', '', '0', '16769024'); INSERT INTO t1 VALUES ( '146', '16', '0.0000000000', '1.9000000000', '', '0', '16769024'); select * from t1 where minvalue<=1 and maxvalue>=-1 and datatype_id=16; select * from t1 where minvalue<=-1 and maxvalue>=-1 and datatype_id=16; drop table t1; # # Test of correct handling leading zero and +/- signs # then values are passed as strings # Also test overflow handling in this case # create table t1 (a decimal(10,2)); insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; create table t1 (a decimal(10,2) unsigned); insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; create table t1 (a decimal(10,2) zerofill); insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; create table t1 (a decimal(10,2)); # The -0.0 needs to be quoted as not all platforms supports this insert into t1 values (0.0),("-0.0"),(+0.0),(01.0),(+01.0),(-01.0); insert into t1 values (-.1),(+.1),(.1); insert into t1 values (00000000000001),(+0000000000001),(-0000000000001); insert into t1 values (+111111111.11),(111111111.11),(-11111111.11); insert into t1 values (-111111111.11),(+1111111111.11),(1111111111.11); insert into t1 values (1e+100),(1e-100),(-1e+100); insert into t1 values (123.4e0),(123.4e+2),(123.4e-2),(123e1),(123e+0); select * from t1; drop table t1; # # Test correct handling of overflowed decimal values # create table t1 (a decimal); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+12345678901'),(99999999999999); select * from t1; drop table t1; create table t1 (a decimal unsigned); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); select * from t1; drop table t1; create table t1 (a decimal zerofill); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); select * from t1; drop table t1; create table t1 (a decimal unsigned zerofill); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); select * from t1; drop table t1; # Exponent overflow bug create table t1(a decimal(10,0)); insert into t1 values ("1e4294967295"); select * from t1; delete from t1; insert into t1 values("1e4294967297"); select * from t1; drop table t1; # # Test of wrong decimal type # --error 1074 CREATE TABLE t1 (a_dec DECIMAL(-1,0)); --error 1074 CREATE TABLE t1 (a_dec DECIMAL(-2,1)); --error 1074 CREATE TABLE t1 (a_dec DECIMAL(-1,1));