Bug #82751 Create View appears to corrupt geospatial function names in version 5.6
Submitted: 27 Aug 2016 9:31 Modified: 27 Aug 2016 9:54
Reporter: Eric Worrall Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6 OS:Any (https://aws.amazon.com/amazon-linux-ami/2016.03-release-notes/)
Assigned to: CPU Architecture:Any
Tags: geospatial, point, polygon, VIEW

[27 Aug 2016 9:31] Eric Worrall
Description:
Create view appears to generate invalid builtin geospatial function names in version 5.6. This does not occur in version 5.5.

point => st_point
polygon => st_multipoint
etc.

How to repeat:
mysql> create view test as select point(1,1);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
ERROR 1356 (HY000): View 'wordpress.test' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> show create view test;
+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                      | character_set_client | collation_connection |
+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| test | CREATE ALGORITHM=UNDEFINED DEFINER=`eric`@`localhost` SQL SECURITY DEFINER VIEW `test` AS select `st_point`(1,1) AS `point(1,1)` | utf8                 | utf8_general_ci      |
+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> drop view test;
Query OK, 0 rows affected (0.00 sec)

packages installed:
sudo yum list installed | grep mysql
mysql-config.x86_64                   5.5.51-1.11.amzn1            @amzn-updates
mysql56.x86_64                        5.6.32-1.16.amzn1            @amzn-updates
mysql56-common.x86_64                 5.6.32-1.16.amzn1            @amzn-updates
mysql56-devel.x86_64                  5.6.32-1.16.amzn1            @amzn-updates
mysql56-embedded.x86_64               5.6.32-1.16.amzn1            @amzn-updates
mysql56-errmsg.x86_64                 5.6.32-1.16.amzn1            @amzn-updates
mysql56-libs.x86_64                   5.6.32-1.16.amzn1            @amzn-updates
mysql56-server.x86_64                 5.6.32-1.16.amzn1            @amzn-updates
php70-mysqlnd.x86_64                  7.0.9-1.14.amzn1             @amzn-updates

Suggested fix:
Restore version 5.5 behaviour - recognise and correctly handle builtin geospatial function names.

Running the same test in version 5.5:
mysql> create view test as select point(1,1);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+---------------------------+
| point(1,1)                |
+---------------------------+
|              ??      ??   |
+---------------------------+
1 row in set (0.00 sec)

mysql> show create view test;
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                 | character_set_client | collation_connection |
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| test | CREATE ALGORITHM=UNDEFINED DEFINER=`eric`@`localhost` SQL SECURITY DEFINER VIEW `test` AS select point(1,1) AS `point(1,1)` | utf8                 | utf8_general_ci      |
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> drop view test;
Query OK, 0 rows affected (0.00 sec)

Version 5.5 packages:
[ec2-user@asignit ~]$ sudo yum list installed | grep mysql;
mysql.noarch                          5.5-1.6.amzn1                @amzn-main   
mysql-config.x86_64                   5.5.51-1.11.amzn1            @amzn-updates
mysql-devel.noarch                    5.5-1.6.amzn1                @amzn-main   
mysql-server.noarch                   5.5-1.6.amzn1                @amzn-main   
mysql55.x86_64                        5.5.51-1.11.amzn1            @amzn-updates
mysql55-devel.x86_64                  5.5.51-1.11.amzn1            @amzn-updates
mysql55-libs.x86_64                   5.5.51-1.11.amzn1            @amzn-updates
mysql55-server.x86_64                 5.5.51-1.11.amzn1            @amzn-updates
php-mysql.x86_64                      5.3.29-1.8.amzn1             @amzn-updates
[27 Aug 2016 9:54] Shane Bester
Thanks for the report.  Verified on 5.6.10+   5.7 works okay.
[13 Jun 2017 4:51] Rob asdfasdfasdfasdfasd
I'm wondering if this is going to be fixed, its still a bug as of 5.6.27 :|

pretty critical issue in my opinion