[Zope-dev] dupe column names not supported (Shared.DC.ZRDB) ?

[Adam Manock]

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