db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [jira] Commented: (DERBY-147) ERROR 42X79 not consistant ? - same column name specified twice
Date Thu, 24 Mar 2005 18:04:13 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
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 <b>some</b> of the queries
and what DB2 does with those, though we should follow SQL guidance for
Derby.<br>
<br>
db2&gt; select i, i from t order by i<br>
I&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
I<br>
----------- -----------<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
0<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>
db2&gt; select i, j as i from t order by i<br>
I&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
I<br>
----------- -----------<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
4<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>
db2&gt; select i as A, j as A from t order by A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&lt;== Explicit
alias. Error.<br>
Error.<br>
<br>
Satheesh<br>
<br>
Jack Klebanoff wrote:
<blockquote cite="mid4242FC75.9030203@sbcglobal.net" type="cite">It is
not so simple. The order by specification may be ambiguous. Derby
allows the following:
  <br>
&nbsp;select t.c1 as a, t.c2 as a from t
  <br>
What should we do if you add "order by a" to the above select? "a" is
truly ambiguous.
  <br>
  <br>
I can think of several alternatives:
  <br>
1. Prohibit duplicate select list column names, whether or not there is
an order by clause.
  <br>
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.
  <br>
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.
  <br>
4. Keep the error checking as is but improve the error message.
Something like "ORDER BY column 'xx' is ambiguous".
  <br>
5. Do nothing.
  <br>
  <br>
Jack Klebanoff
  <br>
Diljeet Dhillon (JIRA) wrote:
  <br>
  <br>
  <blockquote type="cite">&nbsp;&nbsp;&nbsp; [
<a class="moz-txt-link-freetext" href="http://issues.apache.org/jira/browse/DERBY-147?page=comments#action_61483">http://issues.apache.org/jira/browse/DERBY-147?page=comments#action_61483</a>
]
    <br>
&nbsp;&nbsp;&nbsp; Diljeet Dhillon commented on DERBY-147:
    <br>
---------------------------------------
    <br>
    <br>
Hi,
    <br>
Have we established whether a possible patch/fix will be provided for
this issue. and what the possible timescales may be?
    <br>
    <br>
    <br>
    <br>
    <br>
&nbsp;
    <br>
    <br>
    <blockquote type="cite">ERROR 42X79 not consistant ? - same column
name specified twice
      <br>
---------------------------------------------------------------
      <br>
      <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Key: DERBY-147
      <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; URL: <a class="moz-txt-link-freetext"
href="http://issues.apache.org/jira/browse/DERBY-147">http://issues.apache.org/jira/browse/DERBY-147</a>
      <br>
&nbsp;&nbsp;&nbsp; Project: Derby
      <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type: Bug
      <br>
&nbsp;&nbsp; Reporter: Bernd Ruehlicke
      <br>
&nbsp;&nbsp; <br>
    </blockquote>
    <br>
&nbsp;
    <br>
    <br>
    <blockquote type="cite">This happens from JDBC or ij. Here the
output form ij&gt;
      <br>
ij version 10.0 CONNECTION0* -&nbsp;&nbsp;&nbsp;&nbsp; jdbc:derby:phsDB *
= current
connection ij&gt; select a1.XXX_foreign, a1.native, a1.kind,
a1.XXX_foreign FROM slg_name_lookup a1 ORDER BY a1.XXX_foreign;
      <br>
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
      <br>
ij&gt; select a1.XXX_foreign, a1.native, a1.kind, a1.XXX_foreign FROM
slg_name_lookup a1;
      <br>
XXX_FOREIGN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|NATIVE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|KIND&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|XXX_FOREIGN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------&nbsp;
0 rows selected ij&gt; So - it seams to be OK to specify the same
column twice - as long as you do not add the ORDER BY clause.&nbsp; 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.
      <br>
&nbsp;&nbsp; <br>
    </blockquote>
    <br>
&nbsp;
    <br>
    <br>
  </blockquote>
  <br>
  <br>
  <br>
</blockquote>
</body>
</html>


Mime
View raw message