Error 1064 on SELECT from VIEW containing TIMESTAMPDIFF(MICROSECOND, ...)

[Jaime Crespo]

This is a bug coming from upstream: Error 1064 on SELECT from VIEW containing TIMESTAMPDIFF(MICROSECOND, ...)

I run into it when trying to use TIMESTAMPDIFF with microsecond resolution by using a view instead of directly a SELECT 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, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. 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)

I atach a patch to solve it.

[Jaime Crespo (jynus)]

Upstream bug report: http://bugs.mysql.com/bug.php?id=60628

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1686