Horton

Thread

  1. Re: [HACKERS] empty concatenate

    sszabo@bigpanda.com — 1999-12-23T16:58:13Z

    >
>Well, but why PgSQL ignore function result if any argument is NULL. IMHO is
>function's problem what return, and PgSQL must use this result. 
I believe this is a known issue that's being looked at right now.

However, in this case PostgreSQL seems to be correct. 
2) If <concatenation> is specified, then let S1 and S2 be the re-
 sult of the <character value expression> and <character factor>,
 respectively.
 Case:
 a) If either S1 or S2 is the null value, then the result of the
  <concatenation> is the null value.

>How can user write / use function which response on NULL (as IFNULL())?
Well, for now, you probably want to use coalesce around any input that
might be null.  I believe coalesce returns the first non-null parameter,
so coalesce(<column>, '') will return either the column's value (if not
NULL) or the empty string which can then be used for concatenation.

Stephan

  2. Re: [HACKERS] empty concatenate

    Karel Zak <zakkr@zf.jcu.cz> — 1999-12-23T17:01:33Z

    > 
> >
> >Well, but why PgSQL ignore function result if any argument is NULL. IMHO is
> >function's problem what return, and PgSQL must use this result. 
> I believe this is a known issue that's being looked at right now.

 I not agree with this concept:-). 

(My problem is not write query, I know SQL and coalesce()...etc. I want 
good understand current implementation.)

! Why is textcat() (and other) function called if result from this 
function is ignored, it is bad spending (my CPU is not boredom). See 
my 'C' example in my first letter...  

						Karel

  3. Re: [HACKERS] empty concatenate

    Tom Lane <tgl@sss.pgh.pa.us> — 1999-12-23T17:35:43Z

    sszabo@bigpanda.com writes:
>> Well, but why PgSQL ignore function result if any argument is NULL. IMHO is
>> function's problem what return, and PgSQL must use this result. 
>
> I believe this is a known issue that's being looked at right now.

Current plans are to fix it in the release-after-next (7.1).

As you say, the behavior is correct for standard SQL operators; the
only real problem is that user-written operators might want to return
non-null results for null inputs, and we can't handle that right now.

Applying COALESCE before calling the operator will get the job done
in some cases, but it clutters your queries...

			regards, tom lane

  4. Re: [HACKERS] empty concatenate

    D'Arcy Cain <darcy@druid.net> — 1999-12-23T17:47:22Z

    Thus spake Karel Zak - Zakkr
>  I not agree with this concept:-). 

You are not alone.

> (My problem is not write query, I know SQL and coalesce()...etc. I want 
> good understand current implementation.)
> 
> ! Why is textcat() (and other) function called if result from this 
> function is ignored, it is bad spending (my CPU is not boredom). See 
> my 'C' example in my first letter...  

This is the issue no matter which side of the debate you are on.  I
think everyone agrees that either the function should not be called
or else the result should be used if it is.  CPU is a terrible thing
to waste.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

  5. Re: [HACKERS] empty concatenate

    Tom Lane <tgl@sss.pgh.pa.us> — 1999-12-24T00:44:20Z

    "D'Arcy" "J.M." Cain <darcy@druid.net> writes:
>> ! Why is textcat() (and other) function called if result from this 
>> function is ignored, it is bad spending (my CPU is not boredom). See 
>> my 'C' example in my first letter...  

> This is the issue no matter which side of the debate you are on.

"Debate"?  There's no debate --- everybody agrees that the current
fmgr interface doesn't handle NULLs reasonably.  It's just a matter
of finding time to fix it.  It's a fairly large project, given the
amount of code that needs to be touched.

			regards, tom lane

  6. Re: [HACKERS] empty concatenate

    D'Arcy Cain <darcy@druid.net> — 1999-12-24T02:29:08Z

    Thus spake Tom Lane
> "D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> >> ! Why is textcat() (and other) function called if result from this 
> >> function is ignored, it is bad spending (my CPU is not boredom). See 
> >> my 'C' example in my first letter...  
> 
> > This is the issue no matter which side of the debate you are on.
> 
> "Debate"?  There's no debate --- everybody agrees that the current
> fmgr interface doesn't handle NULLs reasonably.  It's just a matter
> of finding time to fix it.  It's a fairly large project, given the
> amount of code that needs to be touched.

Well, it may have been a lopsided (and friendly) debate but there was
definitely two sides.  The one (which I assume you mean as the one
that "everyone" accepts says to stick to SQL conformance and fix it
so that the functions are just never called.  The other said to have
the functions called then use the value returned so that each function
could decide what to do with NULLs.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.