MySQL Bugs: #63569: <hints> in EXPLAIN EXTENDED

Bug #63569	<hints> in EXPLAIN EXTENDED
Submitted:	3 Dec 2011 21:07	Modified:	28 Nov 2012 9:15
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Since 5.5 (I think) EXPLAIN EXTENDED + SHOW WARNINGS will sometimes add information in <brackets> (what is what I call a <hint>).  The problem is that wehen it happens the output is a non-executabel statement (it raises a 1064 syntax error).

An example in this recent report: http://bugs.mysql.com/bug.php?id=63563
.. where EXPLAIN EXTENDED + SHOW WARNINGS returns (on 5.5) 

"SELECT `test`.`testbin`.`c1` AS `c1`,`test`.`testbin`.`c2` AS c2`,`test`.`testbin`.`c3` AS `c3`,`test`.`testbin`.`c4` AS `c4` FROM `test`.`testbin` WHERE (`test`.`testbin`.`c2` LIKE <cache>(CAST('eRiC' AS CHAR CHARSET BINARY))) LIMIT 1"

How to repeat:
See above

Suggested fix:
Please wrap the hints in comments like:

"SELECT `test`.`testbin`.`c1` AS `c1`,`test`.`testbin`.`c2` AS c2`,`test`.`testbin`.`c3` AS `c3`,`test`.`testbin`.`c4` AS `c4` FROM `test`.`testbin` WHERE (`test`.`testbin`.`c2` LIKE /*<cache>*/(CAST('eRiC' AS CHAR CHARSET BINARY))) LIMIT 1"

Thank you for the problem report and solution suggested. I'd say this is a minor bug at the border of feature request.

I believe that this behavior is not a bug.
The string in the warning is not a valid SQL by the definition.
Yes, we can comment out the "<cache>" string without any damage.
But in addition to "<cache>" this "SQL" may contain "<not>(...)",
"<exists>(...)", "<in_optimizer>(...)" and "<is_not_null_test>(field_name)"
predicates, and commenting out of, for example, "<is_not_null_test>" doesn't
make sense.

Example query with many <predicates>:

/* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by
(<not>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from
`test`.`t2` where trigcond_if(outer_field_is_not_null,
((<cache>(`test`.`t1`.`i`) <= `test`.`t2`.`i`) or isnull(`test`.`t2`.`i`)),
true) having trigcond_if(outer_field_is_not_null,
<is_not_null_test>(`test`.`t2`.`i`), true)))) or
<not>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#3 */ select 1 from
`test`.`t2` where trigcond_if(outer_field_is_not_null,
((<cache>(`test`.`t1`.`i`) >= `test`.`t2`.`i`) or isnull(`test`.`t2`.`i`)),
true) having trigcond_if(outer_field_is_not_null,
<is_not_null_test>(`test`.`t2`.`i`), true)))))

Ok .. I never clained it was a big deal. Just a little inconvenience.

It is also difficult for me to discuss as long as this new addition to 5.5 is not documented - what this bug report is about: http://bugs.mysql.com/bug.php?id=63575

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php