Thread: string || NULL ambiguity
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
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"
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
> 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
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
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
> 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
>> 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
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
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