SQL function BIN behaviour differ from MySQL.

[cireu]

Bug Report

Add by ting on 2020-11-19:
after the first round of fix, the original issue has been fixed. by now the diff between Tidb and MySQL for this scene is only the error code diff. detail info please see in last comments.
in tidb:
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------+
| Warning | 8029 | Bad Number |
| Warning | 1292 | Truncated incorrect FLOAT value: '10:02:47.050292' |
+---------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
in MySQL:
mysql [localhost:8021] {msandbox} (test) > SHOW WARNINGS;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: 'p' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '10:02:47.050292' |
+---------+------+-----------------------------------------------------+

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

1. Setup TiDB via docker-copose accroding to TiDB's instructment.
2. Setup MySQL via docker docker run --rm --name msql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_DATABASE=test -d mysql:8.0.15
3. git clone https://github.com/tikv/copr-test && cd copr-test
4. Fill data

mysql -h 127.0.0.1 -P 4000 -u root test < push-down-test/prepare/0_data.sql
mysql -h<yourlocalhost> -P3306 -uroot -p123456 test < ./push-down-test/prepare/0_data.sql

5. Query

select format( '10:02:47.050292', ( 'p' DIV `col_smallint` ) ) AS field1 FROM `table20_int_autoinc` WHERE BIN( `col_smallint` );

2. What did you expect to see?

Output

+--------+
| field1 |
+--------+
| 10     |
| 10     |
| 10     |
| 10     |
| 10     |
| 10     |
| 10     |
| 10     |
| 10     |
| 10     |
| 10     |
| 10     |
+--------+

3. What did you see instead?

MySQL output is correct, TiDB throw an error ERROR 1690 (22003): BIGINT value is out of range in '1111111111111111111111111111111111111111111111111000000000000000'

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

v4.0.0-alpha-851-gaca3f4729

Related: tikv/copr-test#28

[ghost]

TiDB now has the same result as MySQL, except the warning message and error code differ:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
 id INT NOT NULL PRIMARY KEY auto_increment,
  `col_smallint` smallint(6) DEFAULT NULL
);

INSERT INTO t1 (col_smallint) VALUES (-32768);
SELECT format( '10:02:47.050292', ( 'p' DIV `col_smallint` ) ) AS field1 FROM t1 WHERE BIN( `col_smallint` );
SHOW WARNINGS;

..

mysql> SELECT format( '10:02:47.050292', ( 'p' DIV `col_smallint` ) ) AS field1 FROM t1 WHERE BIN( `col_smallint` );
+--------+
| field1 |
+--------+
| 10     |
+--------+
1 row in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 8029 | Bad Number                                         |
| Warning | 1292 | Truncated incorrect FLOAT value: '10:02:47.050292' |
+---------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-782-gb72e47e6d
Edition: Community
Git Commit Hash: b72e47e6db8863c08e47714391cf937b0f1c3269
Git Branch: master
UTC Build Time: 2020-07-15 01:26:06
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

Vs MySQL:

mysql [localhost:8021] {msandbox} (test) > SELECT format( '10:02:47.050292', ( 'p' DIV `col_smallint` ) ) AS field1 FROM t1 WHERE BIN( `col_smallint` );
+--------+
| field1 |
+--------+
| 10     |
+--------+
1 row in set, 2 warnings (0.00 sec)

mysql [localhost:8021] {msandbox} (test) > SHOW WARNINGS;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: 'p'              |
| Warning | 1292 | Truncated incorrect DOUBLE value: '10:02:47.050292' |
+---------+------+-----------------------------------------------------+
2 rows in set (0.00 sec)

[seiya-annie]

degrade severity to minor for error code issue

[solotzg]

Shall we close it?