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

CONNECT engine incorrect datatype mapping with ODBC TDS driver

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.11
    • 10.0.24, 10.1.12
    • Storage Engine - Connect
    • None
    • 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. out.txt
          19 kB
        2. sfout.txt
          5 kB

        Activity

          Transition Time In Source Status Execution Times
          Olivier Bertrand made transition -
          Open Closed
          		6d 13h 41m 1

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.