[MDEV-17518] Range optimization doesn't use ON expressions from nested outer joins Created: 2018-10-22  Updated: 2018-11-08  Resolved: 2018-11-06

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: 10.3.11

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

Issue Links:
Relates
relates to MDEV-17493 Partition pruning doesn't work for ne... Closed

 Description   

This is the range access part of MDEV-17493.

create table t2(a int);
 
insert into t2 values (0),(1);
 
create table t1 (a int, b int, key(a));
 
insert into t1 select A.a + B.a* 10 + C.a * 100, 12345 from ten A, ten B, ten C;

Outer join with single-table on the inner side is able to use the ON expression to construct range access:

mysql> explain select * from ten left join t1 on ten.a=t1.b and t1.a<5;
 
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                           |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
 
|    1 | SIMPLE      | ten   | ALL   | NULL          | NULL | NULL    | NULL |   10 |                                                 |
 
|    1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL |    5 | Using where; Using join buffer (flat, BNL join) |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+

add another table there and range access is not used anymore:

mysql> explain select * from ten left join (t1 join t2) on ten.a=t1.b and t1.a<5;
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                  |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
 
|    1 | SIMPLE      | ten   | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                                        |
 
|    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using join buffer (flat, BNL join)                     |
 
|    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL | 1000 | Using where; Using join buffer (incremental, BNL join) |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+



 Comments   
Comment by Sergei Petrunia [ 2018-10-22 ]

http://lists.askmonty.org/pipermail/commits/2018-October/013030.html

Comment by Sergei Petrunia [ 2018-10-23 ]

http://lists.askmonty.org/pipermail/commits/2018-October/013034.html

Comment by Sergei Petrunia [ 2018-10-23 ]

Buildbot tree: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.3-mdev17518

Comment by Alice Sherepa [ 2018-10-31 ] 

CREATE TABLE A (i1 int) ;
 
CREATE TABLE C (pk int NOT NULL PRIMARY KEY) ;
 
CREATE TABLE H (pk int NOT NULL, i1 int, PRIMARY KEY (pk)) ;
 
INSERT INTO H VALUES (2, NULL);
 
 
 
CREATE TABLE N (pk int NOT NULL, i1 int, PRIMARY KEY (pk), KEY i1 (i1)) ;
 
CREATE VIEW view_N AS SELECT * FROM N;
 
 
 
SELECT 1
 
FROM H RIGHT JOIN A ON H.i1 = A.i1
 
  LEFT JOIN view_N
 
  RIGHT JOIN C ON view_N.i1 = C.pk ON A.i1 = C.pk
 
WHERE H.pk IN (2);


ASAN:SIGSEGV
 
=================================================================
 
==14603==ERROR: AddressSanitizer: SEGV on unknown address 0x62420006b1e0 (pc 0x55f18066b53b bp 0x7f8ce5bfd3d0 sp 0x7f8ce5bfd390 T5)
 
    #0 0x55f18066b53a in join_tab_cmp /git/10.3p/sql/sql_select.cc:7682
 
    #1 0x55f181da6a53 in my_qsort2 /git/10.3p/mysys/mf_qsort.c:144
 
    #2 0x55f18066ad34 in choose_plan(JOIN*, unsigned long long) /git/10.3p/sql/sql_select.cc:7558
 
    #3 0x55f1806598f4 in make_join_statistics /git/10.3p/sql/sql_select.cc:5070
 
    #4 0x55f18063bea0 in JOIN::optimize_inner() /git/10.3p/sql/sql_select.cc:1888
 
    #5 0x55f180637b27 in JOIN::optimize() /git/10.3p/sql/sql_select.cc:1451
 
    #6 0x55f180651e70 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /git/10.3p/sql/sql_select.cc:4213
 
    #7 0x55f18062d1c0 in handle_select(THD*, LEX*, select_result*, unsigned long) /git/10.3p/sql/sql_select.cc:385
 
    #8 0x55f1805b1367 in execute_sqlcom_select /git/10.3p/sql/sql_parse.cc:6547
 
    #9 0x55f18059f9cd in mysql_execute_command(THD*) /git/10.3p/sql/sql_parse.cc:3769
 
    #10 0x55f1805b9d8c in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /git/10.3p/sql/sql_parse.cc:8091
 
    #11 0x55f18059472d in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /git/10.3p/sql/sql_parse.cc:1850
 
    #12 0x55f1805918c5 in do_command(THD*) /git/10.3p/sql/sql_parse.cc:1395
 
    #13 0x55f1808f008c in do_handle_one_connection(CONNECT*) /git/10.3p/sql/sql_connect.cc:1402
 
    #14 0x55f1808efa69 in handle_one_connection /git/10.3p/sql/sql_connect.cc:1308
 
    #15 0x55f181cac7f0 in pfs_spawn_thread /git/10.3p/storage/perfschema/pfs.cc:1862
 
    #16 0x7f8cf13936b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
 
    #17 0x7f8cf082841c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)
 
 
 
