Bug #60514 getColumnTypeName() return VARCHAR for TEXT column
Submitted: 17 Mar 2011 14:25 Modified: 26 Jan 2012 21:32
Reporter: link tony Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S2 (Serious)
Version:5.5.9 OS:Windows (Win7 Pro 64 bit)
Assigned to: Philip Olson CPU Architecture:Any
Tags: Connect/J, metadata, Text type of column

[17 Mar 2011 14:25] link tony
function getColumnTypeName ResultSet's MetaDat return VARCHAR for TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT column.

but getColumnType() returns -1. 

The issue happens in Connect/J 5.1.6, 5.1.8, 5.1.15

How to repeat:
[17 Mar 2011 14:29] Tonci Grgin
Tony, can you please attach small but complete test case here so I can check.
[21 Mar 2011 15:45] Tonci Grgin
I still do not see what's the bug here...

The LONG VARCHAR is identical to VARCHAR, except that you do not have to specify a maximum length when creating columns of this type.

Please try being more clear about what your actual problem is.
[17 Apr 2011 23: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".
[30 Nov 2011 21:30] Chris Condit
Maven project that replicates the problem

Attachment: mysqlTextTypeBug.zip (application/x-zip-compressed, text), 2.66 KiB.

[30 Nov 2011 21:31] Chris Condit
I just noticed this problem as well. I've attached a maven project that replicates the problem. 
Just run mvn test from the command line...
[1 Dec 2011 6:37] Tonci Grgin
Hi Chris. What are you expecting me to return?
[1 Dec 2011 17:38] Chris Condit
Hi Tonci-
I'm was expecting to get back Types.VARCHAR (12) for TEXT, TINYTEXT, MEDIUMTEXT, and LONGTEXT since the type name is returned as "VARCHAR" and http://goo.gl/xsfBA indicates:
"In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like. Similarly, you can regard a TEXT column as a VARCHAR column."
Currently they all return Types.LONGVARCHAR with different getPrecision() values (65535, 255, 16777215, 2147483647 respectively). The spec on this is here: http://goo.gl/5w3O6 and it is up to the driver to decide when it should return Types.LONGVARCHAR. I guess at the very least I wouldn't expect Types.LONGVARCHAR for TINYTEXT or TEXT...

What are your thoughts about this?

Thanks for responding!
[2 Dec 2011 7:13] Tonci Grgin
Hi Chris.

I would expect for the driver to return LONGVARCHAR in case of TEXT column. Even our VARCHAR column falls into this category (check that other SW vendors have much smaller capacity for this type, Sybase is the closest with half the storage capacity). Now, for the sake of consistency, I'd say TINYTEXT should map to LONGVARCHAR too, would be over-complicating to map just that type into something else. Further more, *TEXT types are processed in certain way inside the server as described in http://dev.mysql.com/doc/refman/5.5/en/blob.html which is emphasized by the metadata returned for them.
[2 Dec 2011 18:03] Chris Condit
Hi Tonci-
I think that's fine as long as it's documented. Do you think that getColumnTypeName() for *TEXT columns should return "LONGVARCHAR" to make it consistent? That's what caused my confusion in the first place...

[5 Dec 2011 14:35] Tonci Grgin
Chris, I decided for documenting this behavior... A comment in manual might go something like this:
  TEXT types currently all return Types.LONGVARCHAR with different getPrecision() values (65535, 255, 16777215, 2147483647 respectively) with getColumnType() returning -1. This behavior is intentional even though TINYTEXT does not fall, regarding it's size, into LONGVARCHAR category to avoid different handling inside of the same base type. getColumnType() returning -1 is also correct since internal server handling of TEXT type is similar to that of BLOB.
[5 Dec 2011 18:13] Chris Condit
I think this documentation will be very helpful. Perhaps it's worth mentioning that getColumnTypeName() will return "VARCHAR" even though getColumnType() returns Types.LONGVARCHAR.

Thanks for adding this to the documentation. I think this ticket should be considered resolved...
[26 Jan 2012 21:32] Philip Olson
Fixed in SVN, thanks for the report.

          All TEXT types return
          Types.LONGVARCHAR with different
          getPrecision() values (65535, 255,
          16777215, and 2147483647 respectively) with
          getColumnType() returning
          -1. This behavior is intentional even
          though TINYTEXT does not fall, regarding to its size, within
          the LONGVARCHAR category. This is to avoid
          different handling inside the same base type. And
          getColumnType() returns
          -1 because the internal server handling is
          of type TEXT, which is similar to

          Also note that getColumnTypeName() will
          return VARCHAR even though
          getColumnType() returns
          Types.LONGVARCHAR, because
          VARCHAR is the designated column
          database-specific name for this type.