Thread: coalesce with all nulls can only be assigned to text

Here is sample code demonstrating the issue:

test=# create table test_coalesce(f1 int not null, f2 date);
CREATE TABLE
test=# insert into test_coalesce values (1, null);
INSERT 0 1
test=# insert into test_coalesce values (2, coalesce(null, null));
ERROR:  column "f2" is of type date but expression is of type text
HINT:  You will need to rewrite or cast the expression.
test=# create cast (text as date) with function date(text) as
assignment;
ERROR:  cast from type text to type date already exists
test=# create cast (text as date) with function date(text) as
implicit;
ERROR:  cast from type text to type date already exists

The last statement is not something which would make sense to hand
code, but we have a framework which is plugging in the arguments for the
coalesce function at run time.  One solution to this is to modify the
framework to wrap any null with a cast to a type.  Due to the effort and
risk of that approach, I'm looking for alternatives.  Besides, the above
just doesn't make sense to me.

Any suggestions?

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> [ "coalesce(null, null)" yields type TEXT ]

Well, it has to yield *something*.  You'd get the same result from
"coalesce('2006-11-29', '2006-11-30')" ... you might think this looks
like dates, but it's just some untyped literals and the parser chooses
to resolve those as TEXT if there's no other clue anywhere in the
expression.

If you cast at least one of the nulls to DATE, you'll get what you want.

regression=# create table test_coalesce(f1 int not null, f2 date);
CREATE TABLE
regression=# insert into test_coalesce values (2, coalesce(null, null));
ERROR:  column "f2" is of type date but expression is of type text
HINT:  You will need to rewrite or cast the expression.
regression=# insert into test_coalesce values (2, coalesce(null::date, null));
INSERT 0 1

            regards, tom lane

>>> On Wed, Nov 29, 2006 at 12:15 PM, in message
<21074.1164824140@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> [ "coalesce(null, null)" yields type TEXT ]
>
> Well, it has to yield *something*.  You'd get the same result from
> "coalesce('2006- 11- 29', '2006- 11- 30')" ... you might think this
looks
> like dates, but it's just some untyped literals and the parser
chooses
> to resolve those as TEXT if there's no other clue anywhere in the
> expression.

We never do assume that a text literal is a valid date.  I won't bore
you with all the details unless you ask for them, but we're running on
Java and generating literals based on the object type passed to a low
level method.  A null has no type to use as the basis of a cast.

> If you cast at least one of the nulls to DATE, you'll get what you
want.

I realize that, and I'm working on modifying our framework to get type
information down to where we can do that for nulls.  The problem is,
this is a big enough change to potentially cause problems and hold up
the migration to PostgreSQL on the majority of our databases for an
application release cycle (three months), so I'm hoping for a less
drastic workaround.  It seems odd that a bare null works, but a coalesce
of two nulls fails.  It also seems odd that the automatic casting from
text to date fails to cover this.  (I tried creating a cast to cover
this and it told me there already was one.)

Thanks,

-Kevin

Kevin Grittner wrote:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> We never do assume that a text literal is a valid date.  I won't bore
> you with all the details unless you ask for them, but we're running on
> Java and generating literals based on the object type passed to a low
> level method.  A null has no type to use as the basis of a cast.

Unfortunate. Does your method know what type the database column is?

>> If you cast at least one of the nulls to DATE, you'll get what you
> want.
>
> I realize that, and I'm working on modifying our framework to get type
> information down to where we can do that for nulls.  The problem is,
> this is a big enough change to potentially cause problems and hold up
> the migration to PostgreSQL on the majority of our databases for an
> application release cycle (three months), so I'm hoping for a less
> drastic workaround.  It seems odd that a bare null works, but a coalesce
> of two nulls fails.

It's the coalesce that has the problem, not the insert. The coalesce is
deciding that it's working on text, and so returns text.

 > It also seems odd that the automatic casting from
> text to date fails to cover this.  (I tried creating a cast to cover
> this and it told me there already was one.)

There is a cast from text to date, but I don't think it's automatic...
(checks pg_cast) - no, it's marked as explicit. You could try marking
the cast as implicit, but I'd be concerned about unexpected casts occurring.

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.

--
   Richard Huxton
   Archonet Ltd

>>> On Wed, Nov 29, 2006 at  1:09 PM, in message
<456DDAFA.3000803@archonet.com>,
Richard Huxton <dev@archonet.com> wrote:
> Kevin Grittner wrote:
>> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> We never do assume that a text literal is a valid date.  I won't
bore
>> you with all the details unless you ask for them, but we're running
on
>> Java and generating literals based on the object type passed to a
low
>> level method.  A null has no type to use as the basis of a cast.
>
> Unfortunate. Does your method know what type the database column is?

