[MDEV-17024] Crash on large query Created: 2018-08-20  Updated: 2020-11-13  Resolved: 2018-09-13

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.3.8, 10.3.9, 10.2, 10.3
Fix Version/s: 10.2.18

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

centos 7.4 mariadb 10.3.8 using mariadb.org binaries

Attachments: File crashy.sql     File schema.sql    
Issue Links:
Relates
relates to MDEV-22748 MariaDB crash on WITH RECURSIVE large... Closed
relates to MDEV-18906 lost connection to mysql server durin... Closed

 Description    

Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: pure virtual method called
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: terminate called without an active exception
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: 180820 17:08:32 [ERROR] mysqld got signal 6 ;
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: This could be because you hit a bug. It is also possible that this binary
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: or one of the libraries it was linked against is corrupt, improperly built,
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: or misconfigured. This error can also be caused by malfunctioning hardware.
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: We will try our best to scrape up some info that will hopefully help
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: diagnose the problem, but since we have already crashed,
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: something is definitely wrong and this may fail.
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: Server version: 10.3.8-MariaDB
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: key_buffer_size=134217728
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: read_buffer_size=131072
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: max_used_connections=2
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: max_threads=153
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: thread_count=11
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: It is possible that mysqld could use up to
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467396 K  bytes of memory
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: Hope that's ok; if not, decrease some variables in the equation.
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: Thread pointer: 0x7f1e340009d8
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: Attempting backtrace. You can use the following information to find out
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: where mysqld died. If you see no messages after this, something went
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: terribly wrong...
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: stack_bottom = 0x7f1ee805ed00 thread_stack 0x49000
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: *** buffer overflow detected ***: /usr/sbin/mysqld terminated
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: ======= Backtrace: =========
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /lib64/libc.so.6(__fortify_fail+0x37)[0x7f2b8fc0e6e7]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /lib64/libc.so.6(+0x116862)[0x7f2b8fc0c862]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /lib64/libc.so.6(+0x118647)[0x7f2b8fc0e647]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(my_addr_resolve+0xda)[0x5638834429da]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(my_print_stacktrace+0x1c2)[0x56388342c062]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(handle_fatal_signal+0x357)[0x563882ec8577]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /lib64/libpthread.so.0(+0xf6d0)[0x7f2b918586d0]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /lib64/libc.so.6(gsignal+0x37)[0x7f2b8fb2c277]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /lib64/libc.so.6(abort+0x148)[0x7f2b8fb2d968]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /lib64/libstdc++.so.6(_ZN9__gnu_cxx27__verbose_terminate_handlerEv+0x165)[0x7f2b902257d5]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /lib64/libstdc++.so.6(+0x5e746)[0x7f2b90223746]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /lib64/libstdc++.so.6(+0x5e773)[0x7f2b90223773]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /lib64/libstdc++.so.6(+0x5f2df)[0x7f2b902242df]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN7handler22ha_rnd_init_with_errorEb+0x17)[0x563882eceb17]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN5TABLE30insert_all_rows_into_tmp_tableEP3THDPS_P15TMP_TABLE_PARAMb+0x8a)[0x563882daa0aa]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_Z18mysql_derived_fillP3THDP3LEXP10TABLE_LIST+0x34b)[0x563882cc069b]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xe4)[0x563882cc0164]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN13st_join_table12preread_initEv+0x79)[0x563882d2afe9]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x2f8)[0x563882d2b3a8]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xa42)[0x563882d4c912]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN4JOIN4execEv+0x33)[0x563882d4cb63]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x11a)[0x563882d4ccba]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_Z18mysql_derived_fillP3THDP3LEXP10TABLE_LIST+0x162)[0x563882cc04b2]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xe4)[0x563882cc0164]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN13st_join_table12preread_initEv+0x79)[0x563882d2afe9]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_Z21join_init_read_recordP13st_join_table+0x118)[0x563882d3b758]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN10JOIN_CACHE21join_matching_recordsEb+0x96)[0x563882df73c6]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN10JOIN_CACHE12join_recordsEb+0xf4)[0x563882df6ed4]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_Z16sub_select_cacheP4JOINP13st_join_tableb+0x2e)[0x563882d2b5fe]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0x8fb)[0x563882d4c7cb]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN4JOIN4execEv+0x33)[0x563882d4cb63]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x11a)[0x563882d4ccba]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1cc)[0x563882d4d7cc]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(+0x4b96ef)[0x563882c046ef]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6f0a)[0x563882cf9a8a]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x36)[0x563882c62c16]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x99)[0x563882c6a6c9]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x63b)[0x563882c6b0eb]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x860)[0x563882c665a0]
 
Aug 20 17:08:32 ip-10-0-192-142.ec2.internal mysqld[22827]: /usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x74d)[0x563882c677bd]



 Comments   
Comment by Aria Stewart [ 2018-08-22 ]

This seems to be related to WITH RECURSIVE queries, with a lot of derived tables in the query plan.

I was using simple WITH RECURSIVE CTEs to generate a list of a few months, inductively from the first month in a period, then joining that virtual table with a bunch of subqueries at various levels to build a report grouped by those periods.

Moving those into temporary tables – using WITH RECURSIVE for those still – and using a simple WITH for the main, large, complex query fixed it.

Comment by Alice Sherepa [ 2018-08-23 ]

Thanks for the report! Are you able to repeat the crash? It would be perfect if you could find out the query, that is causing the crash.

Comment by Aria Stewart [ 2018-08-23 ]

crashy.sql This is it, though I've obscured our actual analyses slightly.

Comment by Alice Sherepa [ 2018-08-27 ]

Could you please add structures of the involved tables

SHOW CREATE TABLE erp.sales_flat_order ;
 
SHOW CREATE TABLE erp.sales_flat_invoice ;
 
SHOW CREATE TABLE erp.sales_payment_transaction;
 
SHOW CREATE TABLE erp.frauddetection_order_assessment;
 
SHOW CREATE TABLE erp.sales_flat_invoice_item ;
 
SHOW CREATE TABLE erp.catalog_product_flat_3 ;
 
SHOW CREATE TABLE monthly_wholesale_rev_historic;
 
SHOW CREATE TABLE 2018_daily_analysis1;

Comment by Aria Stewart [ 2018-08-27 ]

Related schema attached!

schema.sql

Comment by Aria Stewart [ 2018-08-27 ]

Cardinality: sales_flat_order, 1.4mil; sales_flat_invoice, 1.2mil; sales_payment_transaction, 1.2 mil; frauddetection_order_assessment, 900k; sales_flat_invoice_item: 12mil; catalog_product_flat_3: 70k; monthly_wholesale_rev_historic: 36; 2018_daily_analysis1 1200.

Comment by Alice Sherepa [ 2018-08-29 ]

Thanks a lot, I can reproduce it on 10.2, 10.3

 on debug version 10.2 2b76f6f61dba93fd920
 
Thread 1 (Thread 0x7f6204095700 (LWP 28494)):
 
 
 
#3  <signal handler called>
 
#4  0x0000559280d7883f in base_list_iterator::next_fast (this=0x7f6204092300) at /git/10.2/sql/sql_list.h:468
 
#5  0x0000559280f3937b in List_iterator_fast<TABLE>::operator++ (this=0x7f6204092300) at /git/10.2/sql/sql_list.h:591
 
#6  0x000055928101f01a in With_element::instantiate_tmp_tables (this=0x7f61b01feba8) at /git/10.2/sql/sql_cte.cc:1405
 
#7  0x0000559280e1f187 in TABLE_LIST::fill_recursive (this=0x7f61b0220230, thd=0x7f61b0000b00) at /git/10.2/sql/sql_derived.cc:1023
 
#8  0x0000559280e1f43e in mysql_derived_fill (thd=0x7f61b0000b00, lex=0x7f61b0004628, derived=0x7f61b0220230) at /git/10.2/sql/sql_derived.cc:1097
 
#9  0x0000559280e1d490 in mysql_handle_single_derived (lex=0x7f61b0004628, derived=0x7f61b0220230, phases=96) at /git/10.2/sql/sql_derived.cc:197
 
#10 0x0000559280eadeb1 in st_join_table::preread_init (this=0x7f61b049f840) at /git/10.2/sql/sql_select.cc:11992
 
