MySQL Bugs: #67791: Create View succeeds, but adds invalid parentheses

Bug #67791	Create View succeeds, but adds invalid parentheses
Submitted:	3 Dec 2012 2:54	Modified:	21 Apr 2015 13:06
Reporter:	Gregory Woolsey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.25a, 5.1.67, 5.5.30, 5.6.8	OS:	Linux (64-bit, w/InnoDB 1.1.8)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
I can create a view that contains an expression as a parameter to a function.  However, the view creation parser, in attempting to standardize the query format, inserts extra parentheses in invalid locations.  Selecting from the view reports a syntax error, and querying the stored view text from the INFORMATION_SCHEMA shows extra parentheses added when the view query was parsed, creating an invalid query.

I can reproduce this with the INTERVAL() function, as it was the function I was trying to use when I encountered the problem.  I do not know if the issue is that function specifically or a category of functions in general.

This does not happen when I replace INTERVAL() with CONCAT(), COALESCE(), FIELD(), or ELT(), which all have variable numbers of arguments (my first thought) and the latter two also use the first value as a reference/key for the other values (my second thought).

So this may be a bug specific to INTERVAL() in a view with an expression for the first parameter.  But that is the case I need.

Rewriting my SQL to avoid INTERVAL will involve a CASE statement which will have to evaluate an expensive expression multiple times, depending on which WHEN clause matches a given row.  The data distribution is flat/random, so ordering the WHEN clauses won't save anything.

How to repeat:
Run these statements to:

1. create a test table
2. populate it
3. create a view on it
4. inspect the actual stored view SQL
5. try to query the view

drop table if exists interval_test;

create table interval_test (c1 int);

insert into interval_test(c1) values(null),(1),(2),(10),(100);

create or replace view interval_test_view as
select interval(greatest(0, c1), 0,1,2,3,20) bucket
from interval_test;

select view_definition from information_schema.views where table_name = 'interval_test_view'\G
/* output ("me" is my test database schema): 
*************************** 1. row ***************************
view_definition: select interval((greatest(0,`me`.`interval_test`.`c1`),0,1,2,3,20)) AS `bucket` from `me`.`interval_test`
1 row in set (0.08 sec)
*/

/* this fails because of the extra parenthesis added by the view statement parser */
select * from interval_test_view;

Running just the SELECT from the view creation returns:

bucket
------
-1
2
3
4
5

Suggested fix:
don't add extraneous parentheses, or fix the logic determining where they should go.  Blindly wrapping the argument list to a function will fail, as this case shows.

Same story with 5.5.27 and latest and greatest 5.6.8:

[openxs@chief 5.6]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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> drop table if exists interval_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table interval_test (c1 int);
Query OK, 0 rows affected (0.42 sec)

mysql> insert into interval_test(c1) values(null),(1),(2),(10),(100);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create or replace view interval_test_view as
    -> select interval(greatest(0, c1), 0,1,2,3,20) bucket
    -> from interval_test;
Query OK, 0 rows affected (0.11 sec)

mysql> select view_definition from information_schema.views where table_name = 'interval_test_view'\G
*************************** 1. row ***************************
view_definition: select interval((greatest(0,`test`.`interval_test`.`c1`),0,1,2,3,20)) AS `bucket` from `test`.`interval_test`
1 row in set (0.00 sec)

mysql> select * from interval_test_view;
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 'AS `bucket` from `test`.`interval_test`' at line 1

Thank you for the report.

Verified as described.

Problem is not repeatable in version 5.0 and 5.7

Still there in 5.6.21, the version used by Amazon RDS.  Just hit it again.  After this long, no action?  I can't create usable views for many of my use cases because of this bug.  And have to do extra testing to ensure views work, since creation succeeds but execution fails.

Posted by developer:
 
This is already fixed in MySQL 5.7 (probably since 5.7.5), thanks to a big refactoring of the SQL parser, which cannot be back-ported to MySQL 5.6 (too big).

Noted in 5.7.5 changelog.

In some cases, a successful CREATE VIEW could add invalid parentheses
to expressions in the view definition.

5.6 is still being patched, and this is still a problem.  This bug should not have been closed.  The INTERVAL() function can't be used in views due to this bug.