select t.c1 as a, t.c2 as a from t
What should we do if you add "order by a" to the above select? "a" is truly ambiguous.
I can think of several alternatives:
1. Prohibit duplicate select list column names, whether or not there is an order by clause.
2. Change ORDER BY processing. When it finds an ambiguous column name check whether all the columns with that name are really the same. Allow the ORDER BY if so. Remember that Tomohito Nakayama is working on allowing general expressions in the ORDER BY clause, so this is not so easy.
3. Change ORDER BY processing. When it finds an ambiguous column name check whether the sort key names a column in an underlying table (not a correlation name). Allow the ORDER BY if so. Throw an SQLException if not, even if all the possible columns have the same value. This fixes the case in the bug report.
4. Keep the error checking as is but improve the error message. Something like "ORDER BY column 'xx' is ambiguous".
5. Do nothing.
Jack Klebanoff Diljeet Dhillon (JIRA) wrote:
[ http://issues.apache.org/jira/browse/DERBY-147?page=comments#action_61483 ]
Diljeet Dhillon commented on DERBY-147:
---------------------------------------
Hi, Have we established whether a possible patch/fix will be provided for this issue. and what the possible timescales may be?
ERROR 42X79 not consistant ? - same column name specified twice ---------------------------------------------------------------
Key: DERBY-147
URL: http://issues.apache.org/jira/browse/DERBY-147
Project: Derby
Type: Bug
Reporter: Bernd Ruehlicke
This happens from JDBC or ij. Here the output form ij>
ij version 10.0 CONNECTION0* - jdbc:derby:phsDB * = current connection ij> select a1.XXX_foreign, a1.native, a1.kind, a1.XXX_foreign FROM slg_name_lookup a1 ORDER BY a1.XXX_foreign;
ERROR 42X79: Column name 'XXX_FOREIGN' appears more than once in the result of the query expression. But when removing the ORDER BY and keeping the 2 same column names it works
ij> select a1.XXX_foreign, a1.native, a1.kind, a1.XXX_foreign FROM slg_name_lookup a1;
XXX_FOREIGN |NATIVE |KIND |XXX_FOREIGN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 rows selected ij> So - it seams to be OK to specify the same column twice - as long as you do not add the ORDER BY clause. I woul dof course like that the system allows this - but at leats it should be consistant and either allow both or none of the two queries above.
