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

David Rowley <david.rowley@2ndquadrant.com> writes:
> On 16 May 2018 at 02:01, Tom Lane <tgl@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;