MySQL Bugs: #60514: getColumnTypeName() return VARCHAR for TEXT column

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:	None
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

Description:
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:
always

Tony, can you please attach small but complete test case here so I can check.

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.

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".

Maven project that replicates the problem

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

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...

Hi Chris. What are you expecting me to return?

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!
-Chris

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.

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...

Thanks,
-Chris

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.

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...

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
          BLOB.

          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.