AddressSanitizer can not provide additional info.
 
SUMMARY: AddressSanitizer: SEGV /git/10.3p/sql/sql_select.cc:7682 join_tab_cmp
 
Thread T5 created by T0 here:
 
    #0 0x7f8cf2735253 in pthread_create (/usr/lib/x86_64-linux-gnu/libasan.so.2+0x36253)
 
    #1 0x55f181cacbdd in spawn_thread_v1 /git/10.3p/storage/perfschema/pfs.cc:1912
 
    #2 0x55f18030ae54 in inline_mysql_thread_create /git/10.3p/include/mysql/psi/mysql_thread.h:1268
 
    #3 0x55f1803204e4 in create_thread_to_handle_connection(CONNECT*) /git/10.3p/sql/mysqld.cc:6560
 
    #4 0x55f180320be4 in create_new_thread /git/10.3p/sql/mysqld.cc:6630
 
    #5 0x55f180321c27 in handle_connections_sockets() /git/10.3p/sql/mysqld.cc:6905
 
    #6 0x55f18031f9a4 in mysqld_main(int, char**) /git/10.3p/sql/mysqld.cc:6182
 
    #7 0x55f18030913f in main /git/10.3p/sql/main.cc:25
 
    #8 0x7f8cf074182f in __libc_start_main (/lib/x86_64-linux-gnu/libc.so.6+0x2082f)
 
 

Comment by Sergei Petrunia [ 2018-10-31 ]

Confirm, the above example crashes for me, too.

If I run the above query on vanilla 10.3, I get some bizarre output in SHOW WARNINGS:

+------+-------------+-------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
 
+------+-------------+-------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
 
|    1 | SIMPLE      | H     | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 |                          |
 
|    1 | SIMPLE      | A     | ALL    | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | Using where              |
 
|    1 | SIMPLE      | C     | eq_ref | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where; Using index |
 
|    1 | SIMPLE      | N     | ref    | i1            | i1      | 5       | const |    1 |   100.00 | Using where; Using index |
 
+------+-------------+-------+--------+---------------+---------+---------+-------+------+----------+--------------------------+

SHOW WARNINGS;

Message: 
select 1 AS 1 
from 
  j8.A 
  join j8.H 
  left join (j8.C left join 
             (j8.N) on (j8.A.i1 = NULL and 
                        j8.C.pk = NULL and 
                        j8.N.i1 = NULL and 
                        NULL is not null)
 
            ) on (j8.A.i1 = NULL and 
                  j8.C.pk = NULL and 
                  NULL is not null) 
where j8.A.i1 = NULL

Comment by Sergei Petrunia [ 2018-11-01 ]

After looking at it carefully, I see that the output is correct.

Comment by Sergei Petrunia [ 2018-11-01 ]

Fix for the above is pushed into bb-10.3-mdev17518 branch.

Generated at Thu Feb 08 08:37:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.