Horton
Menu

Thread

  1. string || NULL ambiguity

    Oleg Bartunov <oleg@sai.msu.su> — 2003-03-19T19:26:05Z

    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

  2. Re: string || NULL ambiguity

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-03-19T20:46:40Z

    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"

  3. Re: string || NULL ambiguity

    Oleg Bartunov <oleg@sai.msu.su> — 2003-03-20T01:32:56Z

    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

  4. Re: string || NULL ambiguity

    Thomas T. Thai <tom@minnesota.com> — 2003-03-20T02:56:24Z

    > 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

  5. Re: string || NULL ambiguity

    Neil Conway <neilc@samurai.com> — 2003-03-20T03:08:57Z

    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

  6. Re: string || NULL ambiguity

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-20T04:14:43Z

    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

  7. Re: string || NULL ambiguity

    Thomas T. Thai <tom@minnesota.com> — 2003-03-20T08:34:21Z

    > 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

  8. Re: string || NULL ambiguity

    Thomas T. Thai <tom@minnesota.com> — 2003-03-20T09:08:54Z

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

  9. Re: string || NULL ambiguity

    Oleg Bartunov <oleg@sai.msu.su> — 2003-03-20T12:36:45Z

    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

  10. Re: string || NULL ambiguity

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-20T14:55:03Z

    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