Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII; format=flowed
Date: Thu, 14 May 2009 12:17:23 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: extra column in DatabaseMetaData.getColumns() with group by in
 view
In-reply-to: <4A0C6163.5090105@amberpoint.com>
Sender: Richard.Hillegas@Sun.COM
To: derby-dev@db.apache.org
Message-id: <4A0C6E43.1090400@sun.com>
References: <4A0C58CB.4020905@sbcglobal.net> <4A0C6163.5090105@amberpoint.com>
User-Agent: Thunderbird 2.0.0.21 (Macintosh/20090302)

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