Bug #50388 partition pruning not working with some right joins
Submitted: 16 Jan 2010 4:13
Reporter: Shannon Wade Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.1.41,5.1.42, 5.1.43-bzr OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[16 Jan 2010 4:13] Shannon Wade
Description:
In some cases partitions are not pruned in some right joins, examining handler status (and mysql debug trace) seems to indicate this is the case (and not just an explain display bug).

1st 

Below example uses 1 table and alias but have tested with multiple tables and engines  as well and explains are the same.

/* pruning working with left join */
SELECT
 t.id,
 t.order_id,
 t.ts,
 t2.id,
 t2.order_id,
 t2.ts,
 t3.id,
 t3.order_id,
 t3.ts
 FROM
 test t
 LEFT JOIN
 test t2
 ON t.id = t2.id
 AND t2.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 LEFT JOIN
 test t3
 ON t2.id = t3.id
 AND t3.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 WHERE
 t.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 ORDER BY
 t.order_id,
 t.ts;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: invalid_dates,day_20091207
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 28
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: invalid_dates,day_20091207
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.t.id
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: invalid_dates,day_20091207
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.t2.id
         rows: 1
        Extra: 
3 rows in set (0.00 sec)

/* pruning not working right join */
SELECT
 t.id,
 t.order_id,
 t.ts,
 t2.id,
 t2.order_id,
 t2.ts,
 t3.id,
 t3.order_id,
 t3.ts
 FROM
 test t2
 LEFT JOIN
 test t3
 ON t2.id = t3.id
 AND t3.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 RIGHT JOIN
 test t
 ON t.id = t2.id
 AND t2.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 WHERE
 t.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 ORDER BY
 t.order_id,
 t.ts;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: invalid_dates,day_20091207
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 28
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: invalid_dates,day_20091129,day_20091130,day_20091201,day_20091202,day_20091203,day_20091204,day_20091205,day_20091206,day_20091207,day_20091208,day_20091209,day_20091210,day_20091211,day_20091212,day_20091213,day_20091214,day_20091215,day_20091216,day_20091217,day_20091218,day_20091219,max
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.t.id
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: invalid_dates,day_20091129,day_20091130,day_20091201,day_20091202,day_20091203,day_20091204,day_20091205,day_20091206,day_20091207,day_20091208,day_20091209,day_20091210,day_20091211,day_20091212,day_20091213,day_20091214,day_20091215,day_20091216,day_20091217,day_20091218,day_20091219,max
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.t.id
         rows: 1
        Extra: 
3 rows in set (0.00 sec)

