Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on

             strings
MySQL is setting the flag HA_END_SPACE_KEYS for all the keys that reference
text or varchar columns with collation different than binary.
This was done to handle correctly the situation where a lookup on such a key
may return more than 1 row because of the presence of many rows that differ
only by the amount of trailing space in the table's string column.
Inserting such values however appears to violate the unique checks on 
INSERT/UPDATE. Thus that flag must not be set as it will prevent the optimizer
from choosing a faster access method.
This fix removes the setting of the HA_END_SPACE_KEYS flag.


include/my_base.h:
  Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on 
               strings
   - disabled HA_END_SPACE_KEY as it's no longer needed
mysql-test/r/func_str.result:
  Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on 
               strings
   - fixed explain in an existing case
mysql-test/r/merge.result:
  Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on 
               strings
   - fixed explain in an existing case
mysql-test/r/select.result:
  Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on 
               strings
   - test case
mysql-test/r/subselect.result:
  Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on 
               strings
   - fixed explain in an existing case
mysql-test/t/select.test:
  Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on 
               strings
   - test case

1 files changed, 6 insertions, 1 deletions

diff --git a/include/my_base.h b/include/my_base.h
index d8a0e15ccbe..985a27a0c54 100644
--- a/include/my_base.h
+++ b/include/my_base.h
@@ -224,12 +224,17 @@ enum ha_base_keytype {
 /* poor old NISAM has 8-bit flags :-( */
 #define HA_SORT_ALLOWS_SAME	 128	/* Intern bit when sorting records */
 #endif
+#if MYSQL_VERSION_ID < 0x50200
 /*
   Key has a part that can have end space.  If this is an unique key
   we have to handle it differently from other unique keys as we can find
   many matching rows for one key (because end space are not compared)
 */
-#define HA_END_SPACE_KEY	4096
+#define HA_END_SPACE_KEY      0 /* was: 4096 */
+#else
+#error HA_END_SPACE_KEY is obsolete, please remove it
+#endif
+
 
 	/* These flags can be added to key-seg-flag */