dupe column names not supported (Shared.DC.ZRDB) ?
Hi there, I'm currently testing (documenting, building RPM packages for) the following configuration: RedHat 8.0 python 2.1.3 postgresql 7.3 mx 2.0.3 psycopg 1.1 Zope-2.6.1b1 Having just got all the packages built and installed, I thought I'd run the database adapter, Zope and postgres through their paces starting with the SQL examples at: http://www.postgresql.org/idocs/index.php?tutorial-sql.html I ran into trouble with: http://www.postgresql.org/idocs/index.php?tutorial-join.html specifically with: SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, W2.city, W2.temp_lo AS low, W2.temp_hi AS high FROM weather W1, weather W2 WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi > W2.temp_hi; Error, exceptions.ValueError: Duplicate column name, city Traceback (innermost last): Module ZPublisher.Publish, line 98, in publish Module ZPublisher.mapply, line 88, in mapply Module ZPublisher.Publish, line 39, in call_object Module Shared.DC.ZRDB.Connection, line 115, in manage_test Module Shared.DC.ZRDB.Results, line 52, in __init__ ValueError: Duplicate column name, city Obviously this will work if you change the query to: SELECT W1.city AS city1, W1.temp_lo AS low1, W1.temp_hi AS high1, W2.city AS city2, W2.temp_lo AS low2, W2.temp_hi AS high2 FROM weather W1, weather W2 WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi > W2.temp_hi; City1 Low1 High1 City2 Low2 High2 San Francisco 43 57 San Francisco 46 50 Hayward 37 54 San Francisco 46 50 I did a little digging to see if the above is valid SQL92 syntax, or just uses a PostgreSQL specific extension, the only things I found: In: http://www.postgresql.org/idocs/index.php?sql-select.html "In the SQL92 standard, the optional keyword AS is just noise and can be omitted without affecting the meaning. The PostgreSQL parser requires this keyword when renaming output columns because the type extensibility features lead to parsing ambiguities in this context. AS is optional in FROM items, however." And In: http://manuals.sybase.com/onlinebooks/group-as/asg1150e/mig_gde/@Generic__Bo... "Correlation Names ANSI requires correlation names on self-joins. The following query is now invalid: select columns from table1, table1 where clause ... This query must be rewritten as: select columns from table1 t1, table1 t2 where clause ..." Trying it without the "noise" in psql: testdb1=> SELECT W1.city, W1.temp_lo, W1.temp_hi, testdb1-> W2.city, W2.temp_lo, W2.temp_hi testdb1-> FROM weather W1, weather W2 testdb1-> WHERE W1.temp_lo < W2.temp_lo testdb1-> AND W1.temp_hi > W2.temp_hi; city | temp_lo | temp_hi | city | temp_lo | temp_hi ---------------+---------+---------+---------------+---------+--------- San Francisco | 43 | 57 | San Francisco | 46 | 50 Hayward | 37 | 54 | San Francisco | 46 | 50 (2 rows) testdb1=> The question: If the above example query is valid ANSI SQL92, do we aim to support it? Adam
Same problem has been reported with Oracle 8.1, since it too will return duplicate names. The SQL92 spec is a bit ambiguous about whether all returned column names must be unique. Ross On Tue, Dec 17, 2002 at 01:09:13PM -0500, Adam Manock wrote:
specifically with:
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, W2.city, W2.temp_lo AS low, W2.temp_hi AS high FROM weather W1, weather W2 WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi > W2.temp_hi;
Error, exceptions.ValueError: Duplicate column name, city
Traceback (innermost last): Module ZPublisher.Publish, line 98, in publish Module ZPublisher.mapply, line 88, in mapply Module ZPublisher.Publish, line 39, in call_object Module Shared.DC.ZRDB.Connection, line 115, in manage_test Module Shared.DC.ZRDB.Results, line 52, in __init__ ValueError: Duplicate column name, city
The semantics of Zope's database functionality is such that duplicate column names are not (and probably never will be) possible in the same result set. This is because Zope expects to access columns by name rather than position. Therefore each one must have a distinct name. The only workaround I can see would be some sort of automatic renaming of columns, but that sounds like a bad idea since it introduces some serious bug potential if the columns are renamed differently then the application expects. Just for curiousity, what is the use case for returning multiple columns with the same name? Sounds dubious to me. -Casey On Tuesday 17 December 2002 02:11 pm, Ross J. Reedstrom wrote:
Same problem has been reported with Oracle 8.1, since it too will return duplicate names. The SQL92 spec is a bit ambiguous about whether all returned column names must be unique.
Ross
On Tue, Dec 17, 2002 at 01:09:13PM -0500, Adam Manock wrote:
specifically with:
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, W2.city, W2.temp_lo AS low, W2.temp_hi AS high FROM weather W1, weather W2 WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi > W2.temp_hi;
Error, exceptions.ValueError: Duplicate column name, city
Traceback (innermost last): Module ZPublisher.Publish, line 98, in publish Module ZPublisher.mapply, line 88, in mapply Module ZPublisher.Publish, line 39, in call_object Module Shared.DC.ZRDB.Connection, line 115, in manage_test Module Shared.DC.ZRDB.Results, line 52, in __init__ ValueError: Duplicate column name, city
On Tue, Dec 17, 2002 at 02:25:27PM -0500, Casey Duncan wrote:
The semantics of Zope's database functionality is such that duplicate column names are not (and probably never will be) possible in the same result set. This is because Zope expects to access columns by name rather than position. Therefore each one must have a distinct name.
Oh, I understand the problem: just reporting that PostgreSQL isn't the only one doing it.
The only workaround I can see would be some sort of automatic renaming of columns, but that sounds like a bad idea since it introduces some serious bug potential if the columns are renamed differently then the application expects.
Yeah, that'd suck.
Just for curiousity, what is the use case for returning multiple columns with the same name? Sounds dubious to me.
I'm not aware of a use case, per se. It comes about from implementing this bit of the SQL92 standard: 7.9.9.b) If the i-th <derived column> in the <select list> does not specify an <as clause> and the <value expression> of that <derived column> is a single <column reference>, then the <column name> of the i-th column of the result is C. Note that that says 'C', not 'CR'. i.e. is _must_ be the undecorated column name, not the column reference (schema.table.column) The next subclause (7.9.9.c) handles the case of it not being a single column reference (like a function, or somthing), and says it's then 'implementation dependent' and only needs to be unique in the set of tables referenced by the SQL. It's not clear whether that's supposed to include the 'derived table' that is the output of this 'query specification'. Ross -- Ross Reedstrom, Ph.D. reedstrm@rice.edu Research Scientist phone: 713-348-6166 The Connexions Project http://cnx./rice.edu fax: 713-348-6182 Rice University MS-39 Houston, TX 77005
Just for curiousity, what is the use case for returning multiple columns with the same name? Sounds dubious to me.
A self join will return multiple columns with the same name unless AS keyword is used. The use of AS keyword is supposed to be optional. Assuming that a user does use AS, labeling of the returned columns will be at the discretion of the user, and therefore again not guaranteed to be unique. I would guess that if Zope assumes that column names are unique, changing that assumption now would be a major p.i.t.a. :-) Adam
participants (3)
-
Adam Manock -
Casey Duncan -
Ross J. Reedstrom