I think we should to enumerate all cases that cause ambiguity. Some are
possibly OK(multiple references to same column), some should raise
error(cases where there is an explicit alias). There is a need to also
check what SQL standard says here. Here are some of the queries
and what DB2 does with those, though we should follow SQL guidance for
Derby.
db2> select i, i from t order by i
I I
----------- -----------
0 0
1 1
2 2
db2> select i, j as i from t order by i
I I
----------- -----------
0 4
1 1
2 2
db2> select i as A, j as A from t order by A <== Explicit
alias. Error.
Error.
Satheesh
Jack Klebanoff wrote:
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.
|
