MySQL Bugs: #87712: Fractional DATETIME setting is not recognized as a change in the API

Bug #87712	Fractional DATETIME setting is not recognized as a change in the API
Submitted:	8 Sep 2017 15:47	Modified:	18 Sep 2017 10:14
Reporter:	Andy Schmidt	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.3.9	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	apply, column definition, datetime, fractional

Description:
"No changes detected" after applying the change to a column DataType from DATETIME to DATETIME(6). The input is accepted as valid syntax, but the "Apply" button will claim that no change was made.

How to repeat:
Update a column definition of a DATETIME field from DATETIME to DATETIME(6), and hit "Apply"

Suggested fix:
Support MySQL 7.1 (fractional second support was added) by recognizing DATETIME(n) as different from DATATIME.

Hello  Andy,

Thank you for the bug report.
This is most likely duplicate of Bug #80204, please see Bug #80204

Thanks,
Chiranjeevi.

Dear Chiranjeevi, I believe this is a completely DISTINCT problem from the one you have been referencing. (Although both deal with the DATETIME type and it certainly would make sense to fix them at the same time.)

Importantly, the other problem occurs earlier in the editing process, likely completely elsewhere in the code, and involves entirely different logic. Also, the two behaviors occur independent from one another and there are no identical work-arounds. This all suggests that these are two different bugs (that just happen to occur with the same data type).

Specifically:
The related problem you are citing deals with INPUT validation, where a missing number of fractional seconds is not accepted when moving the editing focus to the next column, e.g. DATETIME() is not treated as an equivalent to DATETIME or DATETIME(0). The user is never able to press "APPLY" because the input is rejected as invalid syntax. The easily available workaround is use DATETIME or DATETIME(0) - either will be accepted.

The newly reported bug is NOT about INPUT validation. There is NEVER a problem entering the intended datatype of DATETIME(6), and the input is fully accepted! Thus none of the issues reported with the other bug play a part.
The problem only occurs after APPLY is pressed - when any ALTER command should be constructed that accommodates all column changes. At that time, a DIFFERENT part of program code does not detected that DATETIME(6) is a change from the previous value of DATETIME. Instead of creating a proper ALTER statement, a notice appears claiming that nothing had changes thus no ALTER statement is offered.

Finally, the newly reported bug has NO workaround. The user has to manually construct and submit the necessary ALTER statements.

Hello  Andy,

Thank you for the feedback.
Verified this behavior on MySQL Workbench in 6.3.9 version

Thanks,
Chiranjeevi.

Screenshot

Attachment: Bug_87712.JPG (image/jpeg, text), 169.81 KiB.