Thread: 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
<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
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, Pennsylvania19026
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
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
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
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
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
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
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
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
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
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 >
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