Bug #1974 index_merge method never used for non-1st tables in some types of join queries

• Submitted: 27 Nov 2003 12:21
• Modified: 27 Nov 2003 13:46
• Reporter: Sergey Petrunya
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0
• Assigned to: Sergey Petrunya
• CPU Architecture: Any

[27 Nov 2003 12:21] Sergey Petrunya

Description:
index_merge method is never used for non-first tables in join queries if previous tables are not being read using 'ref' access method.

How to repeat:
Query
explain select * from t0,t1 where t0.key2 = 1 and (t1.key1 < t0.key1 or t1.key8 < t0.key1);

will produce:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t0
         type: ref
possible_keys: i1,i2
          key: i2
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: i1,i8
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1024
        Extra: Range checked for each record (index map: 0x81)

while query 
explain select * from t0,t1 
where t0.key2 < 2 and (t1.key1 < t0.key1 or t1.key8 < t0.key1);
will produce:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t0
         type: range
possible_keys: i1,i2
          key: i2
      key_len: 4
          ref: NULL
         rows: 3
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: i1
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1024
        Extra: Range checked for each record (index map: 0x01)
2 rows in set (0.00 sec)
i.e. possible_keys and index map are generated incorrectly.

[27 Nov 2003 13:46] Sergey Petrunya

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html