[MDEV-17871] Crash when running explain with CTE Created: 2018-11-29  Updated: 2018-12-04  Resolved: 2018-12-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.3.10, 10.3.11, 10.2, 10.3
Fix Version/s: 10.2.20

Type: Bug Priority: Major
Reporter: Antonio Fernandes Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 7 x86_64


 Description    

181129 14:16:39 [ERROR] mysqld got signal 11 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed,
 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.3.10-MariaDB-log
 
key_buffer_size=1073741824
 
read_buffer_size=4194304
 
max_used_connections=96
 
max_threads=163
 
thread_count=54
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3055142 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7fa99c35b318
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7faa13158d30 thread_stack 0x49000
 
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x56394bd3b1de]
 
/usr/sbin/mysqld(handle_fatal_signal+0x357)[0x56394b7d56d7]
 
/lib64/libpthread.so.0(+0xf6d0)[0x7faeda9f76d0]
 
/usr/sbin/mysqld(_ZN18st_select_lex_unit7prepareEP10TABLE_LISTP13select_resultm+0xf92)[0x56394b69d982]
 
/usr/sbin/mysqld(_Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result+0x1bf)[0x56394b65a83f]
 
/usr/sbin/mysqld(+0x5ff90f)[0x56394b65a90f]
 
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x33)[0x56394b6599c3]
 
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x11a)[0x56394b659b1a]
 
/usr/sbin/mysqld(_Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result+0x11b)[0x56394b65a79b]
 
/usr/sbin/mysqld(+0x4b5a8e)[0x56394b510a8e]
 
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6d54)[0x56394b606b74]
 
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x22b)[0x56394b60932b]
 
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1d25)[0x56394b60be95]
 
/usr/sbin/mysqld(_Z10do_commandP3THD+0x13e)[0x56394b60ccfe]
 
/usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1aa)[0x56394b6dd1aa]
 
/usr/sbin/mysqld(handle_one_connection+0x3d)[0x56394b6dd2cd]
 
/lib64/libpthread.so.0(+0x7e25)[0x7faeda9efe25]
 
/lib64/libc.so.6(clone+0x6d)[0x7faed8d93bad]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fa99c0347e0): is an invalid pointer
 
Connection ID (thread ID): 552636
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
 
 
 
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
 
information that should help you find out what is causing the crash.
 
 
 
We think the query pointer is invalid, but we will try to print it anyway.
 
Query: EXPLAIN
 
            WITH RECURSIVE successors AS (
 
                    SELECT *
 
                    FROM MCPC_SI.tbl_stock_substituicoes
 
                    WHERE ReferenciaAntiga LIKE '7/%'
 
                    UNION DISTINCT
 
                    SELECT s1.*
 
                    FROM MCPC_SI.tbl_stock_substituicoes AS s1, successors AS a
 
                    WHERE s1.ReferenciaAntiga = a.ReferenciaNova
 
            )
 
            SELECT
 
                *,
 
                IFNULL(ValorPCompra, 0) - ValorFatura AS DiferencaValor,
 
                IFNULL(Quantidade, 0) - QuantidadeFatura AS DiferencaQuantidade
 
            FROM
 
            (
 
                SELECT
 
                    IF(CodigoEntrega = '29003', 'LISBOA', IF(IFNULL(CodigoEntrega,'') <> '', 'PORTO', CodigoEntrega)) AS Destino,
 
                    Guia,
 
                    CONCAT('7/', Referencia) AS ReferenciaCompleta,
 
                    SUM(Quantidade) AS QuantidadeFatura,
 
                    SUM(Valor) AS ValorFatura,
 
                    GROUP_CONCAT(DISTINCT Factura) AS Facturas,
 
                    GROUP_CONCAT(DISTINCT Data) AS Datas
 
                FROM
 
                    MCPC_Marcas.tbl_ford_facturas_ebless_detalhes ebless
 
                WHERE
 
                    ebless.Factura LIKE 'RF%'
 
                    AND ebless.`Data` BETWEEN '2018-08-01' AND LAST_DAY('2018-08-01')
 
                GROUP BY
 
                    Guia,
 
                    Referencia
 
            ) facturas
 
            LEFT JOIN
 
            (
 
                SELECT
 
                    cmp.Documento,
 
                    cmp.Referencia,
 
                    SUM(cmp.Qtd) AS Quantidade,
 
                    SUM(cmp.ValorPCompra) AS ValorPCompra,
 
                    GROUP_CONCAT(DISTINCT NumeroQuiterEntrada) AS NumeroQuiterEntrada,
 
                    GROUP_CONCAT(DISTINCT DataEntrada) AS DatasEntrada
 
                FROM
 
                    MCPC_SI.tbl_compras cmp
 
                WHERE
 
                    cmp.Fornecedor = '10560'
 
                    AND cmp.DataEntrada BETWEEN '2018-08-01' AND LAST_DAY(DATE_ADD(LAST_DAY('2018-08-01'), INTERVAL 1 DAY))
 
                GROUP BY
 
                    cmp.Documento,
 
                    cmp.Referencia
 
            ) entradas ON facturas.Guia = entradas.Documento AND facturas.ReferenciaCompleta = entradas.Referencia
 
 
 
