MySQL Bugs: #13586: getTables() does not handle VIEWS properly

Bug #13586	getTables() does not handle VIEWS properly
Submitted:	28 Sep 2005 21:35	Modified:	30 Sep 2005 13:45
Reporter:	Thomas Kellerer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.2 alpha	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
DatabaseMetaData.getTables() does not seem to handle VIEWS properly.

MySQL version: 5.0.13
Driver version: 3.2. alpha

This is working correctly with 3.1.10

How to repeat:
1) when called with: 
getTables(null, null, null, new String[] { "TABLE"})

It returns tables and views (should only return tables)
The type column always contains TABLE even if it's a VIEW.

2) when called with:
getTables(null, null, null, new String[] { "VIEW"})

It returns tables and views (should only return views) , 
The types in the TABLE_TYPE column are reported correctly though

The type are also reported correctly when called with:
getTables(null, null, null, null)

Could you please provide full text of test case ?

Well it's quite simple. Connect to a database which contains tables and views.  

Then for case 1) do:

DatabaseMetaData meta = con.getMetaData();
rs = meta.getTables(null, null, null, new String[] { "TABLE"} );
while (rs.next())
{
  String name = rs.getString("TABLE_NAME");
  String type = rs.getString("TABLE_TYPE");
  System.out.println("Table=" + name + ", type=" + type);
}

Note that it returns views as well but does report the type "TABLE" for them.
According to the JDBC specs, this should only return objects of type TABLE 

For case 2) use 
rs = meta.getTables(null, null, null, new String[] { "VIEW"} );

This returns tables and views, but should only return views. 
The type column is correct though. 

You can run the same program with 3.1.10 and see the difference (3.1.10 works correctly).

The 3.2 branch will not be released as a product. You can switch to the latest 3.1 which doesn't have this issue, and supports VIEWs in database metadata calls.