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: | |
Category: | MySQL Server: DDL | Severity: | S1 (Critical) |
Version: | 5.7/5.6 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[21 Sep 2015 20:08]
Roland Bouman
[21 Sep 2015 20:52]
Roland Bouman
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.
[21 Sep 2015 20:54]
Roland Bouman
WORKAROUND: 1) Edit the .frm file, replace FRAC_SECONDS with original MICROSECOND constant. 2) Restart MySQL View now works.
[21 Sep 2015 21:56]
MySQL Verification Team
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 >
[18 Feb 2016 14:00]
Paul DuBois
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.