#11 0x0000559280ebdfe9 in sub_select (join=0x7f61b0364ac8, join_tab=0x7f61b049f840, end_of_records=false) at /git/10.2/sql/sql_select.cc:18710
 
#12 0x0000559280ebd722 in do_select (join=0x7f61b0364ac8, procedure=0x0) at /git/10.2/sql/sql_select.cc:18286
 
#13 0x0000559280e981b7 in JOIN::exec_inner (this=0x7f61b0364ac8) at /git/10.2/sql/sql_select.cc:3609
 
#14 0x0000559280e97666 in JOIN::exec (this=0x7f61b0364ac8) at /git/10.2/sql/sql_select.cc:3404
 
#15 0x0000559280e98828 in mysql_select (thd=0x7f61b0000b00, tables=0x7f61b0220230, wild_num=0, fields=..., conds=0x7f61b0397050, og_num=1, order=0x0, group=0x7f61b02216f8, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7f61b0352ad8, unit=0x7f61b021ea20, select_lex=0x7f61b021e630) at /git/10.2/sql/sql_select.cc:3804
 
#16 0x0000559280e1f577 in mysql_derived_fill (thd=0x7f61b0000b00, lex=0x7f61b0004628, derived=0x7f61b022f1d0) at /git/10.2/sql/sql_derived.cc:1124
 
#17 0x0000559280e1d490 in mysql_handle_single_derived (lex=0x7f61b0004628, derived=0x7f61b022f1d0, phases=96) at /git/10.2/sql/sql_derived.cc:197
 
#18 0x0000559280eadeb1 in st_join_table::preread_init (this=0x7f61b06b72a8) at /git/10.2/sql/sql_select.cc:11992
 
#19 0x0000559280ebdfe9 in sub_select (join=0x7f61b02d7008, join_tab=0x7f61b06b72a8, end_of_records=false) at /git/10.2/sql/sql_select.cc:18710
 
#20 0x0000559280ebe896 in evaluate_join_record (join=0x7f61b02d7008, join_tab=0x7f61b06b6ef8, error=0) at /git/10.2/sql/sql_select.cc:18962
 
#21 0x0000559280ebe187 in sub_select (join=0x7f61b02d7008, join_tab=0x7f61b06b6ef8, end_of_records=false) at /git/10.2/sql/sql_select.cc:18742
 
#22 0x0000559280ebe896 in evaluate_join_record (join=0x7f61b02d7008, join_tab=0x7f61b06b6b48, error=0) at /git/10.2/sql/sql_select.cc:18962
 
#23 0x0000559280ebe187 in sub_select (join=0x7f61b02d7008, join_tab=0x7f61b06b6b48, end_of_records=false) at /git/10.2/sql/sql_select.cc:18742
 
#24 0x0000559280ebe896 in evaluate_join_record (join=0x7f61b02d7008, join_tab=0x7f61b06b6798, error=0) at /git/10.2/sql/sql_select.cc:18962
 
#25 0x0000559280ebe187 in sub_select (join=0x7f61b02d7008, join_tab=0x7f61b06b6798, end_of_records=false) at /git/10.2/sql/sql_select.cc:18742
 
#26 0x0000559280ebd722 in do_select (join=0x7f61b02d7008, procedure=0x0) at /git/10.2/sql/sql_select.cc:18286
 
#27 0x0000559280e981b7 in JOIN::exec_inner (this=0x7f61b02d7008) at /git/10.2/sql/sql_select.cc:3609
 
#28 0x0000559280e97666 in JOIN::exec (this=0x7f61b02d7008) at /git/10.2/sql/sql_select.cc:3404
 
#29 0x0000559280e98828 in mysql_select (thd=0x7f61b0000b00, tables=0x7f61b022eb60, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f61b02d6fe8, unit=0x7f61b00046f0, select_lex=0x7f61b0004e28) at /git/10.2/sql/sql_select.cc:3804
 
#30 0x0000559280e8cb6a in handle_select (thd=0x7f61b0000b00, lex=0x7f61b0004628, result=0x7f61b02d6fe8, setup_tables_done_option=0) at /git/10.2/sql/sql_select.cc:376
 
