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
Triage: Needs Triage: D2 (Serious)

[3 Dec 2012 2:54] Gregory Woolsey
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.
[3 Dec 2012 10:16] Valeriy Kravchuk
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
[3 Dec 2012 16:55] Sveta Smirnova
Thank you for the report.

Verified as described.

Problem is not repeatable in version 5.0 and 5.7
[18 Apr 2015 20:51] Greg Woolsey
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.
[21 Apr 2015 9:47] Guilhem Bichot
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).
[21 Apr 2015 13:06] Paul Dubois
Noted in 5.7.5 changelog.

In some cases, a successful CREATE VIEW could add invalid parentheses
to expressions in the view definition.
[5 Aug 2016 3:18] Greg Woolsey
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.