MySQL Bugs: #13455: JOIN: different execution plans for (col=BETWEEN '0' AND '0') AND (col='0')

Bug #13455	JOIN: different execution plans for (col=BETWEEN '0' AND '0') AND (col='0')
Submitted:	24 Sep 2005 6:03	Modified:	30 Sep 2005 18:58
Reporter:	Georg Richter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.14-rc	OS:	Any (all)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
in join optimizer chooses different execution plans for

WHERE T1.MATNR='000000000000001300' 

and

WHERE T1.MATNR BETWEEN'000000000000001300' AND '000000000000001300' 

Table definition and data are attached to this bug report

How to repeat:
5.0.13-beta-log on 10.17.69.84
[08:04] root@MY2>EXPLAIN SELECT T1.* FROM SZMARA T1 JOIN SZMAKT T2 JOIN SZMVKE T3 WHERE T1.MANDT=T2.MANDT AND T1.MATNR=T2.MATNR AND T1.MANDT=T3.MANDT AND T1.MATNR=T3.MATNR AND T1.MATNR='000000000000001300';                                +----+-------------+-------+--------+------------------------------------+---------+---------+--------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys                      | key     | key_len | ref                | rows | Extra                    |
+----+-------------+-------+--------+------------------------------------+---------+---------+--------------------+------+--------------------------+
|  1 | SIMPLE      | T3    | index  | PRIMARY                            | PRIMARY | 35      | NULL               | 7557 | Using where; Using index |
|  1 | SIMPLE      | T2    | ref    | PRIMARY,SZMAKT~M                   | PRIMARY | 25      | MY2.T3.MANDT,const |    1 | Using where; Using index |
|  1 | SIMPLE      | T1    | eq_ref | PRIMARY,SZMARA~L,SZMARA~O,SZMARA~T | PRIMARY | 25      | MY2.T2.MANDT,const |    1 | Using where              |
+----+-------------+-------+--------+------------------------------------+---------+---------+--------------------+------+--------------------------+
3 rows in set (0.00 sec)
00 sec)

5.0.13-beta-log on 10.17.69.84
[07:59] root@MY2>EXPLAIN SELECT T1.* FROM SZMARA T1 JOIN SZMAKT T2 JOIN SZMVKE T3 WHERE T1.MANDT=T2.MANDT AND T1.MATNR=T2.MATNR AND T1.MANDT=T3.MANDT AND T1.MATNR=T3.MATNR AND T1.MATNR BETWEEN '000000000000001300' AND '000000000000001300' ;
+----+-------------+-------+------+------------------------------------+---------+---------+---------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys                      | key     | key_len | ref                       | rows  | Extra                    |
+----+-------------+-------+------+------------------------------------+---------+---------+---------------------------+-------+--------------------------+
|  1 | SIMPLE      | T1    | ALL  | PRIMARY,SZMARA~L,SZMARA~O,SZMARA~T | NULL    | NULL    | NULL                      | 10036 | Using where              |
|  1 | SIMPLE      | T3    | ref  | PRIMARY                            | PRIMARY | 25      | MY2.T1.MANDT,MY2.T1.MATNR |     1 | Using index              |
|  1 | SIMPLE      | T2    | ref  | PRIMARY,SZMAKT~M                   | PRIMARY | 25      | MY2.T3.MANDT,MY2.T1.MATNR |     1 | Using where; Using index |
+----+-------------+-------+------+------------------------------------+---------+---------+---------------------------+-------+--------------------------+
3 rows in set (0.00 sec)

Table definition and data for bug #13455

Attachment: SQL.tar.bz2 (application/x-tbz, text), 144.16 KiB.

mysql> EXPLAIN SELECT T1.* FROM SZMARA T1 JOIN SZMAKT T2 JOIN SZMVKE T3 WHERE T1.MANDT=T2.MANDT AND T1.MATNR=T2.MATNR AND T1.MANDT=T3.MANDT AN
D T1.MATNR=T3.MATNR AND T1.MATNR='000000000000001300'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: T3
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 35
          ref: NULL
         rows: 7578
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: T2
         type: ref
possible_keys: PRIMARY,SZMAKT~M
          key: PRIMARY
      key_len: 25
          ref: MY2.T3.MANDT,const
         rows: 1
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: T1
         type: eq_ref
possible_keys: PRIMARY,SZMARA~L,SZMARA~O,SZMARA~T
          key: PRIMARY
      key_len: 25
          ref: MY2.T2.MANDT,const
         rows: 1
        Extra: Using where
3 rows in set (0.01 sec)

mysql> EXPLAIN SELECT T1.* FROM SZMARA T1 JOIN SZMAKT T2 JOIN SZMVKE
    -> T3 WHERE T1.MANDT=T2.MANDT AND T1.MATNR=T2.MATNR AND T1.MANDT=T3.MANDT AND
    -> T1.MATNR=T3.MATNR AND T1.MATNR BETWEEN '000000000000001300' AND
    -> '000000000000001300'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: T1
         type: ALL
possible_keys: PRIMARY,SZMARA~L,SZMARA~O,SZMARA~T
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10044
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: T3
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 25
          ref: MY2.T1.MANDT,MY2.T1.MATNR
         rows: 1
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: T2
         type: ref
possible_keys: PRIMARY,SZMAKT~M
          key: PRIMARY
      key_len: 25
          ref: MY2.T3.MANDT,MY2.T1.MATNR
         rows: 1
        Extra: Using where; Using index
3 rows in set (0.01 sec)

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.14-rc-debug |
+-----------------+
1 row in set (0.00 sec)

mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30480

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30482

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30525

Fix pushed into 5.0.14 tree. 
Fix description:
ref optimizer is now able to use "ref" access method for "t.key BETWEEN c1 AND c1" (where c1 is a constant) constructs.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30536

Noted in 5.0.14 changelog.