Odd results in SELECT

On Fri, 11 May 2001, Philip Warner wrote:

Can anyone suggest why this might be happening (I think it's in 7.1b4):

SELECT definition as viewdef,
(select oid from pg_rewrite where
rulename='_RETszallitolevel_tetele_ervenyes') as view_oid
from pg_views where viewname = 'szallitolevel_tetele_ervenyes';

=> view_oid is 133652.

SELECT definition as viewdef,
(select oid from pg_rewrite where
rulename='_RET' || 'szallitolevel_tetele_ervenyes') as view_oid
from pg_views where viewname = 'szallitolevel_tetele_ervenyes';

=> view_oid is NULL

I get the same result in 7.1 final. Tom, isn't this in relation with my
complex query you solved yesterday?

Zoltan

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

On Fri, 11 May 2001, Philip Warner wrote:

Can anyone suggest why this might be happening (I think it's in 7.1b4):

SELECT definition as viewdef,
(select oid from pg_rewrite where
rulename='_RETszallitolevel_tetele_ervenyes') as view_oid
from pg_views where viewname = 'szallitolevel_tetele_ervenyes';

=> view_oid is 133652.

SELECT definition as viewdef,
(select oid from pg_rewrite where
rulename='_RET' || 'szallitolevel_tetele_ervenyes') as view_oid
from pg_views where viewname = 'szallitolevel_tetele_ervenyes';

=> view_oid is NULL

I get the same result in 7.1 final. Tom, isn't this in relation with my
complex query you solved yesterday?

Not in that form --- there isn't any parameter being passed down to the
subquery. What plan does EXPLAIN show for the failing query?

regards, tom lane

Philip Warner <pjw@rhyme.com.au> writes:

Can anyone suggest why this might be happening (I think it's in 7.1b4):

Can't duplicate in current sources:

regression=# SELECT definition as viewdef,
regression-# (select oid from pg_rewrite where
regression(# rulename='_RETstreet') as view_oid
regression-# from pg_views where viewname = 'street';
viewdef
| view_oid
-------------------------------------------------------------------------------------------------+----------
SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); | 4001276
(1 row)

regression=# SELECT definition as viewdef,
regression-# (select oid from pg_rewrite where
regression(# rulename='_RET' || 'street') as view_oid
regression-# from pg_views where viewname = 'street';
viewdef
| view_oid
-------------------------------------------------------------------------------------------------+----------
SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); | 4001276
(1 row)

What does EXPLAIN show for your two queries? (Maybe you'd better make
it EXPLAIN VERBOSE.)

regards, tom lane

On Thu, 10 May 2001, Tom Lane wrote:

Philip Warner <pjw@rhyme.com.au> writes:

Can anyone suggest why this might be happening (I think it's in 7.1b4):

Can't duplicate in current sources:

regression=# SELECT definition as viewdef,
regression-# (select oid from pg_rewrite where
regression(# rulename='_RETstreet') as view_oid
regression-# from pg_views where viewname = 'street';
viewdef
| view_oid
-------------------------------------------------------------------------------------------------+----------
SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); | 4001276
(1 row)

regression=# SELECT definition as viewdef,
regression-# (select oid from pg_rewrite where
regression(# rulename='_RET' || 'street') as view_oid
regression-# from pg_views where viewname = 'street';
viewdef
| view_oid
-------------------------------------------------------------------------------------------------+----------
SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); | 4001276
(1 row)

What does EXPLAIN show for your two queries? (Maybe you'd better make
it EXPLAIN VERBOSE.)

I attached both.

Kov\'acs, Zolt\'an
kovacsz@pc10.radnoti-szeged.sulinet.hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz

See my prior reply to Philip: the problem is that the given string is
longer than NAMEDATALEN. When you write
rulename = 'foo'
(rulename is of type NAME) the untyped literal string 'foo' gets coerced
to NAME, ie truncated to fit, and all is well. When you write
rulename = ('foo' || 'bar')
the result of the || operator is type TEXT, so instead rulename is
converted to TEXT and a text comparison is performed. In this case the
righthand value is not truncated and so the match will always fail.

regards, tom lane