Thread: Odd results in SELECT
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
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
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 writerulename = '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 writerulename = ('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