Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <37569.1158526344549.JavaMail.jira@brutus>
Date: Sun, 17 Sep 2006 13:52:24 -0700 (PDT)
From: "Bryan Pendleton (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-147) ERROR 42X79 not consistant ? - same
 column name specified twice
In-Reply-To: <1599192524.1108503225502.JavaMail.root@ajax.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

     [ http://issues.apache.org/jira/browse/DERBY-147?page=all ]

Bryan Pendleton updated DERBY-147:
----------------------------------

    Attachment: patch_with_tests.diff

Attached is "patch_with_tests.diff", a patch proposal.

I took the work that Bernd did, examined the comments from
the reviewers, and added a bunch of tests. During the testing,
I determined that Satheesh's comment was pretty important, and
that there were actually a number of important ambiguous cases
that we needed to catch. Happily, I discovered that there was already
a method on ResultColumn called "isEquivalent" which appears to
have just the right semantics for distinguishing between cases
where the same column has been selected multiple times, and
cases where completely different columns happen to have the same
column name in the result list.

Note that since we still have to catch and reject ambiguous SELECT
statements, we aren't able to remove the error message and the
SQL state as Knut Anders had hoped we could.

Additionally, I discovered that there were some fairly complicated cases
involving the difference between the "exposed" name of a table and the
base name of the table, which meant that it was problematic to merge
the various getOrderByColumn methods as had been proposed in some
of the earlier patch proposals. Therefore, I reverted to a smaller patch,
which doesn't combine the nearly-redundant versions of getOrderByColumn,
but which preserves the existing behavior in the case of table correlation names.

It took me a while to figure out how "orderBySelect" processing worked, so
I added a comment about that.

The end result is a patch proposal which changes just 4 lines of code; the
rest is comments and tests.

derbyall runs clean, and I added as many new tests as I could think of, so
hopefully I've covered the testing pretty well. Still, I would love to hear
suggestions from reviewers regarding additional tests that could be added.

Please have a look at this patch and let us know your comments.


> ERROR 42X79 not consistant ? - same column name specified twice
> ---------------------------------------------------------------
>
>                 Key: DERBY-147
>                 URL: http://issues.apache.org/jira/browse/DERBY-147
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>            Reporter: Bernd Ruehlicke
>         Assigned To: Bryan Pendleton
>         Attachments: derby-147-10.0.2.1.diff, derby-147.diff, patch_with_tests.diff
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira