Re: Do FROM items of different schemas conflict?

Tom Lane writes:

> But what about
>
> SELECT * FROM schema1.tab1, schema2.tab1;
>
> Is this allowed?

Yes. You would just have to schema-qualify any column references.

> 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 <correlation name> that is
> exposed by a <table reference> with the same scope clause as TR
> and shall not be 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.

I think you should be able to say

SELECT * FROM schema1.tab1 WHERE tab1.col1 > 0;

> Which isn't going to work if qualified FROM-items can have duplicate
> unqualified names.

I think the bottom line is that mixing aliased tables and non-aliased
tables in FROM lists is going to be confusing. But for those that stick
to either approach, the restrictions are most flexible, yet for those that
mix it's a sane subset.

For instance, is you don't use aliases you can say

SELECT * FROM sc1.tab1, sc2.tab1 WHERE sc1.tab1.col1 = sc2.tab1.col1;

which looks reasonable.

If you use aliases it basically says the aliases have to be different.

If you mix, it prevents you from doing

SELECT * FROM schema1.tab1, foo AS tab1;

since the reference "tab1" is ambiguous.

Another view is that in a parallel world, explicit table aliases could be
put into a pseudo-schema ALIAS, so you could write

SELECT * FROM schema1.tab1, foo AS tab1
WHERE schema1.tab1.col1 = ALIAS.tab1.col1;

But this is not the real world, so the ambiguity protection afforded to
table aliases needs to be stronger than for non-aliased table references.

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

Syntactically you can resolve tab1.col1 as either

<correlation name> . <column name>
== <identifier> . <identifier>

or

<table name> . <column name>
== <qualified name> . <identifier>

so you can choose to ignore that rules for <qualified name> if no explicit
schema name is given.

Wow, that's whacky.

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

I would like to see the example at the very top working, but if it's too
crazy, we can worry about it in a future life.

> 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",

Is this really necessary? It seems confusing.

--
Peter Eisentraut peter_e(at)gmx(dot)net