#31 0x0000559280e582a8 in execute_sqlcom_select (thd=0x7f61b0000b00, all_tables=0x7f61b022eb60) at /git/10.2/sql/sql_parse.cc:6477
 
#32 0x0000559280e4dfe6 in mysql_execute_command (thd=0x7f61b0000b00) at /git/10.2/sql/sql_parse.cc:3484
 
#33 0x0000559280e5c0be in mysql_parse (thd=0x7f61b0000b00, rawbuf=0x7f61b0012498 "WITH RECURSIVE \nreport AS\n( SELECT  YEAR(@report_date) AS YEAR,\nDATE(DATE_FORMAT(@report_date, '%Y-01-01')) AS report_start,\nDATE(@report_date) AS report_end ), \n_py AS (\nSELECT\nYEAR(report.report_sta"..., length=6036, parser_state=0x7f6204094080, is_com_multi=false, is_next_command=false) at /git/10.2/sql/sql_parse.cc:8009
 
#34 0x0000559280e49857 in dispatch_command (command=COM_QUERY, thd=0x7f61b0000b00, packet=0x7f61b0096061 "", packet_length=6037, is_com_multi=false, is_next_command=false) at /git/10.2/sql/sql_parse.cc:1824
 
#35 0x0000559280e481b3 in do_command (thd=0x7f61b0000b00) at /git/10.2/sql/sql_parse.cc:1378
 
#36 0x0000559280f995c9 in do_handle_one_connection (connect=0x559284b440e0) at /git/10.2/sql/sql_connect.cc:1335
 
#37 0x0000559280f99349 in handle_one_connection (arg=0x559284b440e0) at /git/10.2/sql/sql_connect.cc:1241
 
#38 0x00005592817c6596 in pfs_spawn_thread (arg=0x559284b26d90) at /git/10.2/storage/perfschema/pfs.cc:1862
 
#39 0x00007f620a6c06ba in start_thread (arg=0x7f6204095700) at pthread_create.c:333
 
#40 0x00007f6209b5541d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

on 10.3.9

Version: '10.3.9-MariaDB'   MariaDB Server
 
pure virtual method called
 
terminate called without an active exception
 
180829 14:55:59 [ERROR] mysqld got signal 6 ;
 
 
 
sql/handler.h:3061(handler::ha_rnd_init(bool))[0x55a4df172749]
 
sql/table.cc:8054(TABLE::insert_all_rows_into_tmp_table(THD*, TABLE*, TMP_TABLE_PARAM*, bool))[0x55a4df05ea48]
 
sql/sql_derived.cc:1136(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x55a4def8390b]
 
sql/sql_derived.cc:197(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55a4def827d7]
 
sql/sql_select.cc:12462(st_join_table::preread_init())[0x55a4defe8129]
 
sql/sql_select.cc:19251(sub_select(JOIN*, st_join_table*, bool))[0x55a4defe84c8]
 
sql/sql_select.cc:18824(do_select)[0x55a4df006fc6]
 
sql/sql_select.cc:3823(JOIN::exec())[0x55a4df00721c]
 
sql/sql_select.cc:4229(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*))[0x55a4df0060aa]
 
sql/sql_derived.cc:1163(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x55a4def83702]
 
sql/sql_derived.cc:197(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55a4def827d7]
 
sql/sql_select.cc:12462(st_join_table::preread_init())[0x55a4defe8129]
 
sql/sql_select.cc:19251(sub_select(JOIN*, st_join_table*, bool))[0x55a4defe84c8]
 
sql/sql_class.h:3868(THD::get_stmt_da())[0x55a4defde558]
 
sql/sql_select.cc:19291(sub_select(JOIN*, st_join_table*, bool))[0x55a4defe837e]
 
sql/sql_class.h:3868(THD::get_stmt_da())[0x55a4defde558]
 
sql/sql_select.cc:19291(sub_select(JOIN*, st_join_table*, bool))[0x55a4defe837e]
 
sql/sql_class.h:3868(THD::get_stmt_da())[0x55a4defde558]
 
sql/sql_select.cc:19291(sub_select(JOIN*, st_join_table*, bool))[0x55a4defe837e]
 
sql/sql_select.cc:18824(do_select)[0x55a4df006fc6]
 
sql/sql_select.cc:3823(JOIN::exec())[0x55a4df00721c]
 