No, it's a method that takes an object and generates a proper SQL
literal for the database product.  (Portability is a big issue.)

>>> If you cast at least one of the nulls to DATE, you'll get what you
>> want.
>>
>> I realize that, and I'm working on modifying our framework to get
type
>> information down to where we can do that for nulls.  The problem
is,
>> this is a big enough change to potentially cause problems and hold
up
>> the migration to PostgreSQL on the majority of our databases for an
>> application release cycle (three months), so I'm hoping for a less
>> drastic workaround.  It seems odd that a bare null works, but a
coalesce
>> of two nulls fails.
>
> It's the coalesce that has the problem, not the insert. The coalesce
is
> deciding that it's working on text, and so returns text.

It seems like maybe it would be worth overloading the coalesce method
to handle this particular case differently.  It might allow some queries
to optimize better.  Maybe.  On the other hand, it doesn't sound like it
comes up often, so it's likely not worth the effort.

>  > It also seems odd that the automatic casting from
>> text to date fails to cover this.  (I tried creating a cast to
cover
>> this and it told me there already was one.)
>
> There is a cast from text to date, but I don't think it's
automatic...
> (checks pg_cast) -  no, it's marked as explicit. You could try
marking
> the cast as implicit, but I'd be concerned about unexpected casts
occurring.

Point taken.  I would only do this as a temporary workaround -- it
doesn't seem like a good permanent solution.  If I were to do this,
would I update the existing row in pg_cast or use the DROP CAST and ADD
CAST statements?

> 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.

Thanks,

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Richard Huxton <dev@archonet.com> wrote:
>> It's the coalesce that has the problem, not the insert. The coalesce is
>> deciding that it's working on text, and so returns text.

> It seems like maybe it would be worth overloading the coalesce method
> to handle this particular case differently.

And do what?  The only information you have is that all the inputs are
of unknown type.  You do not get to look at context, because the type
resolution algorithm has to work bottom-up in expressions.

            regards, tom lane

>>> On Wed, Nov 29, 2006 at  1:38 PM, in message
<21909.1164829090@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> Richard Huxton <dev@archonet.com> wrote:
>>> It's the coalesce that has the problem, not the insert. The
coalesce is
>>> deciding that it's working on text, and so returns text.
>
>> It seems like maybe it would be worth overloading the coalesce
method
>> to handle this particular case differently.
>
> And do what?  The only information you have is that all the inputs
are
> of unknown type.

I know this is naive, but, what is the type information of the bare
null?  Could that be used?

Kevin Grittner wrote:
>>>> On Wed, Nov 29, 2006 at  1:38 PM, in message
> <21909.1164829090@sss.pgh.pa.us>,
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>>> Richard Huxton <dev@archonet.com> wrote:
>>>> It's the coalesce that has the problem, not the insert. The
> coalesce is
>>>> deciding that it's working on text, and so returns text.
>>
>>> It seems like maybe it would be worth overloading the coalesce
> method
>>> to handle this particular case differently.
>> And do what?  The only information you have is that all the inputs
> are
>> of unknown type.
>
> I know this is naive, but, what is the type information of the bare
> null?  Could that be used?

A literal null is "unknown", other literals are "unknown" or
"unknown-numeric" based on quoting iirc.

Hmm - Tom would it be possible to create a cast to unknown, explicitly
cast this coalesce to unknown and let it implicitly cast back to
whatever the column needs?

--
   Richard Huxton
   Archonet Ltd

On Wed, Nov 29, 2006 at 01:45:09PM -0600, Kevin Grittner wrote:
> > And do what?  The only information you have is that all the inputs
> > are of unknown type.
>
> I know this is naive, but, what is the type information of the bare
> null?  Could that be used?

A null can be of any type, string, text, integer, etc. If you have a
bare null in a query, it gets type "unknown" and the system has to
guess. Looking up possible matching operators and functions can help,
but if none of those possibilites help, it gets assigned type "text".
That's why as soon as one entry has a type, it works because the system
can assume the others are of the same type.

Compare this with pointers in C. There you can have a char ponter and
and an integer pointer, both NULL yet they cannot be used
interchangably, they are of different types.

I'm curious how in such a strongly typed language as Java you represent
a null without any associated type. Or does Java not distinguish
either?

Does this help?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

>>> 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 */