inputs/postgreSQL/numeric.sql

0001 --
0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
0003 --
0004 --
0005 -- NUMERIC
0006 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/numeric.sql
0007 --
0008
0009 -- [SPARK-28318] Decimal can only support precision up to 38. We rewrite numeric(210,10) to decimal(38,10).
0010 CREATE TABLE num_data (id int, val decimal(38,10)) USING parquet;
0011 CREATE TABLE num_exp_add (id1 int, id2 int, expected decimal(38,10)) USING parquet;
0012 CREATE TABLE num_exp_sub (id1 int, id2 int, expected decimal(38,10)) USING parquet;
0013 CREATE TABLE num_exp_div (id1 int, id2 int, expected decimal(38,10)) USING parquet;
0014 CREATE TABLE num_exp_mul (id1 int, id2 int, expected decimal(38,10)) USING parquet;
0015 CREATE TABLE num_exp_sqrt (id int, expected decimal(38,10)) USING parquet;
0016 CREATE TABLE num_exp_ln (id int, expected decimal(38,10)) USING parquet;
0017 CREATE TABLE num_exp_log10 (id int, expected decimal(38,10)) USING parquet;
0018 CREATE TABLE num_exp_power_10_ln (id int, expected decimal(38,10)) USING parquet;
0019
0020 CREATE TABLE num_result (id1 int, id2 int, result decimal(38,10)) USING parquet;
0021
0022
0023 -- ******************************
0024 -- * The following EXPECTED results are computed by bc(1)
0025 -- * with a scale of 200
0026 -- ******************************
0027
0028 -- BEGIN TRANSACTION;
0029 -- PostgreSQL implicitly casts string literals to data with decimal types, but
0030 -- Spark does not support that kind of implicit casts. To test all the INSERT queries below,
0031 -- we rewrote them into the other typed literals.
0032 INSERT INTO num_exp_add VALUES (0,0,0);
0033 INSERT INTO num_exp_sub VALUES (0,0,0);
0034 INSERT INTO num_exp_mul VALUES (0,0,0);
0035 -- [SPARK-28315] Decimal can not accept NaN as input
0036 INSERT INTO num_exp_div VALUES (0,0,double('NaN'));
0037 INSERT INTO num_exp_add VALUES (0,1,0);
0038 INSERT INTO num_exp_sub VALUES (0,1,0);
0039 INSERT INTO num_exp_mul VALUES (0,1,0);
0040 -- [SPARK-28315] Decimal can not accept NaN as input
0041 INSERT INTO num_exp_div VALUES (0,1,double('NaN'));
0042 INSERT INTO num_exp_add VALUES (0,2,-34338492.215397047);
0043 INSERT INTO num_exp_sub VALUES (0,2,34338492.215397047);
0044 INSERT INTO num_exp_mul VALUES (0,2,0);
0045 INSERT INTO num_exp_div VALUES (0,2,0);
0046 INSERT INTO num_exp_add VALUES (0,3,4.31);
0047 INSERT INTO num_exp_sub VALUES (0,3,-4.31);
0048 INSERT INTO num_exp_mul VALUES (0,3,0);
0049 INSERT INTO num_exp_div VALUES (0,3,0);
0050 INSERT INTO num_exp_add VALUES (0,4,7799461.4119);
0051 INSERT INTO num_exp_sub VALUES (0,4,-7799461.4119);
0052 INSERT INTO num_exp_mul VALUES (0,4,0);
0053 INSERT INTO num_exp_div VALUES (0,4,0);
0054 INSERT INTO num_exp_add VALUES (0,5,16397.038491);
0055 INSERT INTO num_exp_sub VALUES (0,5,-16397.038491);
0056 INSERT INTO num_exp_mul VALUES (0,5,0);
0057 INSERT INTO num_exp_div VALUES (0,5,0);
0058 INSERT INTO num_exp_add VALUES (0,6,93901.57763026);
0059 INSERT INTO num_exp_sub VALUES (0,6,-93901.57763026);
0060 INSERT INTO num_exp_mul VALUES (0,6,0);
0061 INSERT INTO num_exp_div VALUES (0,6,0);
0062 INSERT INTO num_exp_add VALUES (0,7,-83028485);
0063 INSERT INTO num_exp_sub VALUES (0,7,83028485);
0064 INSERT INTO num_exp_mul VALUES (0,7,0);
0065 INSERT INTO num_exp_div VALUES (0,7,0);
0066 INSERT INTO num_exp_add VALUES (0,8,74881);
0067 INSERT INTO num_exp_sub VALUES (0,8,-74881);
0068 INSERT INTO num_exp_mul VALUES (0,8,0);
0069 INSERT INTO num_exp_div VALUES (0,8,0);
0070 INSERT INTO num_exp_add VALUES (0,9,-24926804.045047420);
0071 INSERT INTO num_exp_sub VALUES (0,9,24926804.045047420);
0072 INSERT INTO num_exp_mul VALUES (0,9,0);
0073 INSERT INTO num_exp_div VALUES (0,9,0);
0074 INSERT INTO num_exp_add VALUES (1,0,0);
0075 INSERT INTO num_exp_sub VALUES (1,0,0);
0076 INSERT INTO num_exp_mul VALUES (1,0,0);
0077 -- [SPARK-28315] Decimal can not accept NaN as input
0078 INSERT INTO num_exp_div VALUES (1,0,double('NaN'));
0079 INSERT INTO num_exp_add VALUES (1,1,0);
0080 INSERT INTO num_exp_sub VALUES (1,1,0);
0081 INSERT INTO num_exp_mul VALUES (1,1,0);
0082 -- [SPARK-28315] Decimal can not accept NaN as input
0083 INSERT INTO num_exp_div VALUES (1,1,double('NaN'));
0084 INSERT INTO num_exp_add VALUES (1,2,-34338492.215397047);
0085 INSERT INTO num_exp_sub VALUES (1,2,34338492.215397047);
0086 INSERT INTO num_exp_mul VALUES (1,2,0);
0087 INSERT INTO num_exp_div VALUES (1,2,0);
0088 INSERT INTO num_exp_add VALUES (1,3,4.31);
0089 INSERT INTO num_exp_sub VALUES (1,3,-4.31);
0090 INSERT INTO num_exp_mul VALUES (1,3,0);
0091 INSERT INTO num_exp_div VALUES (1,3,0);
0092 INSERT INTO num_exp_add VALUES (1,4,7799461.4119);
0093 INSERT INTO num_exp_sub VALUES (1,4,-7799461.4119);
0094 INSERT INTO num_exp_mul VALUES (1,4,0);
0095 INSERT INTO num_exp_div VALUES (1,4,0);
0096 INSERT INTO num_exp_add VALUES (1,5,16397.038491);
0097 INSERT INTO num_exp_sub VALUES (1,5,-16397.038491);
0098 INSERT INTO num_exp_mul VALUES (1,5,0);
0099 INSERT INTO num_exp_div VALUES (1,5,0);
0100 INSERT INTO num_exp_add VALUES (1,6,93901.57763026);
0101 INSERT INTO num_exp_sub VALUES (1,6,-93901.57763026);
0102 INSERT INTO num_exp_mul VALUES (1,6,0);
0103 INSERT INTO num_exp_div VALUES (1,6,0);
0104 INSERT INTO num_exp_add VALUES (1,7,-83028485);
0105 INSERT INTO num_exp_sub VALUES (1,7,83028485);
0106 INSERT INTO num_exp_mul VALUES (1,7,0);
0107 INSERT INTO num_exp_div VALUES (1,7,0);
0108 INSERT INTO num_exp_add VALUES (1,8,74881);
0109 INSERT INTO num_exp_sub VALUES (1,8,-74881);
0110 INSERT INTO num_exp_mul VALUES (1,8,0);
0111 INSERT INTO num_exp_div VALUES (1,8,0);
0112 INSERT INTO num_exp_add VALUES (1,9,-24926804.045047420);
0113 INSERT INTO num_exp_sub VALUES (1,9,24926804.045047420);
0114 INSERT INTO num_exp_mul VALUES (1,9,0);
0115 INSERT INTO num_exp_div VALUES (1,9,0);
0116 INSERT INTO num_exp_add VALUES (2,0,-34338492.215397047);
0117 INSERT INTO num_exp_sub VALUES (2,0,-34338492.215397047);
0118 INSERT INTO num_exp_mul VALUES (2,0,0);
0119 -- [SPARK-28315] Decimal can not accept NaN as input
0120 INSERT INTO num_exp_div VALUES (2,0,double('NaN'));
0121 INSERT INTO num_exp_add VALUES (2,1,-34338492.215397047);
0122 INSERT INTO num_exp_sub VALUES (2,1,-34338492.215397047);
0123 INSERT INTO num_exp_mul VALUES (2,1,0);
0124 -- [SPARK-28315] Decimal can not accept NaN as input
0125 INSERT INTO num_exp_div VALUES (2,1,double('NaN'));
0126 INSERT INTO num_exp_add VALUES (2,2,-68676984.430794094);
0127 INSERT INTO num_exp_sub VALUES (2,2,0);
0128 INSERT INTO num_exp_mul VALUES (2,2,1179132047626883.596862135856320209);
0129 INSERT INTO num_exp_div VALUES (2,2,1.00000000000000000000);
0130 INSERT INTO num_exp_add VALUES (2,3,-34338487.905397047);
0131 INSERT INTO num_exp_sub VALUES (2,3,-34338496.525397047);
0132 INSERT INTO num_exp_mul VALUES (2,3,-147998901.44836127257);
0133 INSERT INTO num_exp_div VALUES (2,3,-7967167.56737750510440835266);
0134 INSERT INTO num_exp_add VALUES (2,4,-26539030.803497047);
0135 INSERT INTO num_exp_sub VALUES (2,4,-42137953.627297047);
0136 INSERT INTO num_exp_mul VALUES (2,4,-267821744976817.8111137106593);
0137 INSERT INTO num_exp_div VALUES (2,4,-4.40267480046830116685);
0138 INSERT INTO num_exp_add VALUES (2,5,-34322095.176906047);
0139 INSERT INTO num_exp_sub VALUES (2,5,-34354889.253888047);
0140 INSERT INTO num_exp_mul VALUES (2,5,-563049578578.769242506736077);
0141 INSERT INTO num_exp_div VALUES (2,5,-2094.18866914563535496429);
0142 INSERT INTO num_exp_add VALUES (2,6,-34244590.637766787);
0143 INSERT INTO num_exp_sub VALUES (2,6,-34432393.793027307);
0144 INSERT INTO num_exp_mul VALUES (2,6,-3224438592470.18449811926184222);
0145 INSERT INTO num_exp_div VALUES (2,6,-365.68599891479766440940);
0146 INSERT INTO num_exp_add VALUES (2,7,-117366977.215397047);
0147 INSERT INTO num_exp_sub VALUES (2,7,48689992.784602953);
0148 INSERT INTO num_exp_mul VALUES (2,7,2851072985828710.485883795);
0149 INSERT INTO num_exp_div VALUES (2,7,.41357483778485235518);
0150 INSERT INTO num_exp_add VALUES (2,8,-34263611.215397047);
0151 INSERT INTO num_exp_sub VALUES (2,8,-34413373.215397047);
0152 INSERT INTO num_exp_mul VALUES (2,8,-2571300635581.146276407);
0153 INSERT INTO num_exp_div VALUES (2,8,-458.57416721727870888476);
0154 INSERT INTO num_exp_add VALUES (2,9,-59265296.260444467);
0155 INSERT INTO num_exp_sub VALUES (2,9,-9411688.170349627);
0156 INSERT INTO num_exp_mul VALUES (2,9,855948866655588.453741509242968740);
0157 INSERT INTO num_exp_div VALUES (2,9,1.37757299946438931811);
0158 INSERT INTO num_exp_add VALUES (3,0,4.31);
0159 INSERT INTO num_exp_sub VALUES (3,0,4.31);
0160 INSERT INTO num_exp_mul VALUES (3,0,0);
0161 -- [SPARK-28315] Decimal can not accept NaN as input
0162 INSERT INTO num_exp_div VALUES (3,0,double('NaN'));
0163 INSERT INTO num_exp_add VALUES (3,1,4.31);
0164 INSERT INTO num_exp_sub VALUES (3,1,4.31);
0165 INSERT INTO num_exp_mul VALUES (3,1,0);
0166 -- [SPARK-28315] Decimal can not accept NaN as input
0167 INSERT INTO num_exp_div VALUES (3,1,double('NaN'));
0168 INSERT INTO num_exp_add VALUES (3,2,-34338487.905397047);
0169 INSERT INTO num_exp_sub VALUES (3,2,34338496.525397047);
0170 INSERT INTO num_exp_mul VALUES (3,2,-147998901.44836127257);
0171 INSERT INTO num_exp_div VALUES (3,2,-.00000012551512084352);
0172 INSERT INTO num_exp_add VALUES (3,3,8.62);
0173 INSERT INTO num_exp_sub VALUES (3,3,0);
0174 INSERT INTO num_exp_mul VALUES (3,3,18.5761);
0175 INSERT INTO num_exp_div VALUES (3,3,1.00000000000000000000);
0176 INSERT INTO num_exp_add VALUES (3,4,7799465.7219);
0177 INSERT INTO num_exp_sub VALUES (3,4,-7799457.1019);
0178 INSERT INTO num_exp_mul VALUES (3,4,33615678.685289);
0179 INSERT INTO num_exp_div VALUES (3,4,.00000055260225961552);
0180 INSERT INTO num_exp_add VALUES (3,5,16401.348491);
0181 INSERT INTO num_exp_sub VALUES (3,5,-16392.728491);
0182 INSERT INTO num_exp_mul VALUES (3,5,70671.23589621);
0183 INSERT INTO num_exp_div VALUES (3,5,.00026285234387695504);
0184 INSERT INTO num_exp_add VALUES (3,6,93905.88763026);
0185 INSERT INTO num_exp_sub VALUES (3,6,-93897.26763026);
0186 INSERT INTO num_exp_mul VALUES (3,6,404715.7995864206);
0187 INSERT INTO num_exp_div VALUES (3,6,.00004589912234457595);
0188 INSERT INTO num_exp_add VALUES (3,7,-83028480.69);
0189 INSERT INTO num_exp_sub VALUES (3,7,83028489.31);
0190 INSERT INTO num_exp_mul VALUES (3,7,-357852770.35);
0191 INSERT INTO num_exp_div VALUES (3,7,-.00000005190989574240);
0192 INSERT INTO num_exp_add VALUES (3,8,74885.31);
0193 INSERT INTO num_exp_sub VALUES (3,8,-74876.69);
0194 INSERT INTO num_exp_mul VALUES (3,8,322737.11);
0195 INSERT INTO num_exp_div VALUES (3,8,.00005755799201399553);
0196 INSERT INTO num_exp_add VALUES (3,9,-24926799.735047420);
0197 INSERT INTO num_exp_sub VALUES (3,9,24926808.355047420);
0198 INSERT INTO num_exp_mul VALUES (3,9,-107434525.43415438020);
0199 INSERT INTO num_exp_div VALUES (3,9,-.00000017290624149854);
0200 INSERT INTO num_exp_add VALUES (4,0,7799461.4119);
0201 INSERT INTO num_exp_sub VALUES (4,0,7799461.4119);
0202 INSERT INTO num_exp_mul VALUES (4,0,0);
0203 -- [SPARK-28315] Decimal can not accept NaN as input
0204 INSERT INTO num_exp_div VALUES (4,0,double('NaN'));
0205 INSERT INTO num_exp_add VALUES (4,1,7799461.4119);
0206 INSERT INTO num_exp_sub VALUES (4,1,7799461.4119);
0207 INSERT INTO num_exp_mul VALUES (4,1,0);
0208 -- [SPARK-28315] Decimal can not accept NaN as input
0209 INSERT INTO num_exp_div VALUES (4,1,double('NaN'));
0210 INSERT INTO num_exp_add VALUES (4,2,-26539030.803497047);
0211 INSERT INTO num_exp_sub VALUES (4,2,42137953.627297047);
0212 INSERT INTO num_exp_mul VALUES (4,2,-267821744976817.8111137106593);
0213 INSERT INTO num_exp_div VALUES (4,2,-.22713465002993920385);
0214 INSERT INTO num_exp_add VALUES (4,3,7799465.7219);
0215 INSERT INTO num_exp_sub VALUES (4,3,7799457.1019);
0216 INSERT INTO num_exp_mul VALUES (4,3,33615678.685289);
0217 INSERT INTO num_exp_div VALUES (4,3,1809619.81714617169373549883);
0218 INSERT INTO num_exp_add VALUES (4,4,15598922.8238);
0219 INSERT INTO num_exp_sub VALUES (4,4,0);
0220 INSERT INTO num_exp_mul VALUES (4,4,60831598315717.14146161);
0221 INSERT INTO num_exp_div VALUES (4,4,1.00000000000000000000);
0222 INSERT INTO num_exp_add VALUES (4,5,7815858.450391);
0223 INSERT INTO num_exp_sub VALUES (4,5,7783064.373409);
0224 INSERT INTO num_exp_mul VALUES (4,5,127888068979.9935054429);
0225 INSERT INTO num_exp_div VALUES (4,5,475.66281046305802686061);
0226 INSERT INTO num_exp_add VALUES (4,6,7893362.98953026);
0227 INSERT INTO num_exp_sub VALUES (4,6,7705559.83426974);
0228 INSERT INTO num_exp_mul VALUES (4,6,732381731243.745115764094);
0229 INSERT INTO num_exp_div VALUES (4,6,83.05996138436129499606);
0230 INSERT INTO num_exp_add VALUES (4,7,-75229023.5881);
0231 INSERT INTO num_exp_sub VALUES (4,7,90827946.4119);
0232 INSERT INTO num_exp_mul VALUES (4,7,-647577464846017.9715);
0233 INSERT INTO num_exp_div VALUES (4,7,-.09393717604145131637);
0234 INSERT INTO num_exp_add VALUES (4,8,7874342.4119);
0235 INSERT INTO num_exp_sub VALUES (4,8,7724580.4119);
0236 INSERT INTO num_exp_mul VALUES (4,8,584031469984.4839);
0237 INSERT INTO num_exp_div VALUES (4,8,104.15808298366741897143);
0238 INSERT INTO num_exp_add VALUES (4,9,-17127342.633147420);
0239 INSERT INTO num_exp_sub VALUES (4,9,32726265.456947420);
0240 INSERT INTO num_exp_mul VALUES (4,9,-194415646271340.1815956522980);
0241 INSERT INTO num_exp_div VALUES (4,9,-.31289456112403769409);
0242 INSERT INTO num_exp_add VALUES (5,0,16397.038491);
0243 INSERT INTO num_exp_sub VALUES (5,0,16397.038491);
0244 INSERT INTO num_exp_mul VALUES (5,0,0);
0245 -- [SPARK-28315] Decimal can not accept NaN as input
0246 INSERT INTO num_exp_div VALUES (5,0,double('NaN'));
0247 INSERT INTO num_exp_add VALUES (5,1,16397.038491);
0248 INSERT INTO num_exp_sub VALUES (5,1,16397.038491);
0249 INSERT INTO num_exp_mul VALUES (5,1,0);
0250 -- [SPARK-28315] Decimal can not accept NaN as input
0251 INSERT INTO num_exp_div VALUES (5,1,double('NaN'));
0252 INSERT INTO num_exp_add VALUES (5,2,-34322095.176906047);
0253 INSERT INTO num_exp_sub VALUES (5,2,34354889.253888047);
0254 INSERT INTO num_exp_mul VALUES (5,2,-563049578578.769242506736077);
0255 INSERT INTO num_exp_div VALUES (5,2,-.00047751189505192446);
0256 INSERT INTO num_exp_add VALUES (5,3,16401.348491);
0257 INSERT INTO num_exp_sub VALUES (5,3,16392.728491);
0258 INSERT INTO num_exp_mul VALUES (5,3,70671.23589621);
0259 INSERT INTO num_exp_div VALUES (5,3,3804.41728329466357308584);
0260 INSERT INTO num_exp_add VALUES (5,4,7815858.450391);
0261 INSERT INTO num_exp_sub VALUES (5,4,-7783064.373409);
0262 INSERT INTO num_exp_mul VALUES (5,4,127888068979.9935054429);
0263 INSERT INTO num_exp_div VALUES (5,4,.00210232958726897192);
0264 INSERT INTO num_exp_add VALUES (5,5,32794.076982);
0265 INSERT INTO num_exp_sub VALUES (5,5,0);
0266 INSERT INTO num_exp_mul VALUES (5,5,268862871.275335557081);
0267 INSERT INTO num_exp_div VALUES (5,5,1.00000000000000000000);
0268 INSERT INTO num_exp_add VALUES (5,6,110298.61612126);
0269 INSERT INTO num_exp_sub VALUES (5,6,-77504.53913926);
0270 INSERT INTO num_exp_mul VALUES (5,6,1539707782.76899778633766);
0271 INSERT INTO num_exp_div VALUES (5,6,.17461941433576102689);
0272 INSERT INTO num_exp_add VALUES (5,7,-83012087.961509);
0273 INSERT INTO num_exp_sub VALUES (5,7,83044882.038491);
0274 INSERT INTO num_exp_mul VALUES (5,7,-1361421264394.416135);
0275 INSERT INTO num_exp_div VALUES (5,7,-.00019748690453643710);
0276 INSERT INTO num_exp_add VALUES (5,8,91278.038491);
0277 INSERT INTO num_exp_sub VALUES (5,8,-58483.961509);
0278 INSERT INTO num_exp_mul VALUES (5,8,1227826639.244571);
0279 INSERT INTO num_exp_div VALUES (5,8,.21897461960978085228);
0280 INSERT INTO num_exp_add VALUES (5,9,-24910407.006556420);
0281 INSERT INTO num_exp_sub VALUES (5,9,24943201.083538420);
0282 INSERT INTO num_exp_mul VALUES (5,9,-408725765384.257043660243220);
0283 INSERT INTO num_exp_div VALUES (5,9,-.00065780749354660427);
0284 INSERT INTO num_exp_add VALUES (6,0,93901.57763026);
0285 INSERT INTO num_exp_sub VALUES (6,0,93901.57763026);
0286 INSERT INTO num_exp_mul VALUES (6,0,0);
0287 -- [SPARK-28315] Decimal can not accept NaN as input
0288 INSERT INTO num_exp_div VALUES (6,0,double('NaN'));
0289 INSERT INTO num_exp_add VALUES (6,1,93901.57763026);
0290 INSERT INTO num_exp_sub VALUES (6,1,93901.57763026);
0291 INSERT INTO num_exp_mul VALUES (6,1,0);
0292 -- [SPARK-28315] Decimal can not accept NaN as input
0293 INSERT INTO num_exp_div VALUES (6,1,double('NaN'));
0294 INSERT INTO num_exp_add VALUES (6,2,-34244590.637766787);
0295 INSERT INTO num_exp_sub VALUES (6,2,34432393.793027307);
0296 INSERT INTO num_exp_mul VALUES (6,2,-3224438592470.18449811926184222);
0297 INSERT INTO num_exp_div VALUES (6,2,-.00273458651128995823);
0298 INSERT INTO num_exp_add VALUES (6,3,93905.88763026);
0299 INSERT INTO num_exp_sub VALUES (6,3,93897.26763026);
0300 INSERT INTO num_exp_mul VALUES (6,3,404715.7995864206);
0301 INSERT INTO num_exp_div VALUES (6,3,21786.90896293735498839907);
0302 INSERT INTO num_exp_add VALUES (6,4,7893362.98953026);
0303 INSERT INTO num_exp_sub VALUES (6,4,-7705559.83426974);
0304 INSERT INTO num_exp_mul VALUES (6,4,732381731243.745115764094);
0305 INSERT INTO num_exp_div VALUES (6,4,.01203949512295682469);
0306 INSERT INTO num_exp_add VALUES (6,5,110298.61612126);
0307 INSERT INTO num_exp_sub VALUES (6,5,77504.53913926);
0308 INSERT INTO num_exp_mul VALUES (6,5,1539707782.76899778633766);
0309 INSERT INTO num_exp_div VALUES (6,5,5.72674008674192359679);
0310 INSERT INTO num_exp_add VALUES (6,6,187803.15526052);
0311 INSERT INTO num_exp_sub VALUES (6,6,0);
0312 INSERT INTO num_exp_mul VALUES (6,6,8817506281.4517452372676676);
0313 INSERT INTO num_exp_div VALUES (6,6,1.00000000000000000000);
0314 INSERT INTO num_exp_add VALUES (6,7,-82934583.42236974);
0315 INSERT INTO num_exp_sub VALUES (6,7,83122386.57763026);
0316 INSERT INTO num_exp_mul VALUES (6,7,-7796505729750.37795610);
0317 INSERT INTO num_exp_div VALUES (6,7,-.00113095617281538980);
0318 INSERT INTO num_exp_add VALUES (6,8,168782.57763026);
0319 INSERT INTO num_exp_sub VALUES (6,8,19020.57763026);
0320 INSERT INTO num_exp_mul VALUES (6,8,7031444034.53149906);
0321 INSERT INTO num_exp_div VALUES (6,8,1.25401073209839612184);
0322 INSERT INTO num_exp_add VALUES (6,9,-24832902.467417160);
0323 INSERT INTO num_exp_sub VALUES (6,9,25020705.622677680);
0324 INSERT INTO num_exp_mul VALUES (6,9,-2340666225110.29929521292692920);
0325 INSERT INTO num_exp_div VALUES (6,9,-.00376709254265256789);
0326 INSERT INTO num_exp_add VALUES (7,0,-83028485);
0327 INSERT INTO num_exp_sub VALUES (7,0,-83028485);
0328 INSERT INTO num_exp_mul VALUES (7,0,0);
0329 -- [SPARK-28315] Decimal can not accept NaN as input
0330 INSERT INTO num_exp_div VALUES (7,0,double('NaN'));
0331 INSERT INTO num_exp_add VALUES (7,1,-83028485);
0332 INSERT INTO num_exp_sub VALUES (7,1,-83028485);
0333 INSERT INTO num_exp_mul VALUES (7,1,0);
0334 -- [SPARK-28315] Decimal can not accept NaN as input
0335 INSERT INTO num_exp_div VALUES (7,1,double('NaN'));
0336 INSERT INTO num_exp_add VALUES (7,2,-117366977.215397047);
0337 INSERT INTO num_exp_sub VALUES (7,2,-48689992.784602953);
0338 INSERT INTO num_exp_mul VALUES (7,2,2851072985828710.485883795);
0339 INSERT INTO num_exp_div VALUES (7,2,2.41794207151503385700);
0340 INSERT INTO num_exp_add VALUES (7,3,-83028480.69);
0341 INSERT INTO num_exp_sub VALUES (7,3,-83028489.31);
0342 INSERT INTO num_exp_mul VALUES (7,3,-357852770.35);
0343 INSERT INTO num_exp_div VALUES (7,3,-19264149.65197215777262180974);
0344 INSERT INTO num_exp_add VALUES (7,4,-75229023.5881);
0345 INSERT INTO num_exp_sub VALUES (7,4,-90827946.4119);
0346 INSERT INTO num_exp_mul VALUES (7,4,-647577464846017.9715);
0347 INSERT INTO num_exp_div VALUES (7,4,-10.64541262725136247686);
0348 INSERT INTO num_exp_add VALUES (7,5,-83012087.961509);
0349 INSERT INTO num_exp_sub VALUES (7,5,-83044882.038491);
0350 INSERT INTO num_exp_mul VALUES (7,5,-1361421264394.416135);
0351 INSERT INTO num_exp_div VALUES (7,5,-5063.62688881730941836574);
0352 INSERT INTO num_exp_add VALUES (7,6,-82934583.42236974);
0353 INSERT INTO num_exp_sub VALUES (7,6,-83122386.57763026);
0354 INSERT INTO num_exp_mul VALUES (7,6,-7796505729750.37795610);
0355 INSERT INTO num_exp_div VALUES (7,6,-884.20756174009028770294);
0356 INSERT INTO num_exp_add VALUES (7,7,-166056970);
0357 INSERT INTO num_exp_sub VALUES (7,7,0);
0358 INSERT INTO num_exp_mul VALUES (7,7,6893729321395225);
0359 INSERT INTO num_exp_div VALUES (7,7,1.00000000000000000000);
0360 INSERT INTO num_exp_add VALUES (7,8,-82953604);
0361 INSERT INTO num_exp_sub VALUES (7,8,-83103366);
0362 INSERT INTO num_exp_mul VALUES (7,8,-6217255985285);
0363 INSERT INTO num_exp_div VALUES (7,8,-1108.80577182462841041118);
0364 INSERT INTO num_exp_add VALUES (7,9,-107955289.045047420);
0365 INSERT INTO num_exp_sub VALUES (7,9,-58101680.954952580);
0366 INSERT INTO num_exp_mul VALUES (7,9,2069634775752159.035758700);
0367 INSERT INTO num_exp_div VALUES (7,9,3.33089171198810413382);
0368 INSERT INTO num_exp_add VALUES (8,0,74881);
0369 INSERT INTO num_exp_sub VALUES (8,0,74881);
0370 INSERT INTO num_exp_mul VALUES (8,0,0);
0371 -- [SPARK-28315] Decimal can not accept NaN as input
0372 INSERT INTO num_exp_div VALUES (8,0,double('NaN'));
0373 INSERT INTO num_exp_add VALUES (8,1,74881);
0374 INSERT INTO num_exp_sub VALUES (8,1,74881);
0375 INSERT INTO num_exp_mul VALUES (8,1,0);
0376 -- [SPARK-28315] Decimal can not accept NaN as input
0377 INSERT INTO num_exp_div VALUES (8,1,double('NaN'));
0378 INSERT INTO num_exp_add VALUES (8,2,-34263611.215397047);
0379 INSERT INTO num_exp_sub VALUES (8,2,34413373.215397047);
0380 INSERT INTO num_exp_mul VALUES (8,2,-2571300635581.146276407);
0381 INSERT INTO num_exp_div VALUES (8,2,-.00218067233500788615);
0382 INSERT INTO num_exp_add VALUES (8,3,74885.31);
0383 INSERT INTO num_exp_sub VALUES (8,3,74876.69);
0384 INSERT INTO num_exp_mul VALUES (8,3,322737.11);
0385 INSERT INTO num_exp_div VALUES (8,3,17373.78190255220417633410);
0386 INSERT INTO num_exp_add VALUES (8,4,7874342.4119);
0387 INSERT INTO num_exp_sub VALUES (8,4,-7724580.4119);
0388 INSERT INTO num_exp_mul VALUES (8,4,584031469984.4839);
0389 INSERT INTO num_exp_div VALUES (8,4,.00960079113741758956);
0390 INSERT INTO num_exp_add VALUES (8,5,91278.038491);
0391 INSERT INTO num_exp_sub VALUES (8,5,58483.961509);
0392 INSERT INTO num_exp_mul VALUES (8,5,1227826639.244571);
0393 INSERT INTO num_exp_div VALUES (8,5,4.56673929509287019456);
0394 INSERT INTO num_exp_add VALUES (8,6,168782.57763026);
0395 INSERT INTO num_exp_sub VALUES (8,6,-19020.57763026);
0396 INSERT INTO num_exp_mul VALUES (8,6,7031444034.53149906);
0397 INSERT INTO num_exp_div VALUES (8,6,.79744134113322314424);
0398 INSERT INTO num_exp_add VALUES (8,7,-82953604);
0399 INSERT INTO num_exp_sub VALUES (8,7,83103366);
0400 INSERT INTO num_exp_mul VALUES (8,7,-6217255985285);
0401 INSERT INTO num_exp_div VALUES (8,7,-.00090187120721280172);
0402 INSERT INTO num_exp_add VALUES (8,8,149762);
0403 INSERT INTO num_exp_sub VALUES (8,8,0);
0404 INSERT INTO num_exp_mul VALUES (8,8,5607164161);
0405 INSERT INTO num_exp_div VALUES (8,8,1.00000000000000000000);
0406 INSERT INTO num_exp_add VALUES (8,9,-24851923.045047420);
0407 INSERT INTO num_exp_sub VALUES (8,9,25001685.045047420);
0408 INSERT INTO num_exp_mul VALUES (8,9,-1866544013697.195857020);
0409 INSERT INTO num_exp_div VALUES (8,9,-.00300403532938582735);
0410 INSERT INTO num_exp_add VALUES (9,0,-24926804.045047420);
0411 INSERT INTO num_exp_sub VALUES (9,0,-24926804.045047420);
0412 INSERT INTO num_exp_mul VALUES (9,0,0);
0413 -- [SPARK-28315] Decimal can not accept NaN as input
0414 INSERT INTO num_exp_div VALUES (9,0,double('NaN'));
0415 INSERT INTO num_exp_add VALUES (9,1,-24926804.045047420);
0416 INSERT INTO num_exp_sub VALUES (9,1,-24926804.045047420);
0417 INSERT INTO num_exp_mul VALUES (9,1,0);
0418 -- [SPARK-28315] Decimal can not accept NaN as input
0419 INSERT INTO num_exp_div VALUES (9,1,double('NaN'));
0420 INSERT INTO num_exp_add VALUES (9,2,-59265296.260444467);
0421 INSERT INTO num_exp_sub VALUES (9,2,9411688.170349627);
0422 INSERT INTO num_exp_mul VALUES (9,2,855948866655588.453741509242968740);
0423 INSERT INTO num_exp_div VALUES (9,2,.72591434384152961526);
0424 INSERT INTO num_exp_add VALUES (9,3,-24926799.735047420);
0425 INSERT INTO num_exp_sub VALUES (9,3,-24926808.355047420);
0426 INSERT INTO num_exp_mul VALUES (9,3,-107434525.43415438020);
0427 INSERT INTO num_exp_div VALUES (9,3,-5783481.21694835730858468677);
0428 INSERT INTO num_exp_add VALUES (9,4,-17127342.633147420);
0429 INSERT INTO num_exp_sub VALUES (9,4,-32726265.456947420);
0430 INSERT INTO num_exp_mul VALUES (9,4,-194415646271340.1815956522980);
0431 INSERT INTO num_exp_div VALUES (9,4,-3.19596478892958416484);
0432 INSERT INTO num_exp_add VALUES (9,5,-24910407.006556420);
0433 INSERT INTO num_exp_sub VALUES (9,5,-24943201.083538420);
0434 INSERT INTO num_exp_mul VALUES (9,5,-408725765384.257043660243220);
0435 INSERT INTO num_exp_div VALUES (9,5,-1520.20159364322004505807);
0436 INSERT INTO num_exp_add VALUES (9,6,-24832902.467417160);
0437 INSERT INTO num_exp_sub VALUES (9,6,-25020705.622677680);
0438 INSERT INTO num_exp_mul VALUES (9,6,-2340666225110.29929521292692920);
0439 INSERT INTO num_exp_div VALUES (9,6,-265.45671195426965751280);
0440 INSERT INTO num_exp_add VALUES (9,7,-107955289.045047420);
0441 INSERT INTO num_exp_sub VALUES (9,7,58101680.954952580);
0442 INSERT INTO num_exp_mul VALUES (9,7,2069634775752159.035758700);
0443 INSERT INTO num_exp_div VALUES (9,7,.30021990699995814689);
0444 INSERT INTO num_exp_add VALUES (9,8,-24851923.045047420);
0445 INSERT INTO num_exp_sub VALUES (9,8,-25001685.045047420);
0446 INSERT INTO num_exp_mul VALUES (9,8,-1866544013697.195857020);
0447 INSERT INTO num_exp_div VALUES (9,8,-332.88556569820675471748);
0448 INSERT INTO num_exp_add VALUES (9,9,-49853608.090094840);
0449 INSERT INTO num_exp_sub VALUES (9,9,0);
0450 INSERT INTO num_exp_mul VALUES (9,9,621345559900192.420120630048656400);
0451 INSERT INTO num_exp_div VALUES (9,9,1.00000000000000000000);
0452 -- COMMIT TRANSACTION;
0453 -- BEGIN TRANSACTION;
0454 -- PostgreSQL implicitly casts string literals to data with decimal types, but
0455 -- Spark does not support that kind of implicit casts. To test all the INSERT queries below,
0456 -- we rewrote them into the other typed literals.
0457 INSERT INTO num_exp_sqrt VALUES (0,0);
0458 INSERT INTO num_exp_sqrt VALUES (1,0);
0459 INSERT INTO num_exp_sqrt VALUES (2,5859.90547836712524903505);
0460 INSERT INTO num_exp_sqrt VALUES (3,2.07605394920266944396);
0461 INSERT INTO num_exp_sqrt VALUES (4,2792.75158435189147418923);
0462 INSERT INTO num_exp_sqrt VALUES (5,128.05092147657509145473);
0463 INSERT INTO num_exp_sqrt VALUES (6,306.43364311096782703406);
0464 INSERT INTO num_exp_sqrt VALUES (7,9111.99676251039939975230);
0465 INSERT INTO num_exp_sqrt VALUES (8,273.64392922189960397542);
0466 INSERT INTO num_exp_sqrt VALUES (9,4992.67503899937593364766);
0467 -- COMMIT TRANSACTION;
0468 -- BEGIN TRANSACTION;
0469 -- PostgreSQL implicitly casts string literals to data with decimal types, but
0470 -- Spark does not support that kind of implicit casts. To test all the INSERT queries below,
0471 -- we rewrote them into the other typed literals.
0472 -- [SPARK-28315] Decimal can not accept NaN as input
0473 INSERT INTO num_exp_ln VALUES (0,double('NaN'));
0474 INSERT INTO num_exp_ln VALUES (1,double('NaN'));
0475 INSERT INTO num_exp_ln VALUES (2,17.35177750493897715514);
0476 INSERT INTO num_exp_ln VALUES (3,1.46093790411565641971);
0477 INSERT INTO num_exp_ln VALUES (4,15.86956523951936572464);
0478 INSERT INTO num_exp_ln VALUES (5,9.70485601768871834038);
0479 INSERT INTO num_exp_ln VALUES (6,11.45000246622944403127);
0480 INSERT INTO num_exp_ln VALUES (7,18.23469429965478772991);
0481 INSERT INTO num_exp_ln VALUES (8,11.22365546576315513668);
0482 INSERT INTO num_exp_ln VALUES (9,17.03145425013166006962);
0483 -- COMMIT TRANSACTION;
0484 -- BEGIN TRANSACTION;
0485 -- PostgreSQL implicitly casts string literals to data with decimal types, but
0486 -- Spark does not support that kind of implicit casts. To test all the INSERT queries below,
0487 -- we rewrote them into the other typed literals.
0488 -- [SPARK-28315] Decimal can not accept NaN as input
0489 INSERT INTO num_exp_log10 VALUES (0,double('NaN'));
0490 INSERT INTO num_exp_log10 VALUES (1,double('NaN'));
0491 INSERT INTO num_exp_log10 VALUES (2,7.53578122160797276459);
0492 INSERT INTO num_exp_log10 VALUES (3,.63447727016073160075);
0493 INSERT INTO num_exp_log10 VALUES (4,6.89206461372691743345);
0494 INSERT INTO num_exp_log10 VALUES (5,4.21476541614777768626);
0495 INSERT INTO num_exp_log10 VALUES (6,4.97267288886207207671);
0496 INSERT INTO num_exp_log10 VALUES (7,7.91922711353275546914);
0497 INSERT INTO num_exp_log10 VALUES (8,4.87437163556421004138);
0498 INSERT INTO num_exp_log10 VALUES (9,7.39666659961986567059);
0499 -- COMMIT TRANSACTION;
0500 -- BEGIN TRANSACTION;
0501 -- PostgreSQL implicitly casts string literals to data with decimal types, but
0502 -- Spark does not support that kind of implicit casts. To test all the INSERT queries below,
0503 -- we rewrote them into the other typed literals.
0504 -- [SPARK-28315] Decimal can not accept NaN as input
0505 INSERT INTO num_exp_power_10_ln VALUES (0,double('NaN'));
0506 INSERT INTO num_exp_power_10_ln VALUES (1,double('NaN'));
0507 INSERT INTO num_exp_power_10_ln VALUES (2,224790267919917955.13261618583642653184);
0508 INSERT INTO num_exp_power_10_ln VALUES (3,28.90266599445155957393);
0509 INSERT INTO num_exp_power_10_ln VALUES (4,7405685069594999.07733999469386277636);
0510 INSERT INTO num_exp_power_10_ln VALUES (5,5068226527.32127265408584640098);
0511 INSERT INTO num_exp_power_10_ln VALUES (6,281839893606.99372343357047819067);
0512 -- In Spark, decimal can only support precision up to 38
0513 INSERT INTO num_exp_power_10_ln VALUES (7,1716699575118597095.42330819910640247627);
0514 INSERT INTO num_exp_power_10_ln VALUES (8,167361463828.07491320069016125952);
0515 INSERT INTO num_exp_power_10_ln VALUES (9,107511333880052007.04141124673540337457);
0516 -- COMMIT TRANSACTION;
0517 -- BEGIN TRANSACTION;
0518 -- PostgreSQL implicitly casts string literals to data with decimal types, but
0519 -- Spark does not support that kind of implicit casts. To test all the INSERT queries below,
0520 -- we rewrote them into the other typed literals.
0521 INSERT INTO num_data VALUES (0, 0);
0522 INSERT INTO num_data VALUES (1, 0);
0523 INSERT INTO num_data VALUES (2, -34338492.215397047);
0524 INSERT INTO num_data VALUES (3, 4.31);
0525 INSERT INTO num_data VALUES (4, 7799461.4119);
0526 INSERT INTO num_data VALUES (5, 16397.038491);
0527 INSERT INTO num_data VALUES (6, 93901.57763026);
0528 INSERT INTO num_data VALUES (7, -83028485);
0529 INSERT INTO num_data VALUES (8, 74881);
0530 INSERT INTO num_data VALUES (9, -24926804.045047420);
0531 -- COMMIT TRANSACTION;
0532
0533 SELECT * FROM num_data;
0534
0535 -- ******************************
0536 -- * Create indices for faster checks
0537 -- ******************************
0538
0539 -- CREATE UNIQUE INDEX num_exp_add_idx ON num_exp_add (id1, id2);
0540 -- CREATE UNIQUE INDEX num_exp_sub_idx ON num_exp_sub (id1, id2);
0541 -- CREATE UNIQUE INDEX num_exp_div_idx ON num_exp_div (id1, id2);
0542 -- CREATE UNIQUE INDEX num_exp_mul_idx ON num_exp_mul (id1, id2);
0543 -- CREATE UNIQUE INDEX num_exp_sqrt_idx ON num_exp_sqrt (id);
0544 -- CREATE UNIQUE INDEX num_exp_ln_idx ON num_exp_ln (id);
0545 -- CREATE UNIQUE INDEX num_exp_log10_idx ON num_exp_log10 (id);
0546 -- CREATE UNIQUE INDEX num_exp_power_10_ln_idx ON num_exp_power_10_ln (id);
0547
0548 -- VACUUM ANALYZE num_exp_add;
0549 -- VACUUM ANALYZE num_exp_sub;
0550 -- VACUUM ANALYZE num_exp_div;
0551 -- VACUUM ANALYZE num_exp_mul;
0552 -- VACUUM ANALYZE num_exp_sqrt;
0553 -- VACUUM ANALYZE num_exp_ln;
0554 -- VACUUM ANALYZE num_exp_log10;
0555 -- VACUUM ANALYZE num_exp_power_10_ln;
0556
0557 -- ******************************
0558 -- * Now check the behaviour of the NUMERIC type
0559 -- ******************************
0560
0561 -- ******************************
0562 -- * Addition check
0563 -- ******************************
0564 TRUNCATE TABLE num_result;
0565 INSERT INTO num_result SELECT t1.id, t2.id, t1.val + t2.val
0566     FROM num_data t1, num_data t2;
0567 SELECT t1.id1, t1.id2, t1.result, t2.expected
0568     FROM num_result t1, num_exp_add t2
0569     WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
0570     AND t1.result != t2.expected;
0571
0572 TRUNCATE TABLE num_result;
0573 INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val + t2.val, 10)
0574     FROM num_data t1, num_data t2;
0575 SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 10) as expected
0576     FROM num_result t1, num_exp_add t2
0577     WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
0578     AND t1.result != round(t2.expected, 10);
0579
0580 -- ******************************
0581 -- * Subtraction check
0582 -- ******************************
0583 TRUNCATE TABLE num_result;
0584 INSERT INTO num_result SELECT t1.id, t2.id, t1.val - t2.val
0585     FROM num_data t1, num_data t2;
0586 SELECT t1.id1, t1.id2, t1.result, t2.expected
0587     FROM num_result t1, num_exp_sub t2
0588     WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
0589     AND t1.result != t2.expected;
0590
0591 TRUNCATE TABLE num_result;
0592 INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val - t2.val, 40)
0593     FROM num_data t1, num_data t2;
0594 SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 40)
0595     FROM num_result t1, num_exp_sub t2
0596     WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
0597     AND t1.result != round(t2.expected, 40);
0598
0599 -- ******************************
0600 -- * Multiply check
0601 -- ******************************
0602 -- [SPARK-28316] Decimal precision issue
0603 TRUNCATE TABLE num_result;
0604 INSERT INTO num_result SELECT t1.id, t2.id, t1.val, t2.val, t1.val * t2.val
0605     FROM num_data t1, num_data t2;
0606 SELECT t1.id1, t1.id2, t1.result, t2.expected
0607     FROM num_result t1, num_exp_mul t2
0608     WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
0609     AND t1.result != t2.expected;
0610
0611 TRUNCATE TABLE num_result;
0612 INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val * t2.val, 30)
0613     FROM num_data t1, num_data t2;
0614 SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 30) as expected
0615     FROM num_result t1, num_exp_mul t2
0616     WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
0617     AND t1.result != round(t2.expected, 30);
0618
0619 -- ******************************
0620 -- * Division check
0621 -- ******************************
0622 -- [SPARK-28316] Decimal precision issue
0623 TRUNCATE TABLE num_result;
0624 INSERT INTO num_result SELECT t1.id, t2.id, t1.val / t2.val
0625     FROM num_data t1, num_data t2
0626     WHERE t2.val != '0.0';
0627 SELECT t1.id1, t1.id2, t1.result, t2.expected
0628     FROM num_result t1, num_exp_div t2
0629     WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
0630     AND t1.result != t2.expected;
0631
0632 TRUNCATE TABLE num_result;
0633 INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val / t2.val, 80)
0634     FROM num_data t1, num_data t2
0635     WHERE t2.val != '0.0';
0636 SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 80) as expected
0637     FROM num_result t1, num_exp_div t2
0638     WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
0639     AND t1.result != round(t2.expected, 80);
0640
0641 -- ******************************
0642 -- * Square root check
0643 -- ******************************
0644 TRUNCATE TABLE num_result;
0645 INSERT INTO num_result SELECT id, 0, SQRT(ABS(val))
0646     FROM num_data;
0647 SELECT t1.id1, t1.result, t2.expected
0648     FROM num_result t1, num_exp_sqrt t2
0649     WHERE t1.id1 = t2.id
0650     AND t1.result != t2.expected;
0651
0652 -- ******************************
0653 -- * Natural logarithm check
0654 -- ******************************
0655 TRUNCATE TABLE num_result;
0656 INSERT INTO num_result SELECT id, 0, LN(ABS(val))
0657     FROM num_data
0658     WHERE val != '0.0';
0659 SELECT t1.id1, t1.result, t2.expected
0660     FROM num_result t1, num_exp_ln t2
0661     WHERE t1.id1 = t2.id
0662     AND t1.result != t2.expected;
0663
0664 -- ******************************
0665 -- * Logarithm base 10 check
0666 -- ******************************
0667 TRUNCATE TABLE num_result;
0668 INSERT INTO num_result SELECT id, 0, LOG(cast('10' as decimal(38, 18)), ABS(val))
0669     FROM num_data
0670     WHERE val != '0.0';
0671 SELECT t1.id1, t1.result, t2.expected
0672     FROM num_result t1, num_exp_log10 t2
0673     WHERE t1.id1 = t2.id
0674     AND t1.result != t2.expected;
0675
0676 -- ******************************
0677 -- * POWER(10, LN(value)) check
0678 -- ******************************
0679 -- [SPARK-28316] Decimal precision issue
0680 TRUNCATE TABLE num_result;
0681 INSERT INTO num_result SELECT id, 0, POWER(cast('10' as decimal(38, 18)), LN(ABS(round(val,200))))
0682     FROM num_data
0683     WHERE val != '0.0';
0684 SELECT t1.id1, t1.result, t2.expected
0685     FROM num_result t1, num_exp_power_10_ln t2
0686     WHERE t1.id1 = t2.id
0687     AND t1.result != t2.expected;
0688
0689 -- ******************************
0690 -- * miscellaneous checks for things that have been broken in the past...
0691 -- ******************************
0692 -- numeric AVG used to fail on some platforms
0693 SELECT AVG(val) FROM num_data;
0694 -- [SPARK-28316] STDDEV and VARIANCE returns double type
0695 -- Skip it because: Expected "2.779120328758835[]E7", but got "2.779120328758835[4]E7"
0696 -- SELECT STDDEV(val) FROM num_data;
0697 -- Skip it because: Expected "7.72350980172061[8]E14", but got "7.72350980172061[6]E14"
0698 -- SELECT VARIANCE(val) FROM num_data;
0699
0700 -- Check for appropriate rounding and overflow
0701 CREATE TABLE fract_only (id int, val decimal(4,4)) USING parquet;
0702 INSERT INTO fract_only VALUES (1, 0.0);
0703 INSERT INTO fract_only VALUES (2, 0.1);
0704 -- [SPARK-27923] PostgreSQL throws an exception but Spark SQL is NULL
0705 -- INSERT INTO fract_only VALUES (3, '1.0');    -- should fail
0706 -- PostgreSQL implicitly casts string literals to data with decimal types, but
0707 -- Spark does not support that kind of implicit casts. To test all the INSERT queries below,
0708 -- we rewrote them into the other typed literals.
0709 INSERT INTO fract_only VALUES (4, -0.9999);
0710 INSERT INTO fract_only VALUES (5, 0.99994);
0711 -- [SPARK-27923] PostgreSQL throws an exception but Spark SQL is NULL
0712 -- INSERT INTO fract_only VALUES (6, '0.99995');  -- should fail
0713 -- PostgreSQL implicitly casts string literals to data with decimal types, but
0714 -- Spark does not support that kind of implicit casts. To test all the INSERT queries below,
0715 -- we rewrote them into the other typed literals.
0716 INSERT INTO fract_only VALUES (7, 0.00001);
0717 INSERT INTO fract_only VALUES (8, 0.00017);
0718 SELECT * FROM fract_only;
0719 DROP TABLE fract_only;
0720
0721 -- [SPARK-28315] Decimal can not accept NaN as input
0722 -- [SPARK-27923] Decimal type can not accept Infinity and -Infinity
0723 -- Check inf/nan conversion behavior
0724 SELECT decimal(double('NaN'));
0725 SELECT decimal(double('Infinity'));
0726 SELECT decimal(double('-Infinity'));
0727 SELECT decimal(float('NaN'));
0728 SELECT decimal(float('Infinity'));
0729 SELECT decimal(float('-Infinity'));
0730
0731 -- Simple check that ceil(), floor(), and round() work correctly
0732 CREATE TABLE ceil_floor_round (a decimal(38, 18)) USING parquet;
0733 -- PostgreSQL implicitly casts string literals to data with decimal types, but
0734 -- Spark does not support that kind of implicit casts. To test all the INSERT queries below,
0735 -- we rewrote them into the other typed literals.
0736 INSERT INTO ceil_floor_round VALUES (-5.5);
0737 INSERT INTO ceil_floor_round VALUES (-5.499999);
0738 INSERT INTO ceil_floor_round VALUES (9.5);
0739 INSERT INTO ceil_floor_round VALUES (9.4999999);
0740 INSERT INTO ceil_floor_round VALUES (0.0);
0741 INSERT INTO ceil_floor_round VALUES (0.0000001);
0742 INSERT INTO ceil_floor_round VALUES (-0.000001);
0743 SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round;
0744 DROP TABLE ceil_floor_round;
0745
0746 -- [SPARK-28007] Caret operator (^) means bitwise XOR in Spark and exponentiation in Postgres
0747 -- Check rounding, it should round ties away from zero.
0748 -- SELECT i as pow,
0749 --      round((-2.5 * 10 ^ i)::numeric, -i),
0750 --      round((-1.5 * 10 ^ i)::numeric, -i),
0751 --      round((-0.5 * 10 ^ i)::numeric, -i),
0752 --      round((0.5 * 10 ^ i)::numeric, -i),
0753 --      round((1.5 * 10 ^ i)::numeric, -i),
0754 --      round((2.5 * 10 ^ i)::numeric, -i)
0755 -- FROM generate_series(-5,5) AS t(i);
0756
0757 -- [SPARK-21117] Built-in SQL Function Support - WIDTH_BUCKET
0758 -- Testing for width_bucket(). For convenience, we test both the
0759 -- numeric and float8 versions of the function in this file.
0760
0761 -- errors
0762 -- SELECT width_bucket(5.0, 3.0, 4.0, 0);
0763 -- SELECT width_bucket(5.0, 3.0, 4.0, -5);
0764 -- SELECT width_bucket(3.5, 3.0, 3.0, 888);
0765 -- SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, 0);
0766 -- SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, -5);
0767 -- SELECT width_bucket(3.5::float8, 3.0::float8, 3.0::float8, 888);
0768 -- SELECT width_bucket('NaN', 3.0, 4.0, 888);
0769 -- SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888);
0770
0771 -- normal operation
0772 -- CREATE TABLE width_bucket_test (operand_num numeric, operand_f8 float8);
0773
0774 -- COPY width_bucket_test (operand_num) FROM stdin;
0775 -- -5.2
0776 -- -0.0000000001
0777 -- 0.000000000001
0778 -- 1
0779 -- 1.99999999999999
0780 -- 2
0781 -- 2.00000000000001
0782 -- 3
0783 -- 4
0784 -- 4.5
0785 -- 5
0786 -- 5.5
0787 -- 6
0788 -- 7
0789 -- 8
0790 -- 9
0791 -- 9.99999999999999
0792 -- 10
0793 -- 10.0000000000001
0794 -- \.
0795
0796 -- UPDATE width_bucket_test SET operand_f8 = operand_num::float8;
0797
0798 -- SELECT
0799 --     operand_num,
0800 --     width_bucket(operand_num, 0, 10, 5) AS wb_1,
0801 --     width_bucket(operand_f8, 0, 10, 5) AS wb_1f,
0802 --     width_bucket(operand_num, 10, 0, 5) AS wb_2,
0803 --     width_bucket(operand_f8, 10, 0, 5) AS wb_2f,
0804 --     width_bucket(operand_num, 2, 8, 4) AS wb_3,
0805 --     width_bucket(operand_f8, 2, 8, 4) AS wb_3f,
0806 --     width_bucket(operand_num, 5.0, 5.5, 20) AS wb_4,
0807 --     width_bucket(operand_f8, 5.0, 5.5, 20) AS wb_4f,
0808 --     width_bucket(operand_num, -25, 25, 10) AS wb_5,
0809 --     width_bucket(operand_f8, -25, 25, 10) AS wb_5f
0810 --     FROM width_bucket_test;
0811
0812 -- for float8 only, check positive and negative infinity: we require
0813 -- finite bucket bounds, but allow an infinite operand
0814 -- SELECT width_bucket(0.0::float8, 'Infinity'::float8, 5, 10); -- error
0815 -- SELECT width_bucket(0.0::float8, 5, '-Infinity'::float8, 20); -- error
0816 -- SELECT width_bucket('Infinity'::float8, 1, 10, 10),
0817 --        width_bucket('-Infinity'::float8, 1, 10, 10);
0818
0819 -- DROP TABLE width_bucket_test;
0820
0821 -- [SPARK-28137] Missing Data Type Formatting Functions: TO_CHAR
0822 -- TO_CHAR()
0823 --
0824 -- SELECT '' AS to_char_1, to_char(val, '9G999G999G999G999G999')
0825 --      FROM num_data;
0826
0827 -- SELECT '' AS to_char_2, to_char(val, '9G999G999G999G999G999D999G999G999G999G999')
0828 --      FROM num_data;
0829
0830 -- SELECT '' AS to_char_3, to_char(val, '9999999999999999.999999999999999PR')
0831 --      FROM num_data;
0832
0833 -- SELECT '' AS to_char_4, to_char(val, '9999999999999999.999999999999999S')
0834 --      FROM num_data;
0835
0836 -- SELECT '' AS to_char_5,  to_char(val, 'MI9999999999999999.999999999999999')     FROM num_data;
0837 -- SELECT '' AS to_char_6,  to_char(val, 'FMS9999999999999999.999999999999999')    FROM num_data;
0838 -- SELECT '' AS to_char_7,  to_char(val, 'FM9999999999999999.999999999999999THPR') FROM num_data;
0839 -- SELECT '' AS to_char_8,  to_char(val, 'SG9999999999999999.999999999999999th')   FROM num_data;
0840 -- SELECT '' AS to_char_9,  to_char(val, '0999999999999999.999999999999999')       FROM num_data;
0841 -- SELECT '' AS to_char_10, to_char(val, 'S0999999999999999.999999999999999')      FROM num_data;
0842 -- SELECT '' AS to_char_11, to_char(val, 'FM0999999999999999.999999999999999')     FROM num_data;
0843 -- SELECT '' AS to_char_12, to_char(val, 'FM9999999999999999.099999999999999')  FROM num_data;
0844 -- SELECT '' AS to_char_13, to_char(val, 'FM9999999999990999.990999999999999')  FROM num_data;
0845 -- SELECT '' AS to_char_14, to_char(val, 'FM0999999999999999.999909999999999')  FROM num_data;
0846 -- SELECT '' AS to_char_15, to_char(val, 'FM9999999990999999.099999999999999')  FROM num_data;
0847 -- SELECT '' AS to_char_16, to_char(val, 'L9999999999999999.099999999999999')   FROM num_data;
0848 -- SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999')   FROM num_data;
0849 -- SELECT '' 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;
0850 -- SELECT '' 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;
0851 -- SELECT '' AS to_char_20, to_char(val, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data;
0852 -- SELECT '' AS to_char_21, to_char(val, '999999SG9999999999')                  FROM num_data;
0853 -- SELECT '' AS to_char_22, to_char(val, 'FM9999999999999999.999999999999999')  FROM num_data;
0854 -- SELECT '' AS to_char_23, to_char(val, '9.999EEEE')                           FROM num_data;
0855
0856 -- SELECT '' AS to_char_24, to_char('100'::numeric, 'FM999.9');
0857 -- SELECT '' AS to_char_25, to_char('100'::numeric, 'FM999.');
0858 -- SELECT '' AS to_char_26, to_char('100'::numeric, 'FM999');
0859
0860 -- Check parsing of literal text in a format string
0861 -- SELECT '' AS to_char_27, to_char('100'::numeric, 'foo999');
0862 -- SELECT '' AS to_char_28, to_char('100'::numeric, 'f\oo999');
0863 -- SELECT '' AS to_char_29, to_char('100'::numeric, 'f\\oo999');
0864 -- SELECT '' AS to_char_30, to_char('100'::numeric, 'f\"oo999');
0865 -- SELECT '' AS to_char_31, to_char('100'::numeric, 'f\\"oo999');
0866 -- SELECT '' AS to_char_32, to_char('100'::numeric, 'f"ool"999');
0867 -- SELECT '' AS to_char_33, to_char('100'::numeric, 'f"\ool"999');
0868 -- SELECT '' AS to_char_34, to_char('100'::numeric, 'f"\\ool"999');
0869 -- SELECT '' AS to_char_35, to_char('100'::numeric, 'f"ool\"999');
0870 -- SELECT '' AS to_char_36, to_char('100'::numeric, 'f"ool\\"999');
0871
0872 -- [SPARK-28137] Missing Data Type Formatting Functions: TO_NUMBER
0873 -- TO_NUMBER()
0874 --
0875 -- SET lc_numeric = 'C';
0876 -- SELECT '' AS to_number_1,  to_number('-34,338,492', '99G999G999');
0877 -- SELECT '' AS to_number_2,  to_number('-34,338,492.654,878', '99G999G999D999G999');
0878 -- SELECT '' AS to_number_3,  to_number('<564646.654564>', '999999.999999PR');
0879 -- SELECT '' AS to_number_4,  to_number('0.00001-', '9.999999S');
0880 -- SELECT '' AS to_number_5,  to_number('5.01-', 'FM9.999999S');
0881 -- SELECT '' AS to_number_5,  to_number('5.01-', 'FM9.999999MI');
0882 -- SELECT '' AS to_number_7,  to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9');
0883 -- SELECT '' AS to_number_8,  to_number('.01', 'FM9.99');
0884 -- SELECT '' AS to_number_9,  to_number('.0', '99999999.99999999');
0885 -- SELECT '' AS to_number_10, to_number('0', '99.99');
0886 -- SELECT '' AS to_number_11, to_number('.-01', 'S99.99');
0887 -- SELECT '' AS to_number_12, to_number('.01-', '99.99S');
0888 -- SELECT '' AS to_number_13, to_number(' . 0 1-', ' 9 9 . 9 9 S');
0889 -- SELECT '' AS to_number_14, to_number('34,50','999,99');
0890 -- SELECT '' AS to_number_15, to_number('123,000','999G');
0891 -- SELECT '' AS to_number_16, to_number('123456','999G999');
0892 -- SELECT '' AS to_number_17, to_number('$1234.56','L9,999.99');
0893 -- SELECT '' AS to_number_18, to_number('$1234.56','L99,999.99');
0894 -- SELECT '' AS to_number_19, to_number('$1,234.56','L99,999.99');
0895 -- SELECT '' AS to_number_20, to_number('1234.56','L99,999.99');
0896 -- SELECT '' AS to_number_21, to_number('1,234.56','L99,999.99');
0897 -- SELECT '' AS to_number_22, to_number('42nd', '99th');
0898 -- RESET lc_numeric;
0899
0900 --
0901 -- Input syntax
0902 --
0903
0904 CREATE TABLE num_input_test (n1 decimal(38, 18)) USING parquet;
0905
0906 -- good inputs
0907 -- PostgreSQL implicitly casts string literals to data with decimal types, but
0908 -- Spark does not support that kind of implicit casts. To test all the INSERT queries below,
0909 -- we rewrote them into the other typed literals.
0910 INSERT INTO num_input_test VALUES (double(trim(' 123')));
0911 INSERT INTO num_input_test VALUES (double(trim('   3245874    ')));
0912 INSERT INTO num_input_test VALUES (double(trim('  -93853')));
0913 INSERT INTO num_input_test VALUES (555.50);
0914 INSERT INTO num_input_test VALUES (-555.50);
0915 -- [SPARK-28315] Decimal can not accept NaN as input
0916 -- INSERT INTO num_input_test VALUES (trim('NaN '));
0917 -- INSERT INTO num_input_test VALUES (trim('        nan'));
0918
0919 -- [SPARK-27923] Spark SQL accept bad inputs to NULL
0920 -- bad inputs
0921 -- INSERT INTO num_input_test VALUES ('     ');
0922 -- INSERT INTO num_input_test VALUES ('   1234   %');
0923 -- INSERT INTO num_input_test VALUES ('xyz');
0924 -- INSERT INTO num_input_test VALUES ('- 1234');
0925 -- INSERT INTO num_input_test VALUES ('5 . 0');
0926 -- INSERT INTO num_input_test VALUES ('5. 0   ');
0927 -- INSERT INTO num_input_test VALUES ('');
0928 -- INSERT INTO num_input_test VALUES (' N aN ');
0929
0930 SELECT * FROM num_input_test;
0931
0932 -- [SPARK-28318] Decimal can only support precision up to 38
0933 --
0934 -- Test some corner cases for multiplication
0935 --
0936
0937 -- select 4790999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
0938
0939 -- select 4789999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
0940
0941 -- select 4770999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
0942
0943 -- select 4769999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
0944
0945 --
0946 -- Test some corner cases for division
0947 --
0948 -- 999999999999999999999 is overflow for SYSTEM_DEFAULT(decimal(38, 18)), we use BigIntDecimal(decimal(38, 0)).
0949 select cast(999999999999999999999 as decimal(38, 0))/1000000000000000000000;
0950
0951 select div(cast(999999999999999999999 as decimal(38, 0)),1000000000000000000000);
0952 select mod(cast(999999999999999999999 as decimal(38, 0)),1000000000000000000000);
0953 select div(cast(-9999999999999999999999 as decimal(38, 0)),1000000000000000000000);
0954 select mod(cast(-9999999999999999999999 as decimal(38, 0)),1000000000000000000000);
0955 select div(cast(-9999999999999999999999 as decimal(38, 0)),1000000000000000000000)*1000000000000000000000 + mod(cast(-9999999999999999999999 as decimal(38, 0)),1000000000000000000000);
0956 select mod (70.0,70) ;
0957 select div (70.0,70) ;
0958 select 70.0 / 70 ;
0959 select 12345678901234567890 % 123;
0960 -- [SPARK-2659] HiveQL: Division operator should always perform fractional division
0961 -- select 12345678901234567890 DIV 123;
0962 -- select div(12345678901234567890, 123);
0963 -- select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123;
0964
0965 -- [SPARK-28007] Caret operator (^) means bitwise XOR in Spark and exponentiation in Postgres
0966 --
0967 -- Test code path for raising to integer powers
0968 --
0969
0970 -- select 10.0 ^ -2147483648 as rounds_to_zero;
0971 -- select 10.0 ^ -2147483647 as rounds_to_zero;
0972 -- select 10.0 ^ 2147483647 as overflows;
0973 -- select 117743296169.0 ^ 1000000000 as overflows;
0974
0975 -- cases that used to return inaccurate results
0976 -- select 3.789 ^ 21;
0977 -- select 3.789 ^ 35;
0978 -- select 1.2 ^ 345;
0979 -- select 0.12 ^ (-20);
0980
0981 -- cases that used to error out
0982 -- select 0.12 ^ (-25);
0983 -- select 0.5678 ^ (-85);
0984
0985 --
0986 -- Tests for raising to non-integer powers
0987 --
0988
0989 -- special cases
0990 -- select 0.0 ^ 0.0;
0991 -- select (-12.34) ^ 0.0;
0992 -- select 12.34 ^ 0.0;
0993 -- select 0.0 ^ 12.34;
0994
0995 -- NaNs
0996 -- select 'NaN'::numeric ^ 'NaN'::numeric;
0997 -- select 'NaN'::numeric ^ 0;
0998 -- select 'NaN'::numeric ^ 1;
0999 -- select 0 ^ 'NaN'::numeric;
1000 -- select 1 ^ 'NaN'::numeric;
1001
1002 -- invalid inputs
1003 -- select 0.0 ^ (-12.34);
1004 -- select (-12.34) ^ 1.2;
1005
1006 -- cases that used to generate inaccurate results
1007 -- select 32.1 ^ 9.8;
1008 -- select 32.1 ^ (-9.8);
1009 -- select 12.3 ^ 45.6;
1010 -- select 12.3 ^ (-45.6);
1011
1012 -- big test
1013 -- select 1.234 ^ 5678;
1014
1015 --
1016 -- Tests for EXP()
1017 --
1018
1019 -- special cases
1020 select exp(0.0);
1021 select exp(1.0);
1022 -- [SPARK-28316] EXP returns double type for decimal input
1023 -- [SPARK-28318] Decimal can only support precision up to 38
1024 -- select exp(1.0::numeric(71,70));
1025
1026 -- cases that used to generate inaccurate results
1027 select exp(32.999);
1028 select exp(-32.999);
1029 select exp(123.456);
1030 select exp(-123.456);
1031
1032 -- big test
1033 select exp(1234.5678);
1034
1035 --
1036 -- Tests for generate_series
1037 --
1038 select * from range(cast(0.0 as decimal(38, 18)), cast(4.0 as decimal(38, 18)));
1039 select * from range(cast(0.1 as decimal(38, 18)), cast(4.0 as decimal(38, 18)), cast(1.3 as decimal(38, 18)));
1040 select * from range(cast(4.0 as decimal(38, 18)), cast(-1.5 as decimal(38, 18)), cast(-2.2 as decimal(38, 18)));
1041 -- Trigger errors
1042 -- select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
1043 -- select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
1044 -- select * from generate_series('nan'::numeric, 100::numeric, 10::numeric);
1045 -- select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric);
1046 -- [SPARK-28007] Caret operator (^) means bitwise XOR in Spark and exponentiation in Postgres
1047 -- Checks maximum, output is truncated
1048 -- select (i / (10::numeric ^ 131071))::numeric(1,0)
1049 --      from generate_series(6 * (10::numeric ^ 131071),
1050 --                           9 * (10::numeric ^ 131071),
1051 --                           10::numeric ^ 131071) as a(i);
1052 -- Check usage with variables
1053 -- select * from generate_series(1::numeric, 3::numeric) i, generate_series(i,3) j;
1054 -- select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,i) j;
1055 -- select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,5,i) j;
1056
1057 --
1058 -- Tests for LN()
1059 --
1060
1061 -- [SPARK-27923] Invalid inputs for LN throws exception at PostgreSQL
1062 -- Invalid inputs
1063 -- select ln(-12.34);
1064 -- select ln(0.0);
1065
1066 -- Some random tests
1067 select ln(1.2345678e-28);
1068 select ln(0.0456789);
1069 -- [SPARK-28318] Decimal can only support precision up to 38
1070 -- select ln(0.349873948359354029493948309745709580730482050975);
1071 select ln(0.99949452);
1072 select ln(1.00049687395);
1073 select ln(1234.567890123456789);
1074 select ln(5.80397490724e5);
1075 select ln(9.342536355e34);
1076
1077 --
1078 -- Tests for LOG() (base 10)
1079 --
1080
1081 -- [SPARK-27923] Invalid inputs for LOG throws exception at PostgreSQL
1082 -- invalid inputs
1083 -- select log(-12.34);
1084 -- select log(0.0);
1085
1086 -- some random tests
1087 -- [SPARK-28318] Decimal can only support precision up to 38
1088 -- select log(1.234567e-89);
1089 -- [SPARK-28324] The LOG function using 10 as the base, but Spark using E
1090 select log(3.4634998359873254962349856073435545);
1091 select log(9.999999999999999999);
1092 select log(10.00000000000000000);
1093 select log(10.00000000000000001);
1094 select log(590489.45235237);
1095
1096 --
1097 -- Tests for LOG() (arbitrary base)
1098 --
1099
1100 -- [SPARK-27923] Invalid inputs for LOG throws exception at PostgreSQL
1101 -- invalid inputs
1102 -- select log(-12.34, 56.78);
1103 -- select log(-12.34, -56.78);
1104 -- select log(12.34, -56.78);
1105 -- select log(0.0, 12.34);
1106 -- select log(12.34, 0.0);
1107 -- select log(1.0, 12.34);
1108
1109 -- some random tests
1110 -- [SPARK-28318] Decimal can only support precision up to 38
1111 -- select log(1.23e-89, 6.4689e45);
1112 select log(0.99923, 4.58934e34);
1113 select log(1.000016, 8.452010e18);
1114 -- [SPARK-28318] Decimal can only support precision up to 38
1115 -- select log(3.1954752e47, 9.4792021e-73);
1116
1117 -- [SPARK-28317] Built-in Mathematical Functions: SCALE
1118 --
1119 -- Tests for scale()
1120 --
1121
1122 -- select scale(numeric 'NaN');
1123 -- select scale(NULL::numeric);
1124 -- select scale(1.12);
1125 -- select scale(0);
1126 -- select scale(0.00);
1127 -- select scale(1.12345);
1128 -- select scale(110123.12475871856128);
1129 -- select scale(-1123.12471856128);
1130 -- select scale(-13.000000000000000);
1131
1132 --
1133 -- Tests for SUM()
1134 --
1135
1136 -- cases that need carry propagation
1137 SELECT SUM(decimal(9999)) FROM range(1, 100001);
1138 SELECT SUM(decimal(-9999)) FROM range(1, 100001);
1139
1140 DROP TABLE num_data;
1141 DROP TABLE num_exp_add;
1142 DROP TABLE num_exp_sub;
1143 DROP TABLE num_exp_div;
1144 DROP TABLE num_exp_mul;
1145 DROP TABLE num_exp_sqrt;
1146 DROP TABLE num_exp_ln;
1147 DROP TABLE num_exp_log10;
1148 DROP TABLE num_exp_power_10_ln;
1149 DROP TABLE num_result;
1150 DROP TABLE num_input_test;