sql/sql_select.cc:4229(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*))[0x55a4df0060aa]
 
sql/sql_select.cc:382(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55a4df00742c]
 
sql/sql_parse.cc:6547(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55a4deece4f9]
 
sql/sql_parse.cc:3768(mysql_execute_command(THD*))[0x55a4defb7a4c]
 
sql/sql_class.h:1809(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55a4defb9fc9]
 
sql/sql_parse.cc:1850(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55a4defbc04a]
 
sql/sql_parse.cc:1397(do_command(THD*))[0x55a4defbc7ce]
 
sql/sql_connect.cc:1402(do_handle_one_connection(CONNECT*))[0x55a4df082daf]
 
sql/sql_connect.cc:1310(handle_one_connection)[0x55a4df082ed4]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f3474f086ba]
 
x86_64/clone.S:111(clone)[0x7f3473d6d41d]

Comment by Alice Sherepa [ 2018-08-30 ] 

CREATE TABLE t1 (  YEAR int(4), d1 date , d2 date) ;
 
INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20');
 
CREATE TABLE t2 (id int, tm date);
 
INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30');
 
CREATE TABLE t3 (id int, tm date);
 
INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30');
 
 
WITH RECURSIVE 
cte AS
 
  (SELECT  YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn FROM t1
 
   UNION ALL 
   SELECT YEAR(cte.st + INTERVAL 1 MONTH), cte.st + INTERVAL 1 MONTH,  t1.d2 + INTERVAL 1 DAY
 
   FROM cte JOIN t1
 
   WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
 
               
cte2 AS (SELECT YEAR, COUNT(*) 
        FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
 
cte3 AS (SELECT YEAR, COUNT(*) 
        FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
 
SELECT 1 FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);

10.2 b245023fe0bc6fa0bd6e2df

#4  0x0000557fc9d1583f in base_list_iterator::next_fast (this=0x7faac0190580) at /10.2/sql/sql_list.h:468
 
#5  0x0000557fc9ed637b in List_iterator_fast<TABLE>::operator++ (this=0x7faac0190580) at /10.2/sql/sql_list.h:591
 
#6  0x0000557fc9fbc01a in With_element::instantiate_tmp_tables (this=0x7faab0016af8) at /10.2/sql/sql_cte.cc:1405
 
#7  0x0000557fc9dbc187 in TABLE_LIST::fill_recursive (this=0x7faab0017b50, thd=0x7faab0000b00) at /10.2/sql/sql_derived.cc:1023
 
#8  0x0000557fc9dbc43e in mysql_derived_fill (thd=0x7faab0000b00, lex=0x7faab0004628, derived=0x7faab0017b50) at /10.2/sql/sql_derived.cc:1097
 
#9  0x0000557fc9dba490 in mysql_handle_single_derived (lex=0x7faab0004628, derived=0x7faab0017b50, phases=96) at /10.2/sql/sql_derived.cc:197
 
#10 0x0000557fc9e4aeb1 in st_join_table::preread_init (this=0x7faab005da58) at /10.2/sql/sql_select.cc:11992
 
#11 0x0000557fc9e5afe9 in sub_select (join=0x7faab004ef08, join_tab=0x7faab005da58, end_of_records=false) at /10.2/sql/sql_select.cc:18710
 
#12 0x0000557fc9e5a722 in do_select (join=0x7faab004ef08, procedure=0x0) at /10.2/sql/sql_select.cc:18286
 
#13 0x0000557fc9e351b7 in JOIN::exec_inner (this=0x7faab004ef08) at /10.2/sql/sql_select.cc:3609
 
#14 0x0000557fc9e34666 in JOIN::exec (this=0x7faab004ef08) at /10.2/sql/sql_select.cc:3404
 
#15 0x0000557fc9e35828 in mysql_select (thd=0x7faab0000b00, tables=0x7faab0017b50, wild_num=0, fields=..., conds=0x7faab003b6e0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7faab0042e68, unit=0x7faab00170e0, select_lex=0x7faab0016cf0) at /10.2/sql/sql_select.cc:3804
 
#16 0x0000557fc9dbc577 in mysql_derived_fill (thd=0x7faab0000b00, lex=0x7faab0004628, derived=0x7faab003f038) at /10.2/sql/sql_derived.cc:1124
 
#17 0x0000557fc9dba490 in mysql_handle_single_derived (lex=0x7faab0004628, derived=0x7faab003f038, phases=96) at /10.2/sql/sql_derived.cc:197
 
#18 0x0000557fc9e4aeb1 in st_join_table::preread_init (this=0x7faab018b9a8) at /10.2/sql/sql_select.cc:11992
 
#19 0x0000557fc9e5afe9 in sub_select (join=0x7faab0040b70, join_tab=0x7faab018b9a8, end_of_records=false) at /10.2/sql/sql_select.cc:18710
 
#20 0x0000557fc9e5b896 in evaluate_join_record (join=0x7faab0040b70, join_tab=0x7faab018b5f8, error=0) at /10.2/sql/sql_select.cc:18962
 
#21 0x0000557fc9e5b187 in sub_select (join=0x7faab0040b70, join_tab=0x7faab018b5f8, end_of_records=false) at /10.2/sql/sql_select.cc:18742
 
#22 0x0000557fc9e5a722 in do_select (join=0x7faab0040b70, procedure=0x0) at /10.2/sql/sql_select.cc:18286
 
#23 0x0000557fc9e351b7 in JOIN::exec_inner (this=0x7faab0040b70) at /10.2/sql/sql_select.cc:3609
 
#24 0x0000557fc9e34666 in JOIN::exec (this=0x7faab0040b70) at /10.2/sql/sql_select.cc:3404
 
#25 0x0000557fc9e35828 in mysql_select (thd=0x7faab0000b00, tables=0x7faab003e878, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7faab0040b50, unit=0x7faab00046f0, select_lex=0x7faab0004e28) at /10.2/sql/sql_select.cc:3804
 
#26 0x0000557fc9e29b6a in handle_select (thd=0x7faab0000b00, lex=0x7faab0004628, result=0x7faab0040b50, setup_tables_done_option=0) at /10.2/sql/sql_select.cc:376
 
#27 0x0000557fc9df52a8 in execute_sqlcom_select (thd=0x7faab0000b00, all_tables=0x7faab003e878) at /10.2/sql/sql_parse.cc:6477
 
#28 0x0000557fc9deafe6 in mysql_execute_command (thd=0x7faab0000b00) at /10.2/sql/sql_parse.cc:3484
 
#29 0x0000557fc9df90be in mysql_parse (thd=0x7faab0000b00, rawbuf=0x7faab0012498 "WITH RECURSIVE \ncte AS\n(SELECT  YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn FROM t1\nUNION ALL \nSELECT YEAR(cte.st + INTERVAL 1 MONTH), cte.st + INTERVAL 1 MONTH,  t1.d2 + INTERVAL"..., length=500, parser_state=0x7faac0192080, is_com_multi=false, is_next_command=false) at /10.2/sql/sql_parse.cc:8009
 
#30 0x0000557fc9de6857 in dispatch_command (command=COM_QUERY, thd=0x7faab0000b00, packet=0x7faab008c531 "", packet_length=500, is_com_multi=false, is_next_command=false) at /10.2/sql/sql_parse.cc:1824
 
#31 0x0000557fc9de51b3 in do_command (thd=0x7faab0000b00) at /10.2/sql/sql_parse.cc:1378
 
#32 0x0000557fc9f365c9 in do_handle_one_connection (connect=0x557fcc9a6d90) at /10.2/sql/sql_connect.cc:1335
 
#33 0x0000557fc9f36349 in handle_one_connection (arg=0x557fcc9a6d90) at /10.2/sql/sql_connect.cc:1241
 
#34 0x0000557fca763572 in pfs_spawn_thread (arg=0x557fcc989ed0) at /10.2/storage/perfschema/pfs.cc:1862
 
#35 0x00007faac794b6ba in start_thread (arg=0x7faac0193700) at pthread_create.c:333
 
#36 0x00007faac6de041d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Comment by Oleksandr Byelkin [ 2018-09-07 ]

OK to push after adding comments as we discussed.

Comment by Igor Babaev [ 2018-09-13 ]

A fix for this bug was pushed into 10.2

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