Implicit data type conversion

This topic describes how to avoid implicit data type conversion in OceanBase Database.

Avoid implicit data type conversion

When you use operators in conjunction with operands of different data types, OceanBase Database converts data types to make the operands compatible. In the case of implicit conversion, OceanBase Database automatically converts numbers into strings or the other way around based on the query conditions.

Some SQL statements can cause slow queries in an offline database but can be avoided because the execution logic is correct. You need to pay attention to the data type of fields when you write an SQL statement. The following two examples show how the implicit conversion takes place:

• If you create a table and set the data type of a field to NUMBER, but pass in a character value for the field to start a query, the execution plan uses the index for the query.

  Here are sample statements for creating a table and an index:

  obclient> CREATE TABLE `test1`(`id` INT,`name` VARCHAR(10));
  obclient> CREATE INDEX idx_test_id ON test1(`id`) GLOBAL;
  

  • A numeric value is passed in.

    In this case, the value in the NAME column indicates that the execution plan uses the correct index.

    obclient> EXPLAIN SELECT * FROM test1 WHERE id=100;
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                         |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | =================================================
    |ID|OPERATOR  |NAME              |EST. ROWS|COST|
    -------------------------------------------------
    |0 |TABLE SCAN|test1(idx_test_id)|1        |92  |
    =================================================
    
    Outputs & filters:
    -------------------------------------
      0 - output([test1.id], [test1.name]), filter(nil),
          access([test1.id], [test1.name]), partitions(p0)
     |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set
    

  • A correct character value is passed in.

    In this case, the value in the NAME column indicates that the execution plan uses the correct index.

    obclient> EXPLAIN SELECT * FROM test1 WHERE id='100';
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                         |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | =================================================
    |ID|OPERATOR  |NAME              |EST. ROWS|COST|
    -------------------------------------------------
    |0 |TABLE SCAN|test1(idx_test_id)|1        |92  |
    =================================================
    
    Outputs & filters:
    -------------------------------------
      0 - output([test1.id], [test1.name]), filter(nil),
          access([test1.id], [test1.name]), partitions(p0)
     |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set
    

• If you create a table and set the data type of a field to CHAR, but pass in a numeric value for the field to start a query, the execution plan cannot use the index and therefore scan the table in full.

  Here are sample statements for creating a table and an index:

  obclient> CREATE TABLE `test1` (`id` VARCHAR(10), `name` VARCHAR(10));
  obclient> CREATE INDEX idx_test_id ON test1(`id`) GLOBAL;
  

  • A numeric value is passed in.

    The execution plan shows that the NAME column contains only the table name test1 but does not contain the index idx_test_id(id). This means that the index is not used in the query. The value of COST is 408.

    obclient> EXPLAIN SELECT * FROM test1 WHERE id=100;
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Query Plan                                                                                                                                                                                                                                                                                                                                                                                          |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | ====================================
    |ID|OPERATOR  |NAME |EST. ROWS|COST|
    ------------------------------------
    |0 |TABLE SCAN|test1|5        |408 |
    ====================================
    
    Outputs & filters:
    -------------------------------------
      0 - output([test1.id], [test1.name]), filter([cast(test1.id, DECIMAL(-1, -1)) = ?]),
          access([test1.id], [test1.name]), partitions(p0)
     |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set
    

  • A correct character value is passed in.

    The execution plan shows that the NAME column contains both the table name test1 and the index idx_test_id(id). This means that the index is used in the query. In this case, the value of COST is significantly reduced to 92.

    obclient> EXPLAIN SELECT * FROM test1 WHERE id='100';
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                         |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | =================================================
    |ID|OPERATOR  |NAME              |EST. ROWS|COST|
    -------------------------------------------------
    |0 |TABLE SCAN|test1(idx_test_id)|1        |92  |
    =================================================
    
    Outputs & filters:
    -------------------------------------
      0 - output([test1.id], [test1.name]), filter(nil),
          access([test1.id], [test1.name]), partitions(p0)
     |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set
    

The difference between the two examples is caused because OceanBase Database converts characters into numbers when it compares the two data types.