
 Hi,

 I try concatenate text via standard '||' oprerator, but result is
interesting:  

PgSQL 6.5.3/7.0:
~~~~~~~~~~~~~~~
test=> select * from x;
a  |b
---+---
AAA|BBB
xxx|
(2 rows)

test=> select a || b from x;
?column?
--------
AAABBB
                                  <-------------- empty !
(2 rows)


Oracle8:
~~~~~~~~
SVRMGR> select * from x;
A                                B
-------------------------------- --------------------------------
AAA                              BBB
xxx
2 rows selected.
SVRMGR> select a || b from x;
A||B
----------------------------------------------------------------
AAABBB
xxx                                <---------------- not empty !
2 rows selected.



 I fistly think that problem is in the textcat() routine, but PgSQL ignore 
all functions's results if any argument (column) is empty. Example:

text *
xxx(text *t1, text *t2)
{
        text       *result;

        result = (text *) palloc(10 + VARHDRSZ);
        strcpy(VARDATA(result), "happy");
        VARSIZE(result) = 5 + VARHDRSZ;
        elog(NOTICE, "RETURN: %s", VARDATA(result));

        return result;	                     /* always return 'happy' */
}
 
  
test=> select * from x;
a  |b
---+---
AAA|BBB
xxx|
(2 rows)

test=> select  xxx(a, b) from x;
NOTICE:  RETURN: happy
NOTICE:  RETURN: happy
xxx
----
happy
                                            <--------- empty ?!
(2 rows)


Why is it empty? I believe that is not feature :-)

						Karel

PS. sorry, if this is old point, mail-list archive seacher (htdig) 
    not work...

----------------------------------------------------------------------
Karel Zak <zakkr@zf.jcu.cz>              http://home.zf.jcu.cz/~zakkr/

Docs:        http://docs.linux.cz                    (big docs archive)	
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/        (process manager)
FTP:         ftp://ftp2.zf.jcu.cz/users/zakkr/        (C/ncurses/PgSQL)
-----------------------------------------------------------------------


Thu, 23 Dec 1999 16:20:23 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
empty concatenate
> 
> NULL's can not be concatenated, but '' can.  Looks fine to me:
> 
> ---------------------------------------------------------------------------
> 
> 	test=> create table ff (x text, y text);
> 	insCREATE
> 	test=> insert into ff values ('a','');
> 	INSERT 19082 1
> 	test=> insert into ff values ('b',null);
> 	INSERT 19083 1
> 	test=> select x || y from ff;
> 	 ?column? 
> 	----------
> 	 a
> 	 
> 	(2 rows)


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. 

How can user write / use function which response on NULL (as IFNULL())?

							Karel


Thu, 23 Dec 1999 16:55:55 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] empty concatenate
> 
>  Hi,
> 
>  I try concatenate text via standard '||' oprerator, but result is
> interesting:  
> 
> PgSQL 6.5.3/7.0:
> ~~~~~~~~~~~~~~~
> test=> select * from x;
> a  |b
> ---+---
> AAA|BBB
> xxx|
> (2 rows)
> 
> test=> select a || b from x;
> ?column?
> --------
> AAABBB
>                                   <-------------- empty !
> (2 rows)

NULL's can not be concatenated, but '' can.  Looks fine to me:

---------------------------------------------------------------------------

	test=> create table ff (x text, y text);
	insCREATE
	test=> insert into ff values ('a','');
	INSERT 19082 1
	test=> insert into ff values ('b',null);
	INSERT 19083 1
	test=> select x || y from ff;
	 ?column? 
	----------
	 a
	 
	(2 rows)


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 23 Dec 1999 10:56:20 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] empty concatenate
I think this is a known bug.
You can try the standard COALESCE function as in:

select coalesce(a,'')||coalesce(b,'') from test;
?column?
--------
AAAABBBB
xxxx
(2 rows)

Jose'

Karel Zak - Zakkr wrote:
> 
>  Hi,
> 
>  I try concatenate text via standard '||' oprerator, but result is
> interesting:
> 
> PgSQL 6.5.3/7.0:
> ~~~~~~~~~~~~~~~
> test=> select * from x;
> a  |b
> ---+---
> AAA|BBB
> xxx|
> (2 rows)
> 
> test=> select a || b from x;
> ?column?
> --------
> AAABBB
>                                   <-------------- empty !
> (2 rows)
> 
> Oracle8:
> ~~~~~~~~
> SVRMGR> select * from x;
> A                                B
> -------------------------------- --------------------------------
> AAA                              BBB
> xxx
> 2 rows selected.
> SVRMGR> select a || b from x;
> A||B
> ----------------------------------------------------------------
> AAABBB
> xxx                                <---------------- not empty !
> 2 rows selected.
> 
>  I fistly think that problem is in the textcat() routine, but PgSQL ignore
> all functions's results if any argument (column) is empty. Example:
> 
> text *
> xxx(text *t1, text *t2)
> {
>         text       *result;
> 
>         result = (text *) palloc(10 + VARHDRSZ);
>         strcpy(VARDATA(result), "happy");
>         VARSIZE(result) = 5 + VARHDRSZ;
>         elog(NOTICE, "RETURN: %s", VARDATA(result));
> 
>         return result;                       /* always return 'happy' */
> }
> 
> 
> test=> select * from x;
> a  |b
> ---+---
> AAA|BBB
> xxx|
> (2 rows)
> 
> test=> select  xxx(a, b) from x;
> NOTICE:  RETURN: happy
> NOTICE:  RETURN: happy
> xxx
> ----
> happy
>                                             <--------- empty ?!
> (2 rows)
> 
> Why is it empty? I believe that is not feature :-)
> 
>                                                 Karel
> 
> PS. sorry, if this is old point, mail-list archive seacher (htdig)
>     not work...
> 
> ----------------------------------------------------------------------
> Karel Zak <zakkr@zf.jcu.cz>              http://home.zf.jcu.cz/~zakkr/
> 
> Docs:        http://docs.linux.cz                    (big docs archive)
> Kim Project: http://home.zf.jcu.cz/~zakkr/kim/        (process manager)
> FTP:         ftp://ftp2.zf.jcu.cz/users/zakkr/        (C/ncurses/PgSQL)
> -----------------------------------------------------------------------
> 
> ************

Thu, 23 Dec 1999 17:38:34 +0100
Jose Soares <jose@sferacarta.com>
Re: [HACKERS] empty concatenate

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


Thu, 23 Dec 1999 11:58:13 -0500
sszabo@bigpanda.com
Re: [HACKERS] empty concatenate 

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


Thu, 23 Dec 1999 18:01:33 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] empty concatenate 
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

Thu, 23 Dec 1999 12:35:43 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] empty concatenate 
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.

Thu, 23 Dec 1999 12:47:22 -0500 (EST)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] empty concatenate
"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

Thu, 23 Dec 1999 19:44:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] empty concatenate 
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.

Thu, 23 Dec 1999 21:29:08 -0500 (EST)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] empty concatenate