How to repeat:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) DEFAULT NULL,
`ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`,`ts`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (TO_DAYS(`ts`))
(PARTITION invalid_dates VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION day_20091129 VALUES LESS THAN (734106) ENGINE = InnoDB,
PARTITION day_20091130 VALUES LESS THAN (734107) ENGINE = InnoDB,
PARTITION day_20091201 VALUES LESS THAN (734108) ENGINE = InnoDB,
PARTITION day_20091202 VALUES LESS THAN (734109) ENGINE = InnoDB,
PARTITION day_20091203 VALUES LESS THAN (734110) ENGINE = InnoDB,
PARTITION day_20091204 VALUES LESS THAN (734111) ENGINE = InnoDB,
PARTITION day_20091205 VALUES LESS THAN (734112) ENGINE = InnoDB,
PARTITION day_20091206 VALUES LESS THAN (734113) ENGINE = InnoDB,
PARTITION day_20091207 VALUES LESS THAN (734114) ENGINE = InnoDB,
PARTITION day_20091208 VALUES LESS THAN (734115) ENGINE = InnoDB,
PARTITION day_20091209 VALUES LESS THAN (734116) ENGINE = InnoDB,
PARTITION day_20091210 VALUES LESS THAN (734117) ENGINE = InnoDB,
PARTITION day_20091211 VALUES LESS THAN (734118) ENGINE = InnoDB,
PARTITION day_20091212 VALUES LESS THAN (734119) ENGINE = InnoDB,
PARTITION day_20091213 VALUES LESS THAN (734120) ENGINE = InnoDB,
PARTITION day_20091214 VALUES LESS THAN (734121) ENGINE = InnoDB,
PARTITION day_20091215 VALUES LESS THAN (734122) ENGINE = InnoDB,
PARTITION day_20091216 VALUES LESS THAN (734123) ENGINE = InnoDB,
PARTITION day_20091217 VALUES LESS THAN (734124) ENGINE = InnoDB,
PARTITION day_20091218 VALUES LESS THAN (734125) ENGINE = InnoDB,
PARTITION day_20091219 VALUES LESS THAN (734126) ENGINE = InnoDB,
PARTITION max VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

INSERT INTO test (order_id,ts) VALUES (10,'2009-12-07 00:00:00'),(10,'2009-12-07 01:00:01'),(10,'2009-12-07 02:00:00'),(20,'2009-12-07 01:00:00'),(20,'2009-12-07
03:00:00'),(30,'2009-12-07 04:00:00');

/* pruning working left join */
explain partitions SELECT
 t.id,
 t.order_id,
 t.ts,
 t2.id,
 t2.order_id,
 t2.ts,
 t3.id,
 t3.order_id,
 t3.ts
 FROM
 test t
 LEFT JOIN
 test t2
 ON t.id = t2.id
 AND t2.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 LEFT JOIN
 test t3
 ON t2.id = t3.id
 AND t3.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 WHERE
 t.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 ORDER BY
 t.order_id,
 t.ts;

/* pruning not working right join */
explain partitions SELECT
 t.id,
 t.order_id,
 t.ts,
 t2.id,
 t2.order_id,
 t2.ts,
 t3.id,
 t3.order_id,
 t3.ts
 FROM
 test t2
 LEFT JOIN
 test t3
 ON t2.id = t3.id
 AND t3.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 RIGHT JOIN
 test t
 ON t.id = t2.id
 AND t2.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 WHERE
 t.ts between concat('2009-12-07', ' 00:00:00')
 and concat('2009-12-07', ' 23:59:59')
 ORDER BY
 t.order_id,
 t.ts;

Suggested fix:
na
[16 Jan 2010 4:14] Shannon Wade
Doesn't seem like explain bug as handler_read_key much higher for the non pruning example (34 vs 328)

mysql> \. b2.sql
Query OK, 0 rows affected (0.00 sec)

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
15 rows in set (0.00 sec)

+----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+
| id | order_id | ts                  | id   | order_id | ts                  | id   | order_id | ts                  |
+----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+
|  1 |       10 | 2009-12-07 00:00:00 |    1 |       10 | 2009-12-07 00:00:00 |    1 |       10 | 2009-12-07 00:00:00 |
|  2 |       10 | 2009-12-07 01:00:01 |    2 |       10 | 2009-12-07 01:00:01 |    2 |       10 | 2009-12-07 01:00:01 |
|  3 |       10 | 2009-12-07 02:00:00 |    3 |       10 | 2009-12-07 02:00:00 |    3 |       10 | 2009-12-07 02:00:00 |
|  4 |       20 | 2009-12-07 01:00:00 |    4 |       20 | 2009-12-07 01:00:00 |    4 |       20 | 2009-12-07 01:00:00 |
|  5 |       20 | 2009-12-07 03:00:00 |    5 |       20 | 2009-12-07 03:00:00 |    5 |       20 | 2009-12-07 03:00:00 |
|  6 |       30 | 2009-12-07 04:00:00 |    6 |       30 | 2009-12-07 04:00:00 |    6 |       30 | 2009-12-07 04:00:00 |
+----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+
6 rows in set (0.00 sec)

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 2     |
| Handler_read_key           | 34    |
| Handler_read_next          | 12    |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 8     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
15 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
15 rows in set (0.00 sec)

+----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+
| id | order_id | ts                  | id   | order_id | ts                  | id   | order_id | ts                  |
+----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+
|  1 |       10 | 2009-12-07 00:00:00 |    1 |       10 | 2009-12-07 00:00:00 |    1 |       10 | 2009-12-07 00:00:00 |
|  2 |       10 | 2009-12-07 01:00:01 |    2 |       10 | 2009-12-07 01:00:01 |    2 |       10 | 2009-12-07 01:00:01 |
|  3 |       10 | 2009-12-07 02:00:00 |    3 |       10 | 2009-12-07 02:00:00 |    3 |       10 | 2009-12-07 02:00:00 |
|  4 |       20 | 2009-12-07 01:00:00 |    4 |       20 | 2009-12-07 01:00:00 |    4 |       20 | 2009-12-07 01:00:00 |
|  5 |       20 | 2009-12-07 03:00:00 |    5 |       20 | 2009-12-07 03:00:00 |    5 |       20 | 2009-12-07 03:00:00 |
|  6 |       30 | 2009-12-07 04:00:00 |    6 |       30 | 2009-12-07 04:00:00 |    6 |       30 | 2009-12-07 04:00:00 |
+----+----------+---------------------+------+----------+---------------------+------+----------+---------------------+
6 rows in set (0.00 sec)

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 2     |
| Handler_read_key           | 328   |
| Handler_read_next          | 12    |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 8     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
15 rows in set (0.00 sec)
[16 Jan 2010 14:53] Valeriy Kravchuk
Same results with recent 5.1.43 from bzr.