[MDEV-9449] Optimizer regression in 10.1.9 Created: 2016-01-22  Updated: 2016-02-03  Resolved: 2016-02-01

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.9, 10.1
Fix Version/s: 10.1.11

Type: Bug Priority: Major
Reporter: Will Fong Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None


 Description   

After the upgrade from 10.1.8 to 10.1.9 or 10.1.10, frequent queries involving index merge went crazy.

Here is a repeatable test case :

DROP TABLE if exists t;
CREATE TABLE t
(
id bigint PRIMARY KEY AUTO_INCREMENT,
a bigint,
b int,
c bigint,
KEY(a), KEY(b), KEY(c)
) ENGINE = InnoDB;
 
INSERT INTO t(a, b, c)
VALUES (rand() * 44, rand() * 283, rand() * 131072);
 
INSERT INTO t(a, b, c)
VALUES (rand() * 44, rand() * 283, rand() * 131072);
 
REPLACE INTO t(a, b, c)
SELECT rand() * 44, rand() * 283, rand() * 131072
FROM t t1,
t t2,
t t3,
t t4,
t t5,
t t6,
t t7,
t t8,
t t9,
t t10,
t t11,
t t12,
t t13,
t t14,
t t15,
t t16,
t t17;
ANALYZE TABLE t;
 
EXPLAIN
SELECT DISTINCT c
FROM t
WHERE b = 5 AND a = 10\G

10.1.8 :

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index_merge
possible_keys: a,b
key: b,a
key_len: 5,9
ref: NULL
rows: 10
Extra: Using intersect(b,a); Using where; Using temporary
1 row in set (0.00 sec)

10.1.9 :

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: a,b
key: c
key_len: 9
ref: NULL
rows: 131109
Extra: Using where
1 row in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2016-01-25 ]

The problem appeared in 10.1 tree with the following revision:

commit 7ec655850397a0edfcea8c1fd82650824297e564
Author: Nirbhay Choubey <nirbhay@mariadb.com>
Date:   Fri Nov 6 14:38:03 2015 -0500
 
    MDEV-9021: MYSQLD SEGFAULTS WHEN BUILT USING --WITH-MAX-INDEXES=128
    
    The bitmap implementation defines two template Bitmap classes. One
    optimized for 64-bit (default) wide bitmaps while the other is used for
    all other widths.
    
    In order to optimize the computations, Bitmap<64> class has defined its
    own member functions for bitmap operations, the other one, however,
    relies on mysys' bitmap implementation (mysys/my_bitmap.c).
    
    Issue 1:
    In case of non 64-bit Bitmap class, intersect() wrongly reset the
    received bitmap while initialising a new local bitmap structure
    (bitmap_init() clears the bitmap buffer) thus, the received bitmap was
    getting cleared.
    
    Fixed by initializing the local bitmap structure by using a temporary
    buffer and later copying the received bitmap to the initialised bitmap
    structure.
    
    Issue 2:
    The non 64-bit Bitmap class had the Iterator missing which caused
    compilation failure.
    
    Also added a cmake variable to hold the MAX_INDEXES value when supplied
    from the command prompt. (eg. cmake .. -DMAX_INDEXES=128U). Checks have
    been put in place to trigger build failure if MAX_INDEXES value is
    greater than 128.
    
    Test modifications:
    * Introduced include/have_max_indexes_[64|128].inc to facilitate
    skipping of tests for which the output differs with different
    MAX_INDEXES.
    
    * Introduced include/max_indexes.inc which would get modified by cmake
    to reflect the MAX_INDEXES value used to build the server. This file
    simply sets an mtr variable '$max_indexes' to show the MAX_INDEXES
    value, which will then be consumed by the above introduced include file.
    
    * Some tests (portions), dependent on MAX_INDEXES value, have been moved
    to separate test files.

Comment by Sergei Petrunia [ 2016-02-01 ]

Need to check whether this is the same issue as in MDEV-9457.

Comment by Sergei Petrunia [ 2016-02-01 ]

Indeed, no longer repeatable. Closing as duplicate of MDEV-9457

Comment by Elena Stepanova [ 2016-02-01 ]

Closing as fixed in scope of MDEV-9457. An older bug cannot be a duplicate of a newer one.

Comment by Sergei Petrunia [ 2016-02-03 ]

Just verified again: the behaviour changes back from index to index_merge when one applies patchj ff8d4009a7743b99c1c13831172b08041a4b272e, that is the fix for MDEV-9457 .

Generated at Thu Feb 08 07:34:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.