Re: Do FROM items of different schemas conflict? - Mailing list pgsql-hackers

Tom Lane wrote:
> 
> One *can* write
> 
>         SELECT * FROM tab1, tab1 AS x;
> 
> since the alias x effectively becomes the name of the second FROM item.
> But what about
> 
>         SELECT * FROM schema1.tab1, schema2.tab1;
> 
> Is this allowed? 

Tom, I do not have the standard here.  But as far as I can tell
this is allowed.  However, you'll have to refer to these tables
by the qualified name, like:

SELECT schema1.tab1.col1, schema2.tab1.col5 FROM schema1.tab1,
schema2.tab1 WHERE...

If you had    SELECT * FROM schema1.tab1, schema2.tab2;
you could abbreviate:
SELECT tab1.col1, tab2.col5 FROM schema1.tab1, schema2.tab2 WHERE...

i.e., as long as it is not ambiguous you can omit the schema
qualification.  Otherwise you have to use AS, like in the non-schema
case when you are using the same table twice.

The idea seems to be: if there is ambiguity, you must use AS.
And you cannot cause an ambiguity with the name you give in the AS
clause.


> I don't much care for this since it implies that the system must try to
> associate a schema name with the column reference "tab1.col1" even
> before it looks for matching FROM-items.  What if tab1 is actually a
> reference to an alias?  We might not find any schema containing tab1.
> Certainly this would completely destroy any hope of having a schema
> search path; which path entry should we associate with tab1 if we don't
> find any tab1?
> 

Each SQL-session has a schema associated with it, which should be the
schema
with the same name as the current userid. That is the schema from where
you
must take the path.


> What I would like to do is say the following:
> 
> 1. Two FROM-items in the same scope cannot have equal <correlation
> name>s or <qualified identifier>s.
> 

Only if the qualified identifiers are exposed.  As soon as you give
then an alias with AS the original name is hidden.

> 2. A column reference that includes a table name but no schema name is
> matched to FROM-items on the basis of <correlation name> or <qualified
> identifier> only; that is, "SELECT tab1.col1 FROM schema1.tab1" will
> work whether schema1 is in the search path or not.
> 

Yes.


> 3. A column reference that includes a schema name must refer to an
> extant table, and will match only FROM-items that refer to the same
> table and have the same correlation name.  (Fine point here: this means
> a reference like schema1.tab1.col1 will match "FROM schema1.tab1",
> and it will match "FROM schema1.tab1 AS tab1", but it will not match
> "FROM schema1.tab1 AS x".)  Note also that "same table" avoids the
> question of whether the FROM clause had an implicit or explicit schema
> qualifier.
> 

Yes,  "schema1.tab1 AS x" makes "schema1.tab1" disappear.


> These rules essentially say that a FROM entry "FROM foo.bar" is exactly
> equivalent to "FROM foo.bar AS bar",

A small difference.  With the first you can refer to columns as

foo.bar.col1

with the second you cannot.  You must say:  bar.col1

> and also that "FROM bar" is exactly
> equivalent to "FROM foo.bar" where foo is the schema in which bar is
> found.  

Yes, as long as the path for the session schema finds bar in foo 
first than any other schema.

>I like these symmetries ... and I am not at all sure that they
> hold if we interpret the SQL92 rules literally.
> 
> Comments?  Is anyone familiar with the details of how other DBMSes
> handle these issues?
> 

I remember some professor saying that not using the AS clause is a 
bad SQL programming practice.  With all these resolution rules one
tends to agree with that...



-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9