Обсуждение: BUG #1540: Enhancement request: 'ambiguous' column reference in psql

The following bug has been logged online:

Bug reference:      1540
Logged by:          Richard Neill
Email address:      postgresql@richardneill.org
PostgreSQL version: 8.01
Operating system:   Linux
Description:        Enhancement request: 'ambiguous' column reference in
psql
Details:

Dear Postgresql team,

I have a small suggestion, which isn't quite a bug, but where psql throws an
error which it could in principle recover from. These occur when a column
reference is ambiguous, but isn't really, because of information supplied in
the join.


Here is an example, which I just tested in 8.01.


These are the database tables:
----------------------------------
tbl_instruments:
    instrument    character varying
    priceband    smallint

----------------------------------
tbl_prices:
    priceband    smallint
    pounds        double precision
-----------------------------------


This query fails:
------------------------------------
SELECT instrument,priceband,pounds FROM tbl_instruments,tbl_prices WHERE
tbl_instruments.priceband=tbl_prices.priceband;

ERROR:  column reference "priceband" is ambiguous
-----------------------------------



This query succeeds:
-----------------------------------------
SELECT instrument,tbl_instruments.priceband,pounds FROM
tbl_instruments,tbl_prices WHERE
tbl_instruments.priceband=tbl_prices.priceband;
------------------------------------------


I think that the first query ought to succeed, since although priceband is
ambiguous (it could mean either tbl_prices.priceband or
tbl_instruments.priceband), the information in the WHERE clause means that
they are explicitly equal, and so it doesn't matter which one we use.


Thank you very much for all your work - Postgresql is really useful to me.

Richard

Richard Neill wrote:
> I think that the first query ought to succeed, since although priceband is
> ambiguous (it could mean either tbl_prices.priceband or
> tbl_instruments.priceband), the information in the WHERE clause means that
> they are explicitly equal, and so it doesn't matter which one we use.

Well, it just means the type's equality operator returns true for these
two values -- I'm not sure it is wise to assume they are completely
interchangeable.

More generally, it makes sense to me that resolution of column
references is a property of the syntax of a statement, not something
derived from its semantics (e.g. the fact that we can infer for some
particular statement that two columns are equal).

-Neil

"Richard Neill" <postgresql@richardneill.org> writes:
> SELECT instrument,priceband,pounds FROM tbl_instruments,tbl_prices WHERE
> tbl_instruments.priceband=tbl_prices.priceband;

> ERROR:  column reference "priceband" is ambiguous

> I think that the first query ought to succeed, since although priceband is
> ambiguous (it could mean either tbl_prices.priceband or
> tbl_instruments.priceband), the information in the WHERE clause means that
> they are explicitly equal, and so it doesn't matter which one we use.

Doing that would be contrary to the SQL specification, AFAICS.

However, you can get the effect you want by writing the query like

SELECT instrument,priceband,pounds FROM
tbl_instruments JOIN tbl_prices USING (priceband);

which both provides the join condition and logically merges the two
input columns into just one output column.

            regards, tom lane

Dear Tom and Neil,

Thanks very much for your help, and your explanations. This makes a lot
of sense, and I agree - this bug is definitely invalid.

Best wishes

Richard




Tom Lane wrote:
> "Richard Neill" <postgresql@richardneill.org> writes:
>
>>SELECT instrument,priceband,pounds FROM tbl_instruments,tbl_prices WHERE
>>tbl_instruments.priceband=tbl_prices.priceband;
>
>
>>ERROR:  column reference "priceband" is ambiguous
>
>
>>I think that the first query ought to succeed, since although priceband is
>>ambiguous (it could mean either tbl_prices.priceband or
>>tbl_instruments.priceband), the information in the WHERE clause means that
>>they are explicitly equal, and so it doesn't matter which one we use.
>
>
> Doing that would be contrary to the SQL specification, AFAICS.
>
> However, you can get the effect you want by writing the query like
>
> SELECT instrument,priceband,pounds FROM
> tbl_instruments JOIN tbl_prices USING (priceband);
>
> which both provides the join condition and logically merges the two
> input columns into just one output column.
>
>             regards, tom lane
>