Hi there,

we have a little problem in new version of tsearch we're currently
working. We've implemented concatenation operation for txtidx type
and treat concatenation with NULL as NULL (as it should be).
But people get confused with such behaivour. Do we obliged to
follow NULL rule ? It seems more natural in case of text searching to treat
stringA||NULL as stringA.

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Wed, 19 Mar 2003 22:26:05 +0300 (MSK)
Oleg Bartunov <oleg@sai.msu.su>
string || NULL ambiguity
On Wed, Mar 19, 2003 at 10:26:05PM +0300, Oleg Bartunov wrote:

> we have a little problem in new version of tsearch we're currently
> working. We've implemented concatenation operation for txtidx type
> and treat concatenation with NULL as NULL (as it should be).
> But people get confused with such behaivour. Do we obliged to
> follow NULL rule ? It seems more natural in case of text searching to treat
> stringA||NULL as stringA.

Why don't you just enclose the possibly NULL fields in COALESCE?

stringA||COALESCE(NULL, '')

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Para tener mas hay que desear menos"

Wed, 19 Mar 2003 16:46:40 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: string || NULL ambiguity
On Wed, 19 Mar 2003, Alvaro Herrera wrote:

> On Wed, Mar 19, 2003 at 10:26:05PM +0300, Oleg Bartunov wrote:
>
> > we have a little problem in new version of tsearch we're currently
> > working. We've implemented concatenation operation for txtidx type
> > and treat concatenation with NULL as NULL (as it should be).
> > But people get confused with such behaivour. Do we obliged to
> > follow NULL rule ? It seems more natural in case of text searching to treat
> > stringA||NULL as stringA.
>
> Why don't you just enclose the possibly NULL fields in COALESCE?
>
> stringA||COALESCE(NULL, '')
>

we don't know in advance if it's NULL or not.

>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Thu, 20 Mar 2003 04:32:56 +0300 (MSK)
Oleg Bartunov <oleg@sai.msu.su>
Re: string || NULL ambiguity
> On Wed, 19 Mar 2003, Alvaro Herrera wrote:
>
>> On Wed, Mar 19, 2003 at 10:26:05PM +0300, Oleg Bartunov wrote:
>>
>> > we have a little problem in new version of tsearch we're currently
>> working. We've implemented concatenation operation for txtidx type
>> and treat concatenation with NULL as NULL (as it should be).
>> > But people get confused with such behaivour. Do we obliged to
>> > follow NULL rule ? It seems more natural in case of text searching
>> to treat stringA||NULL as stringA.
>>
>> Why don't you just enclose the possibly NULL fields in COALESCE?
>>
>> stringA||COALESCE(NULL, '')
>>
>
> we don't know in advance if it's NULL or not.

I'm the user who reported this on the OpenFTS list. May I suggest allowing
'' in txtidx column type as we do with datatype text, char, and varchar.
That way concat will work as expected for text searching.

--
Thomnas



Wed, 19 Mar 2003 20:56:24 -0600 (CST)
"Thomas T. Thai" <tom@minnesota.com>
Re: string || NULL ambiguity
On Wed, 2003-03-19 at 20:32, Oleg Bartunov wrote:
> On Wed, 19 Mar 2003, Alvaro Herrera wrote:
> > stringA||COALESCE(NULL, '')
> >
> 
> we don't know in advance if it's NULL or not.

Right, that's the point of COALESCE: the first non-NULL argument is
returned -- so if the first argument to COALESCE happens to be non-NULL,
COALESCE has no effect.

Cheers,

Neil


19 Mar 2003 22:08:57 -0500
Neil Conway <neilc@samurai.com>
Re: string || NULL ambiguity
Oleg Bartunov <oleg@sai.msu.su> writes:
> we have a little problem in new version of tsearch we're currently
> working. We've implemented concatenation operation for txtidx type
> and treat concatenation with NULL as NULL (as it should be).
> But people get confused with such behaivour. Do we obliged to
> follow NULL rule ? It seems more natural in case of text searching to treat
> stringA||NULL as stringA.

This seems a very bad idea.  If you think people are confused by NULLs
now, it'll be ten times worse if some datatypes handle them differently
from others.

			regards, tom lane

Wed, 19 Mar 2003 23:14:43 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: string || NULL ambiguity 
> On Wed, 2003-03-19 at 20:32, Oleg Bartunov wrote:
>> On Wed, 19 Mar 2003, Alvaro Herrera wrote:
>> > stringA||COALESCE(NULL, '')
>> >
>>
>> we don't know in advance if it's NULL or not.
>
> Right, that's the point of COALESCE: the first non-NULL argument is
> returned -- so if the first argument to COALESCE happens to be non-NULL,
> COALESCE has no effect.

I tried this with a txtidx column type:

tsearch=# select coalesce(NULL,'');
 case
------

(1 row)

tsearch=# select coalesce(NULL,'hi');
 case
------
 hi
(1 row)

tsearch=# select title_fts from article;
            title_fts
----------------------------------
 '2':3A 'tsearch':1A 'version':2A
 'easi':1A 'implement':2A

(3 rows)

tsearch=# select coalesce('',title_fts) from article;
ERROR:  Void value
tsearch=# select coalesce('hi',title_fts) from article;
 case
------
 'hi'
 'hi'
 'hi'
(3 rows)

---

Note the error: "ERROR:  Void value" above.  Why is that happening?

--
Thomas



Thu, 20 Mar 2003 02:34:21 -0600 (CST)
"Thomas T. Thai" <tom@minnesota.com>
Re: string || NULL ambiguity
>> On Wed, 2003-03-19 at 20:32, Oleg Bartunov wrote:
>>> On Wed, 19 Mar 2003, Alvaro Herrera wrote:
>>> > stringA||COALESCE(NULL, '')
>>> >
>>>
>>> we don't know in advance if it's NULL or not.
>>
>> Right, that's the point of COALESCE: the first non-NULL argument is
>> returned -- so if the first argument to COALESCE happens to be
>> non-NULL, COALESCE has no effect.
>
> I tried this with a txtidx column type:
>
> tsearch=# select coalesce(NULL,'');
>  case
> ------
>
> (1 row)
>
> tsearch=# select coalesce(NULL,'hi');
>  case
> ------
>  hi
> (1 row)
>
> tsearch=# select title_fts from article;
>             title_fts
> ----------------------------------
>  '2':3A 'tsearch':1A 'version':2A
>  'easi':1A 'implement':2A
>
> (3 rows)
>
> tsearch=# select coalesce('',title_fts) from article;
> ERROR:  Void value
> tsearch=# select coalesce('hi',title_fts) from article;
>  case
> ------
>  'hi'
>  'hi'
>  'hi'
> (3 rows)
>
> ---

Sorry the above should have been:

tsearch=# select coalesce(title_fts, '') from article;
ERROR:  Void value
tsearch=# select coalesce(title_fts, 'hi') from article;
               case
----------------------------------
 '2':3A 'tsearch':1A 'version':2A
 'easi':1A 'implement':2A
 'hi'
(3 rows)

> Note the error: "ERROR:  Void value" above.  Why is that happening?
>
> --
> Thomas
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org




Thu, 20 Mar 2003 03:08:54 -0600 (CST)
"Thomas T. Thai" <tom@minnesota.com>
Re: string || NULL ambiguity
On Thu, 20 Mar 2003, Thomas T. Thai wrote:

> > On Wed, 2003-03-19 at 20:32, Oleg Bartunov wrote:
> >> On Wed, 19 Mar 2003, Alvaro Herrera wrote:
> >> > stringA||COALESCE(NULL, '')
> >> >
> >>
> >> we don't know in advance if it's NULL or not.
> >
> > Right, that's the point of COALESCE: the first non-NULL argument is
> > returned -- so if the first argument to COALESCE happens to be non-NULL,
> > COALESCE has no effect.
>
> I tried this with a txtidx column type:
>
> tsearch=# select coalesce(NULL,'');
>  case
> ------
>
> (1 row)
>
> tsearch=# select coalesce(NULL,'hi');
>  case
> ------
>  hi
> (1 row)
>
> tsearch=# select title_fts from article;
>             title_fts
> ----------------------------------
>  '2':3A 'tsearch':1A 'version':2A
>  'easi':1A 'implement':2A
>
> (3 rows)
>
> tsearch=# select coalesce('',title_fts) from article;
> ERROR:  Void value
> tsearch=# select coalesce('hi',title_fts) from article;
>  case
> ------
>  'hi'
>  'hi'
>  'hi'
> (3 rows)
>
> ---
>
> Note the error: "ERROR:  Void value" above.  Why is that happening?
>

That's because NULL is forbidden for txtidx. We'll fix that.

> --
> Thomas
>
>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Thu, 20 Mar 2003 15:36:45 +0300 (MSK)
Oleg Bartunov <oleg@sai.msu.su>
Re: string || NULL ambiguity
Oleg Bartunov <oleg@sai.msu.su> writes:
> On Thu, 20 Mar 2003, Thomas T. Thai wrote:
>> Note the error: "ERROR:  Void value" above.  Why is that happening?

> That's because NULL is forbidden for txtidx. We'll fix that.

Please note that an empty string is not the same as NULL.  The error
message in txtidx_in appears to be griping about an empty string,
not NULL.

			regards, tom lane

Thu, 20 Mar 2003 09:55:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: string || NULL ambiguity 