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

Fri, 08 Mar 2002 17:00:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Do FROM items of different schemas conflict?
Tom Lane wrote:
> 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;

 From my simplistic understanding, I would say if we allowed this, we
would have to require the schema designtation be on every reference to
tab1 in the query.  Is that something we can do?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 8 Mar 2002 17:27:39 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Do FROM items of different schemas conflict?
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> SELECT * FROM schema1.tab1, schema2.tab1;

> From my simplistic understanding, I would say if we allowed this, we
> would have to require the schema designtation be on every reference to
> tab1 in the query.  Is that something we can do?

Well, that's what's not entirely clear to me.

If you write

	SELECT ... FROM schema1.tab1 AS tab1;

then clearly this item *can* be referenced by just tab1.col1, and
probably a strict reading would say that it *must* be referenced
that way (ie, schema1.tab1.col1 should not work).  But in the case
without the AS clause, I'm not at all sure what the spec means to
allow.

(BTW, the equivalent passages in SQL99 are no help; they are several
times longer but utterly fail to clarify the point.)

			regards, tom lane

Fri, 08 Mar 2002 17:34:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Do FROM items of different schemas conflict? 
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

Fri, 08 Mar 2002 18:13:37 -0500
Fernando Nasser <fnasser@redhat.com>
Re: Do FROM items of different schemas conflict?
Tom Lane wrote:
> Okay folks, time to put on your language-lawyer hats ...
> 
> 	SELECT * FROM schema1.tab1, schema2.tab1;
> 
> Is this allowed?  SQL92 appears to allow it: section 6.3 <table
> reference> says:

FWIW:
This works in Oracle 8.1.6

Connected to Oracle8i Enterprise Edition Release 8.1.6.3.0
Connected as cyapps

SQL> select * from apps.plan_table, cyapps.plan_table;

  <snip>

24 rows selected



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

...so does this...
SQL> select plan_table.operation from apps.plan_table;

  <snip>

12 rows selected


> Comments?  Is anyone familiar with the details of how other DBMSes
> handle these issues?

MSSQL 7 seems to handle the first syntax also, but not the second.

Joe


Fri, 08 Mar 2002 15:26:01 -0800
Joe Conway <mail@joeconway.com>
Re: Do FROM items of different schemas conflict?
Fernando Nasser <fnasser@redhat.com> writes:
> 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.

What I am wondering about is how you tell whether it is ambiguous.

In particular, if schema1 is not in the search path then I do not
see how the spec can be read to say that "tab1.col1" matches "FROM
schema1.tab1" (note no AS here).  It does seem that everyone agrees that
that is the meaning --- there is a footnote in Date that shows he thinks
so too --- but as far as I can tell this directly contradicts the text
of the spec, because there is noplace that says how to match an
unqualified "tab1" against the qualified "schema1.tab1", except for
5.4-10 which would clearly disallow such a match.  Where am I missing
something?

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

Right, of course.  Sorry for the imprecision.

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

Well, the point is that I would like to allow that, specifically because
I would like to say that the equivalence is exact.  I don't see any
value in enforcing this particular nitpick.

			regards, tom lane

Fri, 08 Mar 2002 18:28:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Do FROM items of different schemas conflict? 
Tom Lane wrote:
> 
> Fernando Nasser <fnasser@redhat.com> writes:
> > 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.
> 
> What I am wondering about is how you tell whether it is ambiguous.
> 

Ambiguous == "found more than one match in the list of tables in FROM"


> In particular, if schema1 is not in the search path then I do not
> see how the spec can be read to say that "tab1.col1" matches "FROM
> schema1.tab1" (note no AS here).  It does seem that everyone agrees that
> that is the meaning --- there is a footnote in Date that shows he thinks
> so too 

I will really need some time to see which clauses in the spec can be 
interpreted that way.  But what people seem to believe is that the
match should occur whenever possible (i.e., unless is ambiguous).

> --- but as far as I can tell this directly contradicts the text
> of the spec, because there is noplace that says how to match an
> unqualified "tab1" against the qualified "schema1.tab1", except for
> 5.4-10 which would clearly disallow such a match.  Where am I missing
> something?
> 

Yes, read in solation it looks like it forbids it (darn, I wish I had
the standard here).  But remember that the only place you should look
for a match for "tab1" is in the things listed in the FROM clause.
The POSTQUEL extension of adding the tables for you (if I understood
right) is an aberration (if it is still supported it will ave to be
removed).

