Bug #33595 DatabaseMetaData reports wrong column type for "geometry" columns

• Submitted: 31 Dec 2007 9:19
• Modified: 3 Feb 2008 11:18
• Reporter: Andrea Aime
• Status: No Feedback
• Category: Connector / J
• Severity: S1 (Critical)
• Version: 5.1.5
• OS: Windows
• Assigned to: Assigned Account
• CPU Architecture: Any

[31 Dec 2007 9:19] Andrea Aime

Description:
The DataBaseMetaData.getColumns(...) method returns type 5 for those columns (Types.SMALLINT) whilst the proper response is 1111 (Types.OTHER).

How to repeat:
Given the following two tables:

mysql> describe road;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| fid   | varchar(255) | NO   | PRI |         |       |
| id    | int(11)      | YES  |     | NULL    |       |
| geom  | linestring   | NO   | MUL |         |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> describe tiger_texas_mysql;
+----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| OGR_FID  | int(11)       | NO   | PRI | NULL    | auto_increment |
| SHAPE    | geometry      | NO   | MUL |         |                |
| cfcc     | varchar(3)    | YES  |     | NULL    |                |
| cfcc_1   | varchar(1)    | YES  |     | NULL    |                |
| cfcc_2   | varchar(1)    | YES  |     | NULL    |                |
| cfcc_3   | varchar(1)    | YES  |     | NULL    |                |
| module   | varchar(8)    | YES  |     | NULL    |                |
| tlid     | double(32,10) | YES  |     | NULL    |                |
| name     | varchar(35)   | YES  |     | NULL    |                |
| length_m | double(32,10) | YES  |     | NULL    |                |
| altname1 | varchar(32)   | YES  |     | NULL    |                |
| altname2 | varchar(32)   | YES  |     | NULL    |                |
| altname3 | varchar(30)   | YES  |     | NULL    |                |
| altname4 | varchar(28)   | YES  |     | NULL    |                |
| altname5 | varchar(27)   | YES  |     | NULL    |                |
| altname6 | varchar(32)   | YES  |     | NULL    |                |
| altname7 | varchar(32)   | YES  |     | NULL    |                |
| altname8 | varchar(32)   | YES  |     | NULL    |                |
| altname9 | varchar(32)   | YES  |     | NULL    |                |
+----------+---------------+------+-----+---------+----------------+

DatabaseMetaData returns 1111 as the type of roads.geom but returns 5 as the type of tiger_texas_mysql.SHAPE. Both tables are using the MyIsam engine and do have a spatial index built onto the geometric column. The database is mysql 5.1 (release candidate downloaded Dec 31 2007)

[3 Jan 2008 10:20] Tonci Grgin

Hi Andrea and thanks for your report. Can you please attach complete test case and a DDL script to it? I would also like to know JDK version.

[3 Jan 2008 10:45] Andrea Aime

Hum, yes, I can do that, but I'll have to rewrite the test case from scratch. The current unit tests are part of a 500.000 loc code base and I cannot really give them to you as is.

[3 Jan 2008 11:18] Tonci Grgin

Andrea, can you please check on your other report and see my test case attached? It would be ideal if you can make one like that (based on our test FW).

[4 Feb 2008 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".