MySQL Bugs: #60628: Error 1064 on SELECT from VIEW containing TIMESTAMPDIFF(MICROSECOND, ...)

Bug #60628	Error 1064 on SELECT from VIEW containing TIMESTAMPDIFF(MICROSECOND, ...)
Submitted:	24 Mar 2011 21:23	Modified:	12 May 2016 10:19
Reporter:	Clifford Johnson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.5.9, 5.5.11	OS:	Any (XP SP3)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	1064, MICROSECOND, SECOND_FRAC, TIMESTAMPDIFF, VIEW

Description:
A SELECT over a VIEW using the TIMESTAMPDIFF function specifying units of MICROSECOND results in an error 1064 about "SECOND_FRAC".

The error text (for the example shown below) is "Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SECOND_FRAC,'1970-01-01 00:00:00.000000','1970-01-01 00:00:00.000001') AS `Name_' at line 1".

From looking at information_schema.TABLES, it appears that the VIEW is actually not created but fails with some form of deferred error (information_schema.TABLES.TABLE_COMMENT contains the error message above) that isn't reported until the VIEW is used.

How to repeat:
The following statements can be used to recreate the issue:

CREATE OR REPLACE VIEW bogus AS 
        SELECT TIMESTAMPDIFF(MICROSECOND, 
                '1970-01-01 00:00:00.000000', '1970-01-01 00:00:00.000001');
        
SELECT * FROM bogus;

Same on 5.1.56 on my environment. The View CREATES without errors, but SELECT returns error 

"Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SECOND_FRAC,'1970-01-01 00:00:00.000000','1970-01-01 00:00:00.000001') AS `Name_' at line 1"

Verified on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.11-debug Source distribution

Copyright (c) 2000, 2010, 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> CREATE OR REPLACE VIEW bogus AS 
    ->         SELECT TIMESTAMPDIFF(MICROSECOND, 
    ->                 '1970-01-01 00:00:00.000000', '1970-01-01 00:00:00.000001');
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT * FROM bogus;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SECOND_FRAC,'1970-01-01 00:00:00.000000','1970-01-01 00:00:00.000001') AS `Name_' at line 1
mysql> SELECT TIMESTAMPDIFF(MICROSECOND, '1970-01-01 00:00:00.000000', '1970-01-01 00:00:00.000001');
+----------------------------------------------------------------------------------------+
| TIMESTAMPDIFF(MICROSECOND, '1970-01-01 00:00:00.000000', '1970-01-01 00:00:00.000001') |
+----------------------------------------------------------------------------------------+
|                                                                                      1 |
+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I worked around this bug by creating the following function and referencing it in the view instead of TIMESTAMPDIFF. Using this function instead (or something similar) allows you to successfully create the view:

CREATE FUNCTION `MicroTimestampDiff`(
t1 datetime,
m1 int,
t2 datetime,
m2 int
) RETURNS bigint(20)
    DETERMINISTIC
return TIMESTAMPDIFF(microsecond, concat(convert(t1, char), '.', right(concat('00000', convert(m1, char)), 6)), concat(convert(t2, char), '.', right(concat('00000', convert(m2, char)), 6)));

Bug confirmed again, same scenario as described previously.

My version: 5.6.5-m8
My OS: Mac OS X 10.6.8

I too was able to work around the issue using a function, not sure how much overhead my specific job will face.

Same problem here.
MySQL version: 5.6.14
OS: Ubuntu 12.04

I created also a function, but without adding microseconds, because MySQL 5.6 contains the microseconds in the datetime type.

CREATE FUNCTION MicroTimestampDiff (
t1 datetime,
t2 datetime
) returns bigint(20)
    DETERMINISTIC
return TIMESTAMPDIFF(microsecond, t1,t2);

I forgot to add the precision to the datetime parameters: (6), in order to pass them with microseconds.

CREATE FUNCTION MicroTimestampDiff (
t1 datetime(6),
t2 datetime(6)
) returns bigint(20)
    DETERMINISTIC
return TIMESTAMPDIFF(microsecond, t1,t2);

jynus@sangai:~$ /opt/wmf-mysql57/bin/mysqladmin -u root create heartbeat
jynus@sangai:~$ mysql -u root heartbeat
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> DROP TABLE IF EXISTS `heartbeat`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> /*!40101 SET @saved_cs_client     = @@character_set_client */;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!40101 SET character_set_client = utf8 */;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `heartbeat` (
    ->   `ts` varbinary(26) NOT NULL,
    ->   `server_id` int(10) unsigned NOT NULL,
    ->   `file` varbinary(255) DEFAULT NULL,
    ->   `position` bigint(20) unsigned DEFAULT NULL,
    ->   `relay_master_log_file` varbinary(255) DEFAULT NULL,
    ->   `exec_master_log_pos` bigint(20) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`server_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=binary;
Query OK, 0 rows affected (0.03 sec)

