db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-147) ERROR 42X79 not consistant ? - same column name specified twice
Date Sun, 17 Sep 2006 20:52:24 GMT
     [ 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

        

Mime
View raw message