MySQL Bugs: #14100: STRAIGHT_JOIN hint causes a eq_ref to become ref and fail to see 0/1 rowmatches

Bug #14100	STRAIGHT_JOIN hint causes a eq_ref to become ref and fail to see 0/1 rowmatches
Submitted:	18 Oct 2005 0:54	Modified:	14 Jul 2006 16:10
Reporter:	Tobias Asplund	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.13-rc/4.1.XX	OS:	Windows (Windows/FreeBSD)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
Using the STRAIGHT_JOIN hint for the optimizer seems to chose a sub-optimal retrieval algorithm for UNIQUE indexes.

How to repeat:
/*
  using the world example database available at
  http://downloads.mysql.com/docs/world.sql
  to get an adequate datasize to try to rule out optimizer
  decisions on really small tables
*/

ALTER TABLE Country ADD UNIQUE INDEX (Capital);
-- Query OK, 239 rows affected (0.02 sec)
-- Records: 239  Duplicates: 0  Warnings: 0

EXPLAIN SELECT STRAIGHT_JOIN Country.Name, City.Name FROM City JOIN Country ON Capital = Id\G
/*
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: City
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4079
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Country
         type: ref
possible_keys: Capital
          key: Capital
      key_len: 5
          ref: world.City.Id
         rows: 19
        Extra: Using where
2 rows in set (0.00 sec)
*/

-- What I'm arguing here is that it should find a eq_ref algorithm and that
-- rows: 1  would be the correct estimate, since it's a unique index.

This behavior is as expected because the column "Capital" can contain
NULLs. In fact it is even defined as "DEFAULT NULL". Since it may
contain NULL values, there can be more than one NULL values, thus
we can't use 'eq_ref'.

The documentation also says:
"A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL  values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL."