Bug #23031 SQLTables returns incomplete/inaccurate catalog information on Views
Submitted: 5 Oct 2006 22:52 Modified: 6 Jun 2007 11:56
Reporter: Richard Brown Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51 OS:Windows (Win XP SP2)
Assigned to: Jim Winstead CPU Architecture:Any
Tags: SQLTables

[5 Oct 2006 22:52] Richard Brown 
Description:
SQLTables reports views as table_type "TABLE" instead of table_type "VIEW"

How to repeat:
1) execute SQLTables on any database that has one or more view defined.
2) observe that the table_type field of the rows associated with the views in the resultset from the SQLTables call is set to "TABLE"

Suggested fix:
Return the correct value in the table_type field based upon the object the row is reporting.
[7 Oct 2006 12:41] Valeriy Kravchuk 
Thank you for a problem report. Please, specify the exact version of Connector/ODBC used, 3.51.x.
[7 Oct 2006 12:47] Richard Brown 
The complete version is MySQL ODBC 3.51 Driver, version 3.51.12.00
[13 Oct 2006 9:40] Tonci Grgin 
Hi Richard and thanks for your bug report. I was able to verify this behavior from MS generic ODBC client:
 - Calling SQLTables with TableType = "VIEW" produces no result but view exists in schema:
SQLTables:
				In:				StatementHandle = 0x00841960, CatalogName = "zztestview", 
										NameLength1 = 10, SchemaName = "zztestview", NameLength2 = 10, TableName = "test", 
										NameLength3 = 4, TableType = "VIEW", NameLength4 = 4
				Return:	SQL_SUCCESS=0

Get Data All:
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "TABLE_TYPE", "REMARKS"
0 rows fetched from 5 columns.

 - Calling SQLTables with TableType = "TABLE,VIEW" produces result but with wrong Type ("MySQL table"):
SQLTables:
				In:				StatementHandle = 0x00841960, CatalogName = "zztestview", 
										NameLength1 = 10, SchemaName = "zztestview", NameLength2 = 10, TableName = "test", 
										NameLength3 = 4, TableType = "TABLE,VIEW", NameLength4 = 10
				Return:	SQL_SUCCESS=0

Get Data All:
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "TABLE_TYPE", "REMARKS"
"zztestview", "", "test", "TABLE", "MySQL table"
1 row fetched from 5 columns.

I was also able to reproduce reported behavior in standalone test case:
<SQLTables Test>  
Columns returned by SQLNumResultCols are: 5
Columns: 
TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS, 
----------------------------------------------
zztestview          test     TABLE     MySQL table     
SQLTables ... ok

View definition:
CREATE ALGORITHM=UNDEFINED DEFINER=`zzUser`@`localhost` SQL SECURITY DEFINER VIEW `zztestview`.`test` AS select sql_no_cache `tables`.`TABLE_NAME` AS `table_name` from `information_schema`.`tables`;
[17 May 2007 19:27] Jim Winstead 
add support for views to SQLTables

Attachment: bug23031.patch (application/octet-stream, text), 21.25 KiB.
[30 May 2007 20:26] Jim Winstead 
The fix for this has been committed to the source repository, and will be in 3.51.16. Thanks for the bug report.
[6 Jun 2007 11:56] MC Brown 
A note has been added to the 3.51.16 changelog.