This works in 7.2.1, not in 7.3.2

On Thu, 10 Jul 2003, Frank Millman wrote:

Hi all

The following works in 7.2.1 -

1. select count(*) from SysUsersCompanies where UserRowId = 3 ;
2. select substring('NY',(select count(*) from SysUsersCompanies where UserRowId = 3)+1,1) ;

The first select returns either 1 or 0
The second one uses the result in a more complex expression, and returns either 'Y' or 'N'

In 7.3.2, the first select behaves the same, but the second one returns NULL.

Any advice will be appreciated.

It looks like in 7.2 it'd call substring(text, int, int).

In 7.3, I don't think that'd be a possible target due to some of the
implicit casting changes. It appears that substring(text, text, text) was
added which is then the valid choice, but it works differently (see the
docs). As a workaround, if you cast the count expression to an integer
(it's a bigint right now) it should do what you want.

"Frank Millman" <frank@chagford.com> writes:

The following works in 7.2.1 -

1. select count(*) from SysUsersCompanies where UserRowId =3D 3 ;
2. select substring('NY',(select count(*) from SysUsersCompanies where User=
RowId =3D 3)+1,1) ;

The first select returns either 1 or 0
The second one uses the result in a more complex expression, and returns ei=
ther 'Y' or 'N'

In 7.3.2, the first select behaves the same, but the second one returns NUL=
L.

Try coercing the result of count(*) to integer (it's bigint to start
with).

regards, tom lane