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

ConnectSE: discovery does not work well for Oracle's INT data type

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • 10.0.7
    • None
    • None

    Description

      Create this table in Oracle:

      [bar@home ~]$ sqlplus
      		 
      SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 26 12:37:51 2013
      		 
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      		 
      Enter user-name: system
      		 
      Enter password: 
      Connected to:
      		 
      Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
      		 
      SQL> create table t1 (a int);
      		 
      Table created.
      		 
      SQL> insert into t1 values (10);
      		 
      1 row created.
      		 
      SQL> commit;
      		 
      Commit complete.

      Now create a ConnectSE ODBC table and try to SELECT from it.
      It does not work:

      mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      Query OK, 0 rows affected (0.12 sec)
      		 
       
      		 
      ERROR 1296 (HY000): Got error 122 '[Oracle][ODBC]SQL data type out of range <-25>.' from CONNECT

      If the table structure is specified explicitly, it works fine:

      mysql> drop table if exists t1; create table t1 (a int) engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      Query OK, 0 rows affected (0.06 sec)
      		 
       
      		 
      +------+
      		 
      | a    |
      		 
      +------+
      		 
      |   10 |
      		 
      +------+
      		 
      1 row in set (0.05 sec)

      Note, discovery works fine with VARCHAR columns.
      Create this table on the Oracle side:

      SQL> drop table t1;
      		 
      Table dropped.
      		 
      SQL> create table t1 (a varchar(10));
      		 
      Table created.
      		 
      SQL> insert into t1 values ('test');
      		 
      1 row created.
      		 
      SQL> commit;
      		 
      Commit complete.

      Now try to create an ODBC table on the Maria side and read from it:

      mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      Query OK, 0 rows affected (0.13 sec)
      		 
       
      		 
      +------+
      		 
      | A    |
      		 
      +------+
      		 
      | test |
      		 
      +------+
      		 
      1 row in set (0.06 sec)

      Discovery should be fixed for the Oracle's INT data type.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Description Create this table in Oracle:
          {code}
          [bar@home ~]$ sqlplus
          SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 26 12:37:51 2013
          Copyright (c) 1982, 2011, Oracle. All rights reserved.
          Enter user-name: system
          Enter password:
          Connected to:
          Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
          SQL> create table t1 (a int);
          Table created.
          SQL> insert into t1 values (10);
          1 row created.
          SQL> commit;
          Commit complete.
          {code}

          Now create a ConnectSE ODBC type and try to SELECT from it.
          It does not work:

          {code}
          mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.12 sec)

          ERROR 1296 (HY000): Got error 122 '[Oracle][ODBC]SQL data type out of range <-25>.' from CONNECT
          {code}

          If the table structure is specified explicitly, it works fine:
          {code}
          mysql> drop table if exists t1; create table t1 (a int) engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.06 sec)

          +------+
          | a |
          +------+
          | 10 |
          +------+
          1 row in set (0.05 sec)
          {code}

          Note, discovery works fine with VARCHAR columns.
          Create this table on the Oracle side:
          {code}
          SQL> drop table t1;
          Table dropped.
          SQL> create table t1 (a varchar(10));
          Table created.
          SQL> insert into t1 values ('test');
          1 row created.
          SQL> commit;
          Commit complete.
          {code}

          Now try to create an ODBC table on the Maria side and read from it:
          {code}
          mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.13 sec)

          +------+
          | A |
          +------+
          | test |
          +------+
          1 row in set (0.06 sec)
          {code}
          		 Create this table in Oracle:
          {code}
          [bar@home ~]$ sqlplus
          SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 26 12:37:51 2013
          Copyright (c) 1982, 2011, Oracle. All rights reserved.
          Enter user-name: system
          Enter password:
          Connected to:
          Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
          SQL> create table t1 (a int);
          Table created.
          SQL> insert into t1 values (10);
          1 row created.
          SQL> commit;
          Commit complete.
          {code}

          Now create a ConnectSE ODBC table and try to SELECT from it.
          It does not work:

          {code}
          mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.12 sec)

          ERROR 1296 (HY000): Got error 122 '[Oracle][ODBC]SQL data type out of range <-25>.' from CONNECT
          {code}

          If the table structure is specified explicitly, it works fine:
          {code}
          mysql> drop table if exists t1; create table t1 (a int) engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.06 sec)

          +------+
          | a |
          +------+
          | 10 |
          +------+
          1 row in set (0.05 sec)
          {code}

          Note, discovery works fine with VARCHAR columns.
          Create this table on the Oracle side:
          {code}
          SQL> drop table t1;
          Table dropped.
          SQL> create table t1 (a varchar(10));
          Table created.
          SQL> insert into t1 values ('test');
          1 row created.
          SQL> commit;
          Commit complete.
          {code}

          Now try to create an ODBC table on the Maria side and read from it:
          {code}
          mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.13 sec)

          +------+
          | A |
          +------+
          | test |
          +------+
          1 row in set (0.06 sec)
          {code}
          bar Alexander Barkov made changes -
          Description Create this table in Oracle:
          {code}
          [bar@home ~]$ sqlplus
          SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 26 12:37:51 2013
          Copyright (c) 1982, 2011, Oracle. All rights reserved.
          Enter user-name: system
          Enter password:
          Connected to:
          Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
          SQL> create table t1 (a int);
          Table created.
          SQL> insert into t1 values (10);
          1 row created.
          SQL> commit;
          Commit complete.
          {code}

          Now create a ConnectSE ODBC table and try to SELECT from it.
          It does not work:

          {code}
          mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.12 sec)

          ERROR 1296 (HY000): Got error 122 '[Oracle][ODBC]SQL data type out of range <-25>.' from CONNECT
          {code}

          If the table structure is specified explicitly, it works fine:
          {code}
          mysql> drop table if exists t1; create table t1 (a int) engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.06 sec)

          +------+
          | a |
          +------+
          | 10 |
          +------+
          1 row in set (0.05 sec)
          {code}

          Note, discovery works fine with VARCHAR columns.
          Create this table on the Oracle side:
          {code}
          SQL> drop table t1;
          Table dropped.
          SQL> create table t1 (a varchar(10));
          Table created.
          SQL> insert into t1 values ('test');
          1 row created.
          SQL> commit;
          Commit complete.
          {code}

          Now try to create an ODBC table on the Maria side and read from it:
          {code}
          mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.13 sec)

          +------+
          | A |
          +------+
          | test |
          +------+
          1 row in set (0.06 sec)
          {code}
          		 Create this table in Oracle:
          {code}
          [bar@home ~]$ sqlplus
          SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 26 12:37:51 2013
          Copyright (c) 1982, 2011, Oracle. All rights reserved.
          Enter user-name: system
          Enter password:
          Connected to:
          Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
          SQL> create table t1 (a int);
          Table created.
          SQL> insert into t1 values (10);
          1 row created.
          SQL> commit;
          Commit complete.
          {code}

          Now create a ConnectSE ODBC table and try to SELECT from it.
          It does not work:

          {code}
          mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.12 sec)

          ERROR 1296 (HY000): Got error 122 '[Oracle][ODBC]SQL data type out of range <-25>.' from CONNECT
          {code}

          If the table structure is specified explicitly, it works fine:
          {code}
          mysql> drop table if exists t1; create table t1 (a int) engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.06 sec)

          +------+
          | a |
          +------+
          | 10 |
          +------+
          1 row in set (0.05 sec)
          {code}

          Note, discovery works fine with VARCHAR columns.
          Create this table on the Oracle side:
          {code}
          SQL> drop table t1;
          Table dropped.
          SQL> create table t1 (a varchar(10));
          Table created.
          SQL> insert into t1 values ('test');
          1 row created.
          SQL> commit;
          Commit complete.
          {code}

          Now try to create an ODBC table on the Maria side and read from it:
          {code}
          mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.13 sec)

          +------+
          | A |
          +------+
          | test |
          +------+
          1 row in set (0.06 sec)
          {code}

          Discovery should be fixed for the Oracle's INT data type.
          bertrandop Olivier Bertrand added a comment -

          After the table is created by:

          create table t1 (a int);

          the SQLColumns function (used also by Discovery) returns:

          "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
          ------
          "", "SYSTEM", "T1", "A", 3, "DECIMAL", 38, 40, 0, 10, 1, "", "", 3, , 0, 1, "YES"

          38 is too much for an integer, therefore CONNECT discovery now tests the length and if too big automatically change the column type from int to bigint.

          bertrandop Olivier Bertrand added a comment - After the table is created by: create table t1 (a int); the SQLColumns function (used also by Discovery) returns: "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE" ------ "", "SYSTEM", "T1", "A", 3, "DECIMAL", 38, 40, 0, 10, 1, "", "", 3, , 0, 1, "YES" 38 is too much for an integer, therefore CONNECT discovery now tests the length and if too big automatically change the column type from int to bigint.
          bertrandop Olivier Bertrand made changes -
          Fix Version/s 10.0.7 [ 14100 ]
          Assignee Alexander Barkov [ bar ] Olivier Bertrand [ bertrandop ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          bertrandop Olivier Bertrand added a comment -

          Even if the CONNECT table specifies "A bigint(38)" the error described above still occur when doing the select statement.

          bertrandop Olivier Bertrand added a comment - Even if the CONNECT table specifies "A bigint(38)" the error described above still occur when doing the select statement.
          bertrandop Olivier Bertrand made changes -
          Resolution Fixed [ 1 ]
          Status Closed [ 6 ] Reopened [ 4 ]
          bertrandop Olivier Bertrand added a comment -

          The issue is that Oracle consider "int" as its "number" data type. This is a proprietary type in which numbers are coded internally up to 38 characters. Precision and Scale can be specified but default to 38 and 0.
          CONNECT cannot really know how to translate numbers without knowing their range and size. This could be int, bigint or double.
          When retrieving the result (in the select statement) ODBC provides the SQLBindCOL function, asking the data source to translate the numeric column contains to the proposed type.
          This would be fine except that Oracle, or the Oracle ODBC driver, supports converting the "number" column values to int or double but the conversion to bigint (internally -25) is not supported. Note that this work fine with other drivers.
          One can argue whether this is an Oracle or CONNECT bug but this is irrelevant. A solution must be found that would solve the Oracle problem without penalizing the other drivers.
          Meanwhile, a manual solution is to change the BIGINT column specification to either INT or DOUBLE depending on the range of value the Oracle table column can contain.

          bertrandop Olivier Bertrand added a comment - The issue is that Oracle consider "int" as its "number" data type. This is a proprietary type in which numbers are coded internally up to 38 characters. Precision and Scale can be specified but default to 38 and 0. CONNECT cannot really know how to translate numbers without knowing their range and size. This could be int, bigint or double. When retrieving the result (in the select statement) ODBC provides the SQLBindCOL function, asking the data source to translate the numeric column contains to the proposed type. This would be fine except that Oracle, or the Oracle ODBC driver, supports converting the "number" column values to int or double but the conversion to bigint (internally -25) is not supported. Note that this work fine with other drivers. One can argue whether this is an Oracle or CONNECT bug but this is irrelevant. A solution must be found that would solve the Oracle problem without penalizing the other drivers. Meanwhile, a manual solution is to change the BIGINT column specification to either INT or DOUBLE depending on the range of value the Oracle table column can contain.
          bertrandop Olivier Bertrand added a comment -

          As 38 is also too big for BIGINT, connect now translates numeric types as:
          type = (prec || len > 20) ? TYPE_FLOAT
          : (len > 10) ? TYPE_BIGINT : TYPE_INT;
          Doing so the issue reported above is fixed.
          Note that the error would still occur for a table created as:
          create table t1 (a number(20));
          But this is an Oracle problem, not a CONNECT one.
          In this case the fix will be to manually specify the column type of the connect table.

          bertrandop Olivier Bertrand added a comment - As 38 is also too big for BIGINT, connect now translates numeric types as: type = (prec || len > 20) ? TYPE_FLOAT : (len > 10) ? TYPE_BIGINT : TYPE_INT; Doing so the issue reported above is fixed. Note that the error would still occur for a table created as: create table t1 (a number(20)); But this is an Oracle problem, not a CONNECT one. In this case the fix will be to manually specify the column type of the connect table.
          bertrandop Olivier Bertrand made changes -
          Resolution Fixed [ 1 ]
          Status Reopened [ 4 ] Closed [ 6 ]
          bertrandop Olivier Bertrand added a comment -

          The last version of CONNECT was added the DECIMAL (TYPE_DECIM) type.
          All decimal, number, numeric columns of ODBC and MYSQL tables are now handled by CONNECT using this type
          that does not loose any digits in the translation.

          bertrandop Olivier Bertrand added a comment - The last version of CONNECT was added the DECIMAL (TYPE_DECIM) type. All decimal, number, numeric columns of ODBC and MYSQL tables are now handled by CONNECT using this type that does not loose any digits in the translation.
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 30300 ] MariaDB v2 [ 44609 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 44609 ] MariaDB v3 [ 63962 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 63962 ] MariaDB v4 [ 147260 ]

          People

            bertrandop Olivier Bertrand
            bar Alexander Barkov
            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.