Error 1064 on SELECT from VIEW containing TIMESTAMPDIFF(MICROSECOND, ...)
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Triaged
|
Medium
|
Unassigned | |||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Fix Released
|
Medium
|
Unassigned |
Bug Description
This is a bug coming from upstream: Error 1064 on SELECT from VIEW containing TIMESTAMPDIFF(
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_
Query OK, 0 rows affected (0.00 sec)
mysql> /*!40101 SET character_
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_
-> `exec_master_
-> PRIMARY KEY (`server_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=binary;
Query OK, 0 rows affected (0.03 sec)
mysql> /*!40101 SET character_
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-
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=
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(
+------
| 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(
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(
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(
*******
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
*******
Level: Note
Code: 1003
Message: /* select#1 */ select (case when (`heartbeat`
2 rows in set (0.00 sec)
I atach a patch to solve it.
tags: | added: upstream |
tags: | added: contribution |
Upstream bug report: http:// bugs.mysql. com/bug. php?id= 60628