Bug #82735 Fractional seconds ignored in view definitions
Submitted: 26 Aug 2016 0:32 Modified: 26 Aug 2016 8:40
Reporter: Jonah Berquist Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.6.31, 5.6.32 OS:Any
Assigned to: CPU Architecture:Any

[26 Aug 2016 0:32] Jonah Berquist
Description:
When creating a view with a time function and specifying a fractional seconds precision, the fsp value is ignored.

How to repeat:
mysql> create view microseconds as select now(6);
Query OK, 0 rows affected (0.02 sec)

mysql> select * from microseconds;
+---------------------+
| now(6)              |
+---------------------+
| 2016-08-25 16:20:04 |
+---------------------+
1 row in set (0.00 sec)

mysql> show create view microseconds\G
*************************** 1. row ***************************
                View: microseconds
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `microseconds` AS select now() AS `now(6)`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
[26 Aug 2016 8:40] Umesh Shastry
Hello Jonah,

Thank you for the report.
This is duplicate of my colleague Shane's Bug #72472 which is fixed in 5.7 and I can confirm too. but 5.6, is still affected and hence verifying for 5.6.

-- 5.7 - fixed

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.14: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.14-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

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> use test
Database changed
mysql> create view microseconds as select now(6);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from microseconds;
+----------------------------+
| now(6)                     |
+----------------------------+
| 2016-08-26 10:20:09.533999 |
+----------------------------+
1 row in set (0.00 sec)

mysql> show create view microseconds\G
*************************** 1. row ***************************
                View: microseconds
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `microseconds` AS select now(6) AS `now(6)`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

-- 5.6 affected

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.32-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

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> use test
Database changed
mysql> create view microseconds as select now(6);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from microseconds;
+---------------------+
| now(6)              |
+---------------------+
| 2016-08-26 10:15:44 |
+---------------------+
1 row in set (0.00 sec)

mysql> show create view microseconds\G
*************************** 1. row ***************************
                View: microseconds
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `microseconds` AS select now() AS `now(6)`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

Thanks,
Umesh