Re: pgsql: Have numeric 0 ^ 4.3 return 1, rather than an error, and have 0 ^ - Mailing list pgsql-committers

Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Wikipedia says that exponentiation of zero to a negative power implies
> > division by zero, so shouldn't we throw a "division by zero" error?
>
> I think it should be a specific message like "zero raised to a negative
> power is undefined".  It's not like it's going to take us any extra code
> to know that we are faced with that case.
>
> BTW, I realized that SQL:2003 spells it all out for us in explicit
> detail:

...

> b) If VB is 0 (zero) and VE is negative, then an exception condition is
> raised: data exception � invalid argument for power function.

Well, this indicates we shouldn't return "zero raised to a negative
power is undefined", but rather the power error we are giving now, or
are you saying we should return the "power" error code but an error
message mentioning zero?

> c) If VB is 0 (zero) and VE is 0 (zero), then the result is 1 (one).

I have updated the C comments to mention the spec also requires we
return 1 in this case.

C comment updated attached and applied.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/backend/utils/adt/float.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/float.c,v
retrieving revision 1.155
diff -c -c -r1.155 float.c
*** src/backend/utils/adt/float.c    21 Apr 2008 00:26:45 -0000    1.155
--- src/backend/utils/adt/float.c    9 May 2008 15:34:53 -0000
***************
*** 1331,1337 ****

      /*
       * The SQL spec requires that we emit a particular SQLSTATE error code for
!      * certain error conditions.
       */
      if ((arg1 == 0 && arg2 < 0) ||
          (arg1 < 0 && floor(arg2) != arg2))
--- 1331,1338 ----

      /*
       * The SQL spec requires that we emit a particular SQLSTATE error code for
!      * certain error conditions.  Specifically, we don't return a divide-by-zero
!      * error code for 0 ^ -1.
       */
      if ((arg1 == 0 && arg2 < 0) ||
          (arg1 < 0 && floor(arg2) != arg2))
Index: src/backend/utils/adt/numeric.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/numeric.c,v
retrieving revision 1.112
diff -c -c -r1.112 numeric.c
*** src/backend/utils/adt/numeric.c    8 May 2008 22:17:54 -0000    1.112
--- src/backend/utils/adt/numeric.c    9 May 2008 15:34:53 -0000
***************
*** 1893,1900 ****
      trunc_var(&arg2_trunc, 0);

      /*
!      * Return special SQLSTATE error codes for a few conditions mandated by
!      * the standard.
       */
      if ((cmp_var(&arg1, &const_zero) == 0 &&
           cmp_var(&arg2, &const_zero) < 0) ||
--- 1893,1901 ----
      trunc_var(&arg2_trunc, 0);

      /*
!      * The SQL spec requires that we emit a particular SQLSTATE error code for
!      * certain error conditions.  Specifically, we don't return a divide-by-zero
!      * error code for 0 ^ -1.
       */
      if ((cmp_var(&arg1, &const_zero) == 0 &&
           cmp_var(&arg2, &const_zero) < 0) ||
***************
*** 5283,5288 ****
--- 5284,5290 ----
              /*
               *    While 0 ^ 0 can be either 1 or indeterminate (error), we
               *    treat it as 1 because most programming languages do this.
+              *    SQL:2003 also requires a return value of 1.
               *    http://en.wikipedia.org/wiki/Exponentiation#Zero_to_the_zero_power
               */
              set_var_from_var(&const_one, result);