| Bug #53043 | LIKE ANY produces syntax error | ||
|---|---|---|---|
| Submitted: | 22 Apr 2010 1:22 | Modified: | 6 May 2010 18:05 |
| Reporter: | A A | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.0, 5.1 | OS: | Linux |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | ANY, like, syntax error | ||
[22 Apr 2010 7:50]
Valeriy Kravchuk
In the real grammar (sql/sql_yacc.yy in the sources) comp_op that can be used before ANY does NOT include LIKE. So, this is a documentation problem.
[6 May 2010 18:05]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. Added to http://dev.mysql.com/doc/refman/5.5/en/comparisons-using-subqueries.html: MySQL also allows this construct: non_subquery_operand LIKE (subquery) For ANY, LIKE is not allowed, and <=> is not, either. So I have made an addition to http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html: Where comparison_operator is one of these operators: = > < >= <= <> !=
Description: Consider the query: SELECT 'hi' FROM mytable WHERE mytable.mycolumn LIKE ANY('hello'); MySQL said: #1064 - 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 '('hello')' at line 1 I don't see the error. The mysql documentation v5.1, section 12.2.9.3, "Subqueries with ANY, IN, and SOME," states the format of ANY to be: operand comparison_operator ANY (subquery) The issue seems to be the comparison operator. Section 12.2.9.3 references section 11.2.3, "Comparison Functions and Operators". Section 11.2.3, then, lists LIKE specifically as a comparison operator in table 11.3: LIKE Simple pattern matching The issue, then, may be that subquery was not actually a query. I admit, I was using a literal string value because I can use it with IN(). Nevertheless, SELECT 'hi' FROM mytable WHERE mytable.mycolumn LIKE ANY(SELECT 'hello' FROM sometable); produces an error. Logically, the syntax makes sense: select 'hi' where the column is like anything returned from the subquery. Syntactically, too, it makes sense. One person tries saying that LIKE is not documented as being used for any -- 11.4.1. This is nonsense, as neither "=" nor any other comparative operator is neither documented as having an optional ANY after it. It is in the section on ANY that says you can use any comparative operator with ANY. So, is this a mysql sgrammar parser bug, a documentation bug (is LIKE not a comparitive operator) or something else? How to repeat: SELECT 'hi' FROM mytable WHERE mytable.mycolumn LIKE ANY('hello');