MyODBC SQLGetTypeInfo() returns incorrect data

[Michael Widenius]

Hi!
>>>>> "Michael" == Michael Chen <michaelc@stripped> writes:

Michael> SEND-PR: -*- send-pr -*-
Michael> SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
Michael> SEND-PR: will all comments (text enclosed in `<' and `>').
Michael> SEND-PR:
Michael> From: michaelc
Michael> To: mysql@stripped
Michael> Subject: MyODBC SQLGetTypeInfo() returns incorrect data
>> Description:

Michael> 	A lot of data returned by MyODBC SQLGetTypeInfo() is incorrect.
	
Michael> 	1. The returned resultset is not "ordered by 2,1" (data_type and
Michael> 	   type_name) as defined by ODBC API.

I missed the ordering from the ODBC spec;  Note that actually the
ordering is not by 2,1 but by '2,'closed-matching-types first')

This will be fixed in next release.

Michael> 	2. The literal_prefix column for all binary type should be "0x".
	
As MySQL supports one to have binary items as strings, I don't know if
this would be the right thing to do.

For example

'hello' and 0x68656c6c6f

will both work on MySQL.

In MySQL you can give any column a value of type 0x.

The question is how one can define that if we have '0x' as a prefix,
they strings should be given as a string of bytes?

I have now 'fixed' this by adding long varbinary with 0x prefix and
kept the old blob values.  This 'should' ensure that old applications
still works..

Michael> 	3. Since "Binary" is acceptable as a type name alias, it should
Michael> 	   be used to map to SQL_BINARY type in ODBC. Similarly "Varbinary"
Michael> 	   should be mapped to SQL_VARBINARY, and "Bit" to SQL_BIT, etc.
	
BINARY in MySQL doesn't mean that the column is in binary format, but
how the column is sorted.

What's wrong with mapping 'SQL_BIT' to tinyint, as MySQL doesn't have
a bit type ?

Michael> 	4. The precision of signed "Bigint" type should be 19 not 20 as is
Michael> 	   for unsigned bigint. Similarly mediumint precision is 7 not 8.

Actually the precession for Bigint should be 19 for signed and 20 for
unsigned (I have fixed that).  mediumint should be 7 for signed and 8
for unsigned

Michael> 	5. The precision of "Decimal" and "Numeric" should not be 15. I get
Michael> 	   19 from a SQLDescribeCol() call, but I think 19 is also wrong.
Michael> 	   MySQL doc 7.3 says decimal has the same range as double, meaning
Michael> 	   "-1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and
Michael> 	   2.2250738585072014E-308 to 1.7976931348623157E+308."  Then the
Michael> 	   precision should be 17 not 19 (shouldn't count sign and the
Michael> 	   decimal point).

MySQL supports any precession for Decimal and Numberic;  You will get
the same value you stored in this column on retrieval;  It's only when
you do calculation on this column the value is converted to double
	
Considering this, I have changed this to 19 for the time being.
(To get this in line with SQLDescribeCol())

Michael> 	6. I don't see why "Float" type is "unsearchable", probably typo.
	
This is because too many users have had problems with comparing a column
of type 'float' with a floating point value.  Because of rounding
problems between float/dobule:   'WHERE float_column = 0.5' is never true.


Michael> 	7. "Set" and "Enum" type should be "Varchar" and not "Char".
	
Good idea.

Michael> 	8. Finally, in the source code I submitted, I removed several types
Michael> 	   that has no coresponding ODBC data type such as mediumint and
Michael> 	   year. It is up to MySQL to make the final decision.

Why did you remove these?  If someone wants to use a generic ODBC tool
to create tables, shouldn't SQLGetTypeInfo() return all types so that
the user can access these ?

After merging your changes with mines, I got the following:

----------

#define MYSQL_DATA_TYPES 35

char *SQL_GET_TYPE_INFO_values[MYSQL_DATA_TYPES][15]=
{
  /* SQL_BIT= -7 */
  {"bit",sql_bit,"1",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","char(1)",NULL,NULL},

  /* SQL_TINY= -6 */
  {"tinyint",sql_tinyint,"3",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","tinyint",NULL,NULL},
  {"tinyint unsigned",sql_tinyint,"3",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"1","0","0","tinyint unsigned",NULL,NULL},

  /* SQL_BIGINT= -5 */
  {"bigint",sql_bigint,"19",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","bigint",NULL,NULL},
  {"bigint unsigned",sql_bigint,"20",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"1","0","0","bigint unsigned",NULL,NULL},

  /* SQL_LONGVARBINARY = -3 */
  {"long varbinary",sql_longvarbinary,"16777215","0x",NULL,NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","mediumblob",NULL,NULL},
  {"blob",sql_longvarbinary,"65535","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","binary large object (0-65535)",NULL,NULL},
  {"longblob",sql_longvarbinary,"2147483647","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","binary large object, use mediumblob
instead",NULL,NULL},
  {"tinyblob",sql_longvarbinary,"255","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","binary large object (0-255) ",NULL,NULL},
  {"mediumblob",sql_longvarbinary,"16777215","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","binary large object",NULL,NULL},

  /* SQL_LONGVARCHAR = -1 */
  {"long varchar",sql_longvarchar,"16777215","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","mediumtext",NULL,NULL},
  {"text",sql_longvarchar,"65535","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","text (0-65535)",NULL,NULL},
  {"mediumtext",sql_longvarchar,"16777215","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","mediumtext",NULL,NULL},

  /* SQL_CHAR = 1 */
  {"char",sql_char,"255","'","'","length",sql_nullable,
   "0",sql_searchable,"0","0","0","char",NULL,NULL},

  /* SQL_DECIMAL = 3 */
  {"numeric",sql_numeric,"19",NULL,NULL,"precision,scale",sql_nullable,
   "0",sql_searchable,"0","0","0","numeric","0","19"},
  {"decimal",sql_decimal,"19",NULL,NULL,"precision,scale",sql_nullable,
   "0",sql_searchable,"0","0","0","decimal","0","19"},

  /* SQL_INTEGER = 4 */
  {"integer",sql_integer,"10",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","integer",NULL,NULL},
  {"integer unsigned",sql_integer,"10",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"1","0","0","integer unsigned",NULL,NULL},
  {"int",sql_integer,"10",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","integer",NULL,NULL},
  {"int unsigned",sql_integer,"10",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"1","0","0","integer unsigned",NULL,NULL},
  {"mediumint",sql_integer,"7",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","Medium integer",NULL,NULL},
  {"mediumint unsigned",sql_integer,"8",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"1","0","0","Medium integer unsigned",NULL,NULL},

  /* SQL_SMALLINT = 5 */
  {"smallint",sql_smallint,"5",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","smallint",NULL,NULL},
  {"smallint unsigned",sql_smallint,"5",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"1","0","0","smallint unsigned",NULL,NULL},
  {"year",sql_smallint,"4",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","year",NULL,NULL},

  /* SQL_FLOAT = 6 */
  {"double",sql_float,"15",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","double","0","4"},

  /* SQL_REAL = 7 */
  {"float",sql_real,"7",NULL,NULL,NULL,sql_nullable,
   "0",sql_unsearchable,"0","0","0","float","0","2"},

  /* SQL_DOUBLE = 8 */
  {"double",sql_double,"15",NULL,NULL,NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","double","0","4"},

  /* SQL_DATE = 9 */
  {"date",sql_date,"10","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","date",NULL,NULL},

  /* SQL_TIME = 10 */
  {"time",sql_time,"6","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","time",NULL,NULL},

  /* SQL_TIMESTAMP = 11 */
  {"datetime",sql_timestamp,"21","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","datetime","0","0"},
  {"timestamp",sql_timestamp,"14","'","'",NULL,sql_no_nulls,
   "0",sql_searchable,"0","0","0","timestamp","0","0"},

  /* SQL_VARCHAR = 12 */
  {"varchar",sql_varchar,"255","'","'","maxlength",sql_nullable,
   "0",sql_searchable,"0","0","0","varchar",NULL,NULL},
  {"enum",sql_varchar,"255","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","enum(value1,value2,value3...)",NULL,NULL},
  {"set",sql_varchar,"255","'","'",NULL,sql_nullable,
   "0",sql_searchable,"0","0","0","set(value1,value2,value3...)",NULL,NULL}
};

---------

Regards,
Monty