|
This works:
MariaDB LABS localhost heartbeat_p > 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 | 18588 |
|
| s2 | 18738 |
|
| s7 | 18798 |
|
| s3 | 18788 |
|
| s4 | 18568 |
|
| s1 | 18928 |
|
| s5 | 19068 |
|
+-------+-------+
|
7 rows in set (0.00 sec)
|
This doesn't:
MariaDB LABS localhost heartbeat_p > 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.00 sec)
|
|
MariaDB LABS localhost heartbeat_p > SELECT * FROM heartbeat;
|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SECOND_FRAC,`heartbeat`.`heartbeat`.`ts`,utc_timestamp()) AS `lag` from `heartbe' at line 1
|
The reason why:
MariaDB LABS localhost heartbeat_p > 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
|
type: ALL
|
possible_keys: NULL
|
key: NULL
|
key_len: NULL
|
ref: NULL
|
rows: 7
|
filtered: 100.00
|
Extra:
|
1 row in set, 1 warning (0.00 sec)
|
|
MariaDB LABS localhost heartbeat_p > SHOW WARNINGS\G
|
*************************** 1. row ***************************
|
Level: Note
|
Code: 1003
|
Message: 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()) AS `lag` from `heartbeat`.`heartbeat`
|
1 row in set (0.00 sec)
|
Please note the the SECOND_FRAC, which shoudln't be there (it is deprecated).
|