db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jack Klebanoff <klebanoff-de...@sbcglobal.net>
Subject Re: [jira] Commented: (DERBY-147) ERROR 42X79 not consistant ? - same column name specified twice
Date Thu, 24 Mar 2005 17:44:21 GMT
It is not so simple. The order by specification may be ambiguous. Derby 
allows the following:
  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.
>>    
>>
>
>  
>


Mime
View raw message