Re: Do FROM items of different schemas conflict? - Mailing list pgsql-hackers
From | Mark Pritchard |
---|---|
Subject | Re: Do FROM items of different schemas conflict? |
Date | |
Msg-id | EGECIAPHKLJFDEJBGGOBKEGFHOAA.mark@tangent.net.au Whole thread Raw |
In response to | Do FROM items of different schemas conflict? (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-hackers |
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 >
pgsql-hackers by date: