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"

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)

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