As your namespace is now restricted to the FROM clause it is easy to
see what would be the "implicit" schema you'll give to "tab1" -- the
only one that you find prefixing a tab1 in te FROM list.  If you find
more than one there is ambiguity, so it is not allowed -- AS clause
is required then.



> >> 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
> 
> Well, the point is that I would like to allow that, specifically because
> I would like to say that the equivalence is exact.  I don't see any
> value in enforcing this particular nitpick.
> 

But you must.  As soon as you used "AS bar" it is not a table
name anymore, i.e., it cannot be qualified by an schema.  It is
a correlation name which is a single unqualified name and whatever
refers to it _must_ use only that single name.  So "foo.bar.col1"
makes absolute no sense after you say "AS bar".



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

Fri, 08 Mar 2002 18:48:42 -0500
Fernando Nasser <fnasser@redhat.com>
Re: Do FROM items of different schemas conflict?
Joe Conway <mail@joeconway.com> writes:
> This works in Oracle 8.1.6

So what does Oracle do with

select plan_table.operation from apps.plan_table, cyapps.plan_table;

??

			regards, tom lane

Fri, 08 Mar 2002 19:19:47 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Do FROM items of different schemas conflict? 
Fernando Nasser <fnasser@redhat.com> writes:
> The POSTQUEL extension of adding the tables for you (if I understood
> right) is an aberration (if it is still supported it will ave to be
> removed).

No it won't.  The implicit-RTE extension doesn't come into play until
after you've failed to find a matching RTE.  It cannot break queries
that are valid according to spec --- it only affects queries that should
flag an error according to spec.

My question is about what it means to find a matching RTE and when two
similarly-named RTEs should be rejected as posing a name conflict.
Implicit RTEs are not relevant to the problem.

			regards, tom lane

Fri, 08 Mar 2002 19:29:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Do FROM items of different schemas conflict? 
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> 
>>This works in Oracle 8.1.6
>>
> 
> So what does Oracle do with
> 
> select plan_table.operation from apps.plan_table, cyapps.plan_table;
> 
> ??
> 

SQL> select plan_table.operation from apps.plan_table, cyapps.plan_table;

select plan_table.operation from apps.plan_table, cyapps.plan_table

ORA-00918: column ambiguously defined


Joe


Fri, 08 Mar 2002 16:30:15 -0800
Joe Conway <mail@joeconway.com>
Re: Do FROM items of different schemas conflict?
Tom Lane wrote:
> 
> Fernando Nasser <fnasser@redhat.com> writes:
> > The POSTQUEL extension of adding the tables for you (if I understood
> > right) is an aberration (if it is still supported it will ave to be
> > removed).
> 
> No it won't.  The implicit-RTE extension doesn't come into play until
> after you've failed to find a matching RTE.  It cannot break queries
> that are valid according to spec --- it only affects queries that should
> flag an error according to spec.
> 
> My question is about what it means to find a matching RTE and when two
> similarly-named RTEs should be rejected as posing a name conflict.
> Implicit RTEs are not relevant to the problem.
> 

That was a side question, as I though this could get in the way.
I am glad it doesn't.

The rest I said is still valid and is unrelated to this.

BTW, I believe Oracle got the standard right this time.
What Joe Conway has been posting is exactly what I understood.


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

Fri, 08 Mar 2002 19:57:24 -0500
Fernando Nasser <fnasser@redhat.com>
Re: Do FROM items of different schemas conflict?
Hi,

I'm certainly not a language lawyer, but I tried the following on our Oracle
8.0.5 install:

* Logged in as two separate users (ypsedba, ypkbdba) and ran

	create table test_from_clause (field1 int)

in both of them.

* Logged in as system (the Oracle super user with access to both users
schema).

* Executed

	select * from ypsedba.test_from_clause

and

	select * from ypkbdba.test_from_clause

to verify permissions / sanity.

* Executed

	select * from ypsedba.test_from_clause, ypkbdba.test_from_clause

to check your test case.

Results:

* No errors

* Result set had two columns - "FIELD1" and "FIELD1_1"

As mentioned above, I'm not a language lawyer so I don't know whether the
above is a correct implementation of the standard.

Regards,

Mark Pritchard

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Saturday, 9 March 2002 9:01 AM
> To: pgsql-hackers@postgreSQL.org
> Subject: [HACKERS] Do FROM items of different schemas conflict?
>
>
> 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 <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.
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


Tue, 12 Mar 2002 07:56:23 +1100
"Mark Pritchard" <mark@tangent.net.au>
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@gmx.net


Mon, 11 Mar 2002 16:21:17 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Do FROM items of different schemas conflict?