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

Okay folks, time to put on your language-lawyer hats ...

I have been trying to puzzle out the SQL rules concerning whether two
FROM items conflict in the presence of schemas.  It is entirely clear
that one is not allowed to write
SELECT * FROM tab1, tab1;

since this introduces two FROM items of the same name in the same scope.
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?  SQL92 appears to allow it: section 6.3 <table
reference> says:
        3) A <table name> that is exposed by a <table reference> TR shall           not be the same as any other <table
name>that is exposed by a           <table reference> with the same scope clause as TR.
 

and <table name> quite clearly means the fully qualified table name.
However, the very next paragraph says
        4) A <correlation name> that is exposed by a <table reference> TR           shall not be the same as any other
<correlationname> that is           exposed by a <table reference> with the same scope clause as TR           and shall
notbe the same as the <qualified identifier> of any           <table name> that is exposed by a <table reference> with
the          same scope clause as TR.
 

Here <correlation name> means alias; <qualified identifier> actually means
the unqualified name (sic) of the table, stripped of any schema.  Now as
far as I can see, that last restriction makes no sense unless it is
intended to allow FROM-items to be referenced by unqualified name alone.
Which isn't going to work if qualified FROM-items can have duplicate
unqualified names.

This restriction also suggests strongly that the spec authors intended
to allow unqualified references to qualified FROM-items, viz:
SELECT tab1.col1 FROM schema1.tab1;

But as far as I can tell, this is only valid if schema1 is the schema
that tab1 would have been found in anyway, cf 5.4 syntax rule 10:
        10)Two <qualified name>s are equal if and only if they have the           same <qualified identifier> and the
same<schema name>, regard-           less of whether the <schema name>s are implicit or explicit.
 

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?

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.

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.

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.

These rules essentially say that a FROM entry "FROM foo.bar" is exactly
equivalent to "FROM foo.bar AS bar", and also that "FROM bar" is exactly
equivalent to "FROM foo.bar" where foo is the schema in which bar is
found.  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?
        regards, tom lane