5.7 range optimizer crash

[Miguel Angel Nieto]

Bug description:

Querying using PK(UK) crashes the server.

How to repeat:

## Table Schema
CREATE TABLE `tab` (
`col1` int(11) NOT NULL,
`col2` int(11) NOT NULL,
`col3` text COLLATE utf8mb4_bin,
PRIMARY KEY (`col1`,`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

## Generate Test Data
mysql> insert ignore into tab select rand()*100, rand()*100, uuid();
mysql> insert ignore into tab select rand()*100, rand()*100, uuid() from tab;
.. loop until 10201 rows ..

mysql> select count(*) from tab;
+----------+
| count(*) |
+----------+
| 10201 |
+----------+

mysql> select * from tab
-> where
-> (col1 = 2 and col2 = 1) or
-> (col1 in (3, 100) and col2 = 10) or
-> (col1 = 1 and col2 = 2) or
-> (col1 in (1, 2, 3) and col2 = 2);
ERROR 2013 (HY000): Lost connection to MySQL server during query

In this case I simply repositioned the bottom-most condition, and the server did not crash.
mysql> select * from tab
-> where
-> (col1 = 2 and col2 = 1) or
-> (col1 in (1, 2, 3) and col2 = 2) or
-> (col1 in (3, 100) and col2 = 10) or
-> (col1 = 1 and col2 = 2);

I did not get a crash when I gave the hint to not use the index.

How bug should be fixed:

Do not crash.

[Miguel Angel Nieto (miguelangelnieto)]

Seems to be a duplicated of upstream #84451, marked as private too.

[Miguel Angel Nieto (miguelangelnieto)]

The first version to crash is 5.7.3.

[Laurynas Biveinis (laurynas-biveinis)]

Reverting the following commit makes the crash go away.

commit 951d0e82aa562f05c4b7859a2906aee7bc2e8639
Author: Jorgen Loland <email address hidden>
Date: Thu Oct 10 13:55:38 2013 +0200

    Bug#17405466: USE_COUNT: WRONG COUNT FOR KEY AT 0X27547278,
                  3 SHOULD BE 4

    Consider an index (kp1, kp2, kp3), and a WHERE clause involving
    all three keyparts ('kpx_pred' represents a predicate usable by
    the range access method on keypart 'x'):

    "WHERE (kp2_pred OR kp2_pred) AND
            kp3_pred AND
           (kp1_pred OR kp1_pred)"

    The range optimizer first creates a SEL_ARG tree for kp2 with
    two SEL_ARGs, then another SEL_ARG tree for kp3. Both SEL_ARGs
    for kp2 will point to this kp3-SEL_ARG via the next_key_part
    pointer. The use_count of the root of the SEL_ARG tree for
    kp2 is now 1 (it is pointed to by SEL_TREE::keys[]), and
    the use_count of the root of the SEL_ARG tree for kp3 is 2
    (next_key_part pointers from both kp2 SEL_ARGs).

    Now the range optimizer creates a SEL_ARG tree with two
    SEL_ARGs for kp1. Both these need to point to the SEL_ARG tree
    for kp2 via the next_key_part pointer. This should increase the
    use_count of the kp2 SEL_ARG tree by 1 (+2 next_key_part
    pointers for the kp1 SEL_ARG tree, -1 since SEL_TREE::keys[]
    now points to the kp1 SEL_ARG tree and not directly to the
    kp2 SEL_ARG tree).

    This increase in use_count also needs to be propagated to the
    kp3 SEL_ARG tree, and this is where the bug was: instead of
    increasing the use count for each next_key_part pointing to
    the kp3 SEL_ARG tree, only the next_key_part of the root of
    kp2 SEL_ARG tree was increased. The fix is to propagate the
    increase to all SEL_ARG trees pointed to by all SEL_ARGs.

    Debugging this without printing the full SEL_ARG range trees
    would have been very hard. The patch therefore also adds a
    parameter to print_tree() so that a full printout is done when
    printing to the debug trace but not when printing to optimizer
    trace.

[Laurynas Biveinis (laurynas-biveinis)]

https://github.com/percona/percona-server/pull/1360

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1055