WHERE facturas.Guia = 'N31695'



 Comments   
Comment by Antonio Fernandes [ 2018-11-29 ]

Can replicate on MariaDB 10.3.11.

Comment by Antonio Fernandes [ 2018-11-29 ]

Uploaded MDEV-17871.zip to ftp.askmonty.org/private the SQL and data so that you can replicate the crash.

Best regards,
António

Comment by Alice Sherepa [ 2018-11-29 ]

Thanks a lot for the report!
Reproducible on 10.2,10.3

 create table t1 ( i1 int, i2 int);
 
insert into t1 values (1,1),(2,2);
 
    
explain with recursive cte as
 
  ( select * from t1 union  select s1.* from t1 as s1, cte  where s1.i1 = cte.i2 ) select * from t1;

10.2 7826b9b983e8cc15be

 
#3  <signal handler called>
 
#4  0x00005650f23f86c4 in st_select_lex_unit::prepare (this=0x7f20c8012a38, thd_arg=0x7f20c8000b00, sel_result=0x7f20c8015b68, additional_options=268435460) at int/git/10.2/sql/sql_union.cc:698
 
#5  0x00005650f2390e2e in mysql_explain_union (thd=0x7f20c8000b00, unit=0x7f20c8012a38, result=0x7f20c8015b68) at int/git/10.2/sql/sql_select.cc:25145
 
#6  0x00005650f2390c95 in select_describe (join=0x7f20c80161e8, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at int/git/10.2/sql/sql_select.cc:25117
 
#7  0x00005650f235a33b in JOIN::exec_inner (this=0x7f20c80161e8) at int/git/10.2/sql/sql_select.cc:3593
 
#8  0x00005650f235994a in JOIN::exec (this=0x7f20c80161e8) at int/git/10.2/sql/sql_select.cc:3416
 
#9  0x00005650f235ab0c in mysql_select (thd=0x7f20c8000b00, tables=0x7f20c8015438, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7f20c8015b68, unit=0x7f20c80046e0, select_lex=0x7f20c8004e18) at int/git/10.2/sql/sql_select.cc:3816
 
#10 0x00005650f2390f3a in mysql_explain_union (thd=0x7f20c8000b00, unit=0x7f20c80046e0, result=0x7f20c8015b68) at int/git/10.2/sql/sql_select.cc:25162
 
#11 0x00005650f2319e95 in execute_sqlcom_select (thd=0x7f20c8000b00, all_tables=0x7f20c8015438) at int/git/10.2/sql/sql_parse.cc:6418
 
#12 0x00005650f230fed0 in mysql_execute_command (thd=0x7f20c8000b00) at int/git/10.2/sql/sql_parse.cc:3485
 
#13 0x00005650f231e0df in mysql_parse (thd=0x7f20c8000b00, rawbuf=0x7f20c8012448 "explain with recursive cte as\n( select * from t1 union  select s1.* from t1 as s1, cte  where s1.i1 = cte.i2 ) select * from t1", length=127, parser_state=0x7f21107f7080, is_com_multi=false, is_next_command=false) at int/git/10.2/sql/sql_parse.cc:8013
 
#14 0x00005650f230b730 in dispatch_command (command=COM_QUERY, thd=0x7f20c8000b00, packet=0x7f20c80960f1 "", packet_length=127, is_com_multi=false, is_next_command=false) at int/git/10.2/sql/sql_parse.cc:1824
 
#15 0x00005650f230a08c in do_command (thd=0x7f20c8000b00) at int/git/10.2/sql/sql_parse.cc:1378
 
#16 0x00005650f245c32e in do_handle_one_connection (connect=0x5650f5678db0) at int/git/10.2/sql/sql_connect.cc:1335
 
#17 0x00005650f245c0ae in handle_one_connection (arg=0x5650f5678db0) at int/git/10.2/sql/sql_connect.cc:1241
 
#18 0x00005650f2c8aed2 in pfs_spawn_thread (arg=0x5650f55bf510) at int/git/10.2/storage/perfschema/pfs.cc:1862
 
#19 0x00007f212164d6ba in start_thread (arg=0x7f21107f8700) at pthread_create.c:333
 
#20 0x00007f2120ae241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Comment by Igor Babaev [ 2018-12-04 ]

A fix for this bug was pushed into 10.2

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