Bug #29997 Possible typo in 'IS NULL optimization' docs
Submitted: 24 Jul 2007 3:27 Modified: 24 Jul 2007 15:26
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:All OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[24 Jul 2007 3:27] Baron Schwartz
From http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html,

"MySQL can also optimize the combination col_name = expr AND col_name IS NULL, a form that is common in resolved subqueries. EXPLAIN shows ref_or_null  when this optimization is used."

But from http://dev.mysql.com/doc/refman/5.0/en/explain.html,


This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries. In the following examples, MySQL can use a ref_or_null join to process ref_table:

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;"

The difference is the first example says AND instead of OR.  "col_name = expr AND col_name IS NULL" is always false.  I think it's a typo and should be "col_name = expr OR col_name IS NULL."

How to repeat:
Doc bug.
[24 Jul 2007 6:04] Sveta Smirnova
Thank you for the report.

Verified as described.
[24 Jul 2007 15:26] 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.