Hi, all

While testing RC1, I found CONNECTBY had another problem. 
It seems to me that SCHEMA can't be used in CONNECTBY.
Is it just in time for 7.3 to be added to TODO items ?



CREATE TABLE test (id int4, parent_id int4, t text);
INSERT INTO test VALUES(11, null, 'aaa');
INSERT INTO test VALUES(101, 11, 'bbb');
INSERT INTO test VALUES(110, 11, 'ccc');
INSERT INTO test VALUES(111, 110, 'ddd');
SELECT *
 FROM connectby('test', 'id', 'parent_id', '11', 0, '.')
            as t(id int4, parent_id int4, level int, branch text);

 id  | parent_id | level |   branch   
-----+-----------+-------+------------
  11 |           |     0 | 11
 101 |        11 |     1 | 11.101
 110 |        11 |     1 | 11.110
 111 |       110 |     2 | 11.110.111
(4 rows)



CREATE SCHEMA ms;
CREATE TABLE ms.test (id int4, parent_id int4, t text);
INSERT INTO ms.test VALUES(11, null, 'aaa');
INSERT INTO ms.test VALUES(101, 11, 'bbb');
INSERT INTO ms.test VALUES(110, 11, 'ccc');
INSERT INTO ms.test VALUES(111, 110, 'ddd');
SELECT *
 FROM connectby('ms.test', 'id', 'parent_id', '101', 0, '.')
            as t(id int4, parent_id int4, level int, branch text);

ERROR:  Relation "ms.test" does not exist



Regards,
Masaru Sugawara




Sat, 23 Nov 2002 00:50:50 +0900
Masaru Sugawara <rk73@sea.plala.or.jp>
connectby with schema
Masaru Sugawara wrote:
> CREATE SCHEMA ms;
> CREATE TABLE ms.test (id int4, parent_id int4, t text);
> INSERT INTO ms.test VALUES(11, null, 'aaa');
> INSERT INTO ms.test VALUES(101, 11, 'bbb');
> INSERT INTO ms.test VALUES(110, 11, 'ccc');
> INSERT INTO ms.test VALUES(111, 110, 'ddd');
> SELECT *
>  FROM connectby('ms.test', 'id', 'parent_id', '101', 0, '.')
>             as t(id int4, parent_id int4, level int, branch text);
> 
> ERROR:  Relation "ms.test" does not exist
> 

I've tracked this down to the fact that connectby does a quote_ident on the 
provided relname, and in quote_ident, (quote_ident_required(t)) ends up being 
true. The problem will occur even with a simple query:

test=# SELECT id, parent_id FROM ms.test WHERE parent_id = '101' AND id IS NOT 
NULL;
  id | parent_id
----+-----------
(0 rows)
test=# SELECT id, parent_id FROM "ms.test" WHERE parent_id = '101' AND id IS 
NOT NULL;
ERROR:  Relation "ms.test" does not exist

But this is not the behavior for unqualified table names:

test=# select * from foo;
  f1
----
   1
(1 row)
test=# select * from "foo";
  f1
----
   1
(1 row)

Is quote_ident_required incorrectly dealing with schemas?

Thanks,

Joe


Fri, 22 Nov 2002 10:19:40 -0800
Joe Conway <mail@joeconway.com>
quote_ident and schemas (was Re: connectby with schema)
Joe Conway wrote:
> 
> Is quote_ident_required incorrectly dealing with schemas?
> 

Sorry to reply to myself, but another related question; shouldn't the 
following produce "Ms"."Test"?

test=# select quote_ident('Ms.Test');
  quote_ident
-------------
  "Ms.Test"
(1 row)

Joe


Fri, 22 Nov 2002 10:33:50 -0800
Joe Conway <mail@joeconway.com>
Re: quote_ident and schemas (was Re: connectby with schema)
On Fri, 22 Nov 2002, Joe Conway wrote:

> Masaru Sugawara wrote:
> > CREATE SCHEMA ms;
> > CREATE TABLE ms.test (id int4, parent_id int4, t text);
> > INSERT INTO ms.test VALUES(11, null, 'aaa');
> > INSERT INTO ms.test VALUES(101, 11, 'bbb');
> > INSERT INTO ms.test VALUES(110, 11, 'ccc');
> > INSERT INTO ms.test VALUES(111, 110, 'ddd');
> > SELECT *
> >  FROM connectby('ms.test', 'id', 'parent_id', '101', 0, '.')
> >             as t(id int4, parent_id int4, level int, branch text);
> >
> > ERROR:  Relation "ms.test" does not exist
> >
>
> I've tracked this down to the fact that connectby does a quote_ident on the
> provided relname, and in quote_ident, (quote_ident_required(t)) ends up being
> true. The problem will occur even with a simple query:
>
> test=# SELECT id, parent_id FROM ms.test WHERE parent_id = '101' AND id IS NOT
> NULL;
>   id | parent_id
> ----+-----------
> (0 rows)
> test=# SELECT id, parent_id FROM "ms.test" WHERE parent_id = '101' AND id IS
> NOT NULL;
> ERROR:  Relation "ms.test" does not exist

I think the query result here is correct behavior since in the second the
period shouldn't be a separator for schema and table but instead be part
of the identifier.

Dropping some bits that probably aren't important and merging some states

<table name> -> <qualified name>
<qualified name> -> [<schema name> <period>] <identifier>
<identifer> -> <regular identifier>  |
               <delimited identifier>
<delimited identifier> -> <double quote> <delimited identifier body>
			  <double quote>

I'd think that they'd parse like:
 ms.test  -> <identifier> . <identifier>
"ms.test" -> <delimited identifier>

The first would match <schema name> <period> <identifier>, but the second
would not.




Fri, 22 Nov 2002 11:10:24 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: quote_ident and schemas (was Re: connectby with schema)
Joe Conway <mail@joeconway.com> writes:
> Joe Conway wrote:
>> Is quote_ident_required incorrectly dealing with schemas?

> Sorry to reply to myself, but another related question; shouldn't the 
> following produce "Ms"."Test"?

> test=# select quote_ident('Ms.Test');
>   quote_ident
> -------------
>   "Ms.Test"
> (1 row)

No, it should not.  If it did, it would fail to cope with tablenames
containing dots.

Since connectby takes a string parameter (correct?) for the table name,
my advice would be to have it not do quote_ident, but instead expect the
user to include double quotes in the string value if dealing with
mixed-case names.  Compare the behavior of nextval() for example:

regression=# select nextval('Foo.Bar');
ERROR:  Namespace "foo" does not exist
regression=# select nextval('"Foo"."Bar"');
ERROR:  Namespace "Foo" does not exist
regression=# select nextval('"Foo.Bar"');
ERROR:  Relation "Foo.Bar" does not exist

			regards, tom lane

Fri, 22 Nov 2002 14:15:34 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: quote_ident and schemas (was Re: connectby with schema) 
Tom Lane wrote:
> Since connectby takes a string parameter (correct?) for the table name,
> my advice would be to have it not do quote_ident, but instead expect the
> user to include double quotes in the string value if dealing with
> mixed-case names.  Compare the behavior of nextval() for example:
> 
> regression=# select nextval('Foo.Bar');
> ERROR:  Namespace "foo" does not exist
> regression=# select nextval('"Foo"."Bar"');
> ERROR:  Namespace "Foo" does not exist
> regression=# select nextval('"Foo.Bar"');
> ERROR:  Relation "Foo.Bar" does not exist
> 

OK. Attached patch removes calls within the function to quote_ident, requiring 
the user to appropriately quote their own identifiers. I also tweaked the 
regression test to deal with "value" becoming a reserved word.

If it's not too late, I'd like this to get into 7.3, but in any case, please 
apply to HEAD.

Thanks,

Joe

p.s. There are similar issues in dblink, but they appear a bit more difficult 
to address. I'll attempt to get them resloved this weekend, again in hopes to 
get them applied before 7.3 is released.
Fri, 22 Nov 2002 15:21:48 -0800
Joe Conway <mail@joeconway.com>
Re: quote_ident and schemas (was Re: [HACKERS] connectby with schema)
Joe Conway <mail@joeconway.com> writes:
> OK. Attached patch removes calls within the function to quote_ident, requiring 
> the user to appropriately quote their own identifiers. I also tweaked the 
> regression test to deal with "value" becoming a reserved word.

Applied.  I also threw in a quick note in the README to mention the need
for quoting.

			regards, tom lane

Fri, 22 Nov 2002 20:55:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: quote_ident and schemas (was Re: [HACKERS] connectby with schema)
Joe Conway wrote:
> p.s. There are similar issues in dblink, but they appear a bit more 
> difficult to address. I'll attempt to get them resloved this weekend, 
> again in hopes to get them applied before 7.3 is released.
> 

Attached patch removes most (hopefully just the appropriate ones) calls in 
dblink to quote_ident, requiring the user to quote their own identifiers. I 
also added to the regression test a case for a quoted, schema qualified table 
name.

If it's not too late, I'd like this to get into 7.3, but in any case,
please apply to HEAD.

Thanks,

Joe
Sat, 23 Nov 2002 09:47:57 -0800
Joe Conway <mail@joeconway.com>
Re: quote_ident and schemas (was Re: [HACKERS] connectby
Joe Conway <mail@joeconway.com> writes:
> Attached patch removes most (hopefully just the appropriate ones) calls in 
> dblink to quote_ident, requiring the user to quote their own identifiers. I 
> also added to the regression test a case for a quoted, schema qualified table
> name.

> If it's not too late, I'd like this to get into 7.3, but in any case,
> please apply to HEAD.

Applied --- in 7.3 also, since Marc hasn't rolled RC2 yet.

			regards, tom lane

Sat, 23 Nov 2002 14:02:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: quote_ident and schemas (was Re: [HACKERS] connectby with schema)
On Fri, 22 Nov 2002 15:21:48 -0800
Joe Conway <mail@joeconway.com> wrote:

> OK. Attached patch removes calls within the function to quote_ident, requiring 
> the user to appropriately quote their own identifiers. I also tweaked the 
> regression test to deal with "value" becoming a reserved word.
> 
> If it's not too late, I'd like this to get into 7.3, but in any case, please 
> apply to HEAD.
> 

Thank you for your quick job.


Regards,
Masaru Sugawara

-------------------------------------
CREATE SCHEMA ms;
CREATE TABLE ms.test (id int4, parent_id int4, t text);
INSERT INTO ms.test VALUES(11, null, 'aaa');
INSERT INTO ms.test VALUES(101, 11, 'bbb');
INSERT INTO ms.test VALUES(110, 11, 'ccc');
INSERT INTO ms.test VALUES(111, 110, 'ddd');
SELECT *
 FROM connectby('ms.test', 'id', 'parent_id', '11', 0, '.')
            as t(id int, parent_id int, level int, branch text);

 id  | parent_id | level |   branch   
-----+-----------+-------+------------
  11 |           |     0 | 11
 101 |        11 |     1 | 11.101
 110 |        11 |     1 | 11.110
 111 |       110 |     2 | 11.110.111
(4 rows)



------------------------------------
CREATE SCHEMA "MS";
drop table "MS"."Test";
CREATE TABLE "MS"."Test" (id int4, parent_id int4, t text);
INSERT INTO "MS"."Test" VALUES(22, null, 'aaa');
INSERT INTO "MS"."Test" VALUES(202, 22, 'bbb');
INSERT INTO "MS"."Test" VALUES(220, 22, 'ccc');
INSERT INTO "MS"."Test" VALUES(222, 220, 'ddd');
SELECT *
 FROM connectby('"MS"."Test"', 'id', 'parent_id', '22', 0, '.')
            as t(id int, parent_id int, level int, branch text);


 id  | parent_id | level |   branch   
-----+-----------+-------+------------
  22 |           |     0 | 22
 202 |        22 |     1 | 22.202
 220 |        22 |     1 | 22.220
 222 |       220 |     2 | 22.220.222
(4 rows)






Sun, 24 Nov 2002 13:26:32 +0900
Masaru Sugawara <rk73@sea.plala.or.jp>
Re: quote_ident and schemas (was Re: connectby with schema)