Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9542

CONNECT engine incorrect datatype mapping with ODBC TDS driver

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.11
    • Fix Version/s: 10.0.24, 10.1.12
    • Labels:
      None
    • Environment:
      CentOS7 x86_64 on Google Cloud Platform (standard install) with unixODBC 2.3.1 and freetds v 0.95.19

      Description

      Using Connect engine with the Free TDS driver a couple of the data types are not mapped correctly.

      For example, columns declared as DATE on the remote MS SQL Server come back as character and remote columns declared as DOUBLE or FLOAT are coming back with zero decimal places.

      Note remote DATETIME does come back correctly as TIMESTAMP in Connect, and remote NUMERIC and DECIMAL types seem to come back correctly.

      In the example below, next_bill_date is a DATE on the remote MS server, discount is a FLOAT on the remote server (a percentage) and my_double is a DOUBLE on the remote MS server.

      So, for example, if 0.15 is the value of the discount field, it shows in isql as 0.15 but comes back in CONNECT as 0. Likewise a double as 23.45 comes back as 23.
      Remote types explicitly declared as NUMERIC or DECIMAL appear to work correctly.

      show columns in ms_cust_info
          -> ;
      +----------------+---------------+------+-----+-------------------+-----------------------------+
      | Field          | Type          | Null | Key | Default           | Extra                       |
      +----------------+---------------+------+-----+-------------------+-----------------------------+
      | id             | bigint(19)    | NO   |     | NULL              |                             |
      | company        | varchar(40)   | YES  |     | NULL              |                             |
      | created_on     | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
      | next_bill_date | varchar(10)   | YES  |     | NULL              |                             |
      | discount       | double(8,0)   | YES  |     | NULL              |                             |
      | sales          | decimal(8,2)  | YES  |     | NULL              |                             |
      | percentage     | decimal(17,3) | YES  |     | NULL              |                             |
      | my_double      | double(8,0)   | YES  |     | NULL              |                             |
      +----------------+---------------+------+-----+-------------------+-----------------------------+
      8 rows in set (0.00 sec)

        Attachments

        1. sfout.txt
          5 kB
        2. out.txt
          19 kB

          Activity

            People

            Assignee:
            bertrandop Olivier Bertrand
            Reporter:
            rdyas Robert Dyas
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: