CAST float is not compatible with MySQL

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

select cast(1e308 as signed);
select cast(1e308 + 0 as signed);
select cast(1e308 as unsigned);
select cast(1e308 + 0 as unsigned);

I not sure whether the result of MySQL is more reasonable. I think raise a error when overflow is better than truncate it.

2. What did you expect to see?
   In MySQL 8.0.15

mysql> select cast(1e308 + 0 as signed);
ERROR 1690 (22003): BIGINT value is out of range in '(1e308 + 0)'
mysql> select cast(1e308 as unsigned);
+-------------------------+
| cast(1e308 as unsigned) |
+-------------------------+
|     9223372036854775807 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select cast(1e308 + 0 as unsigned);
ERROR 1690 (22003): BIGINT value is out of range in '(1e308 + 0)'
mysql> select cast(1e308 as signed);
+-----------------------+
| cast(1e308 as signed) |
+-----------------------+
|   9223372036854775807 |
+-----------------------+
1 row in set (0.00 sec)

3. What did you see instead?

mysql> select cast(1e308 + 0 as signed);
ERROR 1690 (22003): constant 1e+308 overflows double
mysql> select cast(1e308 as unsigned);
ERROR 1690 (22003): constant 1e+308 overflows double
mysql> select cast(1e308 + 0 as unsigned);
ERROR 1690 (22003): constant 1e+308 overflows double
mysql> select cast(1e308 as signed);
ERROR 1690 (22003): constant 1e+308 overflows double

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-260-g47a564b-dirty
Git Commit Hash: 47a564be2fc6045c6423e63b28a421924d5557bf
Git Branch: master
UTC Build Time: 2019-03-22 07:10:11
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[jackysp]

Yes, I think to raise an error when overflow is better. Thanks for your report, @wjhuang2016 !

[wjhuang2016]

@jackysp I'm glad you agree with me.
If we deicde to raise an error when overflow, maybe we need to make it consistent with int.

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

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

mysql> select cast(-1 as unsigned);
+----------------------+
| cast(-1 as unsigned) |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.01 sec)

Though cast(-1 as unsigned) will return 18446744073709551615 without warning, it's a verified bug in MySQL, See https://bugs.mysql.com/bug.php?id=79295

[jackysp]

What do you think? @XuHuaiyu

[XuHuaiyu]

I think it's ok. @jackysp
What's your opinion? @morgo

And
Have you tried this in MySQL8.0? @wjhuang2016

[wjhuang2016]

I think it's ok. @jackysp
What's your opinion? @morgo

And
Have you tried this in MySQL8.0? @wjhuang2016

Yeah, My MySQL version is 8.0.15

[morgo]

I am actually a bit surprised by the MySQL 8.0 behavior. Because strict mode is actually strict_trans_tables not strict_all_tables I expected it to be a warning not an error (trans tables means: you need to touch a transactional table).

Just as a general rule, make sure you test context with/without a table. i.e.

DROP TABLE IF EXISTS t;
CREATE TABLE t (a INT);
INSERT INTO t VALUES (1);
select cast(18446744073709551616 as unsigned) from t;
select cast(18446744073709551616 as unsigned);

(it makes no difference in this specific case.)

[wjhuang2016]

I am actually a bit surprised by the MySQL 8.0 behavior. Because strict mode is actually strict_trans_tables not strict_all_tables I expected it to be a warning not an error (trans tables means: you need to touch a transactional table).

Just as a general rule, make sure you test context with/without a table. i.e.

DROP TABLE IF EXISTS t;
CREATE TABLE t (a INT);
INSERT INTO t VALUES (1);
select cast(18446744073709551616 as unsigned) from t;
select cast(18446744073709551616 as unsigned);

(it makes no difference in this specific case.)

I guess that when dealing with cast in MySQL, it doesn't consider the statement context.

[ichn-hu]

@wjhuang2016 I guess we could close this issue as a won't fix and create a new issue that describes more explicitly about fixing the overflowing behavior of casting intergers?

[wjhuang2016]

I think it's ok since this is not a big problem.

[ichn-hu]

/label type/wontfix

[ichn-hu]

I think it's ok since this is not a big problem.

Please help close it~