Cast string like '.1a1' to decimal has no warnings information

[yuqi1129]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Run Master and execute

 select cast('.0a1' as  decimal(10, 2));

2. What did you expect to see? (Required)

MySQL

mysql> select cast('.0a1' as  decimal(10, 2));
+---------------------------------+
| cast('.0a1' as  decimal(10, 2)) |
+---------------------------------+
|                            0.00 |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)

3. What did you see instead (Required)

TiDB

mysql> select cast('.0a1' as  decimal(10, 2));
+---------------------------------+
| cast('.0a1' as  decimal(10, 2)) |
+---------------------------------+
|                            0.00 |
+---------------------------------+
1 row in set (3.40 sec)

4. What is your TiDB version? (Required)

Master branch

[tisonkun]

what do you mean by like '.1a1'? Malformed string to decimal cases?

[yuqi1129]

Yes, this is only one bad cases, for more, you can see the description about the related PR

[yuqi1129]

Moreover, string to decimal also have the following problem compared to MySQL

select cast('1.4a' as decimal); 

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

warning information is 1.4 not 1.4a, which is quite confused as '1.4' can absolutely cast to Decimal

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

mysql> show warnings;
+---------+------+------------+
| Level   | Code | Message    |
+---------+------+------------+
| Warning | 8029 | Bad Number |
+---------+------+------------+
1 row in set (0.00 sec)

Warning info is inconsistent with MySQL

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

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column '%s' at row %d |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

Warning info is somewhat Semi-finished products

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

The above SQL should have warning information

[tisonkun]

mysql> select cast('1.4a' as decimal); 
+-------------------------+
| cast('1.4a' as decimal) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set, 1 warning (0.01 sec)

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

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

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

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

Seems behavior changed. Which version of MySQL you use to test? Is there a source from MySQL document?

[yuqi1129]

The following is the result that executed locally

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 8.0.25-0ubuntu0.20.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select cast('1.4a' as decimal(10,2));
+-------------------------------+
| cast('1.4a' as decimal(10,2)) |
+-------------------------------+
|                          1.40 |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

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

However, In MySQL 5.7 the result is quite different

xxxx@xxxx((none)) > select version();
+---------------+
| version()     |
+---------------+
| 5.7.25-28-log |
+---------------+
1 row in set (0.00 sec)

Sun Jul 11 17:21:55 2021
xxx@xxxx(none)) > select cast('1.4a' as decimal(10,2));
+-------------------------------+
| cast('1.4a' as decimal(10,2)) |
+-------------------------------+
|                          1.40 |
+-------------------------------+
1 row in set (0.00 sec)

Sun Jul 11 17:22:03 2021
xxx@xxx((none)) >

[yuqi1129]

So, I wander what version of MySQL should TiDB consistent with ?

Server version: 5.7.25-TiDB-v5.0.2 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Seems that 5.7 is the right version...

[tangenta]

AFAIK, we don't have a clear goal to be compatible with 5.7 or 8.0.

Unless there is a strong reason, I think both are acceptable. I prefer intuitive behavior(like reporting '1.4a' instead of 1.4 in this case).

[tisonkun]

AFAIK, we don't have a clear goal to be compatible with 5.7 or 8.0.

Unless there is a strong reason, I think both are acceptable. I prefer intuitive behavior(like reporting '1.4a' instead of 1.4 in this case).

But this issue is itself a compatible issue. If it is unclear which version to be compatible with, then the issue itself is unclear to stand or not.

[yuqi1129]

@tisonkun I think 5.7 maybe the right version that tidb should follower with. as there are big difference between version 5.7 and 8.x, what's your thought on this point?

[tisonkun]

@yuqi1129 make sense.

[yuqi1129]

@tisonkun , Thanks your comment, i think the following point needs to be consistent with 5.7

select cast('1.4a' as decimal); 

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

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

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column '%s' at row %d |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

This two problem may have higher priority, other problem can be ignored currently