the behavior of bit type for / is incompatible with MySQL

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

select b'10000000' DIV 10;
select b'10000000' / 10;
select cast(b'10000000' as unsigned) / 10;

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

mysql > select b'10000000' / 10;
+------------------+
| b'10000000' / 10 |
+------------------+
| 12.8000          |
+------------------+

3. What did you see instead (Required)

mysql > select b'10000000' DIV 10;
+--------------------+
| b'10000000' DIV 10 |
+--------------------+
| 12                 |
+--------------------+

mysql > select cast(b'10000000' as unsigned) / 10;
+------------------------------------+
| cast(b'10000000' as unsigned) / 10 |
+------------------------------------+
| 12.8000                            |
+------------------------------------+

mysql > select b'10000000' / 10;
+------------------+
| b'10000000' / 10 |
+------------------+
| 0.9000           |
+------------------+

4. What is your TiDB version? (Required)

master: fd706ab
release-5.0: 03f11d2

[aytrack]

following sqls are execute on tidb.

mysql > select b'10000000' / 10;
+------------------+
| b'10000000' / 10 |
+------------------+
| 0.9000           |
+------------------+
1 row in set
Time: 0.061s
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1690 | DECIMAL value is out of range in '(1, 0)' |
+---------+------+-------------------------------------------+
1 row in set
Time: 0.052s

mysql > create table t1(a bit(8), b int);
Query OK, 0 rows affected
Time: 0.181s
mysql > create table t2(a bit(8) primary key, b int);
Query OK, 0 rows affected
Time: 0.178s
mysql > insert into t1 values (b'10000000', 10);
Query OK, 1 row affected
Time: 0.080s
mysql > insert into t2 values (b'10000000', 10);
Query OK, 1 row affected
Time: 0.274s
mysql > select a/b from t1;
+------+
| a/b  |
+------+
| 12.8 |
+------+
1 row in set
Time: 0.072s
mysql > select a/b from t2;
+------+
| a/b  |
+------+
| 12.8 |
+------+
1 row in set
Time: 0.065s

mysql > create table t3(a bit(8), b decimal(10, 3) as (a / 10));
Query OK, 0 rows affected
Time: 0.303s
mysql > insert into t3(a) values (b'10000000');
Query OK, 1 row affected
Time: 0.073s
mysql> select * from t3;
+------+--------+
| a    | b      |
+------+--------+
| 0x80 | 12.800 |
+------+--------+

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions