1-- 2-- NUMERIC 3-- 4 5CREATE TABLE num_data (id int4, val numeric(210,10)); 6CREATE TABLE num_exp_add (id1 int4, id2 int4, expected numeric(210,10)); 7CREATE TABLE num_exp_sub (id1 int4, id2 int4, expected numeric(210,10)); 8CREATE TABLE num_exp_div (id1 int4, id2 int4, expected numeric(210,10)); 9CREATE TABLE num_exp_mul (id1 int4, id2 int4, expected numeric(210,10)); 10CREATE TABLE num_exp_sqrt (id int4, expected numeric(210,10)); 11CREATE TABLE num_exp_ln (id int4, expected numeric(210,10)); 12CREATE TABLE num_exp_log10 (id int4, expected numeric(210,10)); 13CREATE TABLE num_exp_power_10_ln (id int4, expected numeric(210,10)); 14 15CREATE TABLE num_result (id1 int4, id2 int4, result numeric(210,10)); 16 17 18-- ****************************** 19-- * The following EXPECTED results are computed by bc(1) 20-- * with a scale of 200 21-- ****************************** 22 23BEGIN TRANSACTION; 24INSERT INTO num_exp_add VALUES (0,0,'0'); 25INSERT INTO num_exp_sub VALUES (0,0,'0'); 26INSERT INTO num_exp_mul VALUES (0,0,'0'); 27INSERT INTO num_exp_div VALUES (0,0,'NaN'); 28INSERT INTO num_exp_add VALUES (0,1,'0'); 29INSERT INTO num_exp_sub VALUES (0,1,'0'); 30INSERT INTO num_exp_mul VALUES (0,1,'0'); 31INSERT INTO num_exp_div VALUES (0,1,'NaN'); 32INSERT INTO num_exp_add VALUES (0,2,'-34338492.215397047'); 33INSERT INTO num_exp_sub VALUES (0,2,'34338492.215397047'); 34INSERT INTO num_exp_mul VALUES (0,2,'0'); 35INSERT INTO num_exp_div VALUES (0,2,'0'); 36INSERT INTO num_exp_add VALUES (0,3,'4.31'); 37INSERT INTO num_exp_sub VALUES (0,3,'-4.31'); 38INSERT INTO num_exp_mul VALUES (0,3,'0'); 39INSERT INTO num_exp_div VALUES (0,3,'0'); 40INSERT INTO num_exp_add VALUES (0,4,'7799461.4119'); 41INSERT INTO num_exp_sub VALUES (0,4,'-7799461.4119'); 42INSERT INTO num_exp_mul VALUES (0,4,'0'); 43INSERT INTO num_exp_div VALUES (0,4,'0'); 44INSERT INTO num_exp_add VALUES (0,5,'16397.038491'); 45INSERT INTO num_exp_sub VALUES (0,5,'-16397.038491'); 46INSERT INTO num_exp_mul VALUES (0,5,'0'); 47INSERT INTO num_exp_div VALUES (0,5,'0'); 48INSERT INTO num_exp_add VALUES (0,6,'93901.57763026'); 49INSERT INTO num_exp_sub VALUES (0,6,'-93901.57763026'); 50INSERT INTO num_exp_mul VALUES (0,6,'0'); 51INSERT INTO num_exp_div VALUES (0,6,'0'); 52INSERT INTO num_exp_add VALUES (0,7,'-83028485'); 53INSERT INTO num_exp_sub VALUES (0,7,'83028485'); 54INSERT INTO num_exp_mul VALUES (0,7,'0'); 55INSERT INTO num_exp_div VALUES (0,7,'0'); 56INSERT INTO num_exp_add VALUES (0,8,'74881'); 57INSERT INTO num_exp_sub VALUES (0,8,'-74881'); 58INSERT INTO num_exp_mul VALUES (0,8,'0'); 59INSERT INTO num_exp_div VALUES (0,8,'0'); 60INSERT INTO num_exp_add VALUES (0,9,'-24926804.045047420'); 61INSERT INTO num_exp_sub VALUES (0,9,'24926804.045047420'); 62INSERT INTO num_exp_mul VALUES (0,9,'0'); 63INSERT INTO num_exp_div VALUES (0,9,'0'); 64INSERT INTO num_exp_add VALUES (1,0,'0'); 65INSERT INTO num_exp_sub VALUES (1,0,'0'); 66INSERT INTO num_exp_mul VALUES (1,0,'0'); 67INSERT INTO num_exp_div VALUES (1,0,'NaN'); 68INSERT INTO num_exp_add VALUES (1,1,'0'); 69INSERT INTO num_exp_sub VALUES (1,1,'0'); 70INSERT INTO num_exp_mul VALUES (1,1,'0'); 71INSERT INTO num_exp_div VALUES (1,1,'NaN'); 72INSERT INTO num_exp_add VALUES (1,2,'-34338492.215397047'); 73INSERT INTO num_exp_sub VALUES (1,2,'34338492.215397047'); 74INSERT INTO num_exp_mul VALUES (1,2,'0'); 75INSERT INTO num_exp_div VALUES (1,2,'0'); 76INSERT INTO num_exp_add VALUES (1,3,'4.31'); 77INSERT INTO num_exp_sub VALUES (1,3,'-4.31'); 78INSERT INTO num_exp_mul VALUES (1,3,'0'); 79INSERT INTO num_exp_div VALUES (1,3,'0'); 80INSERT INTO num_exp_add VALUES (1,4,'7799461.4119'); 81INSERT INTO num_exp_sub VALUES (1,4,'-7799461.4119'); 82INSERT INTO num_exp_mul VALUES (1,4,'0'); 83INSERT INTO num_exp_div VALUES (1,4,'0'); 84INSERT INTO num_exp_add VALUES (1,5,'16397.038491'); 85INSERT INTO num_exp_sub VALUES (1,5,'-16397.038491'); 86INSERT INTO num_exp_mul VALUES (1,5,'0'); 87INSERT INTO num_exp_div VALUES (1,5,'0'); 88INSERT INTO num_exp_add VALUES (1,6,'93901.57763026'); 89INSERT INTO num_exp_sub VALUES (1,6,'-93901.57763026'); 90INSERT INTO num_exp_mul VALUES (1,6,'0'); 91INSERT INTO num_exp_div VALUES (1,6,'0'); 92INSERT INTO num_exp_add VALUES (1,7,'-83028485'); 93INSERT INTO num_exp_sub VALUES (1,7,'83028485'); 94INSERT INTO num_exp_mul VALUES (1,7,'0'); 95INSERT INTO num_exp_div VALUES (1,7,'0'); 96INSERT INTO num_exp_add VALUES (1,8,'74881'); 97INSERT INTO num_exp_sub VALUES (1,8,'-74881'); 98INSERT INTO num_exp_mul VALUES (1,8,'0'); 99INSERT INTO num_exp_div VALUES (1,8,'0'); 100INSERT INTO num_exp_add VALUES (1,9,'-24926804.045047420'); 101INSERT INTO num_exp_sub VALUES (1,9,'24926804.045047420'); 102INSERT INTO num_exp_mul VALUES (1,9,'0'); 103INSERT INTO num_exp_div VALUES (1,9,'0'); 104INSERT INTO num_exp_add VALUES (2,0,'-34338492.215397047'); 105INSERT INTO num_exp_sub VALUES (2,0,'-34338492.215397047'); 106INSERT INTO num_exp_mul VALUES (2,0,'0'); 107INSERT INTO num_exp_div VALUES (2,0,'NaN'); 108INSERT INTO num_exp_add VALUES (2,1,'-34338492.215397047'); 109INSERT INTO num_exp_sub VALUES (2,1,'-34338492.215397047'); 110INSERT INTO num_exp_mul VALUES (2,1,'0'); 111INSERT INTO num_exp_div VALUES (2,1,'NaN'); 112INSERT INTO num_exp_add VALUES (2,2,'-68676984.430794094'); 113INSERT INTO num_exp_sub VALUES (2,2,'0'); 114INSERT INTO num_exp_mul VALUES (2,2,'1179132047626883.596862135856320209'); 115INSERT INTO num_exp_div VALUES (2,2,'1.00000000000000000000'); 116INSERT INTO num_exp_add VALUES (2,3,'-34338487.905397047'); 117INSERT INTO num_exp_sub VALUES (2,3,'-34338496.525397047'); 118INSERT INTO num_exp_mul VALUES (2,3,'-147998901.44836127257'); 119INSERT INTO num_exp_div VALUES (2,3,'-7967167.56737750510440835266'); 120INSERT INTO num_exp_add VALUES (2,4,'-26539030.803497047'); 121INSERT INTO num_exp_sub VALUES (2,4,'-42137953.627297047'); 122INSERT INTO num_exp_mul VALUES (2,4,'-267821744976817.8111137106593'); 123INSERT INTO num_exp_div VALUES (2,4,'-4.40267480046830116685'); 124INSERT INTO num_exp_add VALUES (2,5,'-34322095.176906047'); 125INSERT INTO num_exp_sub VALUES (2,5,'-34354889.253888047'); 126INSERT INTO num_exp_mul VALUES (2,5,'-563049578578.769242506736077'); 127INSERT INTO num_exp_div VALUES (2,5,'-2094.18866914563535496429'); 128INSERT INTO num_exp_add VALUES (2,6,'-34244590.637766787'); 129INSERT INTO num_exp_sub VALUES (2,6,'-34432393.793027307'); 130INSERT INTO num_exp_mul VALUES (2,6,'-3224438592470.18449811926184222'); 131INSERT INTO num_exp_div VALUES (2,6,'-365.68599891479766440940'); 132INSERT INTO num_exp_add VALUES (2,7,'-117366977.215397047'); 133INSERT INTO num_exp_sub VALUES (2,7,'48689992.784602953'); 134INSERT INTO num_exp_mul VALUES (2,7,'2851072985828710.485883795'); 135INSERT INTO num_exp_div VALUES (2,7,'.41357483778485235518'); 136INSERT INTO num_exp_add VALUES (2,8,'-34263611.215397047'); 137INSERT INTO num_exp_sub VALUES (2,8,'-34413373.215397047'); 138INSERT INTO num_exp_mul VALUES (2,8,'-2571300635581.146276407'); 139INSERT INTO num_exp_div VALUES (2,8,'-458.57416721727870888476'); 140INSERT INTO num_exp_add VALUES (2,9,'-59265296.260444467'); 141INSERT INTO num_exp_sub VALUES (2,9,'-9411688.170349627'); 142INSERT INTO num_exp_mul VALUES (2,9,'855948866655588.453741509242968740'); 143INSERT INTO num_exp_div VALUES (2,9,'1.37757299946438931811'); 144INSERT INTO num_exp_add VALUES (3,0,'4.31'); 145INSERT INTO num_exp_sub VALUES (3,0,'4.31'); 146INSERT INTO num_exp_mul VALUES (3,0,'0'); 147INSERT INTO num_exp_div VALUES (3,0,'NaN'); 148INSERT INTO num_exp_add VALUES (3,1,'4.31'); 149INSERT INTO num_exp_sub VALUES (3,1,'4.31'); 150INSERT INTO num_exp_mul VALUES (3,1,'0'); 151INSERT INTO num_exp_div VALUES (3,1,'NaN'); 152INSERT INTO num_exp_add VALUES (3,2,'-34338487.905397047'); 153INSERT INTO num_exp_sub VALUES (3,2,'34338496.525397047'); 154INSERT INTO num_exp_mul VALUES (3,2,'-147998901.44836127257'); 155INSERT INTO num_exp_div VALUES (3,2,'-.00000012551512084352'); 156INSERT INTO num_exp_add VALUES (3,3,'8.62'); 157INSERT INTO num_exp_sub VALUES (3,3,'0'); 158INSERT INTO num_exp_mul VALUES (3,3,'18.5761'); 159INSERT INTO num_exp_div VALUES (3,3,'1.00000000000000000000'); 160INSERT INTO num_exp_add VALUES (3,4,'7799465.7219'); 161INSERT INTO num_exp_sub VALUES (3,4,'-7799457.1019'); 162INSERT INTO num_exp_mul VALUES (3,4,'33615678.685289'); 163INSERT INTO num_exp_div VALUES (3,4,'.00000055260225961552'); 164INSERT INTO num_exp_add VALUES (3,5,'16401.348491'); 165INSERT INTO num_exp_sub VALUES (3,5,'-16392.728491'); 166INSERT INTO num_exp_mul VALUES (3,5,'70671.23589621'); 167INSERT INTO num_exp_div VALUES (3,5,'.00026285234387695504'); 168INSERT INTO num_exp_add VALUES (3,6,'93905.88763026'); 169INSERT INTO num_exp_sub VALUES (3,6,'-93897.26763026'); 170INSERT INTO num_exp_mul VALUES (3,6,'404715.7995864206'); 171INSERT INTO num_exp_div VALUES (3,6,'.00004589912234457595'); 172INSERT INTO num_exp_add VALUES (3,7,'-83028480.69'); 173INSERT INTO num_exp_sub VALUES (3,7,'83028489.31'); 174INSERT INTO num_exp_mul VALUES (3,7,'-357852770.35'); 175INSERT INTO num_exp_div VALUES (3,7,'-.00000005190989574240'); 176INSERT INTO num_exp_add VALUES (3,8,'74885.31'); 177INSERT INTO num_exp_sub VALUES (3,8,'-74876.69'); 178INSERT INTO num_exp_mul VALUES (3,8,'322737.11'); 179INSERT INTO num_exp_div VALUES (3,8,'.00005755799201399553'); 180INSERT INTO num_exp_add VALUES (3,9,'-24926799.735047420'); 181INSERT INTO num_exp_sub VALUES (3,9,'24926808.355047420'); 182INSERT INTO num_exp_mul VALUES (3,9,'-107434525.43415438020'); 183INSERT INTO num_exp_div VALUES (3,9,'-.00000017290624149854'); 184INSERT INTO num_exp_add VALUES (4,0,'7799461.4119'); 185INSERT INTO num_exp_sub VALUES (4,0,'7799461.4119'); 186INSERT INTO num_exp_mul VALUES (4,0,'0'); 187INSERT INTO num_exp_div VALUES (4,0,'NaN'); 188INSERT INTO num_exp_add VALUES (4,1,'7799461.4119'); 189INSERT INTO num_exp_sub VALUES (4,1,'7799461.4119'); 190INSERT INTO num_exp_mul VALUES (4,1,'0'); 191INSERT INTO num_exp_div VALUES (4,1,'NaN'); 192INSERT INTO num_exp_add VALUES (4,2,'-26539030.803497047'); 193INSERT INTO num_exp_sub VALUES (4,2,'42137953.627297047'); 194INSERT INTO num_exp_mul VALUES (4,2,'-267821744976817.8111137106593'); 195INSERT INTO num_exp_div VALUES (4,2,'-.22713465002993920385'); 196INSERT INTO num_exp_add VALUES (4,3,'7799465.7219'); 197INSERT INTO num_exp_sub VALUES (4,3,'7799457.1019'); 198INSERT INTO num_exp_mul VALUES (4,3,'33615678.685289'); 199INSERT INTO num_exp_div VALUES (4,3,'1809619.81714617169373549883'); 200INSERT INTO num_exp_add VALUES (4,4,'15598922.8238'); 201INSERT INTO num_exp_sub VALUES (4,4,'0'); 202INSERT INTO num_exp_mul VALUES (4,4,'60831598315717.14146161'); 203INSERT INTO num_exp_div VALUES (4,4,'1.00000000000000000000'); 204INSERT INTO num_exp_add VALUES (4,5,'7815858.450391'); 205INSERT INTO num_exp_sub VALUES (4,5,'7783064.373409'); 206INSERT INTO num_exp_mul VALUES (4,5,'127888068979.9935054429'); 207INSERT INTO num_exp_div VALUES (4,5,'475.66281046305802686061'); 208INSERT INTO num_exp_add VALUES (4,6,'7893362.98953026'); 209INSERT INTO num_exp_sub VALUES (4,6,'7705559.83426974'); 210INSERT INTO num_exp_mul VALUES (4,6,'732381731243.745115764094'); 211INSERT INTO num_exp_div VALUES (4,6,'83.05996138436129499606'); 212INSERT INTO num_exp_add VALUES (4,7,'-75229023.5881'); 213INSERT INTO num_exp_sub VALUES (4,7,'90827946.4119'); 214INSERT INTO num_exp_mul VALUES (4,7,'-647577464846017.9715'); 215INSERT INTO num_exp_div VALUES (4,7,'-.09393717604145131637'); 216INSERT INTO num_exp_add VALUES (4,8,'7874342.4119'); 217INSERT INTO num_exp_sub VALUES (4,8,'7724580.4119'); 218INSERT INTO num_exp_mul VALUES (4,8,'584031469984.4839'); 219INSERT INTO num_exp_div VALUES (4,8,'104.15808298366741897143'); 220INSERT INTO num_exp_add VALUES (4,9,'-17127342.633147420'); 221INSERT INTO num_exp_sub VALUES (4,9,'32726265.456947420'); 222INSERT INTO num_exp_mul VALUES (4,9,'-194415646271340.1815956522980'); 223INSERT INTO num_exp_div VALUES (4,9,'-.31289456112403769409'); 224INSERT INTO num_exp_add VALUES (5,0,'16397.038491'); 225INSERT INTO num_exp_sub VALUES (5,0,'16397.038491'); 226INSERT INTO num_exp_mul VALUES (5,0,'0'); 227INSERT INTO num_exp_div VALUES (5,0,'NaN'); 228INSERT INTO num_exp_add VALUES (5,1,'16397.038491'); 229INSERT INTO num_exp_sub VALUES (5,1,'16397.038491'); 230INSERT INTO num_exp_mul VALUES (5,1,'0'); 231INSERT INTO num_exp_div VALUES (5,1,'NaN'); 232INSERT INTO num_exp_add VALUES (5,2,'-34322095.176906047'); 233INSERT INTO num_exp_sub VALUES (5,2,'34354889.253888047'); 234INSERT INTO num_exp_mul VALUES (5,2,'-563049578578.769242506736077'); 235INSERT INTO num_exp_div VALUES (5,2,'-.00047751189505192446'); 236INSERT INTO num_exp_add VALUES (5,3,'16401.348491'); 237INSERT INTO num_exp_sub VALUES (5,3,'16392.728491'); 238INSERT INTO num_exp_mul VALUES (5,3,'70671.23589621'); 239INSERT INTO num_exp_div VALUES (5,3,'3804.41728329466357308584'); 240INSERT INTO num_exp_add VALUES (5,4,'7815858.450391'); 241INSERT INTO num_exp_sub VALUES (5,4,'-7783064.373409'); 242INSERT INTO num_exp_mul VALUES (5,4,'127888068979.9935054429'); 243INSERT INTO num_exp_div VALUES (5,4,'.00210232958726897192'); 244INSERT INTO num_exp_add VALUES (5,5,'32794.076982'); 245INSERT INTO num_exp_sub VALUES (5,5,'0'); 246INSERT INTO num_exp_mul VALUES (5,5,'268862871.275335557081'); 247INSERT INTO num_exp_div VALUES (5,5,'1.00000000000000000000'); 248INSERT INTO num_exp_add VALUES (5,6,'110298.61612126'); 249INSERT INTO num_exp_sub VALUES (5,6,'-77504.53913926'); 250INSERT INTO num_exp_mul VALUES (5,6,'1539707782.76899778633766'); 251INSERT INTO num_exp_div VALUES (5,6,'.17461941433576102689'); 252INSERT INTO num_exp_add VALUES (5,7,'-83012087.961509'); 253INSERT INTO num_exp_sub VALUES (5,7,'83044882.038491'); 254INSERT INTO num_exp_mul VALUES (5,7,'-1361421264394.416135'); 255INSERT INTO num_exp_div VALUES (5,7,'-.00019748690453643710'); 256INSERT INTO num_exp_add VALUES (5,8,'91278.038491'); 257INSERT INTO num_exp_sub VALUES (5,8,'-58483.961509'); 258INSERT INTO num_exp_mul VALUES (5,8,'1227826639.244571'); 259INSERT INTO num_exp_div VALUES (5,8,'.21897461960978085228'); 260INSERT INTO num_exp_add VALUES (5,9,'-24910407.006556420'); 261INSERT INTO num_exp_sub VALUES (5,9,'24943201.083538420'); 262INSERT INTO num_exp_mul VALUES (5,9,'-408725765384.257043660243220'); 263INSERT INTO num_exp_div VALUES (5,9,'-.00065780749354660427'); 264INSERT INTO num_exp_add VALUES (6,0,'93901.57763026'); 265INSERT INTO num_exp_sub VALUES (6,0,'93901.57763026'); 266INSERT INTO num_exp_mul VALUES (6,0,'0'); 267INSERT INTO num_exp_div VALUES (6,0,'NaN'); 268INSERT INTO num_exp_add VALUES (6,1,'93901.57763026'); 269INSERT INTO num_exp_sub VALUES (6,1,'93901.57763026'); 270INSERT INTO num_exp_mul VALUES (6,1,'0'); 271INSERT INTO num_exp_div VALUES (6,1,'NaN'); 272INSERT INTO num_exp_add VALUES (6,2,'-34244590.637766787'); 273INSERT INTO num_exp_sub VALUES (6,2,'34432393.793027307'); 274INSERT INTO num_exp_mul VALUES (6,2,'-3224438592470.18449811926184222'); 275INSERT INTO num_exp_div VALUES (6,2,'-.00273458651128995823'); 276INSERT INTO num_exp_add VALUES (6,3,'93905.88763026'); 277INSERT INTO num_exp_sub VALUES (6,3,'93897.26763026'); 278INSERT INTO num_exp_mul VALUES (6,3,'404715.7995864206'); 279INSERT INTO num_exp_div VALUES (6,3,'21786.90896293735498839907'); 280INSERT INTO num_exp_add VALUES (6,4,'7893362.98953026'); 281INSERT INTO num_exp_sub VALUES (6,4,'-7705559.83426974'); 282INSERT INTO num_exp_mul VALUES (6,4,'732381731243.745115764094'); 283INSERT INTO num_exp_div VALUES (6,4,'.01203949512295682469'); 284INSERT INTO num_exp_add VALUES (6,5,'110298.61612126'); 285INSERT INTO num_exp_sub VALUES (6,5,'77504.53913926'); 286INSERT INTO num_exp_mul VALUES (6,5,'1539707782.76899778633766'); 287INSERT INTO num_exp_div VALUES (6,5,'5.72674008674192359679'); 288INSERT INTO num_exp_add VALUES (6,6,'187803.15526052'); 289INSERT INTO num_exp_sub VALUES (6,6,'0'); 290INSERT INTO num_exp_mul VALUES (6,6,'8817506281.4517452372676676'); 291INSERT INTO num_exp_div VALUES (6,6,'1.00000000000000000000'); 292INSERT INTO num_exp_add VALUES (6,7,'-82934583.42236974'); 293INSERT INTO num_exp_sub VALUES (6,7,'83122386.57763026'); 294INSERT INTO num_exp_mul VALUES (6,7,'-7796505729750.37795610'); 295INSERT INTO num_exp_div VALUES (6,7,'-.00113095617281538980'); 296INSERT INTO num_exp_add VALUES (6,8,'168782.57763026'); 297INSERT INTO num_exp_sub VALUES (6,8,'19020.57763026'); 298INSERT INTO num_exp_mul VALUES (6,8,'7031444034.53149906'); 299INSERT INTO num_exp_div VALUES (6,8,'1.25401073209839612184'); 300INSERT INTO num_exp_add VALUES (6,9,'-24832902.467417160'); 301INSERT INTO num_exp_sub VALUES (6,9,'25020705.622677680'); 302INSERT INTO num_exp_mul VALUES (6,9,'-2340666225110.29929521292692920'); 303INSERT INTO num_exp_div VALUES (6,9,'-.00376709254265256789'); 304INSERT INTO num_exp_add VALUES (7,0,'-83028485'); 305INSERT INTO num_exp_sub VALUES (7,0,'-83028485'); 306INSERT INTO num_exp_mul VALUES (7,0,'0'); 307INSERT INTO num_exp_div VALUES (7,0,'NaN'); 308INSERT INTO num_exp_add VALUES (7,1,'-83028485'); 309INSERT INTO num_exp_sub VALUES (7,1,'-83028485'); 310INSERT INTO num_exp_mul VALUES (7,1,'0'); 311INSERT INTO num_exp_div VALUES (7,1,'NaN'); 312INSERT INTO num_exp_add VALUES (7,2,'-117366977.215397047'); 313INSERT INTO num_exp_sub VALUES (7,2,'-48689992.784602953'); 314INSERT INTO num_exp_mul VALUES (7,2,'2851072985828710.485883795'); 315INSERT INTO num_exp_div VALUES (7,2,'2.41794207151503385700'); 316INSERT INTO num_exp_add VALUES (7,3,'-83028480.69'); 317INSERT INTO num_exp_sub VALUES (7,3,'-83028489.31'); 318INSERT INTO num_exp_mul VALUES (7,3,'-357852770.35'); 319INSERT INTO num_exp_div VALUES (7,3,'-19264149.65197215777262180974'); 320INSERT INTO num_exp_add VALUES (7,4,'-75229023.5881'); 321INSERT INTO num_exp_sub VALUES (7,4,'-90827946.4119'); 322INSERT INTO num_exp_mul VALUES (7,4,'-647577464846017.9715'); 323INSERT INTO num_exp_div VALUES (7,4,'-10.64541262725136247686'); 324INSERT INTO num_exp_add VALUES (7,5,'-83012087.961509'); 325INSERT INTO num_exp_sub VALUES (7,5,'-83044882.038491'); 326INSERT INTO num_exp_mul VALUES (7,5,'-1361421264394.416135'); 327INSERT INTO num_exp_div VALUES (7,5,'-5063.62688881730941836574'); 328INSERT INTO num_exp_add VALUES (7,6,'-82934583.42236974'); 329INSERT INTO num_exp_sub VALUES (7,6,'-83122386.57763026'); 330INSERT INTO num_exp_mul VALUES (7,6,'-7796505729750.37795610'); 331INSERT INTO num_exp_div VALUES (7,6,'-884.20756174009028770294'); 332INSERT INTO num_exp_add VALUES (7,7,'-166056970'); 333INSERT INTO num_exp_sub VALUES (7,7,'0'); 334INSERT INTO num_exp_mul VALUES (7,7,'6893729321395225'); 335INSERT INTO num_exp_div VALUES (7,7,'1.00000000000000000000'); 336INSERT INTO num_exp_add VALUES (7,8,'-82953604'); 337INSERT INTO num_exp_sub VALUES (7,8,'-83103366'); 338INSERT INTO num_exp_mul VALUES (7,8,'-6217255985285'); 339INSERT INTO num_exp_div VALUES (7,8,'-1108.80577182462841041118'); 340INSERT INTO num_exp_add VALUES (7,9,'-107955289.045047420'); 341INSERT INTO num_exp_sub VALUES (7,9,'-58101680.954952580'); 342INSERT INTO num_exp_mul VALUES (7,9,'2069634775752159.035758700'); 343INSERT INTO num_exp_div VALUES (7,9,'3.33089171198810413382'); 344INSERT INTO num_exp_add VALUES (8,0,'74881'); 345INSERT INTO num_exp_sub VALUES (8,0,'74881'); 346INSERT INTO num_exp_mul VALUES (8,0,'0'); 347INSERT INTO num_exp_div VALUES (8,0,'NaN'); 348INSERT INTO num_exp_add VALUES (8,1,'74881'); 349INSERT INTO num_exp_sub VALUES (8,1,'74881'); 350INSERT INTO num_exp_mul VALUES (8,1,'0'); 351INSERT INTO num_exp_div VALUES (8,1,'NaN'); 352INSERT INTO num_exp_add VALUES (8,2,'-34263611.215397047'); 353INSERT INTO num_exp_sub VALUES (8,2,'34413373.215397047'); 354INSERT INTO num_exp_mul VALUES (8,2,'-2571300635581.146276407'); 355INSERT INTO num_exp_div VALUES (8,2,'-.00218067233500788615'); 356INSERT INTO num_exp_add VALUES (8,3,'74885.31'); 357INSERT INTO num_exp_sub VALUES (8,3,'74876.69'); 358INSERT INTO num_exp_mul VALUES (8,3,'322737.11'); 359INSERT INTO num_exp_div VALUES (8,3,'17373.78190255220417633410'); 360INSERT INTO num_exp_add VALUES (8,4,'7874342.4119'); 361INSERT INTO num_exp_sub VALUES (8,4,'-7724580.4119'); 362INSERT INTO num_exp_mul VALUES (8,4,'584031469984.4839'); 363INSERT INTO num_exp_div VALUES (8,4,'.00960079113741758956'); 364INSERT INTO num_exp_add VALUES (8,5,'91278.038491'); 365INSERT INTO num_exp_sub VALUES (8,5,'58483.961509'); 366INSERT INTO num_exp_mul VALUES (8,5,'1227826639.244571'); 367INSERT INTO num_exp_div VALUES (8,5,'4.56673929509287019456'); 368INSERT INTO num_exp_add VALUES (8,6,'168782.57763026'); 369INSERT INTO num_exp_sub VALUES (8,6,'-19020.57763026'); 370INSERT INTO num_exp_mul VALUES (8,6,'7031444034.53149906'); 371INSERT INTO num_exp_div VALUES (8,6,'.79744134113322314424'); 372INSERT INTO num_exp_add VALUES (8,7,'-82953604'); 373INSERT INTO num_exp_sub VALUES (8,7,'83103366'); 374INSERT INTO num_exp_mul VALUES (8,7,'-6217255985285'); 375INSERT INTO num_exp_div VALUES (8,7,'-.00090187120721280172'); 376INSERT INTO num_exp_add VALUES (8,8,'149762'); 377INSERT INTO num_exp_sub VALUES (8,8,'0'); 378INSERT INTO num_exp_mul VALUES (8,8,'5607164161'); 379INSERT INTO num_exp_div VALUES (8,8,'1.00000000000000000000'); 380INSERT INTO num_exp_add VALUES (8,9,'-24851923.045047420'); 381INSERT INTO num_exp_sub VALUES (8,9,'25001685.045047420'); 382INSERT INTO num_exp_mul VALUES (8,9,'-1866544013697.195857020'); 383INSERT INTO num_exp_div VALUES (8,9,'-.00300403532938582735'); 384INSERT INTO num_exp_add VALUES (9,0,'-24926804.045047420'); 385INSERT INTO num_exp_sub VALUES (9,0,'-24926804.045047420'); 386INSERT INTO num_exp_mul VALUES (9,0,'0'); 387INSERT INTO num_exp_div VALUES (9,0,'NaN'); 388INSERT INTO num_exp_add VALUES (9,1,'-24926804.045047420'); 389INSERT INTO num_exp_sub VALUES (9,1,'-24926804.045047420'); 390INSERT INTO num_exp_mul VALUES (9,1,'0'); 391INSERT INTO num_exp_div VALUES (9,1,'NaN'); 392INSERT INTO num_exp_add VALUES (9,2,'-59265296.260444467'); 393INSERT INTO num_exp_sub VALUES (9,2,'9411688.170349627'); 394INSERT INTO num_exp_mul VALUES (9,2,'855948866655588.453741509242968740'); 395INSERT INTO num_exp_div VALUES (9,2,'.72591434384152961526'); 396INSERT INTO num_exp_add VALUES (9,3,'-24926799.735047420'); 397INSERT INTO num_exp_sub VALUES (9,3,'-24926808.355047420'); 398INSERT INTO num_exp_mul VALUES (9,3,'-107434525.43415438020'); 399INSERT INTO num_exp_div VALUES (9,3,'-5783481.21694835730858468677'); 400INSERT INTO num_exp_add VALUES (9,4,'-17127342.633147420'); 401INSERT INTO num_exp_sub VALUES (9,4,'-32726265.456947420'); 402INSERT INTO num_exp_mul VALUES (9,4,'-194415646271340.1815956522980'); 403INSERT INTO num_exp_div VALUES (9,4,'-3.19596478892958416484'); 404INSERT INTO num_exp_add VALUES (9,5,'-24910407.006556420'); 405INSERT INTO num_exp_sub VALUES (9,5,'-24943201.083538420'); 406INSERT INTO num_exp_mul VALUES (9,5,'-408725765384.257043660243220'); 407INSERT INTO num_exp_div VALUES (9,5,'-1520.20159364322004505807'); 408INSERT INTO num_exp_add VALUES (9,6,'-24832902.467417160'); 409INSERT INTO num_exp_sub VALUES (9,6,'-25020705.622677680'); 410INSERT INTO num_exp_mul VALUES (9,6,'-2340666225110.29929521292692920'); 411INSERT INTO num_exp_div VALUES (9,6,'-265.45671195426965751280'); 412INSERT INTO num_exp_add VALUES (9,7,'-107955289.045047420'); 413INSERT INTO num_exp_sub VALUES (9,7,'58101680.954952580'); 414INSERT INTO num_exp_mul VALUES (9,7,'2069634775752159.035758700'); 415INSERT INTO num_exp_div VALUES (9,7,'.30021990699995814689'); 416INSERT INTO num_exp_add VALUES (9,8,'-24851923.045047420'); 417INSERT INTO num_exp_sub VALUES (9,8,'-25001685.045047420'); 418INSERT INTO num_exp_mul VALUES (9,8,'-1866544013697.195857020'); 419INSERT INTO num_exp_div VALUES (9,8,'-332.88556569820675471748'); 420INSERT INTO num_exp_add VALUES (9,9,'-49853608.090094840'); 421INSERT INTO num_exp_sub VALUES (9,9,'0'); 422INSERT INTO num_exp_mul VALUES (9,9,'621345559900192.420120630048656400'); 423INSERT INTO num_exp_div VALUES (9,9,'1.00000000000000000000'); 424COMMIT TRANSACTION; 425BEGIN TRANSACTION; 426INSERT INTO num_exp_sqrt VALUES (0,'0'); 427INSERT INTO num_exp_sqrt VALUES (1,'0'); 428INSERT INTO num_exp_sqrt VALUES (2,'5859.90547836712524903505'); 429INSERT INTO num_exp_sqrt VALUES (3,'2.07605394920266944396'); 430INSERT INTO num_exp_sqrt VALUES (4,'2792.75158435189147418923'); 431INSERT INTO num_exp_sqrt VALUES (5,'128.05092147657509145473'); 432INSERT INTO num_exp_sqrt VALUES (6,'306.43364311096782703406'); 433INSERT INTO num_exp_sqrt VALUES (7,'9111.99676251039939975230'); 434INSERT INTO num_exp_sqrt VALUES (8,'273.64392922189960397542'); 435INSERT INTO num_exp_sqrt VALUES (9,'4992.67503899937593364766'); 436COMMIT TRANSACTION; 437BEGIN TRANSACTION; 438INSERT INTO num_exp_ln VALUES (0,'NaN'); 439INSERT INTO num_exp_ln VALUES (1,'NaN'); 440INSERT INTO num_exp_ln VALUES (2,'17.35177750493897715514'); 441INSERT INTO num_exp_ln VALUES (3,'1.46093790411565641971'); 442INSERT INTO num_exp_ln VALUES (4,'15.86956523951936572464'); 443INSERT INTO num_exp_ln VALUES (5,'9.70485601768871834038'); 444INSERT INTO num_exp_ln VALUES (6,'11.45000246622944403127'); 445INSERT INTO num_exp_ln VALUES (7,'18.23469429965478772991'); 446INSERT INTO num_exp_ln VALUES (8,'11.22365546576315513668'); 447INSERT INTO num_exp_ln VALUES (9,'17.03145425013166006962'); 448COMMIT TRANSACTION; 449BEGIN TRANSACTION; 450INSERT INTO num_exp_log10 VALUES (0,'NaN'); 451INSERT INTO num_exp_log10 VALUES (1,'NaN'); 452INSERT INTO num_exp_log10 VALUES (2,'7.53578122160797276459'); 453INSERT INTO num_exp_log10 VALUES (3,'.63447727016073160075'); 454INSERT INTO num_exp_log10 VALUES (4,'6.89206461372691743345'); 455INSERT INTO num_exp_log10 VALUES (5,'4.21476541614777768626'); 456INSERT INTO num_exp_log10 VALUES (6,'4.97267288886207207671'); 457INSERT INTO num_exp_log10 VALUES (7,'7.91922711353275546914'); 458INSERT INTO num_exp_log10 VALUES (8,'4.87437163556421004138'); 459INSERT INTO num_exp_log10 VALUES (9,'7.39666659961986567059'); 460COMMIT TRANSACTION; 461BEGIN TRANSACTION; 462INSERT INTO num_exp_power_10_ln VALUES (0,'NaN'); 463INSERT INTO num_exp_power_10_ln VALUES (1,'NaN'); 464INSERT INTO num_exp_power_10_ln VALUES (2,'224790267919917955.13261618583642653184'); 465INSERT INTO num_exp_power_10_ln VALUES (3,'28.90266599445155957393'); 466INSERT INTO num_exp_power_10_ln VALUES (4,'7405685069594999.07733999469386277636'); 467INSERT INTO num_exp_power_10_ln VALUES (5,'5068226527.32127265408584640098'); 468INSERT INTO num_exp_power_10_ln VALUES (6,'281839893606.99372343357047819067'); 469INSERT INTO num_exp_power_10_ln VALUES (7,'1716699575118597095.42330819910640247627'); 470INSERT INTO num_exp_power_10_ln VALUES (8,'167361463828.07491320069016125952'); 471INSERT INTO num_exp_power_10_ln VALUES (9,'107511333880052007.04141124673540337457'); 472COMMIT TRANSACTION; 473BEGIN TRANSACTION; 474INSERT INTO num_data VALUES (0, '0'); 475INSERT INTO num_data VALUES (1, '0'); 476INSERT INTO num_data VALUES (2, '-34338492.215397047'); 477INSERT INTO num_data VALUES (3, '4.31'); 478INSERT INTO num_data VALUES (4, '7799461.4119'); 479INSERT INTO num_data VALUES (5, '16397.038491'); 480INSERT INTO num_data VALUES (6, '93901.57763026'); 481INSERT INTO num_data VALUES (7, '-83028485'); 482INSERT INTO num_data VALUES (8, '74881'); 483INSERT INTO num_data VALUES (9, '-24926804.045047420'); 484COMMIT TRANSACTION; 485 486-- ****************************** 487-- * Create indices for faster checks 488-- ****************************** 489 490CREATE UNIQUE INDEX num_exp_add_idx ON num_exp_add (id1, id2); 491CREATE UNIQUE INDEX num_exp_sub_idx ON num_exp_sub (id1, id2); 492CREATE UNIQUE INDEX num_exp_div_idx ON num_exp_div (id1, id2); 493CREATE UNIQUE INDEX num_exp_mul_idx ON num_exp_mul (id1, id2); 494CREATE UNIQUE INDEX num_exp_sqrt_idx ON num_exp_sqrt (id); 495CREATE UNIQUE INDEX num_exp_ln_idx ON num_exp_ln (id); 496CREATE UNIQUE INDEX num_exp_log10_idx ON num_exp_log10 (id); 497CREATE UNIQUE INDEX num_exp_power_10_ln_idx ON num_exp_power_10_ln (id); 498 499VACUUM ANALYZE num_exp_add; 500VACUUM ANALYZE num_exp_sub; 501VACUUM ANALYZE num_exp_div; 502VACUUM ANALYZE num_exp_mul; 503VACUUM ANALYZE num_exp_sqrt; 504VACUUM ANALYZE num_exp_ln; 505VACUUM ANALYZE num_exp_log10; 506VACUUM ANALYZE num_exp_power_10_ln; 507 508-- ****************************** 509-- * Now check the behaviour of the NUMERIC type 510-- ****************************** 511 512-- ****************************** 513-- * Addition check 514-- ****************************** 515DELETE FROM num_result; 516INSERT INTO num_result SELECT t1.id, t2.id, t1.val + t2.val 517 FROM num_data t1, num_data t2; 518SELECT t1.id1, t1.id2, t1.result, t2.expected 519 FROM num_result t1, num_exp_add t2 520 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2 521 AND t1.result != t2.expected; 522 523DELETE FROM num_result; 524INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val + t2.val, 10) 525 FROM num_data t1, num_data t2; 526SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 10) as expected 527 FROM num_result t1, num_exp_add t2 528 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2 529 AND t1.result != round(t2.expected, 10); 530 531-- ****************************** 532-- * Subtraction check 533-- ****************************** 534DELETE FROM num_result; 535INSERT INTO num_result SELECT t1.id, t2.id, t1.val - t2.val 536 FROM num_data t1, num_data t2; 537SELECT t1.id1, t1.id2, t1.result, t2.expected 538 FROM num_result t1, num_exp_sub t2 539 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2 540 AND t1.result != t2.expected; 541 542DELETE FROM num_result; 543INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val - t2.val, 40) 544 FROM num_data t1, num_data t2; 545SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 40) 546 FROM num_result t1, num_exp_sub t2 547 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2 548 AND t1.result != round(t2.expected, 40); 549 550-- ****************************** 551-- * Multiply check 552-- ****************************** 553DELETE FROM num_result; 554INSERT INTO num_result SELECT t1.id, t2.id, t1.val * t2.val 555 FROM num_data t1, num_data t2; 556SELECT t1.id1, t1.id2, t1.result, t2.expected 557 FROM num_result t1, num_exp_mul t2 558 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2 559 AND t1.result != t2.expected; 560 561DELETE FROM num_result; 562INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val * t2.val, 30) 563 FROM num_data t1, num_data t2; 564SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 30) as expected 565 FROM num_result t1, num_exp_mul t2 566 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2 567 AND t1.result != round(t2.expected, 30); 568 569-- ****************************** 570-- * Division check 571-- ****************************** 572DELETE FROM num_result; 573INSERT INTO num_result SELECT t1.id, t2.id, t1.val / t2.val 574 FROM num_data t1, num_data t2 575 WHERE t2.val != '0.0'; 576SELECT t1.id1, t1.id2, t1.result, t2.expected 577 FROM num_result t1, num_exp_div t2 578 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2 579 AND t1.result != t2.expected; 580 581DELETE FROM num_result; 582INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val / t2.val, 80) 583 FROM num_data t1, num_data t2 584 WHERE t2.val != '0.0'; 585SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 80) as expected 586 FROM num_result t1, num_exp_div t2 587 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2 588 AND t1.result != round(t2.expected, 80); 589 590-- ****************************** 591-- * Square root check 592-- ****************************** 593DELETE FROM num_result; 594INSERT INTO num_result SELECT id, 0, SQRT(ABS(val)) 595 FROM num_data; 596SELECT t1.id1, t1.result, t2.expected 597 FROM num_result t1, num_exp_sqrt t2 598 WHERE t1.id1 = t2.id 599 AND t1.result != t2.expected; 600 601-- ****************************** 602-- * Natural logarithm check 603-- ****************************** 604DELETE FROM num_result; 605INSERT INTO num_result SELECT id, 0, LN(ABS(val)) 606 FROM num_data 607 WHERE val != '0.0'; 608SELECT t1.id1, t1.result, t2.expected 609 FROM num_result t1, num_exp_ln t2 610 WHERE t1.id1 = t2.id 611 AND t1.result != t2.expected; 612 613-- ****************************** 614-- * Logarithm base 10 check 615-- ****************************** 616DELETE FROM num_result; 617INSERT INTO num_result SELECT id, 0, LOG(numeric '10', ABS(val)) 618 FROM num_data 619 WHERE val != '0.0'; 620SELECT t1.id1, t1.result, t2.expected 621 FROM num_result t1, num_exp_log10 t2 622 WHERE t1.id1 = t2.id 623 AND t1.result != t2.expected; 624 625-- ****************************** 626-- * POWER(10, LN(value)) check 627-- ****************************** 628DELETE FROM num_result; 629INSERT INTO num_result SELECT id, 0, POWER(numeric '10', LN(ABS(round(val,200)))) 630 FROM num_data 631 WHERE val != '0.0'; 632SELECT t1.id1, t1.result, t2.expected 633 FROM num_result t1, num_exp_power_10_ln t2 634 WHERE t1.id1 = t2.id 635 AND t1.result != t2.expected; 636 637-- ****************************** 638-- * miscellaneous checks for things that have been broken in the past... 639-- ****************************** 640-- numeric AVG used to fail on some platforms 641SELECT AVG(val) FROM num_data; 642SELECT STDDEV(val) FROM num_data; 643SELECT VARIANCE(val) FROM num_data; 644 645-- Check for appropriate rounding and overflow 646CREATE TABLE fract_only (id int, val numeric(4,4)); 647INSERT INTO fract_only VALUES (1, '0.0'); 648INSERT INTO fract_only VALUES (2, '0.1'); 649INSERT INTO fract_only VALUES (3, '1.0'); -- should fail 650INSERT INTO fract_only VALUES (4, '-0.9999'); 651INSERT INTO fract_only VALUES (5, '0.99994'); 652INSERT INTO fract_only VALUES (6, '0.99995'); -- should fail 653INSERT INTO fract_only VALUES (7, '0.00001'); 654INSERT INTO fract_only VALUES (8, '0.00017'); 655SELECT * FROM fract_only; 656DROP TABLE fract_only; 657 658-- Check conversion to integers 659SELECT (-9223372036854775808.5)::int8; -- should fail 660SELECT (-9223372036854775808.4)::int8; -- ok 661SELECT 9223372036854775807.4::int8; -- ok 662SELECT 9223372036854775807.5::int8; -- should fail 663SELECT (-2147483648.5)::int4; -- should fail 664SELECT (-2147483648.4)::int4; -- ok 665SELECT 2147483647.4::int4; -- ok 666SELECT 2147483647.5::int4; -- should fail 667SELECT (-32768.5)::int2; -- should fail 668SELECT (-32768.4)::int2; -- ok 669SELECT 32767.4::int2; -- ok 670SELECT 32767.5::int2; -- should fail 671 672-- Check inf/nan conversion behavior 673SELECT 'NaN'::float8::numeric; 674SELECT 'Infinity'::float8::numeric; 675SELECT '-Infinity'::float8::numeric; 676SELECT 'NaN'::float4::numeric; 677SELECT 'Infinity'::float4::numeric; 678SELECT '-Infinity'::float4::numeric; 679 680-- Simple check that ceil(), floor(), and round() work correctly 681CREATE TABLE ceil_floor_round (a numeric); 682INSERT INTO ceil_floor_round VALUES ('-5.5'); 683INSERT INTO ceil_floor_round VALUES ('-5.499999'); 684INSERT INTO ceil_floor_round VALUES ('9.5'); 685INSERT INTO ceil_floor_round VALUES ('9.4999999'); 686INSERT INTO ceil_floor_round VALUES ('0.0'); 687INSERT INTO ceil_floor_round VALUES ('0.0000001'); 688INSERT INTO ceil_floor_round VALUES ('-0.000001'); 689SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round; 690DROP TABLE ceil_floor_round; 691 692-- Check rounding, it should round ties away from zero. 693SELECT i as pow, 694 round((-2.5 * 10 ^ i)::numeric, -i), 695 round((-1.5 * 10 ^ i)::numeric, -i), 696 round((-0.5 * 10 ^ i)::numeric, -i), 697 round((0.5 * 10 ^ i)::numeric, -i), 698 round((1.5 * 10 ^ i)::numeric, -i), 699 round((2.5 * 10 ^ i)::numeric, -i) 700FROM generate_series(-5,5) AS t(i); 701 702-- Testing for width_bucket(). For convenience, we test both the 703-- numeric and float8 versions of the function in this file. 704 705-- errors 706SELECT width_bucket(5.0, 3.0, 4.0, 0); 707SELECT width_bucket(5.0, 3.0, 4.0, -5); 708SELECT width_bucket(3.5, 3.0, 3.0, 888); 709SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, 0); 710SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, -5); 711SELECT width_bucket(3.5::float8, 3.0::float8, 3.0::float8, 888); 712SELECT width_bucket('NaN', 3.0, 4.0, 888); 713SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888); 714 715-- normal operation 716CREATE TABLE width_bucket_test (operand_num numeric, operand_f8 float8); 717 718COPY width_bucket_test (operand_num) FROM stdin; 719-5.2 720-0.0000000001 7210.000000000001 7221 7231.99999999999999 7242 7252.00000000000001 7263 7274 7284.5 7295 7305.5 7316 7327 7338 7349 7359.99999999999999 73610 73710.0000000000001 738\. 739 740UPDATE width_bucket_test SET operand_f8 = operand_num::float8; 741 742SELECT 743 operand_num, 744 width_bucket(operand_num, 0, 10, 5) AS wb_1, 745 width_bucket(operand_f8, 0, 10, 5) AS wb_1f, 746 width_bucket(operand_num, 10, 0, 5) AS wb_2, 747 width_bucket(operand_f8, 10, 0, 5) AS wb_2f, 748 width_bucket(operand_num, 2, 8, 4) AS wb_3, 749 width_bucket(operand_f8, 2, 8, 4) AS wb_3f, 750 width_bucket(operand_num, 5.0, 5.5, 20) AS wb_4, 751 width_bucket(operand_f8, 5.0, 5.5, 20) AS wb_4f, 752 width_bucket(operand_num, -25, 25, 10) AS wb_5, 753 width_bucket(operand_f8, -25, 25, 10) AS wb_5f 754 FROM width_bucket_test; 755 756-- for float8 only, check positive and negative infinity: we require 757-- finite bucket bounds, but allow an infinite operand 758SELECT width_bucket(0.0::float8, 'Infinity'::float8, 5, 10); -- error 759SELECT width_bucket(0.0::float8, 5, '-Infinity'::float8, 20); -- error 760SELECT width_bucket('Infinity'::float8, 1, 10, 10), 761 width_bucket('-Infinity'::float8, 1, 10, 10); 762 763DROP TABLE width_bucket_test; 764 765-- TO_CHAR() 766-- 767SELECT '' AS to_char_1, to_char(val, '9G999G999G999G999G999') 768 FROM num_data; 769 770SELECT '' AS to_char_2, to_char(val, '9G999G999G999G999G999D999G999G999G999G999') 771 FROM num_data; 772 773SELECT '' AS to_char_3, to_char(val, '9999999999999999.999999999999999PR') 774 FROM num_data; 775 776SELECT '' AS to_char_4, to_char(val, '9999999999999999.999999999999999S') 777 FROM num_data; 778 779SELECT '' AS to_char_5, to_char(val, 'MI9999999999999999.999999999999999') FROM num_data; 780SELECT '' AS to_char_6, to_char(val, 'FMS9999999999999999.999999999999999') FROM num_data; 781SELECT '' AS to_char_7, to_char(val, 'FM9999999999999999.999999999999999THPR') FROM num_data; 782SELECT '' AS to_char_8, to_char(val, 'SG9999999999999999.999999999999999th') FROM num_data; 783SELECT '' AS to_char_9, to_char(val, '0999999999999999.999999999999999') FROM num_data; 784SELECT '' AS to_char_10, to_char(val, 'S0999999999999999.999999999999999') FROM num_data; 785SELECT '' AS to_char_11, to_char(val, 'FM0999999999999999.999999999999999') FROM num_data; 786SELECT '' AS to_char_12, to_char(val, 'FM9999999999999999.099999999999999') FROM num_data; 787SELECT '' AS to_char_13, to_char(val, 'FM9999999999990999.990999999999999') FROM num_data; 788SELECT '' AS to_char_14, to_char(val, 'FM0999999999999999.999909999999999') FROM num_data; 789SELECT '' AS to_char_15, to_char(val, 'FM9999999990999999.099999999999999') FROM num_data; 790SELECT '' AS to_char_16, to_char(val, 'L9999999999999999.099999999999999') FROM num_data; 791SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999') FROM num_data; 792SELECT '' AS to_char_18, to_char(val, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data; 793SELECT '' AS to_char_19, to_char(val, 'FMS 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data; 794SELECT '' AS to_char_20, to_char(val, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data; 795SELECT '' AS to_char_21, to_char(val, '999999SG9999999999') FROM num_data; 796SELECT '' AS to_char_22, to_char(val, 'FM9999999999999999.999999999999999') FROM num_data; 797SELECT '' AS to_char_23, to_char(val, '9.999EEEE') FROM num_data; 798 799SELECT '' AS to_char_24, to_char('100'::numeric, 'FM999.9'); 800SELECT '' AS to_char_25, to_char('100'::numeric, 'FM999.'); 801SELECT '' AS to_char_26, to_char('100'::numeric, 'FM999'); 802 803-- Check parsing of literal text in a format string 804SELECT '' AS to_char_27, to_char('100'::numeric, 'foo999'); 805SELECT '' AS to_char_28, to_char('100'::numeric, 'f\oo999'); 806SELECT '' AS to_char_29, to_char('100'::numeric, 'f\\oo999'); 807SELECT '' AS to_char_30, to_char('100'::numeric, 'f\"oo999'); 808SELECT '' AS to_char_31, to_char('100'::numeric, 'f\\"oo999'); 809SELECT '' AS to_char_32, to_char('100'::numeric, 'f"ool"999'); 810SELECT '' AS to_char_33, to_char('100'::numeric, 'f"\ool"999'); 811SELECT '' AS to_char_34, to_char('100'::numeric, 'f"\\ool"999'); 812SELECT '' AS to_char_35, to_char('100'::numeric, 'f"ool\"999'); 813SELECT '' AS to_char_36, to_char('100'::numeric, 'f"ool\\"999'); 814 815-- Test scientific notation with various exponents 816WITH v(exp) AS 817 (VALUES(-16379),(-16378),(-1234),(-789),(-45),(-5),(-4),(-3),(-2),(-1),(0), 818 (1),(2),(3),(4),(5),(38),(275),(2345),(45678),(131070),(131071)) 819SELECT exp, 820 to_char(('1.2345e'||exp)::numeric, '9.999EEEE') as numeric 821FROM v; 822 823-- TO_NUMBER() 824-- 825SET lc_numeric = 'C'; 826SELECT '' AS to_number_1, to_number('-34,338,492', '99G999G999'); 827SELECT '' AS to_number_2, to_number('-34,338,492.654,878', '99G999G999D999G999'); 828SELECT '' AS to_number_3, to_number('<564646.654564>', '999999.999999PR'); 829SELECT '' AS to_number_4, to_number('0.00001-', '9.999999S'); 830SELECT '' AS to_number_5, to_number('5.01-', 'FM9.999999S'); 831SELECT '' AS to_number_5, to_number('5.01-', 'FM9.999999MI'); 832SELECT '' AS to_number_7, to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9'); 833SELECT '' AS to_number_8, to_number('.01', 'FM9.99'); 834SELECT '' AS to_number_9, to_number('.0', '99999999.99999999'); 835SELECT '' AS to_number_10, to_number('0', '99.99'); 836SELECT '' AS to_number_11, to_number('.-01', 'S99.99'); 837SELECT '' AS to_number_12, to_number('.01-', '99.99S'); 838SELECT '' AS to_number_13, to_number(' . 0 1-', ' 9 9 . 9 9 S'); 839SELECT '' AS to_number_14, to_number('34,50','999,99'); 840SELECT '' AS to_number_15, to_number('123,000','999G'); 841SELECT '' AS to_number_16, to_number('123456','999G999'); 842SELECT '' AS to_number_17, to_number('$1234.56','L9,999.99'); 843SELECT '' AS to_number_18, to_number('$1234.56','L99,999.99'); 844SELECT '' AS to_number_19, to_number('$1,234.56','L99,999.99'); 845SELECT '' AS to_number_20, to_number('1234.56','L99,999.99'); 846SELECT '' AS to_number_21, to_number('1,234.56','L99,999.99'); 847SELECT '' AS to_number_22, to_number('42nd', '99th'); 848RESET lc_numeric; 849 850-- 851-- Input syntax 852-- 853 854CREATE TABLE num_input_test (n1 numeric); 855 856-- good inputs 857INSERT INTO num_input_test(n1) VALUES (' 123'); 858INSERT INTO num_input_test(n1) VALUES (' 3245874 '); 859INSERT INTO num_input_test(n1) VALUES (' -93853'); 860INSERT INTO num_input_test(n1) VALUES ('555.50'); 861INSERT INTO num_input_test(n1) VALUES ('-555.50'); 862INSERT INTO num_input_test(n1) VALUES ('NaN '); 863INSERT INTO num_input_test(n1) VALUES (' nan'); 864 865-- bad inputs 866INSERT INTO num_input_test(n1) VALUES (' '); 867INSERT INTO num_input_test(n1) VALUES (' 1234 %'); 868INSERT INTO num_input_test(n1) VALUES ('xyz'); 869INSERT INTO num_input_test(n1) VALUES ('- 1234'); 870INSERT INTO num_input_test(n1) VALUES ('5 . 0'); 871INSERT INTO num_input_test(n1) VALUES ('5. 0 '); 872INSERT INTO num_input_test(n1) VALUES (''); 873INSERT INTO num_input_test(n1) VALUES (' N aN '); 874 875SELECT * FROM num_input_test; 876 877-- 878-- Test some corner cases for multiplication 879-- 880 881select 4790999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999; 882 883select 4789999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999; 884 885select 4770999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999; 886 887select 4769999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999; 888 889select trim_scale((0.1 - 2e-16383) * (0.1 - 3e-16383)); 890 891-- 892-- Test some corner cases for division 893-- 894 895select 999999999999999999999::numeric/1000000000000000000000; 896select div(999999999999999999999::numeric,1000000000000000000000); 897select mod(999999999999999999999::numeric,1000000000000000000000); 898select div(-9999999999999999999999::numeric,1000000000000000000000); 899select mod(-9999999999999999999999::numeric,1000000000000000000000); 900select div(-9999999999999999999999::numeric,1000000000000000000000)*1000000000000000000000 + mod(-9999999999999999999999::numeric,1000000000000000000000); 901select mod (70.0,70) ; 902select div (70.0,70) ; 903select 70.0 / 70 ; 904select 12345678901234567890 % 123; 905select 12345678901234567890 / 123; 906select div(12345678901234567890, 123); 907select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123; 908 909-- 910-- Test some corner cases for square root 911-- 912 913select sqrt(1.000000000000003::numeric); 914select sqrt(1.000000000000004::numeric); 915select sqrt(96627521408608.56340355805::numeric); 916select sqrt(96627521408608.56340355806::numeric); 917select sqrt(515549506212297735.073688290367::numeric); 918select sqrt(515549506212297735.073688290368::numeric); 919select sqrt(8015491789940783531003294973900306::numeric); 920select sqrt(8015491789940783531003294973900307::numeric); 921 922-- 923-- Test code path for raising to integer powers 924-- 925 926select 10.0 ^ -2147483648 as rounds_to_zero; 927select 10.0 ^ -2147483647 as rounds_to_zero; 928select 10.0 ^ 2147483647 as overflows; 929select 117743296169.0 ^ 1000000000 as overflows; 930 931-- cases that used to return inaccurate results 932select 3.789 ^ 21; 933select 3.789 ^ 35; 934select 1.2 ^ 345; 935select 0.12 ^ (-20); 936select 1.000000000123 ^ (-2147483648); 937select coalesce(nullif(0.9999999999 ^ 23300000000000, 0), 0) as rounds_to_zero; 938select round(((1 - 1.500012345678e-1000) ^ 1.45e1003) * 1e1000); 939 940-- cases that used to error out 941select 0.12 ^ (-25); 942select 0.5678 ^ (-85); 943select coalesce(nullif(0.9999999999 ^ 70000000000000, 0), 0) as underflows; 944 945-- negative base to integer powers 946select (-1.0) ^ 2147483646; 947select (-1.0) ^ 2147483647; 948select (-1.0) ^ 2147483648; 949select (-1.0) ^ 1000000000000000; 950select (-1.0) ^ 1000000000000001; 951 952-- 953-- Tests for raising to non-integer powers 954-- 955 956-- special cases 957select 0.0 ^ 0.0; 958select (-12.34) ^ 0.0; 959select 12.34 ^ 0.0; 960select 0.0 ^ 12.34; 961 962-- NaNs 963select 'NaN'::numeric ^ 'NaN'::numeric; 964select 'NaN'::numeric ^ 0; 965select 'NaN'::numeric ^ 1; 966select 0 ^ 'NaN'::numeric; 967select 1 ^ 'NaN'::numeric; 968 969-- invalid inputs 970select 0.0 ^ (-12.34); 971select (-12.34) ^ 1.2; 972 973-- cases that used to generate inaccurate results 974select 32.1 ^ 9.8; 975select 32.1 ^ (-9.8); 976select 12.3 ^ 45.6; 977select 12.3 ^ (-45.6); 978 979-- big test 980select 1.234 ^ 5678; 981 982-- 983-- Tests for EXP() 984-- 985 986-- special cases 987select exp(0.0); 988select exp(1.0); 989select exp(1.0::numeric(71,70)); 990select coalesce(nullif(exp(-5000::numeric), 0), 0) as rounds_to_zero; 991select coalesce(nullif(exp(-10000::numeric), 0), 0) as underflows; 992 993-- cases that used to generate inaccurate results 994select exp(32.999); 995select exp(-32.999); 996select exp(123.456); 997select exp(-123.456); 998 999-- big test 1000select exp(1234.5678); 1001 1002-- 1003-- Tests for generate_series 1004-- 1005select * from generate_series(0.0::numeric, 4.0::numeric); 1006select * from generate_series(0.1::numeric, 4.0::numeric, 1.3::numeric); 1007select * from generate_series(4.0::numeric, -1.5::numeric, -2.2::numeric); 1008-- Trigger errors 1009select * from generate_series(-100::numeric, 100::numeric, 0::numeric); 1010select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric); 1011select * from generate_series('nan'::numeric, 100::numeric, 10::numeric); 1012select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric); 1013-- Checks maximum, output is truncated 1014select (i / (10::numeric ^ 131071))::numeric(1,0) 1015 from generate_series(6 * (10::numeric ^ 131071), 1016 9 * (10::numeric ^ 131071), 1017 10::numeric ^ 131071) as a(i); 1018-- Check usage with variables 1019select * from generate_series(1::numeric, 3::numeric) i, generate_series(i,3) j; 1020select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,i) j; 1021select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,5,i) j; 1022 1023-- 1024-- Tests for LN() 1025-- 1026 1027-- Invalid inputs 1028select ln(-12.34); 1029select ln(0.0); 1030 1031-- Some random tests 1032select ln(1.2345678e-28); 1033select ln(0.0456789); 1034select ln(0.349873948359354029493948309745709580730482050975); 1035select ln(0.99949452); 1036select ln(1.00049687395); 1037select ln(1234.567890123456789); 1038select ln(5.80397490724e5); 1039select ln(9.342536355e34); 1040 1041-- 1042-- Tests for LOG() (base 10) 1043-- 1044 1045-- invalid inputs 1046select log(-12.34); 1047select log(0.0); 1048 1049-- some random tests 1050select log(1.234567e-89); 1051select log(3.4634998359873254962349856073435545); 1052select log(9.999999999999999999); 1053select log(10.00000000000000000); 1054select log(10.00000000000000001); 1055select log(590489.45235237); 1056 1057-- 1058-- Tests for LOG() (arbitrary base) 1059-- 1060 1061-- invalid inputs 1062select log(-12.34, 56.78); 1063select log(-12.34, -56.78); 1064select log(12.34, -56.78); 1065select log(0.0, 12.34); 1066select log(12.34, 0.0); 1067select log(1.0, 12.34); 1068 1069-- some random tests 1070select log(1.23e-89, 6.4689e45); 1071select log(0.99923, 4.58934e34); 1072select log(1.000016, 8.452010e18); 1073select log(3.1954752e47, 9.4792021e-73); 1074 1075-- 1076-- Tests for scale() 1077-- 1078 1079select scale(numeric 'NaN'); 1080select scale(NULL::numeric); 1081select scale(1.12); 1082select scale(0); 1083select scale(0.00); 1084select scale(1.12345); 1085select scale(110123.12475871856128); 1086select scale(-1123.12471856128); 1087select scale(-13.000000000000000); 1088 1089-- 1090-- Tests for min_scale() 1091-- 1092 1093select min_scale(numeric 'NaN') is NULL; -- should be true 1094select min_scale(0); -- no digits 1095select min_scale(0.00); -- no digits again 1096select min_scale(1.0); -- no scale 1097select min_scale(1.1); -- scale 1 1098select min_scale(1.12); -- scale 2 1099select min_scale(1.123); -- scale 3 1100select min_scale(1.1234); -- scale 4, filled digit 1101select min_scale(1.12345); -- scale 5, 2 NDIGITS 1102select min_scale(1.1000); -- 1 pos in NDIGITS 1103select min_scale(1e100); -- very big number 1104 1105-- 1106-- Tests for trim_scale() 1107-- 1108 1109select trim_scale(numeric 'NaN'); 1110select trim_scale(1.120); 1111select trim_scale(0); 1112select trim_scale(0.00); 1113select trim_scale(1.1234500); 1114select trim_scale(110123.12475871856128000); 1115select trim_scale(-1123.124718561280000000); 1116select trim_scale(-13.00000000000000000000); 1117select trim_scale(1e100); 1118 1119-- 1120-- Tests for SUM() 1121-- 1122 1123-- cases that need carry propagation 1124SELECT SUM(9999::numeric) FROM generate_series(1, 100000); 1125SELECT SUM((-9999)::numeric) FROM generate_series(1, 100000); 1126 1127-- 1128-- Tests for GCD() 1129-- 1130SELECT a, b, gcd(a, b), gcd(a, -b), gcd(-b, a), gcd(-b, -a) 1131FROM (VALUES (0::numeric, 0::numeric), 1132 (0::numeric, numeric 'NaN'), 1133 (0::numeric, 46375::numeric), 1134 (433125::numeric, 46375::numeric), 1135 (43312.5::numeric, 4637.5::numeric), 1136 (4331.250::numeric, 463.75000::numeric)) AS v(a, b); 1137 1138-- 1139-- Tests for LCM() 1140-- 1141SELECT a,b, lcm(a, b), lcm(a, -b), lcm(-b, a), lcm(-b, -a) 1142FROM (VALUES (0::numeric, 0::numeric), 1143 (0::numeric, numeric 'NaN'), 1144 (0::numeric, 13272::numeric), 1145 (13272::numeric, 13272::numeric), 1146 (423282::numeric, 13272::numeric), 1147 (42328.2::numeric, 1327.2::numeric), 1148 (4232.820::numeric, 132.72000::numeric)) AS v(a, b); 1149 1150SELECT lcm(9999 * (10::numeric)^131068 + (10::numeric^131068 - 1), 2); -- overflow 1151