Data Type Conversion

Differences in Implicit Type Conversion

• In GaussDB, the conversion rules from small types to small types are used. In MySQL, the conversion rules from small types to large types and from large types to small types are used.
• Due to data type differences, some output formats of implicit conversion in GaussDB are inconsistent.
• During implicit conversion from the BIT data type to the character data type and binary data type in GaussDB, some output behaviors are inconsistent. GaussDB outputs a hexadecimal character string, and MySQL escapes the character string based on the ASCII code table. If the character string cannot be escaped, the output is empty.

  Example:

  m_db=# CREATE TABLE bit_storage (
  	VS_COL1 BIT(4),
  	VS_COL2 BIT(4),
  	VS_COL3 BIT(4),
  	VS_COL4 BIT(4),
  	VS_COL5 BIT(4),
  	VS_COL6 BIT(4),
  	VS_COL7 BIT(4),
  	VS_COL8 BIT(4)
  ) DISTRIBUTE BY REPLICATION;
  m_db=# CREATE TABLE string_storage (
  	VS_COL1 BLOB,
  	VS_COL2 TINYBLOB,
  	VS_COL3 MEDIUMBLOB,
  	VS_COL4 LONGBLOB,
  	VS_COL5 TEXT,
  	VS_COL6 TINYTEXT,
  	VS_COL7 MEDIUMTEXT,
  	VS_COL8 LONGTEXT
  ) DISTRIBUTE BY REPLICATION;
  m_db=# INSERT INTO bit_storage VALUES(B'101', B'101', B'101', B'101', B'101', B'101', B'101', B'101');
  m_db=# INSERT INTO string_storage SELECT * FROM bit_storage;
  m_db=# SELECT * FROM string_storage;
   VS_COL1 | VS_COL2 | VS_COL3 | VS_COL4 | VS_COL5 | VS_COL6 | VS_COL7 | VS_COL8 
  ---------+---------+---------+---------+---------+---------+---------+---------
   \x05    | \x05    | \x05    | \x05    | \x05    | \x05    | \x05    | \x05
  (1 row)
  m_db=# DROP TABLE bit_storage, string_storage;
  
  mysql> CREATE TABLE bit_storage (
  	VS_COL1 BIT(4),
  	VS_COL2 BIT(4),
  	VS_COL3 BIT(4),
  	VS_COL4 BIT(4),
  	VS_COL5 BIT(4),
  	VS_COL6 BIT(4),
  	VS_COL7 BIT(4),
  	VS_COL8 BIT(4)
  );
  mysql> CREATE TABLE bit_storage (
  	VS_COL1 BIT(4),
  	VS_COL2 BIT(4),
  	VS_COL3 BIT(4),
  	VS_COL4 BIT(4),
  	VS_COL5 BIT(4),
  	VS_COL6 BIT(4),
  	VS_COL7 BIT(4),
  	VS_COL8 BIT(4)
  );
  mysql> INSERT INTO bit_storage VALUES(B'101', B'101', B'101', B'101', B'101', B'101', B'101', B'101');
  mysql> INSERT INTO string_storage SELECT * FROM bit_storage;
  mysql> SELECT * FROM string_storage;
  +---------+---------+---------+---------+---------+---------+---------+---------+
  | VS_COL1 | VS_COL2 | VS_COL3 | VS_COL4 | VS_COL5 | VS_COL6 | VS_COL7 | VS_COL8 |
  +---------+---------+---------+---------+---------+---------+---------+---------+
  |        |        |        |        |        |        |        |        |
  +---------+---------+---------+---------+---------+---------+---------+---------+
  1 row in set (0.00 sec)
  mysql> DROP TABLE bit_storage, string_storage;

Differences in Explicit Type Conversion

• In GaussDB, the conversion rules for each target type are used. In MySQL, C++ polymorphic overloading functions are used, causing inconsistent behavior in nesting scenarios.
  Example:

  m_db=# SELECT CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED);
  WARNING:  Truncated incorrect INTEGER value: '2023-01-01'
  CONTEXT:  referenced column: cast
   cast 
  ------
   2023
  (1 row)
  
  mysql> SELECT CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED);
  +---------------------------------------------------------+
  | CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED) |
  +---------------------------------------------------------+
  |                                                20230101 |
  +---------------------------------------------------------+

