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(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
> Sent: Saturday, 9 March 2002 9:01 AM
> To: pgsql-hackers(at)postgreSQL(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>