Thread: 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)
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)
-----------------------------------------------------------------------
>
> 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);insCREATEtest=> insert into ff values ('a','');INSERT 19082 1test=> insert into
ffvalues ('b',null);INSERT 19083 1test=> 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,
Pennsylvania19026
>
> 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
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)
> -----------------------------------------------------------------------
>
> ************
> >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 <characterfactor>,respectively.Case:a) If either S1 or S2 is the null value, then the result of the <concatenation> is thenull 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
>
> >
> >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
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
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.
"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
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.