mysql> /*!40101 SET character_set_client = @saved_cs_client */;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> --
mysql> -- Dumping data for table `heartbeat`
mysql> --
mysql> 
mysql> LOCK TABLES `heartbeat` WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!40000 ALTER TABLE `heartbeat` DISABLE KEYS */;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO `heartbeat` VALUES ('2015-12-26T17:44:49.501000',101612,'db1023-bin.001050',284299334,NULL,NULL),('2015-12-26T17:44:49.501040',101613,'db1024-bin.001842',92370622,NULL,NULL),('2015-12-26T17:44:49.501130',101622,'db1033-bin.001626',644340006,NULL,NULL),('2015-12-26T17:44:49.501010',101627,'db1038-bin.002286',173500436,NULL,NULL),('2015-12-26T17:44:49.501040',101629,'db1040-bin.001463',657668076,NULL,NULL),('2015-12-26T17:44:49.500870',103222,'db1052-bin.002983',210543457,NULL,NULL),('2015-12-26T17:44:49.500750',103228,'db1058-bin.003803',898146447,NULL,NULL);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> /*!40000 ALTER TABLE `heartbeat` ENABLE KEYS */;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
ERROR 1231 (42000): Variable 'time_zone' can't be set to the value of 'NULL'
mysql> SELECT CASE WHEN file like 'db1052%' THEN 's1' WHEN file like 'db1024%' THEN 's2' WHEN file like 'db1038%' THEN 's3' WHEN file like 'db1040%' THEN 's4' WHEN file like 'db1058%' THEN 's5' WHEN file like 'db1023%' THEN 's6' WHEN file like 'db1033%' THEN 's7' ELSE 'unknown' END as `shard`, TIMESTAMPDIFF(MICROSECOND,ts,UTC_TIMESTAMP(6)) AS `lag` FROM heartbeat.heartbeat;
+-------+-----------+
| shard | lag       |
+-------+-----------+
| s6    | 233005996 |
| s2    | 233005956 |
| s7    | 233005866 |
| s3    | 233005986 |
| s4    | 233005956 |
| s1    | 233006126 |
| s5    | 233006246 |
+-------+-----------+
7 rows in set (0.00 sec)

mysql> CREATE VIEW `heartbeat` AS SELECT CASE WHEN file like 'db1052%' THEN 's1' WHEN file like 'db1024%' THEN 's2' WHEN file like 'db1038%' THEN 's3' WHEN file like 'db1040%' THEN 's4' WHEN file like 'db1058%' THEN 's5' WHEN file like 'db1023%' THEN 's6' WHEN file like 'db1033%' THEN 's7' ELSE 'unknown' END as `shard`, TIMESTAMPDIFF(MICROSECOND,ts,UTC_TIMESTAMP(6)) AS `lag` FROM heartbeat.heartbeat;
ERROR 1050 (42S01): Table 'heartbeat' already exists
mysql> create database heartbeat_p
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> use heartbeat_p
Database changed
mysql> CREATE VIEW `heartbeat` AS SELECT CASE WHEN file like 'db1052%' THEN 's1' WHEN file like 'db1024%' THEN 's2' WHEN file like 'db1038%' THEN 's3' WHEN file like 'db1040%' THEN 's4' WHEN file like 'db1058%' THEN 's5' WHEN file like 'db1023%' THEN 's6' WHEN file like 'db1033%' THEN 's7' ELSE 'unknown' END as `shard`, TIMESTAMPDIFF(MICROSECOND,ts,UTC_TIMESTAMP(6)) AS `lag` FROM heartbeat.heartbeat;
Query OK, 0 rows affected (0.01 sec)

mysql> EXPLAIN EXTENDED SELECT CASE WHEN file like 'db1052%' THEN 's1' WHEN file like 'db1024%' THEN 's2' WHEN file like 'db1038%' THEN 's3' WHEN file like 'db1040%' THEN 's4' WHEN file like 'db1058%' THEN 's5' WHEN file like 'db1023%' THEN 's6' WHEN file like 'db1033%' THEN 's7' ELSE 'unknown' END as `shard`, TIMESTAMPDIFF(MICROSECOND,ts,UTC_TIMESTAMP(6)) AS `lag` FROM heartbeat.heartbeat\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: heartbeat
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1681
Message: 'EXTENDED' is deprecated and will be removed in a future release.
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select (case when (`heartbeat`.`heartbeat`.`file` like 'db1052%') then 's1' when (`heartbeat`.`heartbeat`.`file` like 'db1024%') then 's2' when (`heartbeat`.`heartbeat`.`file` like 'db1038%') then 's3' when (`heartbeat`.`heartbeat`.`file` like 'db1040%') then 's4' when (`heartbeat`.`heartbeat`.`file` like 'db1058%') then 's5' when (`heartbeat`.`heartbeat`.`file` like 'db1023%') then 's6' when (`heartbeat`.`heartbeat`.`file` like 'db1033%') then 's7' else 'unknown' end) AS `shard`,timestampdiff(SECOND_FRAC,`heartbeat`.`heartbeat`.`ts`,utc_timestamp(6)) AS `lag` from `heartbeat`.`heartbeat`
2 rows in set (0.00 sec)

My incredible complex patch (5.7) works for me:
https://github.com/mysql/mysql-server/pull/43

Contribution submitted via Github - Replacing reference of SECOND_FRAC (deprecated) for MICROSECOND (*) Contribution by Jaime Crespo (Github jynus, mysql-server/pull/43#issuecomment-169372061): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_54607131.txt (text/plain), 2.29 KiB.

Thank you for the contribution. We can see that the above mentioned problem has been resolved with bug 78506.

Posted by developer:
 
Hi Jaime, As noted above, this bug is now fixed in http://bugs.mysql.com/bug.php?id=78506 , so your contribution was not used. Thanks for the contribution anyway!