[MDEV-16726] Assertion `tab->type == JT_REF || tab->type == JT_EQ_REF' failed - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2(EOL), 10.3(EOL)
Fix Version/s: 5.5.61
Component/s: Optimizer
Labels:
None

Description

Reproducible with MyIsam, not with Innodb

CREATE TABLE t1 ( pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1),KEY v1 (v1,i1)) engine=MyIsam;

INSERT INTO t1 VALUES (8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'),(14,226,'m','m'),(15,133,'p','p');

CREATE TABLE t2 ( pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1)) engine=MyIsam;

INSERT INTO t2 VALUES (10,6,'p','p');

SELECT DISTINCT

STRAIGHT_JOIN table2.i1

FROM ((SELECT t1.* FROM (t1 LEFT JOIN t1 AS tb1 ON (tb1.v1 = t1.v2))) AS table1

      RIGHT JOIN

      (SELECT t2.* FROM (t2 JOIN t1 ON (t1.pk = t2.pk))) AS table2

ON (table2.v2 = table1.v1))

WHERE table1.pk = 40

ORDER BY table1.i1;

Thread 1 (Thread 0x7f665c228700 (LWP 31959)):

#0  __pthread_kill (threadid=<optimized out>, signo=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62

#1  0x000055f02a33d393 in my_write_core (sig=6) at /home/alice/git/10.3/mysys/stacktrace.c:481

#2  0x000055f029b80cdd in handle_fatal_signal (sig=6) at /home/alice/git/10.3/sql/signal_handler.cc:305

#3  <signal handler called>

#4  0x00007f6661889428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54

#5  0x00007f666188b02a in __GI_abort () at abort.c:89

#6  0x00007f6661881bd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x55f02a4179a0 "tab->type == JT_REF || tab->type == JT_EQ_REF", file=file@entry=0x55f02a4158f8 "/home/alice/git/10.3/sql/sql_select.cc", line=line@entry=22394, function=function@entry=0x55f02a41a1c0 <create_sort_index(THD*, JOIN*, st_join_table*, Filesort*)::__PRETTY_FUNCTION__> "int create_sort_index(THD*, JOIN*, JOIN_TAB*, Filesort*)") at assert.c:92

#7  0x00007f6661881c82 in __GI___assert_fail (assertion=0x55f02a4179a0 "tab->type == JT_REF || tab->type == JT_EQ_REF", file=0x55f02a4158f8 "/home/alice/git/10.3/sql/sql_select.cc", line=22394, function=0x55f02a41a1c0 <create_sort_index(THD*, JOIN*, st_join_table*, Filesort*)::__PRETTY_FUNCTION__> "int create_sort_index(THD*, JOIN*, JOIN_TAB*, Filesort*)") at assert.c:101

#8  0x000055f02991029d in create_sort_index (thd=0x7f6640000b00, join=0x7f664003e088, tab=0x7f664004f598, fsort=0x7f6640051ef0) at /home/alice/git/10.3/sql/sql_select.cc:22394

#9  0x000055f02990ab2a in st_join_table::sort_table (this=0x7f664004f598) at /home/alice/git/10.3/sql/sql_select.cc:20242

#10 0x000055f02990a73d in join_init_read_record (tab=0x7f664004f598) at /home/alice/git/10.3/sql/sql_select.cc:20183

#11 0x000055f0299085c7 in sub_select (join=0x7f664003e088, join_tab=0x7f664004f598, end_of_records=false) at /home/alice/git/10.3/sql/sql_select.cc:19268

#12 0x000055f029907b65 in do_select (join=0x7f664003e088, procedure=0x0) at /home/alice/git/10.3/sql/sql_select.cc:18811

#13 0x000055f0298e0f99 in JOIN::exec_inner (this=0x7f664003e088) at /home/alice/git/10.3/sql/sql_select.cc:4021

#14 0x000055f0298e040c in JOIN::exec (this=0x7f664003e088) at /home/alice/git/10.3/sql/sql_select.cc:3815

#15 0x000055f0298e1673 in mysql_select (thd=0x7f6640000b00, tables=0x7f6640018360, wild_num=0, fields=..., conds=0x7f664003dc58, og_num=1, order=0x7f664003dee0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748611, result=0x7f664003e068, unit=0x7f66400049a8, select_lex=0x7f6640005118) at /home/alice/git/10.3/sql/sql_select.cc:4220

#16 0x000055f0298d3796 in handle_select (thd=0x7f6640000b00, lex=0x7f66400048e0, result=0x7f664003e068, setup_tables_done_option=0) at /home/alice/git/10.3/sql/sql_select.cc:382

#17 0x000055f02989e118 in execute_sqlcom_select (thd=0x7f6640000b00, all_tables=0x7f6640018360) at /home/alice/git/10.3/sql/sql_parse.cc:6542

#18 0x000055f029894571 in mysql_execute_command (thd=0x7f6640000b00) at /home/alice/git/10.3/sql/sql_parse.cc:3765

#19 0x000055f0298a1ff8 in mysql_parse (thd=0x7f6640000b00, rawbuf=0x7f6640014d08 "SELECT DISTINCT\nSTRAIGHT_JOIN table2.i1\nFROM ((SELECT t1.* FROM (t1 LEFT JOIN t1 AS tb1 ON (tb1.v1 = t1.v2))) AS table1\nRIGHT JOIN\n(SELECT t2.* FROM (t2 JOIN t1 ON (t1.pk = t2.pk))) AS table2 \nON (tab"..., length=260, parser_state=0x7f665c2275f0, is_com_multi=false, is_next_command=false) at /home/alice/git/10.3/sql/sql_parse.cc:8073

#20 0x000055f02988f046 in dispatch_command (command=COM_QUERY, thd=0x7f6640000b00, packet=0x7f664000b211 "", packet_length=260, is_com_multi=false, is_next_command=false) at /home/alice/git/10.3/sql/sql_parse.cc:1847

#21 0x000055f02988da63 in do_command (thd=0x7f6640000b00) at /home/alice/git/10.3/sql/sql_parse.cc:1392

#22 0x000055f0299f3987 in do_handle_one_connection (connect=0x55f02d432370) at /home/alice/git/10.3/sql/sql_connect.cc:1402

#23 0x000055f0299f36d8 in handle_one_connection (arg=0x55f02d432370) at /home/alice/git/10.3/sql/sql_connect.cc:1308

#24 0x000055f02a2cd8a8 in pfs_spawn_thread (arg=0x55f02d445d10) at /home/alice/git/10.3/storage/perfschema/pfs.cc:1862

#25 0x00007f66624c66ba in start_thread (arg=0x7f665c228700) at pthread_create.c:333

#26 0x00007f666195b41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Attachments

Activity

Ascending order - Click to sort in descending order

Igor Babaev (Inactive) added a comment - 2018-07-18 03:34 - edited

The reported test case can be simplified up to the query

SELECT STRAIGHT_JOIN DISTINCT t2.v2

FROM

  (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)

  RIGHT JOIN

  (t2,t1)

  ON t1.pk = t2.pk AND t2.v2 = tb1.v1

WHERE tb1.pk = 40

ORDER BY tb1.i1;

that fails in the same way as the original query.
This query has a very strange execution plan

MariaDB [test]> EXPLAIN EXTENDED

    -> SELECT STRAIGHT_JOIN DISTINCT t2.v2

    -> FROM

    ->   (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)

    ->   RIGHT JOIN

    ->   (t2,t1)

    ->   ON t1.pk = t2.pk AND t2.v2 = tb1.v1

    -> WHERE tb1.pk = 40

    -> ORDER BY tb1.i1;

+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-----------------------------+

| id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                       |

+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-----------------------------+

|    1 | SIMPLE      | t2    | system | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using temporary             |

|    1 | SIMPLE      | t1    | const  | PRIMARY       | PRIMARY | 4       | const       |    1 |   100.00 | Using index                 |

|    1 | SIMPLE      | tb1   | const  | PRIMARY,v1    | PRIMARY | 4       | const       |    1 |   100.00 | Using where; Using filesort |

|    1 | SIMPLE      | tb2   | ref    | v1            | v1      | 4       | test.tb1.v2 |    1 |   100.00 | Using where; Using index    |

+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-----------------------------+

MariaDB [test]> SHOW WARNINGS;

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message                                                                                                                                                                                                                                                                       |

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1003 | select straight_join distinct 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(`test`.`tb2`.`v1` = `test`.`tb1`.`v2` and `test`.`tb1`.`v2` is not null) where `test`.`tb1`.`pk` = 40 and `test`.`tb1`.`v1` = 'p' order by `test`.`tb1`.`i1` |

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Notice that we sort here two last tables though the first one is a constant table and RIGHT JOIN is converted to INNER JOIN.
If we remove DISTINCT then the new query (RJ) does not fail and it has the following execution plan:

MariaDB [test]> EXPLAIN EXTENDED

    -> SELECT STRAIGHT_JOIN t2.v2

    -> FROM

    ->     (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)

    ->    RIGHT JOIN (t2,t1)

    ->    ON t1.pk = t2.pk AND t2.v2 = tb1.v1

    -> WHERE tb1.pk = 40

    -> ORDER BY tb1.i1;

+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+

| id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                    |

+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+

|    1 | SIMPLE      | t2    | system | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 |                          |

|    1 | SIMPLE      | t1    | const  | PRIMARY       | PRIMARY | 4       | const       |    1 |   100.00 | Using index              |

|    1 | SIMPLE      | tb1   | const  | PRIMARY,v1    | PRIMARY | 4       | const       |    1 |   100.00 | Using where              |

|    1 | SIMPLE      | tb2   | ref    | v1            | v1      | 4       | test.tb1.v2 |    1 |   100.00 | Using where; Using index |

+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+

It's interesting that the equivalent query (LJ):

SELECT STRAIGHT_JOIN DISTINCT t2.v2

FROM

    (t2,t1)

    LEFT JOIN

    (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)

   ON t1.pk = t2.pk AND t2.v2 = tb1.v1

WHERE tb1.pk = 40

ORDER BY tb1.i1;

has quite a different execution plan:

MariaDB [test]> EXPLAIN EXTENDED

    -> SELECT STRAIGHT_JOIN DISTINCT t2.v2

    -> FROM

    ->     (t2,t1)

    ->     LEFT JOIN

    ->     (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)

    ->    ON t1.pk = t2.pk AND t2.v2 = tb1.v1

    -> WHERE tb1.pk = 40

    -> ORDER BY tb1.i1;

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+

|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+

This is already not good.

Igor Babaev (Inactive) added a comment - 2018-07-18 03:34 - edited The reported test case can be simplified up to the query SELECT STRAIGHT_JOIN DISTINCT t2.v2 FROM (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) RIGHT JOIN (t2,t1) ON t1.pk = t2.pk AND t2.v2 = tb1.v1 WHERE tb1.pk = 40 ORDER BY tb1.i1; that fails in the same way as the original query. This query has a very strange execution plan MariaDB [test]> EXPLAIN EXTENDED -> SELECT STRAIGHT_JOIN DISTINCT t2.v2 -> FROM -> (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) -> RIGHT JOIN -> (t2,t1) -> ON t1.pk = t2.pk AND t2.v2 = tb1.v1 -> WHERE tb1.pk = 40 -> ORDER BY tb1.i1; +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-----------------------------+ | 1 | SIMPLE | t2 | system | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using temporary | | 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 1 | SIMPLE | tb1 | const | PRIMARY,v1 | PRIMARY | 4 | const | 1 | 100.00 | Using where; Using filesort | | 1 | SIMPLE | tb2 | ref | v1 | v1 | 4 | test.tb1.v2 | 1 | 100.00 | Using where; Using index | +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-----------------------------+ MariaDB [test]> SHOW WARNINGS; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select straight_join distinct 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(`test`.`tb2`.`v1` = `test`.`tb1`.`v2` and `test`.`tb1`.`v2` is not null) where `test`.`tb1`.`pk` = 40 and `test`.`tb1`.`v1` = 'p' order by `test`.`tb1`.`i1` | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Notice that we sort here two last tables though the first one is a constant table and RIGHT JOIN is converted to INNER JOIN. If we remove DISTINCT then the new query (RJ) does not fail and it has the following execution plan: MariaDB [test]> EXPLAIN EXTENDED -> SELECT STRAIGHT_JOIN t2.v2 -> FROM -> (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) -> RIGHT JOIN (t2,t1) -> ON t1.pk = t2.pk AND t2.v2 = tb1.v1 -> WHERE tb1.pk = 40 -> ORDER BY tb1.i1; +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+ | 1 | SIMPLE | t2 | system | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | | | 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 1 | SIMPLE | tb1 | const | PRIMARY,v1 | PRIMARY | 4 | const | 1 | 100.00 | Using where | | 1 | SIMPLE | tb2 | ref | v1 | v1 | 4 | test.tb1.v2 | 1 | 100.00 | Using where; Using index | +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+ It's interesting that the equivalent query (LJ): SELECT STRAIGHT_JOIN DISTINCT t2.v2 FROM (t2,t1) LEFT JOIN (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) ON t1.pk = t2.pk AND t2.v2 = tb1.v1 WHERE tb1.pk = 40 ORDER BY tb1.i1; has quite a different execution plan: MariaDB [test]> EXPLAIN EXTENDED -> SELECT STRAIGHT_JOIN DISTINCT t2.v2 -> FROM -> (t2,t1) -> LEFT JOIN -> (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) -> ON t1.pk = t2.pk AND t2.v2 = tb1.v1 -> WHERE tb1.pk = 40 -> ORDER BY tb1.i1; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ This is already not good.

Igor Babaev (Inactive) added a comment - 2018-07-18 06:08 - edited

Although we do not have an ASSERT failure for the reported query in 5.5 the problem with different execution plans for the queries (RJ) and (LJ) still exists there. So the problem will be fixed in 5.5 first.

Igor Babaev (Inactive) added a comment - 2018-07-18 06:08 - edited Although we do not have an ASSERT failure for the reported query in 5.5 the problem with different execution plans for the queries (RJ) and (LJ) still exists there. So the problem will be fixed in 5.5 first.

Igor Babaev (Inactive) added a comment - 2018-07-20 04:25

A fix for this bug was pushed into 5.5.
It should be applied upstream as it is.

Igor Babaev (Inactive) added a comment - 2018-07-20 04:25 A fix for this bug was pushed into 5.5. It should be applied upstream as it is.

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Alice Sherepa

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-07-11 07:57

Updated:: 2018-07-20 04:25

Resolved:: 2018-07-20 04:25

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server