| Bug #83146 | time to bit produces inconsistent results | ||
|---|---|---|---|
| Submitted: | 26 Sep 2016 7:39 | Modified: | 26 Sep 2016 10:31 |
| Reporter: | 帅 Bang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.6, 5.7 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[26 Sep 2016 10:31]
MySQL Verification Team
Thank you for the bug report.
c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.97-Win X64 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.0 > use test
Database changed
mysql 5.0 > drop table t1,t2,t3;
ERROR 1051 (42S02): Unknown table 't1,t2,t3'
mysql 5.0 > create table t1(c1 bit(64));
Query OK, 0 rows affected (0.19 sec)
mysql 5.0 > create table t2(c1 bit(64));
Query OK, 0 rows affected (0.04 sec)
mysql 5.0 > create table t3(c1 time);
Query OK, 0 rows affected (0.07 sec)
mysql 5.0 > insert into t3 values("01:01:01");
Query OK, 1 row affected (0.03 sec)
mysql 5.0 > insert into t1 select * from t3;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql 5.0 > insert into t2 values(cast("01:01:01" as time));
Query OK, 1 row affected (0.01 sec)
mysql 5.0 > select hex(c1) from t1;
+------------------+
| hex(c1) |
+------------------+
| 30313A30313A3031 |
+------------------+
1 row in set (0.00 sec)
mysql 5.0 > select hex(c1) from t2;
+------------------+
| hex(c1) |
+------------------+
| 30313A30313A3031 |
+------------------+
1 row in set (0.00 sec)
c:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.16 Source distribution PULL: 2016-SEP-07
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > use test
Database changed
mysql 5.7 > drop table t1,t2,t3;
Query OK, 0 rows affected (0.82 sec)
mysql 5.7 > create table t1(c1 bit(64));
Query OK, 0 rows affected (0.33 sec)
mysql 5.7 > create table t2(c1 bit(64));
Query OK, 0 rows affected (0.33 sec)
mysql 5.7 > create table t3(c1 time);
Query OK, 0 rows affected (0.35 sec)
mysql 5.7 > insert into t3 values("01:01:01");
Query OK, 1 row affected (0.04 sec)
mysql 5.7 > insert into t1 select * from t3;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql 5.7 > insert into t2 values(cast("01:01:01" as time));
Query OK, 1 row affected (0.04 sec)
mysql 5.7 > select hex(c1) from t1;
+---------+
| hex(c1) |
+---------+
| 2775 |
+---------+
1 row in set (0.00 sec)
mysql 5.7 > select hex(c1) from t2;
+------------------+
| hex(c1) |
+------------------+
| 30313A30313A3031 |
+------------------+
1 row in set (0.00 sec)
c:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.53 Source distribution PULL: 2016-SEP-07
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.5 > use test
Database changed
mysql 5.5 > drop table t1,t2,t3;
ERROR 1051 (42S02): Unknown table 't2,t3'
mysql 5.5 > create table t1(c1 bit(64));
Query OK, 0 rows affected (0.09 sec)
mysql 5.5 > create table t2(c1 bit(64));
Query OK, 0 rows affected (0.11 sec)
mysql 5.5 > create table t3(c1 time);
Query OK, 0 rows affected (0.08 sec)
mysql 5.5 > insert into t3 values("01:01:01");
Query OK, 1 row affected (0.05 sec)
mysql 5.5 > insert into t1 select * from t3;
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql 5.5 > insert into t2 values(cast("01:01:01" as time));
Query OK, 1 row affected (0.06 sec)
mysql 5.5 > select hex(c1) from t1;
+------------------+
| hex(c1) |
+------------------+
| 30313A30313A3031 |
+------------------+
1 row in set (0.04 sec)
mysql 5.5 > select hex(c1) from t2;
+------------------+
| hex(c1) |
+------------------+
| 30313A30313A3031 |
+------------------+
1 row in set (0.00 sec)
mysql 5.5 >
Description: drop table t1,t2,t3; create table t1(c1 bit(64)); create table t2(c1 bit(64)); create table t3(c1 time); insert into t3 values("01:01:01"); insert into t1 select * from t3; insert into t2 values(cast("01:01:01" as time)); select hex(c1) from t1; ok, what we get is: mysql> select hex(c1) from t1; +---------+ | hex(c1) | +---------+ | 2775 | +---------+ 1 row in set (0.00 sec) But , when we type this, things get weird: select hex(c1) from t2; what we get is : mysql> select hex(c1) from t2; +------------------+ | hex(c1) | +------------------+ | 30313A30313A3031 | +------------------+ 1 row in set (0.00 sec) It is no doubt that this is inconsistent. BTW，similar bugs observed with datetime to bit and date to bit. How to repeat: drop table t1,t2,t3; create table t1(c1 bit(64)); create table t2(c1 bit(64)); create table t3(c1 time); insert into t3 values("01:01:01"); insert into t1 select * from t3; insert into t2 values(cast("01:01:01" as time)); select hex(c1) from t1; select hex(c1) from t2; Suggested fix: both returns 10101 or both returns 30313A30313A3031