• In GaussDB, the BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB, BINARY, VARBINARY, BIT, and YEAR types are explicitly converted to the JSON type. The result is different from that in MySQL.

  Example:

  m_db=# CREATE TABLE test_blob (c1 BLOB, c2 TINYBLOB, c3 MEDIUMBLOB, c4 LONGBLOB, c5 BINARY(32), c6 VARBINARY(100), c7 BIT(64), c8 YEAR);
  CREATE TABLE
  m_db=# INSERT INTO test_blob VALUES('[1, "json"]', 'true', 'abc', '{"jsnid": 1, "tag": "ab"}', '[1, "json"]', '{"jsnid": 1, "tag": "ab"}', '20', '2020');
  INSERT 0 1
  m_db=# SELECT CAST(c1 AS JSON), CAST(c2 AS JSON), CAST(c3 AS JSON), CAST(c4 AS JSON), CAST(c5 AS JSON), CAST(c6 AS JSON), CAST(c7 AS JSON), CAST(c8 AS JSON) FROM test_blob;
        CAST       |  CAST  | CAST  |               CAST                |                 CAST                 |               CAST                | CAST |  CAST  
  -----------------+--------+-------+-----------------------------------+--------------------------------------+-----------------------------------+------+--------
   "[1, \"json\"]" | "true" | "abc" | "{\"jsnid\": 1, \"tag\": \"ab\"}" | "[1, \"json\"]                     " | "{\"jsnid\": 1, \"tag\": \"ab\"}" | "20" | "2020"
  (1 row)
  
  mysql> CREATE TABLE test_blob (c1 BLOB, c2 TINYBLOB, c3 MEDIUMBLOB, c4 LONGBLOB, c5 BINARY(32), c6 VARBINARY(100), c7 BIT(64), c8 YEAR);
  Query OK, 0 rows affected (0.02 sec)
  mysql> INSERT INTO test_blob VALUES('[1, "json"]', 'true', 'abc', '{"jsnid": 1, "tag": "ab"}', '[1, "json"]', '{"jsnid": 1, "tag": "ab"}', '20', '2020');
  Query OK, 1 row affected (0.00 sec)
  mysql> SELECT CAST(c1 AS JSON), CAST(c2 AS JSON), CAST(c3 AS JSON), CAST(c4 AS JSON), CAST(c5 AS JSON), CAST(c6 AS JSON), CAST(c7 AS JSON), CAST(c8 AS JSON) FROM test_blob;
  +-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
  | CAST(c1 AS JSON)                  | CAST(c2 AS JSON)          | CAST(c3 AS JSON)      | CAST(c4 AS JSON)                                      | CAST(c5 AS JSON)                                              | CAST(c6 AS JSON)                                     | CAST(c7 AS JSON)             | CAST(c8 AS JSON)         |
  +-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
  | "base64:type252:WzEsICJqc29uIl0=" | "base64:type249:dHJ1ZQ==" | "base64:type250:YWJj" | "base64:type251:eyJqc25pZCI6IDEsICJ0YWciOiAiYWIifQ==" | "base64:type254:WzEsICJqc29uIl0AAAAAAAAAAAAAAAAAAAAAAAAAAAA=" | "base64:type15:eyJqc25pZCI6IDEsICJ0YWciOiAiYWIifQ==" | "base64:type16:AAAAAAAAMjA=" | "base64:type13:MjAyMA==" |
  +-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
  1 row in set (0.00 sec)

Differences Between UNION, CASE, and Related Structures

• In MySQL, POLYGON+NULL, POINT+NULL, and POLYGON+POINT return the GEOMETRY type. They are not involved in GaussDB and considered as errors.
• The SET and ENUM types are not supported currently and are considered as errors.
• For UNION or UNION ALL that combines the JSON and binary types (BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB), the LONGBLOB type is returned in MySQL and the JSON type is returned in GaussDB. In addition, binary types (BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB) can be implicitly converted to JSON.
• If m_format_behavior_compat_options is not set to enable_precision_decimal, when the constant type is aggregated with other types, the precision of the output type is the precision of other types. For example, the precision of the result of "select "helloworld" union select p from t;" is the precision of attribute p.
• If m_format_behavior_compat_options is not set to enable_precision_decimal, when fixed-point constants and types without precision constraints (non-string types such as int, bool, and year, and the fixed-point type of aggregation result type) are aggregated, the precision constraint is output based on the default precision 31 of fixed-point numbers.
• Differences in merge rules:

  MySQL 5.7 has some improper type derivation. For example, the VARBINARY type is derived from the BIT type and integer/YEAR type, and the UNSIGNED type is derived from the UNSIGNED type and non-UNSIGNED type. In addition, the aggregation results of CASE WHEN and UNION are different. If the type derivation result is too small, data overflow may occur. The preceding issues have been resolved in MySQL 8.0. Therefore, the merge rule in MySQL 8.0 prevails.

• In MySQL, BINARY and CHAR use different padding characters. BINARY is padded with '\0', and CHAR is padded with spaces. In GaussDB, BINARY and CHAR are padded with spaces.
• In the precision transfer scenario, when the CASE WHEN statement is used, type conversion and precision recalculation are performed. As a result, trailing zeros may be inconsistent with those in the output result of the CASE clause.
  • More trailing zeros: The CASE node calculates the precision of the CASE node based on the precision of the CASE clause. If the precision of the THEN clause is lower than that of the CASE node, zeros are added to the end of the CASE node.
  • Less trailing zeros: When multiple layers of CASE WHEN are nested, only the precision of the inner CASE is retained after the inner CASE performs type conversion. The outer CASE cannot obtain the precision information of the THEN clause. Therefore, the outer CASE performs type conversion based on the precision calculated according to that of the inner CASE. When the outer CASE clause is converted, if the precision of the inner CASE clause is less than that of the THEN clause, there will be less trailing zeros.

  Example:

  • -- Trailing zeros

    -- More trailing zeros
    m_db=# SELECT 15.6 AS result;
     result 
    --------
       15.6
    (1 row)
    
    m_db=# SELECT CASE WHEN 1 < 2 THEN 15.6 ELSE  23.578 END AS result;
     result 
    --------
     15.600
    (1 row)
    
    m_db=# SELECT  greatest(12, 3.4, 15.6) AS result;
     result 
    --------
       15.6
    (1 row)
    
    m_db=# SELECT CASE WHEN 1 < 2 THEN greatest(12, 3.4, 15.6) ELSE greatest(123.4, 23.578, 36) END AS result;
     result 
    --------
     15.600
    (1 row)
    
    -- Less trailing zeros
    m_db=# create table t1 as select (false/-timestamp '2008-12-31 23:59:59.678') as result;
    INSERT 0  1
    m_db=# desc t1;
     Field  |    Type     | Null | Key | Default | Extra 
    --------+-------------+------+-----+---------+-------
     result | double(8,7) | YES  |     |         | 
    (1 row)
    
    m_db=# select (false/-timestamp '2008-12-31 23:59:59.678') as result;
       result   
    ------------
     -0.0000000
    (1 row)
    
    m_db=# create table t1 as select (case when 1<2 then false/-timestamp '2008-12-31 23:59:59.678' else 0016.11e3/'22.2' end) as result;
    INSERT 0 1
    m_db=# desc t1;
     Field  |  Type  | Null | Key | Default | Extra 
    --------+--------+------+-----+---------+-------
     result | double | YES  |     |         | 
    (1 row)
    
    m_db=# select (case when 1<2 then false/-timestamp '2008-12-31 23:59:59.678' else 0016.11e3/'22.2' end) as result;
     result 
    --------
         -0
    (1 row)
    
    m_db=# drop table t1;
    DROP TABLE
    m_db=# create table t1 as select (case when 1+1=2 then case when 1<2 then false/-timestamp '2008-12-31 23:59:59.678' else 0016.11e3/'22.2' end else 'test' end) as result;
    INSERT 0 1
    m_db=# desc t1;
     Field  |    Type     | Null | Key | Default | Extra 
    --------+-------------+------+-----+---------+-------
     result | varchar(23) | YES  |     |         | 
    (1 row)
    
    m_db=# select (case when 1+1=2 then case when 1<2 then false/-timestamp '2008-12-31 23:59:59.678' else 0016.11e3/'22.2' end else 'test' end) as result;
     result 
    --------
     -0
    (1 row)

  • When the precision transfer parameter is enabled, set operations (UNION, MINUS, EXCEPT, and INTERSECT) are used. If the fields queried by the query statements involved in set operations are functions and expressions instead of directly using fields in the table, if the data type of the query result is INT or INT UNSIGNED, the return data type is different. In MySQL, the returned data type is BIGINT or BIGINT UNSIGNED. In GaussDB, the returned data type is INT/INT UNSIGNED.

    -- Execution result in GaussDB
    m_db=# SET m_format_behavior_compat_options='select_column_name,enable_precision_decimal';
    SET
    m_db=# DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
    DROP TABLE
    m_db=# CREATE TABLE t1(a INT, b INT);
    CREATE TABLE
    m_db=# CREATE TABLE t2(c INT UNSIGNED, d INT UNSIGNED);
    CREATE TABLE
    m_db=# CREATE TABLE ctas1 AS (SELECT a, ABS(a) FROM t1) UNION (SELECT b, ABS(b) FROM t1);
    INSERT 0 0
    m_db=# DESC ctas1;
     Field  |    Type     | Null | Key | Default | Extra 
    --------+-------------+------+-----+---------+-------
     a      | integer(11) | YES  |     |         | 
     ABS(a) | integer(11) | YES  |     |         | 
    (2 rows)
    
    m_db=# CREATE TABLE ctas2 AS (SELECT c, ABS(c) FROM t2) UNION (SELECT d, ABS(d) FROM t2);
    INSERT 0 0
    m_db=# DESC ctas2;
     Field  |         Type         | Null | Key | Default | Extra 
    --------+----------------------+------+-----+---------+-------
     c      | integer(11) unsigned | YES  |     |         | 
     ABS(c) | integer(11) unsigned | YES  |     |         | 
    (2 rows)
    
    m_db=# DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
    DROP TABLE
    
    -- Execution result in MySQL
    mysql> DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
    Query OK, 0 rows affected, 4 warnings (0.00 sec)
    
    mysql> CREATE TABLE t1(a INT, b INT);
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> CREATE TABLE t2(c INT UNSIGNED, d INT UNSIGNED);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> CREATE TABLE ctas1 AS (SELECT a, ABS(a) FROM t1) UNION (SELECT b, ABS(b) FROM t1);
    Query OK, 0 rows affected (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> DESC ctas1;
    +--------+------------+------+-----+---------+-------+
    | Field  | Type       | Null | Key | Default | Extra |
    +--------+------------+------+-----+---------+-------+
    | a      | int(11)    | YES  |     | NULL    |       |
    | ABS(a) | bigint(20) | YES  |     | NULL    |       |
    +--------+------------+------+-----+---------+-------+
    2 rows in set (0.01 sec)
    
    mysql> CREATE TABLE ctas2 AS (SELECT c, ABS(c) FROM t2) UNION (SELECT d, ABS(d) FROM t2);
    Query OK, 0 rows affected (0.05 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> DESC ctas2;
    +--------+---------------------+------+-----+---------+-------+
    | Field  | Type                | Null | Key | Default | Extra |
    +--------+---------------------+------+-----+---------+-------+
    | c      | int(11) unsigned    | YES  |     | NULL    |       |
    | ABS(c) | bigint(20) unsigned | YES  |     | NULL    |       |
    +--------+---------------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    mysql> DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
    Query OK, 0 rows affected (0.07 sec)

  • When precision transfer is enabled, the result in the CASE WHEN nesting scenario is different from that in MySQL. In MySQL, a type can be directly converted despite multiple layers. However, in GaussDB, the result precision is determined and the type is converted layer by layer. As a result, the decimal places or carry of the result may be inconsistent with that of MySQL.

    -- GaussDB:
    m_db=# SET m_format_behavior_compat_options='enable_precision_decimal';
    SET
    m_db=# SELECT (CASE WHEN 1+1=3 THEN 'test' ELSE CASE WHEN 1>2 THEN '-1.5'%06.6600e1 ELSE -TIME '10:10:10.456'%2.2 END END) RES;
             res
    ---------------------
     -1.8559999999974321
    (1 row)
    
    -- MySQL:
    mysql> SELECT (CASE WHEN 1+1=3 THEN 'test' ELSE CASE WHEN 1>2 THEN '-1.5'%06.6600e1 ELSE -TIME '10:10:10.456'%2.2 END END) RES;
    +--------+
    | res    |
    +--------+
    | -1.856 |
    +--------+
    1 row in set (0.00 sec)

  • If operators of the int type (such as ~, &, |, <<, and >>) are nested in a CASE WHEN statement and the return type of the CASE WHEN statement is VARCHAR, truncation may occur in actual situations (you can determine whether truncation will occur by analyzing the original table data). In GaussDB, an error will be reported (a warning is reported when SELECT is used for query and an error is reported when a table is created). MySQL does not report an error. (If you want to CREATE TABLE in GaussDB, you can set sql_mode to disable the strict mode.)

    -- GaussDB:
    m_db=# create table t_base (num_var numeric(20, 10), time_var time(6));
    CREATE TABLE
    m_db=# insert into t_base values ('-2514.1441000000','12:10:10.125000'),('-417.2147000000',' 11:30:25.258000');
    INSERT 0 2
    m_db=# select (~(case when false then time_var else num_var end)) as res2 from t_base;
    WARNING:  Truncated incorrect INTEGER value: '-2514.1441000000'
    CONTEXT:  referenced column: res2
    WARNING:  Truncated incorrect INTEGER value: '-417.2147000000'
    CONTEXT:  referenced column: res2
     res2 
    ------
     2513
     416
    (2 rows)
    m_db=# create table t1 as select (~(case when false then time_var else num_var end)) as res2 from t_base;
    ERROR:  Truncated incorrect INTEGER value: '-2514.1441000000'
    CONTEXT:  referenced column: res2
    m_db=# set sql_mode="";
    SET
    m_db=# create table t1 as select (~(case when false then time_var else num_var end)) as res2 from t_base;
    WARNING:  Truncated incorrect INTEGER value: '-2514.1441000000'
    CONTEXT:  referenced column: res2
    WARNING:  Truncated incorrect INTEGER value: '-417.2147000000'
    CONTEXT:  referenced column: res2
    INSERT 0 2
    m_db=# desc t1;
     Field |        Type         | Null | Key | Default | Extra 
    -------+---------------------+------+-----+---------+-------
     res2  | bigint(21) unsigned | YES  |     |         | 
    (1 row)
    
    -- Mysql:
    mysql> create table t_base (num_var numeric(20, 10), time_var time(6));
    Query OK, 0 rows affected (0.01 sec)
    mysql> insert into t_base values ('-2514.1441000000','12:10:10.125000'),('-417.2147000000',' 11:30:25.258000');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    mysql> select (~(case when false then time_var else num_var end)) as res2 from t_base;
    +------+
    | res2 |
    +------+
    | 2513 |
    |  416 |
    +------+
    2 rows in set (0.00 sec)
    mysql> create table t1 as select (~(case when false then time_var else num_var end)) as res2 from t_base;
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> desc t1;
    +-------+---------------------+------+-----+---------+-------+
    | Field | Type                | Null | Key | Default | Extra |
    +-------+---------------------+------+-----+---------+-------+
    | res2  | bigint(21) unsigned | YES  |     | NULL    |       |
    +-------+---------------------+------+-----+---------+-------+
    1 row in set (0.00 sec)

  • When precision transfer is enabled, if constants are nested in CREATE VIEW AS SELECT CASE WHEN and SELECT CASE WHEN statements (including constant calculation and nesting functions with constants), the values in GaussDB are the same. In MySQL, some precision may be lost in SELECT CASE WHEN statements.

    -- GaussDB:
    m_db=# CREATE OR REPLACE VIEW test_view AS
    m_db-# SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
    CREATE VIEW
    m_db=# SELECT * FROM test_view;
         c1     |    c2     
    ------------+-----------
     0.74663677 | 0.7466368
    (1 row)
    m_db=# SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
         c1     |    c2     
    ------------+-----------
     0.74663677 | 0.7466368
    (1 row)
    
    -- MySQL:
    mysql> CREATE OR REPLACE VIEW test_view AS
        -> SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
    Query OK, 0 rows affected (0.00 sec)
    mysql> SELECT * FROM test_view;
    +------------+-----------+
    | c1         | c2        |
    +------------+-----------+
    | 0.74663677 | 0.7466368 |
    +------------+-----------+
    1 row in set (0.00 sec)
    mysql> SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
    +----------+----------+
    | c1       | c2       |
    +----------+----------+
    | 0.746637 | 0.746637 |
    +----------+----------+
    1 row in set (0.00 sec)

  • When precision transfer is enabled, an M-compatible database supports table creation using the UNION/CASE WHEN statement. However, due to different architectures, the database does not ensure that all types of created tables are the same as those of MySQL 8.0. The scenarios where character strings and binary-related types are returned and some function nesting scenarios in MySQL are different from those in GaussDB.

    -- GaussDB:
    m_db=# CREATE TABLE IF NOT EXISTS testcase (id int, col_text1 tinytext, col_text2 text, col_blob1 tinyblob, col_blob2 blob, col_blob3 mediumblob, col_blob4 longblob);
    CREATE TABLE
    m_db=# CREATE TABLE t1 AS SELECT id,(CASE WHEN id=2 THEN col_text1 ELSE 'test' END) f35,  (CASE WHEN id=2 THEN col_text2 ELSE 'test' END) f36,(CASE WHEN id=2 THEN col_blob1 ELSE 'test' END) f41,  (CASE WHEN id=2 THEN col_blob2 ELSE 'test' END) f42,  (CASE WHEN id=2 THEN col_blob3 ELSE 'test' END) f43,  (CASE WHEN id=2 THEN col_blob4 ELSE 'test' END) f44 FROM testcase;
    INSERT 0 0
    m_db=# DESC t1;
    Field |      Type      | Null | Key | Default | Extra
    -------+----------------+------+-----+---------+-------
    id    | integer(11)    | YES  |     |         |
    f35   | varchar(255)   | YES  |     |         |
    f36   | mediumtext     | YES  |     |         |
    f41   | varbinary(255) | YES  |     |         |
    f42   | blob           | YES  |     |         |
    f43   | mediumblob     | YES  |     |         |
    f44   | longblob       | YES  |     |         |
    (7 rows)
    
    m_db=# CREATE TABLE IF NOT EXISTS testtext1 (col10 text);
    CREATE TABLE
    m_db=# CREATE TABLE IF NOT EXISTS testtext2 (col10 text);
    CREATE TABLE
    m_db=# CREATE TABLE testtext AS (select * from testtext1) UNION (select * from testtext2);
    CREATE TABLE
    m_db=# desc testtext;
    m_db=# 
     Field | Type | Null | Key | Default | Extra 
    -------+------+------+-----+---------+-------
     col10 | text | YES  |     |         | 
    (1 row)
    
    m_db=# create table testchar as  select (select lcase(-6873.4354)) a, (select sec_to_time(-485769.567)) b union all select (select bin(-58768923.21321)), (select asin(-0.7237465));
    INSERT 0 2
    m_db=# desc testchar;
     Field |    Type     | Null | Key | Default | Extra 
    -------+-------------+------+-----+---------+-------
     a     | text        | YES  |     |         | 
     b     | varchar(23) | YES  |     |         | 
    (2 rows)
    
    m_db=# CREATE TABLE test_func (col_text char(29));
    CREATE TABLE
    m_db=# CREATE TABLE test1 AS SELECT * FROM ( SELECT 
            GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
            ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
            SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
            DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
            DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
            ADDTIME(col_text, '8:20:20.3554') f7,
            SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t1
    UNION ALL 
            SELECT * FROM ( SELECT
            GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
            ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
            SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
            DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
            DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
            ADDTIME(col_text, '8:20:20.3554') f7,
            SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t2;
    INSERT 0 0
    m_db=# DESC test1;
     Field |    Type     | Null | Key | Default | Extra 
    -------+-------------+------+-----+---------+-------
     f1    | double      | YES  |     |         | 
     f2    | double      | YES  |     |         | 
     f3    | varchar(29) | YES  |     |         | 
     f4    | varchar(29) | YES  |     |         | 
     f5    | varchar(29) | YES  |     |         | 
     f6    | varchar(29) | YES  |     |         | 
     f7    | varchar(29) | YES  |     |         | 
     f8    | varchar(29) | YES  |     |         | 
    (8 rows)
    
    -- MySQL:
    mysql> CREATE TABLE IF NOT EXISTS testcase (id int, col_text1 tinytext, col_text2 text, col_blob1 tinyblob, col_blob2 blob, col_blob3 mediumblob, col_blob4 longblob);
    Query OK, 0 rows affected (0.01 sec)
    mysql> CREATE TABLE t1 AS SELECT id,(CASE WHEN id=2 THEN col_text1 ELSE 'test' END) f35,  (CASE WHEN id=2 THEN col_text2 ELSE 'test' END) f36,(CASE WHEN id=2 THEN col_blob1 ELSE 'test' END) f41,  (CASE WHEN id=2 THEN col_blob2 ELSE 'test' END) f42,  (CASE WHEN id=2 THEN col_blob3 else 'test' END) f43,  (CASE WHEN id=2 THEN col_blob4 ELSE 'test' END) f44 FROM testcase;
    Query OK, 0 rows affected (0.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> DESC t1;
    +-------+----------+------+-----+---------+-------+
    | Field | Type     | Null | Key | Default | Extra |
    +-------+----------+------+-----+---------+-------+
    | id    | int      | YES  |     | NULL    |       |
    | f35   | longtext | YES  |     | NULL    |       |
    | f36   | longtext | YES  |     | NULL    |       |
    | f41   | longblob | YES  |     | NULL    |       |
    | f42   | longblob | YES  |     | NULL    |       |
    | f43   | longblob | YES  |     | NULL    |       |
    | f44   | longblob | YES  |     | NULL    |       |
    +-------+----------+------+-----+---------+-------+
    7 rows in set (0.00 sec)
    
    mysql> CREATE TABLE IF NOT EXISTS testtext1 (col10 text);
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> CREATE TABLE IF NOT EXISTS testtext2 (col10 text);
    Query OK, 0 rows affected (0.02 sec)
    
    mysql>  CREATE TABLE testtext AS (select * from testtext1) UNION (select * from testtext2);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc testtext;
    +-------+------------+------+-----+---------+-------+
    | Field | Type       | Null | Key | Default | Extra |
    +-------+------------+------+-----+---------+-------+
    | col10 | mediumtext | YES  |     | NULL    |       |
    +-------+------------+------+-----+---------+-------+
    1 row in set (0.00 sec)
    
    mysql> set sql_mode='';
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    mysql> create table testchar as  select (select lcase(-6873.4354)) a, (select sec_to_time(-485769.567)) b union all select (select bin(-58768923.21321)), (select asin(-0.7237465));
    Query OK, 2 rows affected, 1 warning (0.02 sec)
    Records: 2  Duplicates: 0  Warnings: 1
    
    mysql> desc testchar;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | a     | varchar(21) | YES  |     | NULL    |       |
    | b     | varchar(53) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    mysql> CREATE TABLE test_func (col_text char(29));
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> CREATE TABLE test1 AS SELECT * FROM ( SELECT 
        -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
        -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
        -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
        -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
        -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
        -> ADDTIME(col_text, '8:20:20.3554') f7,
        -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t1
        -> UNION ALL 
        -> SELECT * FROM ( SELECT
        -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
        -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
        -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
        -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
        -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
        -> ADDTIME(col_text, '8:20:20.3554') f7,
        -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t2;
        -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t1
        -> UNION ALL 
        -> SELECT * FROM ( SELECT
        -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
        -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
        -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
        -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
        -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
        -> ADDTIME(col_text, '8:20:20.3554') f7,
        -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t2;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> 
    mysql> DESC test1;
    +-------+------------+------+-----+---------+-------+
    | Field | Type       | Null | Key | Default | Extra |
    +-------+------------+------+-----+---------+-------+
    | f1    | binary(23) | YES  |     | NULL    |       |
    | f2    | binary(23) | YES  |     | NULL    |       |
    | f3    | char(29)   | YES  |     | NULL    |       |
    | f4    | char(29)   | YES  |     | NULL    |       |
    | f5    | char(29)   | YES  |     | NULL    |       |
    | f6    | char(29)   | YES  |     | NULL    |       |
    | f7    | char(29)   | YES  |     | NULL    |       |
    | f8    | char(29)   | YES  |     | NULL    |       |
    +-------+------------+------+-----+---------+-------+
    8 rows in set (0.01 sec)

  • In the scenario where precision transfer is enabled, for the CREATE TABLE AS SELECT A % (CASE WHEN) statement, if A is of the DECIMAL type and the result of CASE WHEN is of the date type (DATE, TIME, or DATETIME), the two databases are different in the precision obtained by performing the modulo operation (%). The precision obtained by GaussDB is the same as that obtained by performing modulo operations on the decimal type and date type.

    -- GaussDB: (decimal % date type case) and (numeric%date) have the same precision, that is, decimal(24,10).
    m_db=# SET m_format_behavior_compat_options = 'enable_precision_decimal';
    SET
    m_db=# DROP TABLE IF EXISTS t1, t2;
    DROP TABLE
    m_db=# CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
    CREATE TABLE
    m_db=# CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1;
    INSERT 0 0
    m_db=# DESC t2;
     Field |      Type      | Null | Key | Default | Extra
    -------+----------------+------+-----+---------+-------
     res1  | decimal(24,10) | YES  |     |         |
    (1 row)
    
    m_db=# DROP TABLE IF EXISTS t1, t2;
    DROP TABLE
    m_db=# CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
    CREATE TABLE
    m_db=# CREATE TABLE t2 AS SELECT num_var % dt_var AS RES1 from t1;
    INSERT 0 0
    m_db=# DESC t2;
     Field |      Type      | Null | Key | Default | Extra
    -------+----------------+------+-----+---------+-------
     res1  | decimal(24,10) | YES  |     |         |
    (1 row)
    
    -- MySQL 5.7: The precision is different. The precision of (decimal % date type case) is decimal(65,10), and that of (numeric%date) is decimal(24,10).
    mysql> DROP TABLE IF EXISTS t1, t2;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> DESC t2;
    
    +-------+----------------+------+-----+---------+-------+
    | Field | Type           | Null | Key | Default | Extra |
    +-------+----------------+------+-----+---------+-------+
    | res1  | decimal(65,10) | YES  |     | NULL    |       |
    +-------+----------------+------+-----+---------+-------+
    1 row in set (0.00 sec)
    
    mysql> DROP TABLE IF EXISTS t1, t2;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> CREATE TABLE t2 AS SELECT num_var % dt_var AS res1 FROM t1;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> DESC t2;
    +-------+----------------+------+-----+---------+-------+
    | Field | Type           | Null | Key | Default | Extra |
    +-------+----------------+------+-----+---------+-------+
    | res1  | decimal(24,10) | YES  |     | NULL    |       |
    +-------+----------------+------+-----+---------+-------+
    1 row in set (0.00 sec)
    
    -- MySQL 8.0: The precision of (decimal % date type case) and (numeric%date) is decimal(20,10).
    mysql> DROP TABLE IF EXISTS t1, t2;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> DESC t2;
    
    +-------+----------------+------+-----+---------+-------+
    | Field | Type           | Null | Key | Default | Extra |
    +-------+----------------+------+-----+---------+-------+
    | res1  | decimal(20,10) | YES  |     | NULL    |       |
    +-------+----------------+------+-----+---------+-------+
    1 row in set (0.00 sec)
    
    mysql> DROP TABLE IF EXISTS t1, t2;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
    
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> CREATE TABLE t2 AS SELECT num_var % dt_var AS res1 FROM t1;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> DESC t2;
    +-------+----------------+------+-----+---------+-------+
    | Field | Type           | Null | Key | Default | Extra |
    +-------+----------------+------+-----+---------+-------+
    | res1  | decimal(20,10) | YES  |     | NULL    |       |
    +-------+----------------+------+-----+---------+-------+
    1 row in set (0.00 sec)

  • When precision transfer is enabled and UNION is used, if the query statement participates in set calculation, the queried column is a constant, and the query result data type is INT or DECIMAL, the returned precision is different. In MySQL 5.7, the returned precision is related to the left/right sequence of UNION. In MySQL 8.0 and GaussDB, they are irrelevant.

    -- GaussDB:
    m_db=# CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2);
    INSERT 0 2
    m_db=# DESC t1;
    Field |     Type     | Null | Key | Default | Extra
    -------+--------------+------+-----+---------+-------
    c2    | decimal(5,3) | YES  |     |         |        
    (1 row)
    m_db=# CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2);
    INSERT 0 2
    m_db=# DESC t2;
    Field |     Type     | Null | Key | Default | Extra
    -------+--------------+------+-----+---------+-------
    c2    | decimal(5,3) | YES  |     |         |        
    (1 row)
    
    -- Mysql5.7:
    mysql> CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2);
    Query OK, 2 rows affected (2.28 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    mysql> DESC t1;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | c2    | decimal(6,3) | NO   |     | 0.000   |       |
    +-------+--------------+------+-----+---------+-------+
    1 row in set (0.00 sec)
    mysql> CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2);
    Query OK, 2 rows affected (2.22 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    mysql> DESC t2;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | c2    | decimal(5,3) | NO   |     | 0.000   |       |
    +-------+--------------+------+-----+---------+-------+
    1 row in set (0.00 sec)
    
    -- Mysql8.0:
    mysql> CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2);
    Query OK, 2 rows affected (0.02 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    mysql> DESC t1;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | c2    | decimal(5,3) | NO   |     | 0.000   |       |
    +-------+--------------+------+-----+---------+-------+
    1 row in set (0.03 sec)
    mysql>  CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2);   
    Query OK, 2 rows affected (0.03 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> DESC t2;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | c2    | decimal(5,3) | NO   |     | 0.000   |       |
    +-------+--------------+------+-----+---------+-------+
    1 row in set (0.02 sec)

Differences in Double Colon Conversion

In GaussDB, if you use double colons to convert input parameters of a function to another type, the result may be unexpected. In MySQL, double colons do not take effect.

m_db=# SELECT POW("12"::VARBINARY,"12"::VARBINARY);
ERROR:  value out of range: overflow
CONTEXT:  referenced column: pow

varbinary col
m_db=# CREATE TABLE test_varbinary (
        A VARBINARY(10)
);
m_db=# INSERT INTO test_varbinary VALUES ('12');
m_db=# SELECT POW(A, A) FROM test_varbinary;
      pow      
---------------
 8916100448256
(1 row)

Differences in Decimal Types

m_db=# create table test as select 004.01 col1;
INSERT 0 1
m_db=# desc test;
 Field |     Type     | Null | Key | Default | Extra 
-------+--------------+------+-----+---------+-------
 col1  | decimal(3,2) | YES  |     |         | 
(1 row)

mysql 5.7
mysql> create table test as select 004.01 col1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | decimal(5,2) | NO   |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql 8.0
mysql> create table test as select 004.01 col1;
Query OK, 1 row affected (0.23 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | decimal(4,2) | NO   |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)