[Fdo-trac] [fdo-trac] #227: MySQL Defect : wrong column type in SQLCommand

[FDO]

#227: MySQL Defect : wrong column type in SQLCommand
----------------------------+-------------------------
 Reporter:  bscott          |        Owner:  danstoica
     Type:  defect          |       Status:  closed
 Priority:  major           |    Milestone:  3.4.0
Component:  MySQL Provider  |      Version:  3.2.0
 Severity:  2               |   Resolution:  fixed
 Keywords:                  |  External ID:
----------------------------+-------------------------
Changes (by jng):

 * resolution:   => fixed
 * status:  new => closed

Comment:

 In [changeset:"8397" 8397]:
 {{{#!CommitTicketReference repository="" revision="8397"
 Report the correct column types in MySQL native queries

 FdoISQLDataReader reported a native FdoISQLCommand query's SMALLINT,
 MEDIUMINT and INT
 columns - and its FLOAT columns - as FdoDataType_Double. The MySQL
 driver's
 mysql_to_rdbi_type() mapped MYSQL_TYPE_SHORT, MYSQL_TYPE_LONG,
 MYSQL_TYPE_INT24 and
 MYSQL_TYPE_FLOAT to RDBI_DOUBLE, each marked "this should be fixed", and
 FdoRdbmsUtil::DbiToFdoType() turns that into FdoDataType_Double, so
 callers were told to
 read an integer column through the Double accessors. The schema manager
 already got this
 right (FdoSmPhMySqlColTypeMapper maps a MySQL int to Int32), so only the
 native query path
 disagreed with the schema it was describing.

 The rdbi type also decides how GdbiQueryResult converts the value out of
 its fetch buffer,
 so an integer column has to describe as RDBI_SHORT / RDBI_INT /
 RDBI_LONGLONG and a MySQL
 FLOAT as RDBI_FLOAT. An unsigned integer column does not fit its signed
 counterpart's rdbi
 type, so desc_slct now passes the column's UNSIGNED_FLAG down, and
 mysql_to_rdbi_type
 widens an unsigned SMALLINT to RDBI_INT and an unsigned INT to
 RDBI_LONGLONG - the same
 widening FdoSmPhMySqlColTypeMapper applies. Without it an unsigned INT
 holding 4294967295
 truncates at fetch time instead of reading as an FDO Int64.

 Provider:
 * Providers/GenericRdbms/Src/MySQL/Driver/type.cpp, type.h:
 mysql_to_rdbi_type() now maps
   MYSQL_TYPE_SHORT to RDBI_SHORT (RDBI_INT when unsigned),
 MYSQL_TYPE_INT24 and
   MYSQL_TYPE_LONG to RDBI_INT (RDBI_LONGLONG when unsigned) and
 MYSQL_TYPE_FLOAT to
   RDBI_FLOAT, instead of RDBI_DOUBLE for all four, and takes an
 is_unsigned flag.
   MYSQL_TYPE_TINY and MYSQL_TYPE_LONGLONG are unchanged. DECIMAL is
 deliberately untouched:
   an FDO Decimal column has no rdbi type to describe it as, and the ODBC
 driver describes
   SQL_DECIMAL identically.
 * Providers/GenericRdbms/Src/MySQL/Driver/desc_slct.cpp: pass
   (fields[position].flags & UNSIGNED_FLAG) to mysql_to_rdbi_type().

 Tests:
 * MySqlFdoSqlCmdTest::QueryColumnTypes (new): a native query over a table
 of MySQL
   SMALLINT, MEDIUMINT, INT, BIGINT, INT UNSIGNED, FLOAT, DOUBLE, VARCHAR
 and DATETIME
   columns asserts both the type GetColumnType() reports for each column
 and that the value
   still round-trips through the getter that type implies. It failed before
 the fix with
   "Column 'smallint_val' is reported as 'Double' but expected 'Int16'".
 * DataTypeTests::floating and SelectTests::describe asserted the double
 MySQL used to lie
   about. Both now take the expected FLOAT column type (and size) from a
 provider hook whose
   default preserves the SQL Server expectation (a SQL Server float is 53
 bits, so
   RDBI_DOUBLE).
 * SelectTests::describe's integer column assertion, commented out with
 "want RDBI_INT but we
   get RDBI_DOUBLE", is enabled; both MySQL and SQL Server describe an
 integer column as
   RDBI_INT.

 Verified with the full MySql unit test suite in the baseline ubuntu22
 containers against
 MySQL 5.6: OK (248) in both the Debug (ASan) and Release configurations,
 up from OK (247).
 The Debug LeakSanitizer total is 46236 bytes in 491 allocations (46976 in
 493 before).

 Fixes #227
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/fdo/ticket/227#comment:3>
FDO <http://fdo.osgeo.org/>
Feature Data Objects