MySQL Bugs: #53900: SQLColAttribute/SQL_DESC_TYPE_NAME text instead of longtext for longtext col

Bug #53900	SQLColAttribute/SQL_DESC_TYPE_NAME text instead of longtext for longtext col
Submitted:	21 May 2010 19:01	Modified:	27 Jun 2018 18:56
Reporter:	Farid Zidan (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S4 (Feature request)
Version:	5.01.06.00	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	qc

Description:
        // type_name
        rc = SQLColAttribute(hstmt,
                             nCol,
                             SQL_DESC_TYPE_NAME,
                             pRow->type_name,
                             sizeof(pRow->type_name),
                             &nLen,
                             NULL);

Same thing for tinytext, mediumtext

How to repeat:
create table test_longtext (col1 integer, col2 text, col3 longtext, col4 mediumtext, col5 tinytext)

Execute: 
select * from test_longtext

Describe the resultset column type names using SQLColAttribute and SQL_DESC_TYPE_NAME. I get 'text' for col3, col4 and col5 instead of 'longtext', 'mediumtext', 'tinytext'

Similar issue with binary blobs.

create table test_binary(col1 integer, 
col2 binary(32),
col3 varbinary(2000),
col4 tinyblob,
col5 blob,
col6 mediumblob,
col7 longblob)

I get 'blob' for col4 thru 7 instead of actual data type names (col2 and col3 are correctly reported)

Same issue with wide-text.

create table test_wlongtext (col1 integer, 
col2 text character set utf8, 
col3 longtext character set utf8, 
col4 mediumtext character set utf8, 
col5 tinytext character set utf8)

I get 'text' for col3, col4, col5 instead of actual data type names

Farid, this is not a bug (both for TEXT and BLOB). Please check ODBC specs and put things into perspective... We did not wrote framework nor can we force it to accept our types but rather we are bound by what ODBC specs can consume.

Hello Tonci

Here is what ODBC spec says about SQL_DESC_TYPE_NAME:
http://msdn.microsoft.com/en-us/library/ms713558(v=VS.85).aspx

SQL_DESC_TYPE_NAME (ODBC 1.0)

Data source–dependent data type name; for example, "CHAR", "VARCHAR", "MONEY", "LONG VARBINARY", or "CHAR ( ) FOR BIT DATA".

As you can see from ODBC spec documentation, the driver needs to return the data source-dependent type name. 

However, you are correct that the base ODBC data type for all MySQL text columns is SQL_LONGVARCHAR, but that is a different attribute SQL_DESC_CONCISE_TYPE. This bug report about SQL_DESC_TYPE_NAME.

SQL_DESC_TYPE_NAME should return one of the data type names returned by the driver for SQLGetTypeInfo. MySQL ODBC driver returns longtext and other text data types correctly for SQLGetTypeInfo as you can see from screenshot.

MySQL SQLGetTypeInfo and long data types

Attachment: mysql_longdatatypes.jpg (image/jpeg, text), 68.50 KiB.

Tonci,

Just to re-iterate that this is a bug. Please update the issue status accordingly.

SQL_DESC_TYPE is used by ODBC clients to retrieve the DBMS data type name for a resultset column. The API for this call uses a string buffer and ODBC-spec says that driver should return the data-source dependent data type name for the resulstset column. ODBC does not define specific data type names, these are rather specific to each DBMS and one DBMS may have multiple data type names for the same basic ODBC data type SQL_VARCHAR, etc. 

The ODBC driver can return anything it likes for the call since the call API uses a string buffer, but obviously it should return the DBMS data type name for the column. So in the example, create table test_datatype(col1 longtext) I should get 'longtext' instead of 'text' for col1 when I describe select * from test_datatype otherwise the info is incorrect.

Tonci,

Can you explain why you do not think this is a bug?

Farid,

Yes, that name is DSN-specific. In other words - driver decides how to call types. and there is no obligations in the specs that it should be exactly like a type is called in dbms.
Thus i'm gonna call it a "Feature Request" so far. I think we could have reasons to return same type name for all those types. That requires investigation at least.
Also looking in code i see we return "longtext", "mediumtext" in other functions.
It can either evidence about inconsistency or, again, about some reason behind it. so maybe we will end up with determining it as a "bug"

S4 (Feature request): There is no loss in service; a cosmetic or enhancement request.

This is not true for this issue. There is loss of information, the information returned by the driver for the column data type name is incorrect and this is not a cosmetic and neither an enhancement request. It is not an enhancement request for the driver to return the correct data type name for the column, it is expected that the driver returns the correct data type name for the column.

The column data type name is 'longtext' and the driver is returning 'text' which is not the same as 'longtext', these are different data types in MySQL and not synonyms for the same data type.

All ODBC client apps that display column information to the user are affected by this issue including data modeling tools that reverse-engineer a table and other general ODBC apps that display column information to the user. In all these apps the test table col3 data type is displayed as 'text' instead of 'longtext' causing problems.

See attached screen shot for WinSQL for example where col3 data type name is displayed as 'text' instead of 'longtext' for the test table due to this issue.

If this is by design, then I need to have an explanation why the driver is doing this, since it is not useful for clients apps to receive this incorrect information from the driver while the driver could just as well return the correct info if that's possible.

WinSQL screen shot showing incorrect data type name for col3

Attachment: mysql_incorrect_type_name.jpg (image/jpeg, text), 89.32 KiB.

Farid, Lawrin was more than polite in his explanation promising further investigation. Maybe he could have been clearer on differences between DATA and METADATA though. So there is no way this can be S2 (Serious: Represents a severe loss of service, significant functionality is missing; but a workaround is available.) no matter how problematic this is in your particular case.

Nothing you said made me change my mind. Let's see about this:
  The column data type name is 'longtext' and the driver is returning 'text' which is not the same as 'longtext', these are different data types in MySQL and not synonyms for the same data type.

There is no loss of *data* in this case; it is well known, ODBC standard does not deal with types as big as LONGTEXT (thus the option "Limit column size to signed 32-bit"). So, managing MySQL LONGTEXT fields via ODBC is useless in itself. However, I agree that *metadata* should be returned correctly (as far as it does not violate API limitations) and that's what Lawrin was about.

Lawrin, imo, we need to separate ODBC calls into "API-safe" ones (which are safe to return MySQL native types) and "not-safe" calls which should change MySQL native types to the ones ODBC API understands.

Farid, please point me to the place in specs that proves "it is expected that the driver returns the correct data type name for the column", or, as i expressed that, and it looks more correct for me, that the name should be exactly the same as in dbms. where it is said something more than just "data source specific"?
Without additional investigation why was it done this way, that is nothing more than just a feature request. Excuse me if I remind that you not the only user of c/odbc. We appreciate the amount of time you spend on reporting bugs, but we can't change things just because they don't fit your expectations from the driver, and when it's not absolutely clear that is really a bug. Besides there probably other ways to tell longtext field from mediumtext through other odbc calls.
I estimate the probability that eventually we/I will admit that is a bug as pretty high(significantly more than 50%), but at the moment it's too early to tell.
Setting back to S4.

Hello Lawrin,

Going back to ODBC spec:
http://msdn.microsoft.com/en-us/library/ms713558(v=VS.85).aspx

ODBC spec says that driver needs to return data source-dependent data type name for SQL_DESC_TYPE_NAME. So 'text' is correct as far as it is a MySQL data type name, however, for the test table col3 the returned value is incorrect as col3 data type name is 'longtext' and not 'text' and the two data type names are not synonymous.

>>The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT

Here is another example,

create table test_type_name (col1 tinytext,
col2 text,
col3 mediumtext,
col4 longtext);

select * from test_type_name

Currently I get 'text', 'text', 'text' and 'text' when I ask the driver to describe the data type names for the resultset columns col1, col2, col3, and col4. As you can see that's not a correct description of these columns data type names (correct only for col2). A correct description for the data type names for the resultset columns would be 'tinytext', 'text', 'mediumtext' and 'longtext' (all MySQL data type names and all are the data type names for the columns).

So the driver, per ODBC spec, needs to return a MySQL data type name for SQL_DESC_TYPE_NAME (data source-dependent data type name) and also the returned value must be the data type name for the column being described (column number is part of the API call).

Other users of MySQL ODBC driver want the driver to return the correct information for the data type names being described, they do not want to write special code just for MySQL when there is an established ODBC API call that is designed to do the job. It is not helpful for other users of MySQL ODBC driver to get 'text', 'text', 'text' and 'text' when they describe the resultset column data type names when the data type names are 'tinytext', 'text', 'mediumtext' and 'longtext'.

Corrected truncated attribute name in title

Posted by developer:
 
The initial problem was this:

For a variety of types (such as TINYTEXT, MEDIUMTEXT, TEXT, LONGTEXT) the ODBC driver reported the database type name as "TEXT".
Same thing was observed for TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB types reported as "BLOB".

This was confusing for the client applications.
The patch corrects this behaviour and allows the driver to return the appropriate string type name for all types:

TINYTEXT is reported as "TINYTEXT"
MEDIUMTEXT is reported as "MEDIUMTEXT"
TEXT is reported as TEXT
LONGTEXT is reported as LONGTEXT

TINYBLOB is reported as "TINYBLOB"
MEDIUMBLOB is reported as "MEDIUMBLOB
BLOB is reported as BLOB
LONGBLOB is reported as LONGBLOB

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.12 release, and here's the changelog entry:

The data source dependent type's name was not always returned. For
example, the ODBC driver reported TEXT as the database type for TINYTEXT,
MEDIUMTEXT, and LONGTEXT, and reported BLOB for TINYBLOB, MEDIUMBLOB, and
LONGBLOB.

Thank you for the bug report.