
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   |/

Fri, 11 May 2001 02:32:24 +1000
Philip Warner <pjw@rhyme.com.au>
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


Thu, 10 May 2001 18:50:01 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
Re: Odd results in SELECT
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

Thu, 10 May 2001 16:57:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Odd results in SELECT 
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

Thu, 10 May 2001 17:31:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Odd results in SELECT 
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
Fri, 11 May 2001 14:42:22 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
Re: Odd results in SELECT 
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

Fri, 11 May 2001 09:53:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Odd results in SELECT 