Fields used in ON condition of JOIN have an explicit alias |
Robbert Jan Grootjans |
2010-10-08 03:32:31 |
When executing a query with the following syntax:
SELECT a."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
The resultset contains a column with name 'a.barid' in stead of the expected 'barid', as the column is not ambiguous in this context.
|
Re:Fields used in ON condition of JOIN have an explicit alias |
HXTT Support |
2010-10-08 06:11:38 |
>SELECT a."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
Test. It's normal.
Both of ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnName will return BARID when bar table has NOT the same column name.
Both of ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnName will return a.BARID when bar table HAS the same column name.
SELECT a."barid" AS BARID from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id" will return BARID any condition:)
|
Re:Fields used in ON condition of JOIN have an explicit alias |
HXTT Support |
2010-10-08 06:12:11 |
>ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnName
It should be ResultSetMetaData.getColumnLabeland ResultSetMetaData.getColumnName
|
Re:Fields used in ON condition of JOIN have an explicit alias |
Robbert Jan Grootjans |
2010-10-08 06:29:54 |
True, I'm sorry, the expectation that we had was one would get 'a.BARID' when two
1. 'bar' table and 'foo' table both have the column 'barid'
2. both have been selected in the query
when only one of the columns has been selected in the query, the result would be 'barid'
so:
SELECT a."barid", b."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
would result in 'a.barid' and 'b.barid' (instead of an error that the query was ambiguous, which you get in most DBMS's)
SELECT a."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
would result in 'barid'
I'm sorry I was unclear the first time.
|
Re:Re:Re:Re:Fields used in ON condition of JOIN have an explicit alias |
Robbert Jan Grootjans |
2010-10-11 06:49:34 |
I am unsure if you guys interpretted my follow-up as an aknowledgement or a new question. Let me state the following. I understand the way you describe it should work, but I would was expecting a slightly different approach.
In you statement you say the mandatory aliasing triggered by the presence of two identical fields in joined tables. I would expect it to be triggered in the situation that two identical fields are selected. So:
SELECT a."barid", b."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
would result in 'a.barid' and 'b.barid' (instead of an error that the query was ambiguous, which you get in most DBMS's)
SELECT a."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
would result in 'barid'
My apologies for replying in such a hasty fashion and assuming you missed my point, but you guys always respond really fast ;)
|
Re:Re:Re:Re:Re:Fields used in ON condition of JOIN have an explicit alias |
HXTT Support |
2010-10-11 07:19:40 |
>so:
>SELECT a."barid", b."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
>would result in 'a.barid' and 'b.barid' (instead of an error that the query was
>ambiguous, which you get in most DBMS's)
Yeah. Supported.
>SELECT a."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
>would result in 'barid'
It will resulted barid only when b hasn't barid column, and will resulted a.barid when b has a barid column. Sorry. It seems that you wish to get barid also when b has a barid columen ?
|
Re:Re:Re:Re:Re:Re:Fields used in ON condition of JOIN have an explicit alias |
Robbert Jan Grootjans |
2010-10-11 07:50:03 |
Exactly, I would expect to get barid when a AND b have a barid column, but only one is selected in the result.
If both are selected I would expect a.barid and b.barid.
I'm not saying my wishes or expectations are correct, but this differs from other SQL/JDBC implementations we support as they do have a similar behaviour (though they throw an exception in the case a statement is ambiguous).
thanks for the quick response!
|
Re:Re:Re:Re:Re:Re:Re:Fields used in ON condition of JOIN have an explicit alias |
HXTT Support |
2010-10-12 19:26:51 |
>I'm not saying my wishes or expectations are correct
Supported. Please download the latest package.
|
Main
webmaster@hxtt.com