extra column in DatabaseMetaData.getColumns() with group by in view

[Kathey Marsden <km...@sbcglobal.net>]

I have user report that DatabaseMetaData.getColumns() on a view with a 
group by returns an extra column.  Attached is the reproduction.  Run 
the create.sql script and then run the program ViewTest. The bug is a 
regression in 10.3 (Latest on the 10.1 and 10.2 branch works fine and it 
still exists in trunk.)

This sounds very familiar so I was wondering if there is already an 
issue filed.  The closest thing I could find was 
https://issues.apache.org/jira/browse/DERBY-3141 which was regarding 
ResultSetMetaData and an order by.  I haven't checked yet to see if the 
ResultSetMetaData is also wrong.

Kathey

Re: extra column in DatabaseMetaData.getColumns() with group by in view

[Kathey Marsden <km...@sbcglobal.net>]

Rick Hillegas wrote:
> Note that a query against the view returns the correct number of 
> columns, it is only the metadata which is wrong. 
Thanks Rick and Bryan, based on your input, I think this is a new 
issue.  If filed DERBY-4230 and pointed to this thread to make the 
alternate repro available to whomever is working on it.

Kathey

Re: extra column in DatabaseMetaData.getColumns() with group by in view

[Rick Hillegas <Ri...@Sun.COM>]

Bryan Pendleton wrote:
>> This sounds very familiar so I was wondering if there is already an 
>> issue filed.  The closest thing I could find was 
>> https://issues.apache.org/jira/browse/DERBY-3141 
>
> Also might have some similarities with DERBY-3997.
>
> thanks,
>
> bryan
>
I have pushed the view definition around a little. In order to reproduce 
the problem I needed both the expression in the SELECT list (data2 + 2) 
and the GROUP BY clause. Note that a query against the view returns the 
correct number of columns, it is only the metadata which is wrong. The 
following simpler script demonstrates the problem:

drop view v;
drop table a;

create table A (data2 integer);
create view V (num) as select data2 + 2 from A group by data2;

-- returns only 1 column, as expected
select * from v;

-- reports that v has 2 columns, which is not true
--
-- here we use the metadata vtis from DBMDWrapper, attached to 
http://issues.apache.org/jira/browse/DERBY-3973
select column_name
from table ( getColumns( null, null, 'V', null ) ) s;

Regards,
-Rick

Re: extra column in DatabaseMetaData.getColumns() with group by in view

[Bryan Pendleton <bp...@amberpoint.com>]

> This sounds very familiar so I was wondering if there is already an 
> issue filed.  The closest thing I could find was 
> https://issues.apache.org/jira/browse/DERBY-3141 

Also might have some similarities with DERBY-3997.

thanks,

bryan