Bug #79563 After "update t1 set b(bigint) = a(date)", '0000000' are appended at tail
Submitted: 9 Dec 2015 6:47 Modified: 14 Dec 2015 8:03
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.7.8 OS:Any
Assigned to: CPU Architecture:Any

[9 Dec 2015 6:47] Su Dylan
Description:
Output:
=======
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(a date, b bigint, c bigint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(a) values ('2015-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set b = a, c = cast(a as signed);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *, a + 1 from t1;
+------------+----------------+----------+----------+
| a          | b              | c        | a + 1    |
+------------+----------------+----------+----------+
| 2015-01-01 | 20150101000000 | 20150101 | 20150102 |
+------------+----------------+----------+----------+
1 row in set (0.00 sec)

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

Problem:
========
After "set b = a" is successfully executed, 20150101 is expected in column b.

How to repeat:

drop table t1;
create table t1(a date, b bigint, c bigint);
insert into t1(a) values ('2015-01-01');
update t1 set b = a, c = cast(a as signed);
select *, a + 1 from t1;

Suggested fix:
After "set b = a" is successfully executed, 20150101 is set in column b.
[9 Dec 2015 16:48] MySQL Verification Team
Hi!

Thank you for your bug report. However, this is not a bug. Zeroes are appended at the end, as you have observed, because when you are assigning a temporal data type to a bigint column, then it is always presumed that bigint should accomodate time part as well.

This is standard conversion routine.
[10 Dec 2015 1:57] Su Dylan
Hi Sinisa,
Thanks for ur reply.

However, this conversion routine is not explaining the result for "c = cast(a as signed)" well.
There is no time part appended, when doing this explicit cast from date to bigint.
[10 Dec 2015 14:20] MySQL Verification Team
Yes, you noticed that well. When an explicit cast is used then a completely different set of conversion routines are deployed. 

This is why we recommend doing explicit casting instead of the implicit conversion that is used in the assignment.

Not a bug.
[10 Dec 2015 15:11] Su Dylan
Hi Sinisa,
This inconsistency is totally unacceptable for me as a user.
Are you sure this is by design?

If this is a restriction which are documented, please direct me to the document link. I appreciate it very much for any kind of documentation reference.
[10 Dec 2015 15:53] MySQL Verification Team
Hi,

Our manual is truly large. You can search for it by looking for casting and conversions.

I do not remember the exact chapter, as my knowledge of the subject does not come from reading the manual. Simply, I was in the team of developers that have created partially that code many years ago.
[10 Dec 2015 16:19] MySQL Verification Team
Actually, there is another way to treat your question.

First of all, let me explain more about assignment conversion. All SQL servers expect that on the right side of the assignment is an expression. You can not cast the entire expression. Hence, current behavior. But, when entire expression consists of a single variable, there could be theoretically be a possibility of treating it as a special case.

That would resolve this report as a feature request. Would you consider yourself a proponent of such a feature ???

Now, let me inform you that there are already applications written with this behavior in mind. Hence, such a feature request, if approved at all, would not be implemented for some time, in order that users have time to change their applications. 

Hence, do you want a feature of automatic casting in the assignment of the expression consisting of a single variable ???
[11 Dec 2015 0:39] Su Dylan
Hi Sinisa,

Thanks for all these explanation.

Yes. I want a feature of this kind of automatic casting.
How could I do that?
[11 Dec 2015 18:24] MySQL Verification Team
Hi Su,

You do not have to do anything. I am filing this as a feature request. We shall not know when it is considered and what is the decision. But, if any positive decision is made, then the deprecation process will be announced on the launch of the new version, like 5.8 or 5.9 ...

In anyway, I will formulate in our internal process what is it that is exactly required with this feature request.
[14 Dec 2015 8:03] Su Dylan
Here is another set of sql, which is not easy to understand:

Recreation:
===========

drop table if exists t1;
Create table t1(c1 date, c2 bigint, c3 bigint);
Insert into t1 values('2000-1-1', 1, 1);
Update t1 set c2= c1, c3 = c1 + 1;
Select * from t1;

Output:
=======
mysql> drop table if exists t1;
CrQuery OK, 0 rows affected (0.00 sec)

mysql> Create table t1(c1 date, c2 bigint, c3 bigint);
Insert intQuery OK, 0 rows affected (0.01 sec)

mysql> Insert into t1 values('2000-1-1', 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> Update t1 set c2= c1, c3 = c1 + 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> Select * from t1;
+------------+----------------+----------+
| c1         | c2             | c3       |
+------------+----------------+----------+
| 2000-01-01 | 20000101000000 | 20000102 |
+------------+----------------+----------+
1 row in set (0.00 sec)

Problem:
========
Here, implicit casting is used, but the result is still inconsistent.