I am writing some queries and stored procedure on MySQL these days. Coming from an Oracle background that can be somewhat frustrating at times. Some query constructs are not available, but also the procedural language is quite limited compared to PL/SQL.
I must say, most limitations are well documented. That means that at least you have some closure when things do not work out. Examples I encountered are:
- Correlated subqueries in FROM-clauses are not supported at all;
- LIMIT in subqueries is mostly not supported;
- Also not supported is ALL or ANY with a subquery returning more than one column.
At other times the optimizer is being difficult. I tried a JOIN where the ON-clause compared a tuple of two fields with the result of a query. That worked fine, except that MySQL refused to use the (primary) index on those two fields. After redefining the primary index to single field and using that, it was a lot faster.
Views are another thing. You can not use a subquery within the FROM-clause of a view. The work-around here is to use a separate view to define the subquery and to refer to it from the main view. That should work fine, as correlated subqueries in FROM-clauses are not supported anyway.
My latest find has me puzzled. It has to do with defining views from prepared statements. Observe this code:
PREPARE stmt_a FROM 'CREATE VIEW test1 AS SELECT 10';
CREATE VIEW test2 AS SELECT * from test1;
PREPARE stmt_b FROM 'CREATE VIEW test3 AS SELECT * FROM test1';
The last EXECUTE fails with an error ERROR 1142 (42000): ANY command denied to user 'database'@'localhost' for table '(null)'. Why would it do that, as defining the view outside a prepared statement works fine and all objects are in the same schema owned by the same user? This seems to be bug 71908. It turns out that granting select-rights on all database objects resolves this for now:
grant select on *.* to `user`@`localhost`;