NaNs in numeric_power (was Re: Postgres 11 release notes)

[Tom Lane]

David Rowley <david.row...@2ndquadrant.com> writes:
> On 16 May 2018 at 02:01, Tom Lane <t...@sss.pgh.pa.us> wrote:
>> I'm not particularly fussed about getting credit for that.  However,
>> looking again at how that patch series turned out --- ie, that
>> we ensured POSIX behavior for NaNs only in HEAD --- I wonder
>> whether we shouldn't do what was mentioned in the commit log for
>> 6bdf1303, and teach numeric_pow() about these same special cases.
>> It seems like it would be more consistent to change both functions
>> for v11, rather than letting that other shoe drop in some future
>> major release.

> I'm inclined to agree. It's hard to imagine these two functions
> behaving differently in regards to NaN input is useful to anyone.

Here's a proposed patch for that.

                        regards, tom lane


diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index dcf31e3..8dfdffc 100644
*** a/src/backend/utils/adt/numeric.c
--- b/src/backend/utils/adt/numeric.c
*************** numeric_power(PG_FUNCTION_ARGS)
*** 2972,2981 ****
  	NumericVar	result;
  
  	/*
! 	 * Handle NaN
  	 */
! 	if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
  		PG_RETURN_NUMERIC(make_result(&const_nan));
  
  	/*
  	 * Initialize things
--- 2972,2998 ----
  	NumericVar	result;
  
  	/*
! 	 * Handle NaN cases.  We follow the POSIX spec for pow(3), which says that
! 	 * NaN ^ 0 = 1, and 1 ^ NaN = 1, while all other cases with NaN inputs
! 	 * yield NaN (with no error).
  	 */
! 	if (NUMERIC_IS_NAN(num1))
! 	{
! 		if (!NUMERIC_IS_NAN(num2))
! 		{
! 			init_var_from_num(num2, &arg2);
! 			if (cmp_var(&arg2, &const_zero) == 0)
! 				PG_RETURN_NUMERIC(make_result(&const_one));
! 		}
! 		PG_RETURN_NUMERIC(make_result(&const_nan));
! 	}
! 	if (NUMERIC_IS_NAN(num2))
! 	{
! 		init_var_from_num(num1, &arg1);
! 		if (cmp_var(&arg1, &const_one) == 0)
! 			PG_RETURN_NUMERIC(make_result(&const_one));
  		PG_RETURN_NUMERIC(make_result(&const_nan));
+ 	}
  
  	/*
  	 * Initialize things
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index 17985e8..1cb3c3b 100644
*** a/src/test/regress/expected/numeric.out
--- b/src/test/regress/expected/numeric.out
*************** select 0.0 ^ 12.34;
*** 1664,1669 ****
--- 1664,1700 ----
   0.0000000000000000
  (1 row)
  
+ -- NaNs
+ select 'NaN'::numeric ^ 'NaN'::numeric;
+  ?column? 
+ ----------
+       NaN
+ (1 row)
+ 
+ select 'NaN'::numeric ^ 0;
+  ?column? 
+ ----------
+         1
+ (1 row)
+ 
+ select 'NaN'::numeric ^ 1;
+  ?column? 
+ ----------
+       NaN
+ (1 row)
+ 
+ select 0 ^ 'NaN'::numeric;
+  ?column? 
+ ----------
+       NaN
+ (1 row)
+ 
+ select 1 ^ 'NaN'::numeric;
+  ?column? 
+ ----------
+         1
+ (1 row)
+ 
  -- invalid inputs
  select 0.0 ^ (-12.34);
  ERROR:  zero raised to a negative power is undefined
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index d77504e..a939412 100644
*** a/src/test/regress/sql/numeric.sql
--- b/src/test/regress/sql/numeric.sql
*************** select (-12.34) ^ 0.0;
*** 911,916 ****
--- 911,923 ----
  select 12.34 ^ 0.0;
  select 0.0 ^ 12.34;
  
+ -- NaNs
+ select 'NaN'::numeric ^ 'NaN'::numeric;
+ select 'NaN'::numeric ^ 0;
+ select 'NaN'::numeric ^ 1;
+ select 0 ^ 'NaN'::numeric;
+ select 1 ^ 'NaN'::numeric;
+ 
  -- invalid inputs
  select 0.0 ^ (-12.34);
  select (-12.34) ^ 1.2;