MySQL Bugs: #78506: min/max in VIEW on timestamdiff in VIEW confuses optimizer to throw syntax error

Bug #78506	min/max in VIEW on timestamdiff in VIEW confuses optimizer to throw syntax error
Submitted:	21 Sep 2015 20:08	Modified:	18 Feb 2016 14:00
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	5.7/5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A view that calculates the min and max on top of a view that calculates the duration from two timestamps fails with the error message:

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,`test`.`t`.`ts1`,`test`.`t`.`ts2`) AS `d` from `test`.`t`' at line 1

How to repeat:
create database test;

use test;

create table t(
  ts1 datetime(6)
, ts2 datetime(6)
);

create view v1
as
select timestampdiff(microsecond, ts1, ts2) d
from   t
;

create view v2
as
select min(d) as dmin
,      max(d) as dmax
from   v1
;

Suggested fix:
The error message does not make sense. It refers to an expression:

'SECOND_FRAC,`test`.`t`.`ts1`,`test`.`t`.`ts2`) AS `d`

that originates not in the current but in the underlying view. I believe this indicates a bug in the optimizer (or maybe parser)

Don't report an error, create the requested view instead.

Actually - the problem is not even as complicated as "VIEW on VIEW", the original view is at fault:

mysql> select * from v1;
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,`test`.`t`.`ts1`,`test`.`t`.`ts2`) AS `d` from `test`.`t`' at line 1

The contents of v_benchmark_duration.frm are:

TYPE=VIEW
query=select `stored_routines_benchmarks`.`t_benchmark_result`.`test_name` AS `test_name`,`stored_routines_benchmarks`.`t_benchmark_result`.`metric_name` AS `metric_name`,timestampdiff(SECOND_FRAC,`stored_routines_benchmarks`.`t_benchmark_result`.`ts_start`,`stored_routines_benchmarks`.`t_benchmark_result`.`ts_end`) AS `duration` from `stored_routines_benchmarks`.`t_benchmark_result`
md5=a53f6e631ff52726810eb295d5e8335b
updatable=1
algorithm=0
definer_user=root
definer_host=localhost
suid=2
with_check_option=0
timestamp=2015-09-21 19:55:45
create-version=1
source=select    test_name\n,         metric_name\n,         timestampdiff(microsecond, ts_start, ts_end)  duration\nfrom      t_benchmark_result
client_cs_name=utf8
connection_cl_name=utf8_general_ci
view_body_utf8=select `stored_routines_benchmarks`.`t_benchmark_result`.`test_name` AS `test_name`,`stored_routines_benchmarks`.`t_benchmark_result`.`metric_name` AS `metric_name`,timestampdiff(SECOND_FRAC,`stored_routines_benchmarks`.`t_benchmark_result`.`ts_start`,`stored_routines_benchmarks`.`t_benchmark_result`.`ts_end`) AS `duration` from `stored_routines_benchmarks`.`t_benchmark_result`

So bascically, mysql processes the view definition text and for some reason replaces the first argument to TIMESTAMPDIFF, "MICROSECOND" to the formal argument name SECOND_FRAC.

WORKAROUND: 

1) Edit the .frm file, replace FRAC_SECONDS with original MICROSECOND constant. 2) Restart MySQL

View now works.

Thank you for the bug report.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.9 Source distribution PULL: 2015-SEP-19

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 5.7 > create database test;
Query OK, 1 row affected (0.03 sec)

mysql 5.7 > use test
Database changed
mysql 5.7 > create table t(
    ->   ts1 datetime(6)
    -> , ts2 datetime(6)
    -> );
Query OK, 0 rows affected (0.27 sec)

mysql 5.7 >
mysql 5.7 > create view v1
    -> as
    -> select timestampdiff(microsecond, ts1, ts2) d
    -> from   t
    -> ;
Query OK, 0 rows affected (0.08 sec)

mysql 5.7 >
mysql 5.7 > create view v2
    -> as
    -> select min(d) as dmin
    -> ,      max(d) as dmax
    -> from   v1
    -> ;
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,`test`.`t`.`ts1`,`test`.`t`.`ts2`) AS `d` from `test`.`t`' at line 1
mysql 5.7 >

Noted in 5.7.12, 5.8.0 changelogs.

CREATE VIEW statements that used the TIMESTAMPDIFF() function with
MICROSECOND as the unit resulted in incorrect view definitions.