Re: coalesce with all nulls can only be assigned to - Mailing list pgsql-general

>>> On Wed, Nov 29, 2006 at  1:32 PM, in message
<456D8C05.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:
>>>> On Wed, Nov 29, 2006 at  1:09 PM, in message
> <456DDAFA.3000803@archonet.com>,
> Richard Huxton <dev@archonet.com> wrote:
>
>> Another option I can think of: Spot the case where all values in
the
>> coalesce are null and just replace with a single literal null.
>
> This would have to be done in the JDBC driver's handling of the "{fn
> IFNULL" portability escape code.  That might be a decent stop- gap.
I
> think I'll do that to support preliminary testing, and work on the
> framework changes for the long- term solution.

The JDBC hack was easy, although nothing to be proud of or suggest for
inclusion in the product.  I'll paste it below for the benefit of anyone
in similar circumstances who finds this thread.

Martijn, Java is indeed strongly typed, but, there is a type hierarchy
with class Object at its root.  The framework passes along collections
where the values are declared as type Object, and the low level routines
count on being able to interrogate the objects to determine the specific
subclass of Object for a value to be able to handle it correctly.  A
null is really the absence of an object, and can not be interrogated for
a specific type.  So far this has not caused us any problems, but I can
see benefits to carrying type information deeper into the framework.  In
particular, there is an opportunity to overload methods and move some of
the type checking to compile time, for a little run-time performance
boost.

Thanks to all for the information and suggestions.

-Kevin


Index: EscapedFunctions.java
===================================================================
RCS file:
/usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/EscapedFunctions.java,v
retrieving revision 1.8
diff -c -r1.8 EscapedFunctions.java
*** EscapedFunctions.java    4 Apr 2006 22:52:42 -0000    1.8
--- EscapedFunctions.java    29 Nov 2006 21:18:17 -0000
***************
*** 593,599 ****
              throw new PSQLException(GT.tr("{0} function takes two and
only two arguments.","ifnull"),
                                      PSQLState.SYNTAX_ERROR);
          }
!         return
"coalesce("+parsedArgs.get(0)+","+parsedArgs.get(1)+")";
      }

      /** user translation */
--- 593,604 ----
              throw new PSQLException(GT.tr("{0} function takes two and
only two arguments.","ifnull"),
                                      PSQLState.SYNTAX_ERROR);
          }
!         String arg0 = String.valueOf(parsedArgs.get(0));
!         String arg1 = String.valueOf(parsedArgs.get(1));
!         if ("null".equals(arg0.trim().toLowerCase()) &&
"null".equals(arg1.trim().toLowerCase())){
!             return "null";
!         }
!         return "coalesce("+arg0+","+arg1+")";
      }

      /** user translation */