Re: [BUGS] BUG #1540: Enhancement request: 'ambiguous' column reference

#1

Richard Neill wrote:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#2

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:

Quote:

"Richard Neill" <postgresql (AT) richardneill (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] BUG #1540: Enhancement request: 'ambiguous' column reference

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] BUG #1540: Enhancement request: 'ambiguous' column reference - 03-13-2005 , 09:03 PM

Re: [BUGS] BUG #1540: Enhancement request: 'ambiguous' column reference - 03-17-2005 , 11:22 PM