Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
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) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
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)
|
|
|
|
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
|
http://lists.askmonty.org/pipermail/commits/2018-October/013030.html