Bug #72472 now() : missing fractional seconds from a view
Submitted: 28 Apr 2014 14:12 Modified: 23 Sep 2014 22:26
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.6.18 OS:Windows
Assigned to: CPU Architecture:Any

[28 Apr 2014 14:12] Shane Bester 
Description:
Using NOW(6) in a view turns it into NOW(), so we lose fractional seconds!

How to repeat:
select now(6);
drop view if exists `v1`;
create or replace view `v1` as select now(6);
select * from `v1`;
show create table `v1`;
[28 Apr 2014 17:30] MySQL Verification Team 
mysql> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2014-04-28 19:30:09.293766 |
+----------------------------+
1 row in set (0.00 sec)

mysql> drop view if exists `v1`;
Query OK, 0 rows affected (0.00 sec)

mysql> create or replace view `v1` as select now(6);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `v1`;
+---------------------+
| now(6)              |
+---------------------+
| 2014-04-28 19:30:09 |
+---------------------+
1 row in set (0.00 sec)
[29 Apr 2014 6:21] MySQL Verification Team 
Hello Shane,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh
[29 Apr 2014 6:22] MySQL Verification Team 
// 5.6.18, 5.6.19 and 5.7.5 - all are affected

mysql> select version();
+---------------------------------------+
| version()                             |
+---------------------------------------+
| 5.6.18-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2014-05-01 00:44:14.310971 |
+----------------------------+
1 row in set (0.00 sec)

mysql> drop view if exists `v1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create or replace view `v1` as select now(6);
Query OK, 0 rows affected (0.05 sec)

mysql> select * from `v1`;
+---------------------+
| now(6)              |
+---------------------+
| 2014-05-01 00:44:14 |
+---------------------+
1 row in set (0.00 sec)

mysql> show create table `v1`;
+------+------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                      | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select now() AS `now(6)` | utf8                 | utf8_general_ci      |
+------+------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

// 5.6.19

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.19-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> use test
Database changed
mysql> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2014-05-01 02:18:03.201013 |
+----------------------------+
1 row in set (0.00 sec)

mysql> drop view if exists `v1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create or replace view `v1` as select now(6);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `v1`;
+---------------------+
| now(6)              |
+---------------------+
| 2014-05-01 02:18:03 |
+---------------------+
1 row in set (0.01 sec)

mysql> show create table `v1`;
+------+------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                      | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select now() AS `now(6)` | utf8                 | utf8_general_ci      |
+------+------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

// 5.7.5

mysql> select version();
+----------------------------------------------+
| version()                                    |
+----------------------------------------------+
| 5.7.5-m15-enterprise-commercial-advanced-log |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> use test
Database changed
mysql> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2014-05-01 01:04:06.801002 |
+----------------------------+
1 row in set (0.00 sec)

mysql> drop view if exists `v1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create or replace view `v1` as select now(6);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from `v1`;
+---------------------+
| now(6)              |
+---------------------+
| 2014-05-01 01:04:06 |
+---------------------+
1 row in set (0.00 sec)

mysql> show create table `v1`;
+------+------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                      | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select now() AS `now(6)` | utf8                 | utf8_general_ci      |
+------+------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
[23 Sep 2014 22:26] Paul DuBois 
Noted in 5.7.5 changelog.

NOW(N) in a view definition was stored as NOW(), thus losing the
fractional seconds part.