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
/*
*-----------------------------------------------------------------
* IBM Confidential
*
* OCO Source Materials
*
* WebSphere Commerce
*
* (C) Copyright IBM Corp. 2009
*
* The source code for this program is not published or otherwise
* divested of its trade secrets, irrespective of what has
* been deposited with the U.S. Copyright Office.
*-----------------------------------------------------------------
*/
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ViewTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Connection connection = DriverManager.getConnection("jdbc:derby:wombat");
DatabaseMetaData metaData = connection.getMetaData();
ResultSet columns = metaData.getColumns(null, null, "V", null);
while (columns.next()) {
String name = columns.getString("COLUMN_NAME");
String pos = columns.getString("ORDINAL_POSITION");
String type = columns.getString("TYPE_NAME");
System.out.println("name = " + name + ", pos = " + pos + ", type = " + type);
}
columns.close();
connection.close();
}
}
connect 'jdbc:derby:wombat;create=true';
create table A (id integer, data varchar(20), data2 integer);
create view V (data, num) as select data, data2 + 2 from A group by data, data2;
insert into A values (3, 'G', 5), (23, 'G', 4), (5, 'F', 1), (2, 'H', 4), (1, 'F', 5);
