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__BookTextView/4167 "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 _______________________________________________ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )