Builtin function "CAST" compatibilities

[hanfei1991]

select cast(cast(1-2 as unsigned) as signed integer);

MySQL:
mysql> select cast(cast(1-2 as unsigned) as signed integer);
+-----------------------------------------------+
| cast(cast(1-2 as unsigned) as signed integer) |
+-----------------------------------------------+
| -1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

TiDB:
mysql> select cast(cast(1-2 as unsigned) as signed integer);
ERROR 1690 (22003): constant 18446744073709551615 overflows bigint

[winkyao]

select cast('18446744073709551616' as unsigned);

MySQL:

mysql> select cast('18446744073709551616' as unsigned);
+------------------------------------------+
| cast('18446744073709551616' as unsigned) |
+------------------------------------------+
|                     18446744073709551615 |
+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '18446744073709551616' |
+---------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

TiDB:

mysql> select cast('18446744073709551616' as unsigned);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '18446744073709551616'

see sql-mode-strict
and out-of-range-and-overflow

For statements such as SELECT that do not change data, invalid values generate a warning in strict mode, not an error.

Strict mode produces an error for attempts to create a key that exceeds the maximum key length. When strict mode is not enabled, this results in a warning and truncation of the key to the maximum key length.

[winkyao]

select cast(1 as signed int);

MySQL:

mysql> select cast(1 as signed int);
+-----------------------+
| cast(1 as signed int) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

TiDB:

mysql> select cast(1 as signed int);
ERROR 1105 (HY000): line 0 column 27 near ")" (total length 28)

[winkyao]

create table t1(s1 time);
insert into t1 values('11:11:11');
select cast(s1 as decimal(7, 2)) from t1;

Mysql:

mysql> create table t1(s1 time);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values('11:11:11');
Query OK, 1 row affected (0.00 sec)

mysql> select cast(s1 as decimal(7, 2)) from t1;
+---------------------------+
| cast(s1 as decimal(7, 2)) |
+---------------------------+
|                  99999.99 |
+---------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------+
| Level   | Code | Message                                                            |
+---------+------+--------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'cast(s1 as decimal(7, 2))' at row 1 |
+---------+------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

TiDB:

mysql> create table t1(s1 time);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values('11:11:11');
Query OK, 1 row affected (0.02 sec)

mysql> select cast(s1 as decimal(7, 2)) from t1;
ERROR 1690 (22003): DECIMAL value is out of range in '(7, 2)'

see https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html

the maximum ranges of values for the arguments is also not considered(open a new issue #3912, will be solved there):

MySQL:

mysql> select cast(11111 as decimal(66, 2));
ERROR 1426 (42000): Too big precision 66 specified for column '11111'. Maximum is 65.

TiDB:

mysql> select cast(11111 as decimal(66, 2));
+-------------------------------+
| cast(11111 as decimal(66, 2)) |
+-------------------------------+
|                      11111.00 |
+-------------------------------+
1 row in set (0.00 sec)

[winkyao]

select CAST(0x8fffffffffffffff as signed) a, 
CAST(0xfffffffffffffffe as signed) b, 
CAST(0xffffffffffffffff as unsigned) c;

MySQL:

mysql> select CAST(0x8fffffffffffffff as signed) a, CAST(0xfffffffffffffffe as signed) b, CAST(0xffffffffffffffff as unsigned) c;
+----------------------+----+----------------------+
| a                    | b  | c                    |
+----------------------+----+----------------------+
| -8070450532247928833 | -2 | 18446744073709551615 |
+----------------------+----+----------------------+
1 row in set (0.00 sec)

TiDB:

mysql> select CAST(0x8fffffffffffffff as signed) a, CAST(0xfffffffffffffffe as signed) b, CAST(0xffffffffffffffff as unsigned) c;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    0 |    0 |    0 |
+------+------+------+
1 row in set (0.00 sec)

[winkyao]

select cast("1:2:3" as TIME) = "1:02:03";

MySQL:

mysql> select cast("1:2:3" as TIME) = "1:02:03";
+-----------------------------------+
| cast("1:2:3" as TIME) = "1:02:03" |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)

TiDB:

mysql> select cast("1:2:3" as TIME) = "1:02:03";
+-----------------------------------+
| cast("1:2:3" as TIME) = "1:02:03" |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

[winkyao]

select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00');

MySQL:

mysql> select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00');
+-----------------------------------------------------------+
| timediff(cast('2004-12-30 12:00:00' as time), '12:00:00') |
+-----------------------------------------------------------+
| 00:00:00                                                  |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

TiDB:

mysql> select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00');
+-----------------------------------------------------------+
| timediff(cast('2004-12-30 12:00:00' as time), '12:00:00') |
+-----------------------------------------------------------+
| 49212:00:00.000000                                        |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

will be solved in #3923

[winkyao]

MySQL:

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+

TiDB:

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

will be solved in #3917

[winkyao]

MySQL:

mysql> select cast(-9223372036854775809 as signed);
+--------------------------------------+
| cast(-9223372036854775809 as signed) |
+--------------------------------------+
|                 -9223372036854775808 |
+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '-9223372036854775809' |
+---------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

TiDB:

mysql> select cast(-9223372036854775809 as signed);
+--------------------------------------+
| cast(-9223372036854775809 as signed) |
+--------------------------------------+
|                  9223372036854775807 |
+--------------------------------------+
1 row in set (0.00 sec)

see #3762