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-- * Check behavior with Inf and NaN inputs. It's easiest to handle these 639-- * separately from the num_data framework used above, because some input 640-- * combinations will throw errors. 641-- ****************************** 642 643WITH v(x) AS 644 (VALUES('0'::numeric),('1'),('-1'),('4.2'),('inf'),('-inf'),('nan')) 645SELECT x1, x2, 646 x1 + x2 AS sum, 647 x1 - x2 AS diff, 648 x1 * x2 AS prod 649FROM v AS v1(x1), v AS v2(x2); 650 651WITH v(x) AS 652 (VALUES('0'::numeric),('1'),('-1'),('4.2'),('inf'),('-inf'),('nan')) 653SELECT x1, x2, 654 x1 / x2 AS quot, 655 x1 % x2 AS mod, 656 div(x1, x2) AS div 657FROM v AS v1(x1), v AS v2(x2) WHERE x2 != 0; 658 659SELECT 'inf'::numeric / '0'; 660SELECT '-inf'::numeric / '0'; 661SELECT 'nan'::numeric / '0'; 662SELECT '0'::numeric / '0'; 663SELECT 'inf'::numeric % '0'; 664SELECT '-inf'::numeric % '0'; 665SELECT 'nan'::numeric % '0'; 666SELECT '0'::numeric % '0'; 667SELECT div('inf'::numeric, '0'); 668SELECT div('-inf'::numeric, '0'); 669SELECT div('nan'::numeric, '0'); 670SELECT div('0'::numeric, '0'); 671 672WITH v(x) AS 673 (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('inf'),('-inf'),('nan')) 674SELECT x, -x as minusx, abs(x), floor(x), ceil(x), sign(x), numeric_inc(x) as inc 675FROM v; 676 677WITH v(x) AS 678 (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('inf'),('-inf'),('nan')) 679SELECT x, round(x), round(x,1) as round1, trunc(x), trunc(x,1) as trunc1 680FROM v; 681 682-- the large values fall into the numeric abbreviation code's maximal classes 683WITH v(x) AS 684 (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('1e340'),('-1e340'), 685 ('inf'),('-inf'),('nan'), 686 ('inf'),('-inf'),('nan')) 687SELECT substring(x::text, 1, 32) 688FROM v ORDER BY x; 689 690WITH v(x) AS 691 (VALUES('0'::numeric),('1'),('4.2'),('inf'),('nan')) 692SELECT x, sqrt(x) 693FROM v; 694 695SELECT sqrt('-1'::numeric); 696SELECT sqrt('-inf'::numeric); 697 698WITH v(x) AS 699 (VALUES('1'::numeric),('4.2'),('inf'),('nan')) 700SELECT x, 701 log(x), 702 log10(x), 703 ln(x) 704FROM v; 705 706SELECT ln('0'::numeric); 707SELECT ln('-1'::numeric); 708SELECT ln('-inf'::numeric); 709 710WITH v(x) AS 711 (VALUES('2'::numeric),('4.2'),('inf'),('nan')) 712SELECT x1, x2, 713 log(x1, x2) 714FROM v AS v1(x1), v AS v2(x2); 715 716SELECT log('0'::numeric, '10'); 717SELECT log('10'::numeric, '0'); 718SELECT log('-inf'::numeric, '10'); 719SELECT log('10'::numeric, '-inf'); 720SELECT log('inf'::numeric, '0'); 721SELECT log('inf'::numeric, '-inf'); 722SELECT log('-inf'::numeric, 'inf'); 723 724WITH v(x) AS 725 (VALUES('0'::numeric),('1'),('2'),('4.2'),('inf'),('nan')) 726SELECT x1, x2, 727 power(x1, x2) 728FROM v AS v1(x1), v AS v2(x2) WHERE x1 != 0 OR x2 >= 0; 729 730SELECT power('0'::numeric, '-1'); 731SELECT power('0'::numeric, '-inf'); 732SELECT power('-1'::numeric, 'inf'); 733SELECT power('-2'::numeric, '3'); 734SELECT power('-2'::numeric, '3.3'); 735SELECT power('-2'::numeric, '-1'); 736SELECT power('-2'::numeric, '-1.5'); 737SELECT power('-2'::numeric, 'inf'); 738SELECT power('-2'::numeric, '-inf'); 739SELECT power('inf'::numeric, '-2'); 740SELECT power('inf'::numeric, '-inf'); 741SELECT power('-inf'::numeric, '2'); 742SELECT power('-inf'::numeric, '3'); 743SELECT power('-inf'::numeric, '4.5'); 744SELECT power('-inf'::numeric, '-2'); 745SELECT power('-inf'::numeric, '-3'); 746SELECT power('-inf'::numeric, '0'); 747SELECT power('-inf'::numeric, 'inf'); 748SELECT power('-inf'::numeric, '-inf'); 749 750-- ****************************** 751-- * miscellaneous checks for things that have been broken in the past... 752-- ****************************** 753-- numeric AVG used to fail on some platforms 754SELECT AVG(val) FROM num_data; 755SELECT MAX(val) FROM num_data; 756SELECT MIN(val) FROM num_data; 757SELECT STDDEV(val) FROM num_data; 758SELECT VARIANCE(val) FROM num_data; 759 760-- Check for appropriate rounding and overflow 761CREATE TABLE fract_only (id int, val numeric(4,4)); 762INSERT INTO fract_only VALUES (1, '0.0'); 763INSERT INTO fract_only VALUES (2, '0.1'); 764INSERT INTO fract_only VALUES (3, '1.0'); -- should fail 765INSERT INTO fract_only VALUES (4, '-0.9999'); 766INSERT INTO fract_only VALUES (5, '0.99994'); 767INSERT INTO fract_only VALUES (6, '0.99995'); -- should fail 768INSERT INTO fract_only VALUES (7, '0.00001'); 769INSERT INTO fract_only VALUES (8, '0.00017'); 770INSERT INTO fract_only VALUES (9, 'NaN'); 771INSERT INTO fract_only VALUES (10, 'Inf'); -- should fail 772INSERT INTO fract_only VALUES (11, '-Inf'); -- should fail 773SELECT * FROM fract_only; 774DROP TABLE fract_only; 775 776-- Check conversion to integers 777SELECT (-9223372036854775808.5)::int8; -- should fail 778SELECT (-9223372036854775808.4)::int8; -- ok 779SELECT 9223372036854775807.4::int8; -- ok 780SELECT 9223372036854775807.5::int8; -- should fail 781SELECT (-2147483648.5)::int4; -- should fail 782SELECT (-2147483648.4)::int4; -- ok 783SELECT 2147483647.4::int4; -- ok 784SELECT 2147483647.5::int4; -- should fail 785SELECT (-32768.5)::int2; -- should fail 786SELECT (-32768.4)::int2; -- ok 787SELECT 32767.4::int2; -- ok 788SELECT 32767.5::int2; -- should fail 789 790-- Check inf/nan conversion behavior 791SELECT 'NaN'::float8::numeric; 792SELECT 'Infinity'::float8::numeric; 793SELECT '-Infinity'::float8::numeric; 794SELECT 'NaN'::numeric::float8; 795SELECT 'Infinity'::numeric::float8; 796SELECT '-Infinity'::numeric::float8; 797SELECT 'NaN'::float4::numeric; 798SELECT 'Infinity'::float4::numeric; 799SELECT '-Infinity'::float4::numeric; 800SELECT 'NaN'::numeric::float4; 801SELECT 'Infinity'::numeric::float4; 802SELECT '-Infinity'::numeric::float4; 803SELECT '42'::int2::numeric; 804SELECT 'NaN'::numeric::int2; 805SELECT 'Infinity'::numeric::int2; 806SELECT '-Infinity'::numeric::int2; 807SELECT 'NaN'::numeric::int4; 808SELECT 'Infinity'::numeric::int4; 809SELECT '-Infinity'::numeric::int4; 810SELECT 'NaN'::numeric::int8; 811SELECT 'Infinity'::numeric::int8; 812SELECT '-Infinity'::numeric::int8; 813 814-- Simple check that ceil(), floor(), and round() work correctly 815CREATE TABLE ceil_floor_round (a numeric); 816INSERT INTO ceil_floor_round VALUES ('-5.5'); 817INSERT INTO ceil_floor_round VALUES ('-5.499999'); 818INSERT INTO ceil_floor_round VALUES ('9.5'); 819INSERT INTO ceil_floor_round VALUES ('9.4999999'); 820INSERT INTO ceil_floor_round VALUES ('0.0'); 821INSERT INTO ceil_floor_round VALUES ('0.0000001'); 822INSERT INTO ceil_floor_round VALUES ('-0.000001'); 823SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round; 824DROP TABLE ceil_floor_round; 825 826-- Check rounding, it should round ties away from zero. 827SELECT i as pow, 828 round((-2.5 * 10 ^ i)::numeric, -i), 829 round((-1.5 * 10 ^ i)::numeric, -i), 830 round((-0.5 * 10 ^ i)::numeric, -i), 831 round((0.5 * 10 ^ i)::numeric, -i), 832 round((1.5 * 10 ^ i)::numeric, -i), 833 round((2.5 * 10 ^ i)::numeric, -i) 834FROM generate_series(-5,5) AS t(i); 835 836-- Testing for width_bucket(). For convenience, we test both the 837-- numeric and float8 versions of the function in this file. 838 839-- errors 840SELECT width_bucket(5.0, 3.0, 4.0, 0); 841SELECT width_bucket(5.0, 3.0, 4.0, -5); 842SELECT width_bucket(3.5, 3.0, 3.0, 888); 843SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, 0); 844SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, -5); 845SELECT width_bucket(3.5::float8, 3.0::float8, 3.0::float8, 888); 846SELECT width_bucket('NaN', 3.0, 4.0, 888); 847SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888); 848SELECT width_bucket(2.0, 3.0, '-inf', 888); 849SELECT width_bucket(0::float8, '-inf', 4.0::float8, 888); 850 851-- normal operation 852CREATE TABLE width_bucket_test (operand_num numeric, operand_f8 float8); 853 854COPY width_bucket_test (operand_num) FROM stdin; 855-5.2 856-0.0000000001 8570.000000000001 8581 8591.99999999999999 8602 8612.00000000000001 8623 8634 8644.5 8655 8665.5 8676 8687 8698 8709 8719.99999999999999 87210 87310.0000000000001 874\. 875 876UPDATE width_bucket_test SET operand_f8 = operand_num::float8; 877 878SELECT 879 operand_num, 880 width_bucket(operand_num, 0, 10, 5) AS wb_1, 881 width_bucket(operand_f8, 0, 10, 5) AS wb_1f, 882 width_bucket(operand_num, 10, 0, 5) AS wb_2, 883 width_bucket(operand_f8, 10, 0, 5) AS wb_2f, 884 width_bucket(operand_num, 2, 8, 4) AS wb_3, 885 width_bucket(operand_f8, 2, 8, 4) AS wb_3f, 886 width_bucket(operand_num, 5.0, 5.5, 20) AS wb_4, 887 width_bucket(operand_f8, 5.0, 5.5, 20) AS wb_4f, 888 width_bucket(operand_num, -25, 25, 10) AS wb_5, 889 width_bucket(operand_f8, -25, 25, 10) AS wb_5f 890 FROM width_bucket_test; 891 892-- Check positive and negative infinity: we require 893-- finite bucket bounds, but allow an infinite operand 894SELECT width_bucket(0.0::numeric, 'Infinity'::numeric, 5, 10); -- error 895SELECT width_bucket(0.0::numeric, 5, '-Infinity'::numeric, 20); -- error 896SELECT width_bucket('Infinity'::numeric, 1, 10, 10), 897 width_bucket('-Infinity'::numeric, 1, 10, 10); 898SELECT width_bucket(0.0::float8, 'Infinity'::float8, 5, 10); -- error 899SELECT width_bucket(0.0::float8, 5, '-Infinity'::float8, 20); -- error 900SELECT width_bucket('Infinity'::float8, 1, 10, 10), 901 width_bucket('-Infinity'::float8, 1, 10, 10); 902 903DROP TABLE width_bucket_test; 904 905-- Simple test for roundoff error when results should be exact 906SELECT x, width_bucket(x::float8, 10, 100, 9) as flt, 907 width_bucket(x::numeric, 10, 100, 9) as num 908FROM generate_series(0, 110, 10) x; 909SELECT x, width_bucket(x::float8, 100, 10, 9) as flt, 910 width_bucket(x::numeric, 100, 10, 9) as num 911FROM generate_series(0, 110, 10) x; 912 913-- 914-- TO_CHAR() 915-- 916SELECT to_char(val, '9G999G999G999G999G999') 917 FROM num_data; 918 919SELECT to_char(val, '9G999G999G999G999G999D999G999G999G999G999') 920 FROM num_data; 921 922SELECT to_char(val, '9999999999999999.999999999999999PR') 923 FROM num_data; 924 925SELECT to_char(val, '9999999999999999.999999999999999S') 926 FROM num_data; 927 928SELECT to_char(val, 'MI9999999999999999.999999999999999') FROM num_data; 929SELECT to_char(val, 'FMS9999999999999999.999999999999999') FROM num_data; 930SELECT to_char(val, 'FM9999999999999999.999999999999999THPR') FROM num_data; 931SELECT to_char(val, 'SG9999999999999999.999999999999999th') FROM num_data; 932SELECT to_char(val, '0999999999999999.999999999999999') FROM num_data; 933SELECT to_char(val, 'S0999999999999999.999999999999999') FROM num_data; 934SELECT to_char(val, 'FM0999999999999999.999999999999999') FROM num_data; 935SELECT to_char(val, 'FM9999999999999999.099999999999999') FROM num_data; 936SELECT to_char(val, 'FM9999999999990999.990999999999999') FROM num_data; 937SELECT to_char(val, 'FM0999999999999999.999909999999999') FROM num_data; 938SELECT to_char(val, 'FM9999999990999999.099999999999999') FROM num_data; 939SELECT to_char(val, 'L9999999999999999.099999999999999') FROM num_data; 940SELECT to_char(val, 'FM9999999999999999.99999999999999') FROM num_data; 941SELECT 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; 942SELECT 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; 943SELECT to_char(val, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data; 944SELECT to_char(val, '999999SG9999999999') FROM num_data; 945SELECT to_char(val, 'FM9999999999999999.999999999999999') FROM num_data; 946SELECT to_char(val, '9.999EEEE') FROM num_data; 947 948WITH v(val) AS 949 (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan')) 950SELECT val, 951 to_char(val, '9.999EEEE') as numeric, 952 to_char(val::float8, '9.999EEEE') as float8, 953 to_char(val::float4, '9.999EEEE') as float4 954FROM v; 955 956WITH v(exp) AS 957 (VALUES(-16379),(-16378),(-1234),(-789),(-45),(-5),(-4),(-3),(-2),(-1),(0), 958 (1),(2),(3),(4),(5),(38),(275),(2345),(45678),(131070),(131071)) 959SELECT exp, 960 to_char(('1.2345e'||exp)::numeric, '9.999EEEE') as numeric 961FROM v; 962 963WITH v(val) AS 964 (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan')) 965SELECT val, 966 to_char(val, 'MI9999999999.99') as numeric, 967 to_char(val::float8, 'MI9999999999.99') as float8, 968 to_char(val::float4, 'MI9999999999.99') as float4 969FROM v; 970 971WITH v(val) AS 972 (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan')) 973SELECT val, 974 to_char(val, 'MI99.99') as numeric, 975 to_char(val::float8, 'MI99.99') as float8, 976 to_char(val::float4, 'MI99.99') as float4 977FROM v; 978 979SELECT to_char('100'::numeric, 'FM999.9'); 980SELECT to_char('100'::numeric, 'FM999.'); 981SELECT to_char('100'::numeric, 'FM999'); 982 983-- Check parsing of literal text in a format string 984SELECT to_char('100'::numeric, 'foo999'); 985SELECT to_char('100'::numeric, 'f\oo999'); 986SELECT to_char('100'::numeric, 'f\\oo999'); 987SELECT to_char('100'::numeric, 'f\"oo999'); 988SELECT to_char('100'::numeric, 'f\\"oo999'); 989SELECT to_char('100'::numeric, 'f"ool"999'); 990SELECT to_char('100'::numeric, 'f"\ool"999'); 991SELECT to_char('100'::numeric, 'f"\\ool"999'); 992SELECT to_char('100'::numeric, 'f"ool\"999'); 993SELECT to_char('100'::numeric, 'f"ool\\"999'); 994 995-- TO_NUMBER() 996-- 997SET lc_numeric = 'C'; 998SELECT to_number('-34,338,492', '99G999G999'); 999SELECT to_number('-34,338,492.654,878', '99G999G999D999G999'); 1000SELECT to_number('<564646.654564>', '999999.999999PR'); 1001SELECT to_number('0.00001-', '9.999999S'); 1002SELECT to_number('5.01-', 'FM9.999999S'); 1003SELECT to_number('5.01-', 'FM9.999999MI'); 1004SELECT to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9'); 1005SELECT to_number('.01', 'FM9.99'); 1006SELECT to_number('.0', '99999999.99999999'); 1007SELECT to_number('0', '99.99'); 1008SELECT to_number('.-01', 'S99.99'); 1009SELECT to_number('.01-', '99.99S'); 1010SELECT to_number(' . 0 1-', ' 9 9 . 9 9 S'); 1011SELECT to_number('34,50','999,99'); 1012SELECT to_number('123,000','999G'); 1013SELECT to_number('123456','999G999'); 1014SELECT to_number('$1234.56','L9,999.99'); 1015SELECT to_number('$1234.56','L99,999.99'); 1016SELECT to_number('$1,234.56','L99,999.99'); 1017SELECT to_number('1234.56','L99,999.99'); 1018SELECT to_number('1,234.56','L99,999.99'); 1019SELECT to_number('42nd', '99th'); 1020RESET lc_numeric; 1021 1022-- 1023-- Input syntax 1024-- 1025 1026CREATE TABLE num_input_test (n1 numeric); 1027 1028-- good inputs 1029INSERT INTO num_input_test(n1) VALUES (' 123'); 1030INSERT INTO num_input_test(n1) VALUES (' 3245874 '); 1031INSERT INTO num_input_test(n1) VALUES (' -93853'); 1032INSERT INTO num_input_test(n1) VALUES ('555.50'); 1033INSERT INTO num_input_test(n1) VALUES ('-555.50'); 1034INSERT INTO num_input_test(n1) VALUES ('NaN '); 1035INSERT INTO num_input_test(n1) VALUES (' nan'); 1036INSERT INTO num_input_test(n1) VALUES (' inf '); 1037INSERT INTO num_input_test(n1) VALUES (' +inf '); 1038INSERT INTO num_input_test(n1) VALUES (' -inf '); 1039INSERT INTO num_input_test(n1) VALUES (' Infinity '); 1040INSERT INTO num_input_test(n1) VALUES (' +inFinity '); 1041INSERT INTO num_input_test(n1) VALUES (' -INFINITY '); 1042 1043-- bad inputs 1044INSERT INTO num_input_test(n1) VALUES (' '); 1045INSERT INTO num_input_test(n1) VALUES (' 1234 %'); 1046INSERT INTO num_input_test(n1) VALUES ('xyz'); 1047INSERT INTO num_input_test(n1) VALUES ('- 1234'); 1048INSERT INTO num_input_test(n1) VALUES ('5 . 0'); 1049INSERT INTO num_input_test(n1) VALUES ('5. 0 '); 1050INSERT INTO num_input_test(n1) VALUES (''); 1051INSERT INTO num_input_test(n1) VALUES (' N aN '); 1052INSERT INTO num_input_test(n1) VALUES ('+ infinity'); 1053 1054SELECT * FROM num_input_test; 1055 1056-- 1057-- Test some corner cases for multiplication 1058-- 1059 1060select 4790999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999; 1061 1062select 4789999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999; 1063 1064select 4770999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999; 1065 1066select 4769999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999; 1067 1068select trim_scale((0.1 - 2e-16383) * (0.1 - 3e-16383)); 1069 1070-- 1071-- Test some corner cases for division 1072-- 1073 1074select 999999999999999999999::numeric/1000000000000000000000; 1075select div(999999999999999999999::numeric,1000000000000000000000); 1076select mod(999999999999999999999::numeric,1000000000000000000000); 1077select div(-9999999999999999999999::numeric,1000000000000000000000); 1078select mod(-9999999999999999999999::numeric,1000000000000000000000); 1079select div(-9999999999999999999999::numeric,1000000000000000000000)*1000000000000000000000 + mod(-9999999999999999999999::numeric,1000000000000000000000); 1080select mod (70.0,70) ; 1081select div (70.0,70) ; 1082select 70.0 / 70 ; 1083select 12345678901234567890 % 123; 1084select 12345678901234567890 / 123; 1085select div(12345678901234567890, 123); 1086select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123; 1087 1088-- 1089-- Test some corner cases for square root 1090-- 1091 1092select sqrt(1.000000000000003::numeric); 1093select sqrt(1.000000000000004::numeric); 1094select sqrt(96627521408608.56340355805::numeric); 1095select sqrt(96627521408608.56340355806::numeric); 1096select sqrt(515549506212297735.073688290367::numeric); 1097select sqrt(515549506212297735.073688290368::numeric); 1098select sqrt(8015491789940783531003294973900306::numeric); 1099select sqrt(8015491789940783531003294973900307::numeric); 1100 1101-- 1102-- Test code path for raising to integer powers 1103-- 1104 1105select 10.0 ^ -2147483648 as rounds_to_zero; 1106select 10.0 ^ -2147483647 as rounds_to_zero; 1107select 10.0 ^ 2147483647 as overflows; 1108select 117743296169.0 ^ 1000000000 as overflows; 1109 1110-- cases that used to return inaccurate results 1111select 3.789 ^ 21; 1112select 3.789 ^ 35; 1113select 1.2 ^ 345; 1114select 0.12 ^ (-20); 1115select 1.000000000123 ^ (-2147483648); 1116select coalesce(nullif(0.9999999999 ^ 23300000000000, 0), 0) as rounds_to_zero; 1117select round(((1 - 1.500012345678e-1000) ^ 1.45e1003) * 1e1000); 1118 1119-- cases that used to error out 1120select 0.12 ^ (-25); 1121select 0.5678 ^ (-85); 1122select coalesce(nullif(0.9999999999 ^ 70000000000000, 0), 0) as underflows; 1123 1124-- negative base to integer powers 1125select (-1.0) ^ 2147483646; 1126select (-1.0) ^ 2147483647; 1127select (-1.0) ^ 2147483648; 1128select (-1.0) ^ 1000000000000000; 1129select (-1.0) ^ 1000000000000001; 1130 1131-- 1132-- Tests for raising to non-integer powers 1133-- 1134 1135-- special cases 1136select 0.0 ^ 0.0; 1137select (-12.34) ^ 0.0; 1138select 12.34 ^ 0.0; 1139select 0.0 ^ 12.34; 1140 1141-- NaNs 1142select 'NaN'::numeric ^ 'NaN'::numeric; 1143select 'NaN'::numeric ^ 0; 1144select 'NaN'::numeric ^ 1; 1145select 0 ^ 'NaN'::numeric; 1146select 1 ^ 'NaN'::numeric; 1147 1148-- invalid inputs 1149select 0.0 ^ (-12.34); 1150select (-12.34) ^ 1.2; 1151 1152-- cases that used to generate inaccurate results 1153select 32.1 ^ 9.8; 1154select 32.1 ^ (-9.8); 1155select 12.3 ^ 45.6; 1156select 12.3 ^ (-45.6); 1157 1158-- big test 1159select 1.234 ^ 5678; 1160 1161-- 1162-- Tests for EXP() 1163-- 1164 1165-- special cases 1166select exp(0.0); 1167select exp(1.0); 1168select exp(1.0::numeric(71,70)); 1169select exp('nan'::numeric); 1170select exp('inf'::numeric); 1171select exp('-inf'::numeric); 1172select coalesce(nullif(exp(-5000::numeric), 0), 0) as rounds_to_zero; 1173select coalesce(nullif(exp(-10000::numeric), 0), 0) as underflows; 1174 1175-- cases that used to generate inaccurate results 1176select exp(32.999); 1177select exp(-32.999); 1178select exp(123.456); 1179select exp(-123.456); 1180 1181-- big test 1182select exp(1234.5678); 1183 1184-- 1185-- Tests for generate_series 1186-- 1187select * from generate_series(0.0::numeric, 4.0::numeric); 1188select * from generate_series(0.1::numeric, 4.0::numeric, 1.3::numeric); 1189select * from generate_series(4.0::numeric, -1.5::numeric, -2.2::numeric); 1190-- Trigger errors 1191select * from generate_series(-100::numeric, 100::numeric, 0::numeric); 1192select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric); 1193select * from generate_series('nan'::numeric, 100::numeric, 10::numeric); 1194select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric); 1195select * from generate_series('inf'::numeric, 'inf'::numeric, 10::numeric); 1196select * from generate_series(0::numeric, 'inf'::numeric, 10::numeric); 1197select * from generate_series(0::numeric, '42'::numeric, '-inf'::numeric); 1198-- Checks maximum, output is truncated 1199select (i / (10::numeric ^ 131071))::numeric(1,0) 1200 from generate_series(6 * (10::numeric ^ 131071), 1201 9 * (10::numeric ^ 131071), 1202 10::numeric ^ 131071) as a(i); 1203-- Check usage with variables 1204select * from generate_series(1::numeric, 3::numeric) i, generate_series(i,3) j; 1205select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,i) j; 1206select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,5,i) j; 1207 1208-- 1209-- Tests for LN() 1210-- 1211 1212-- Invalid inputs 1213select ln(-12.34); 1214select ln(0.0); 1215 1216-- Some random tests 1217select ln(1.2345678e-28); 1218select ln(0.0456789); 1219select ln(0.349873948359354029493948309745709580730482050975); 1220select ln(0.99949452); 1221select ln(1.00049687395); 1222select ln(1234.567890123456789); 1223select ln(5.80397490724e5); 1224select ln(9.342536355e34); 1225 1226-- 1227-- Tests for LOG() (base 10) 1228-- 1229 1230-- invalid inputs 1231select log(-12.34); 1232select log(0.0); 1233 1234-- some random tests 1235select log(1.234567e-89); 1236select log(3.4634998359873254962349856073435545); 1237select log(9.999999999999999999); 1238select log(10.00000000000000000); 1239select log(10.00000000000000001); 1240select log(590489.45235237); 1241 1242-- 1243-- Tests for LOG() (arbitrary base) 1244-- 1245 1246-- invalid inputs 1247select log(-12.34, 56.78); 1248select log(-12.34, -56.78); 1249select log(12.34, -56.78); 1250select log(0.0, 12.34); 1251select log(12.34, 0.0); 1252select log(1.0, 12.34); 1253 1254-- some random tests 1255select log(1.23e-89, 6.4689e45); 1256select log(0.99923, 4.58934e34); 1257select log(1.000016, 8.452010e18); 1258select log(3.1954752e47, 9.4792021e-73); 1259 1260-- 1261-- Tests for scale() 1262-- 1263 1264select scale(numeric 'NaN'); 1265select scale(numeric 'inf'); 1266select scale(NULL::numeric); 1267select scale(1.12); 1268select scale(0); 1269select scale(0.00); 1270select scale(1.12345); 1271select scale(110123.12475871856128); 1272select scale(-1123.12471856128); 1273select scale(-13.000000000000000); 1274 1275-- 1276-- Tests for min_scale() 1277-- 1278 1279select min_scale(numeric 'NaN') is NULL; -- should be true 1280select min_scale(numeric 'inf') is NULL; -- should be true 1281select min_scale(0); -- no digits 1282select min_scale(0.00); -- no digits again 1283select min_scale(1.0); -- no scale 1284select min_scale(1.1); -- scale 1 1285select min_scale(1.12); -- scale 2 1286select min_scale(1.123); -- scale 3 1287select min_scale(1.1234); -- scale 4, filled digit 1288select min_scale(1.12345); -- scale 5, 2 NDIGITS 1289select min_scale(1.1000); -- 1 pos in NDIGITS 1290select min_scale(1e100); -- very big number 1291 1292-- 1293-- Tests for trim_scale() 1294-- 1295 1296select trim_scale(numeric 'NaN'); 1297select trim_scale(numeric 'inf'); 1298select trim_scale(1.120); 1299select trim_scale(0); 1300select trim_scale(0.00); 1301select trim_scale(1.1234500); 1302select trim_scale(110123.12475871856128000); 1303select trim_scale(-1123.124718561280000000); 1304select trim_scale(-13.00000000000000000000); 1305select trim_scale(1e100); 1306 1307-- 1308-- Tests for SUM() 1309-- 1310 1311-- cases that need carry propagation 1312SELECT SUM(9999::numeric) FROM generate_series(1, 100000); 1313SELECT SUM((-9999)::numeric) FROM generate_series(1, 100000); 1314 1315-- 1316-- Tests for GCD() 1317-- 1318SELECT a, b, gcd(a, b), gcd(a, -b), gcd(-b, a), gcd(-b, -a) 1319FROM (VALUES (0::numeric, 0::numeric), 1320 (0::numeric, numeric 'NaN'), 1321 (0::numeric, 46375::numeric), 1322 (433125::numeric, 46375::numeric), 1323 (43312.5::numeric, 4637.5::numeric), 1324 (4331.250::numeric, 463.75000::numeric), 1325 ('inf', '0'), 1326 ('inf', '42'), 1327 ('inf', 'inf') 1328 ) AS v(a, b); 1329 1330-- 1331-- Tests for LCM() 1332-- 1333SELECT a,b, lcm(a, b), lcm(a, -b), lcm(-b, a), lcm(-b, -a) 1334FROM (VALUES (0::numeric, 0::numeric), 1335 (0::numeric, numeric 'NaN'), 1336 (0::numeric, 13272::numeric), 1337 (13272::numeric, 13272::numeric), 1338 (423282::numeric, 13272::numeric), 1339 (42328.2::numeric, 1327.2::numeric), 1340 (4232.820::numeric, 132.72000::numeric), 1341 ('inf', '0'), 1342 ('inf', '42'), 1343 ('inf', 'inf') 1344 ) AS v(a, b); 1345 1346SELECT lcm(9999 * (10::numeric)^131068 + (10::numeric^131068 - 1), 2); -- overflow 1347 1348-- 1349-- Tests for factorial 1350-- 1351SELECT factorial(4); 1352SELECT factorial(15); 1353SELECT factorial(100000); 1354SELECT factorial(0); 1355SELECT factorial(-4); 1356 1357-- 1358-- Tests for pg_lsn() 1359-- 1360SELECT pg_lsn(23783416::numeric); 1361SELECT pg_lsn(0::numeric); 1362SELECT pg_lsn(18446744073709551615::numeric); 1363SELECT pg_lsn(-1::numeric); 1364SELECT pg_lsn(18446744073709551616::numeric); 1365SELECT pg_lsn('NaN'::numeric); 1366