[MDEV-32697] Assertion `m_psi_batch_mode == PSI_BATCH_MODE_NONE' failed in handler::start_psi_batch_mode and Assertion `inited == RND' failed in handler::ha_rnd_next upon WITH RECURSIVE - Jira

XML

Word

Printable

Details

Type: Bug
Status: Stalled (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4
Fix Version/s: 10.6, 10.11, 11.0, 11.1, 11.2, 11.3
Component/s: Optimizer - CTE, Server
Labels:
- CTE

Description

Run these queries in debug build:

CREATE TABLE x ( x BOOLEAN ) ;

INSERT INTO x ( x ) VALUES ( 1 ) ;

UPDATE x SET x = CASE WHEN NOT x != 1 THEN 1 ELSE 1 END WHERE x = 1 ;

INSERT INTO x ( x ) VALUES ( 1 ) , ( 1 ) ;

WITH RECURSIVE x ( x ) AS ( SELECT 'x' EXCEPT SELECT x + 1 FROM x GROUP BY - 'x' >= x IS NOT NULL = ( x = 1 AND x * 1 / ( SELECT 1 AS x UNION SELECT 1.000000 ORDER BY x IN ( SELECT x FROM ( SELECT DISTINCT - x , x FROM x WHERE x = ( SELECT CASE WHEN x = 1 THEN 'x' ELSE x END FROM x WHERE ( SELECT x IN ( SELECT x FROM ( SELECT x FROM x UNION SELECT x FROM x ) AS x WHERE x IS NULL AND x IN ( 1.000000 , 1 ) ) FROM x WHERE x != 'x' ) NOT BETWEEN ( SELECT x AS x FROM x AS x GROUP BY x HAVING x ) AND 1 ) GROUP BY 1 , 'x' , NULL HAVING EXISTS ( WITH RECURSIVE x AS ( SELECT 1 ) SELECT * FROM x WHERE x = x ) ) AS x ) ) IN ( WITH x ( x ) AS ( SELECT 1 EXCEPT SELECT 1.000000 + 1 FROM x ) SELECT x FROM ( SELECT x FROM x UNION SELECT x FROM x ) AS x ) AND 1 + 1 ) ) SELECT 1 EXCEPT SELECT 1.000000 EXCEPT SELECT x + 1 FROM x WHERE x LIKE ( x BETWEEN 1 AND 1 ) GROUP BY x HAVING x > 'x' ;

Will trigger Assertion Failed. GDB info:

#0  0x00007ffff6c0c00b in raise () from /lib/x86_64-linux-gnu/libc.so.6

#1  0x00007ffff6beb859 in abort () from /lib/x86_64-linux-gnu/libc.so.6

#2  0x00007ffff6beb729 in ?? () from /lib/x86_64-linux-gnu/libc.so.6

#3  0x00007ffff6bfcfd6 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6

#4  0x0000555557b2c32d in handler::start_psi_batch_mode (this=0x61b000050228)

    at /home/wx/mariadb-11.3.0/sql/handler.cc:3447

#5  0x000055555735ffb5 in sub_select (join=0x62d00011a2a8, join_tab=0x62f00000ae78, end_of_records=false)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23437

#6  0x000055555735dadd in do_select (join=0x62d00011a2a8, procedure=0x0)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:22961

#7  0x00005555572dbfe9 in JOIN::exec_inner (this=0x62d00011a2a8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4941

#8  0x00005555572d93a0 in JOIN::exec (this=0x62d00011a2a8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4718

#9  0x000055555752c2cf in st_select_lex_unit::exec_recursive (this=0x6290000f60a8)

    at /home/wx/mariadb-11.3.0/sql/sql_union.cc:2618

#10 0x00005555570d8d53 in TABLE_LIST::fill_recursive (this=0x629000137ca0, thd=0x62c0001d0288)

    at /home/wx/mariadb-11.3.0/sql/sql_derived.cc:1155

#11 0x00005555570d986b in mysql_derived_fill (thd=0x62c0001d0288, lex=0x62c0001d45f8, derived=0x629000137ca0)

    at /home/wx/mariadb-11.3.0/sql/sql_derived.cc:1250

#12 0x00005555570d27b6 in mysql_handle_single_derived (lex=0x62c0001d45f8, derived=0x629000137ca0, phases=96)

    at /home/wx/mariadb-11.3.0/sql/sql_derived.cc:200

#13 0x000055555732a50c in st_join_table::preread_init (this=0x63100007fb38)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:16029

#14 0x000055555735f8c8 in sub_select (join=0x62d00011dfb0, join_tab=0x63100007fb38, end_of_records=false)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23392

#15 0x000055555735dadd in do_select (join=0x62d00011dfb0, procedure=0x0)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:22961

#16 0x00005555572dbfe9 in JOIN::exec_inner (this=0x62d00011dfb0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4941

#17 0x00005555572d93a0 in JOIN::exec (this=0x62d00011dfb0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4718

#18 0x00005555572ddbab in mysql_select (thd=0x62c0001d0288, tables=0x629000137ca0, fields=..., conds=0x629000168ae8,

    og_num=3, order=0x0, group=0x629000167158, having=0x62d000129e88, proc_param=0x0, select_options=2201187781377,

    result=0x62d00011deb8, unit=0x62d0001044a8, select_lex=0x629000137410)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:5249

#19 0x00005555570d9bea in mysql_derived_fill (thd=0x62c0001d0288, lex=0x62c0001d45f8, derived=0x62900016bb00)

    at /home/wx/mariadb-11.3.0/sql/sql_derived.cc:1266

#20 0x00005555570d27b6 in mysql_handle_single_derived (lex=0x62c0001d45f8, derived=0x62900016bb00, phases=96)

    at /home/wx/mariadb-11.3.0/sql/sql_derived.cc:200

#21 0x000055555732a50c in st_join_table::preread_init (this=0x631000086ac8)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:16029

#22 0x000055555735f8c8 in sub_select (join=0x62d00011d6b8, join_tab=0x631000086ac8, end_of_records=false)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23392

#23 0x000055555735dadd in do_select (join=0x62d00011d6b8, procedure=0x0)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:22961

#24 0x00005555572dbfe9 in JOIN::exec_inner (this=0x62d00011d6b8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4941

#25 0x00005555572d93a0 in JOIN::exec (this=0x62d00011d6b8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4718

#26 0x0000555557dce72f in subselect_single_select_engine::exec (this=0x62d000105730)

    at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:4159

#27 0x0000555557da9c85 in Item_subselect::exec (this=0x62d000105528)

    at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:812

#28 0x0000555557dab291 in Item_in_subselect::exec (this=0x62d000105528)

    at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:994

#29 0x0000555557db650f in Item_in_subselect::val_bool (this=0x62d000105528)

    at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:1991

#30 0x0000555556e14cd4 in Item::val_bool_result (this=0x62d000105528) at /home/wx/mariadb-11.3.0/sql/item.h:1797

#31 0x0000555557bf3f02 in Item_in_optimizer::val_int (this=0x62d00012a310)

    at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:1664

#32 0x0000555556e14b78 in Item::val_int_result (this=0x62d00012a310) at /home/wx/mariadb-11.3.0/sql/item.h:1793

#33 0x0000555557bc30d8 in Item_cache_int::cache_value (this=0x631000088720)

    at /home/wx/mariadb-11.3.0/sql/item.cc:10161

#34 0x0000555557bdad9f in Item_cache_wrapper::cache (this=0x631000088670) at /home/wx/mariadb-11.3.0/sql/item.cc:8915

#35 0x0000555557bb7dac in Item_cache_wrapper::save_val (this=0x631000088670, to=0x61a0001f9928)

    at /home/wx/mariadb-11.3.0/sql/item.cc:8941

#36 0x0000555557bd1043 in Item_cache_wrapper::save_in_result_field (this=0x631000088670)

    at /home/wx/mariadb-11.3.0/sql/item.h:5958

#37 0x00005555573879d1 in copy_funcs (func_ptr=0x62000003cb78, thd=0x62c0001d0288)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:28843

#38 0x000055555736c428 in end_write (join=0x62d00011c1c8, join_tab=0x62f000023e90, end_of_records=false)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:24979

#39 0x00005555573a1a77 in AGGR_OP::put_record (this=0x63100008cb18, end_of_records=false)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:32254

#40 0x00005555573b632d in AGGR_OP::put_record (this=0x63100008cb18) at /home/wx/mariadb-11.3.0/sql/sql_select.h:1180

#41 0x000055555735ec6a in sub_select_postjoin_aggr (join=0x62d00011c1c8, join_tab=0x62f000023e90,

    end_of_records=false) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23134

#42 0x000055555736194d in evaluate_join_record (join=0x62d00011c1c8, join_tab=0x62f000023a18, error=0)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23677

#43 0x00005555573601c2 in sub_select (join=0x62d00011c1c8, join_tab=0x62f000023a18, end_of_records=false)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23444

#44 0x000055555735dadd in do_select (join=0x62d00011c1c8, procedure=0x0)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:22961

#45 0x00005555572dbfe9 in JOIN::exec_inner (this=0x62d00011c1c8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4941

#46 0x00005555572d93a0 in JOIN::exec (this=0x62d00011c1c8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4718

#47 0x00005555572ddbab in mysql_select (thd=0x62c0001d0288, tables=0x6290000f8a08, fields=..., conds=0x0, og_num=1,

    order=0x62d000105798, group=0x0, having=0x0, proc_param=0x0, select_options=2199023255552,

    result=0x62d000105998, unit=0x6290000f89c0, select_lex=0x629000136820)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:5249

#48 0x000055555752ae77 in st_select_lex_unit::exec_inner (this=0x6290000f89c0)

    at /home/wx/mariadb-11.3.0/sql/sql_union.cc:2480

#49 0x0000555557528545 in st_select_lex_unit::exec (this=0x6290000f89c0)

    at /home/wx/mariadb-11.3.0/sql/sql_union.cc:2292

#50 0x0000555557dcefe5 in subselect_union_engine::exec (this=0x62d0001059d0)

    at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:4187

#51 0x0000555557da9c85 in Item_subselect::exec (this=0x62d000105830)

    at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:812

#52 0x0000555557daf7a1 in Item_singlerow_subselect::val_real (this=0x62d000105830)

    at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:1441

#53 0x0000555557c783a0 in Item_func_div::real_op (this=0x62d000105a10)

    at /home/wx/mariadb-11.3.0/sql/item_func.cc:1503

#54 0x00005555578e4952 in Item_func_hybrid_field_type::val_real_from_real_op (this=0x62d000105a10)

    at /home/wx/mariadb-11.3.0/sql/item_func.h:854

#55 0x00005555578b7ed0 in Type_handler_real_result::Item_func_hybrid_field_type_val_real (

    this=0x55555b7b6c00 <type_handler_double>, item=0x62d000105a10) at /home/wx/mariadb-11.3.0/sql/sql_type.cc:5457

#56 0x0000555557181299 in Item_func_hybrid_field_type::val_real (this=0x62d000105a10)

    at /home/wx/mariadb-11.3.0/sql/item_func.h:900

#57 0x0000555556e14b0c in Item::val_result (this=0x62d000105a10) at /home/wx/mariadb-11.3.0/sql/item.h:1792

#58 0x0000555557bc5850 in Item_cache_real::cache_value (this=0x62f000007d58)

    at /home/wx/mariadb-11.3.0/sql/item.cc:10388

#59 0x0000555557bf3310 in Item_in_optimizer::val_int (this=0x62f000007c80)

    at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:1577

#60 0x00005555578b4b6e in Type_handler_int_result::Item_val_bool (this=0x55555b7b68c0 <type_handler_bool>,

    item=0x62f000007c80) at /home/wx/mariadb-11.3.0/sql/sql_type.cc:5082

#61 0x0000555556e147f6 in Item::val_bool (this=0x62f000007c80) at /home/wx/mariadb-11.3.0/sql/item.h:1701

#62 0x0000555557c1797a in Item_cond_and::val_int (this=0x62d00010bf30)

    at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:5524

#63 0x0000555557beca8a in Arg_comparator::compare_int_signed (this=0x62d00010e560)

    at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:947

#64 0x0000555557c2e1d4 in Arg_comparator::compare (this=0x62d00010e560)

    at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.h:104

#65 0x0000555557bf5bdb in Item_func_eq::val_int (this=0x62d00010e4a8)

    at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:1780

#66 0x0000555557ba521f in Item::save_int_in_field (this=0x62d00010e4a8, field=0x61a0001f7b28, no_conversions=true)

    at /home/wx/mariadb-11.3.0/sql/item.cc:6843

#67 0x00005555578b2242 in Type_handler_int_result::Item_save_in_field (this=0x55555b7b68c0 <type_handler_bool>,

    item=0x62d00010e4a8, field=0x61a0001f7b28, no_conversions=true) at /home/wx/mariadb-11.3.0/sql/sql_type.cc:4341

#68 0x0000555557ba540b in Item::save_in_field (this=0x62d00010e4a8, field=0x61a0001f7b28, no_conversions=true)

    at /home/wx/mariadb-11.3.0/sql/item.cc:6853

#69 0x0000555556ea7174 in Item_result_field::save_in_result_field (this=0x62d00010e4a8, no_conversions=true)

    at /home/wx/mariadb-11.3.0/sql/item.h:3490

#70 0x00005555573879d1 in copy_funcs (func_ptr=0x620000039b78, thd=0x62c0001d0288)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:28843

#71 0x000055555736c428 in end_write (join=0x62d00011a2a8, join_tab=0x62f00000b2f0, end_of_records=false)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:24979

#72 0x00005555573a1a77 in AGGR_OP::put_record (this=0x62f00001a428, end_of_records=false)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:32254

#73 0x00005555573b632d in AGGR_OP::put_record (this=0x62f00001a428) at /home/wx/mariadb-11.3.0/sql/sql_select.h:1180

#74 0x000055555735ec6a in sub_select_postjoin_aggr (join=0x62d00011a2a8, join_tab=0x62f00000b2f0,

    end_of_records=false) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23134

#75 0x000055555736194d in evaluate_join_record (join=0x62d00011a2a8, join_tab=0x62f00000ae78, error=0)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23677

#76 0x00005555573601c2 in sub_select (join=0x62d00011a2a8, join_tab=0x62f00000ae78, end_of_records=false)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23444

#77 0x000055555735dadd in do_select (join=0x62d00011a2a8, procedure=0x0)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:22961

#78 0x00005555572dbfe9 in JOIN::exec_inner (this=0x62d00011a2a8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4941

#79 0x00005555572d93a0 in JOIN::exec (this=0x62d00011a2a8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4718

#80 0x000055555752c2cf in st_select_lex_unit::exec_recursive (this=0x6290000f60a8)

    at /home/wx/mariadb-11.3.0/sql/sql_union.cc:2618

#81 0x00005555570d8d53 in TABLE_LIST::fill_recursive (this=0x62d000110b50, thd=0x62c0001d0288)

    at /home/wx/mariadb-11.3.0/sql/sql_derived.cc:1155

#82 0x00005555570d986b in mysql_derived_fill (thd=0x62c0001d0288, lex=0x62c0001d45f8, derived=0x62d000110b50)

    at /home/wx/mariadb-11.3.0/sql/sql_derived.cc:1250

#83 0x00005555570d27b6 in mysql_handle_single_derived (lex=0x62c0001d45f8, derived=0x62d000110b50, phases=96)

    at /home/wx/mariadb-11.3.0/sql/sql_derived.cc:200

#84 0x000055555732a50c in st_join_table::preread_init (this=0x62f000020578)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:16029

#85 0x000055555735f8c8 in sub_select (join=0x62d0001190b8, join_tab=0x62f000020578, end_of_records=false)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23392

#86 0x000055555735dadd in do_select (join=0x62d0001190b8, procedure=0x0)

    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:22961

#87 0x00005555572dbfe9 in JOIN::exec_inner (this=0x62d0001190b8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4941

#88 0x00005555572d93a0 in JOIN::exec (this=0x62d0001190b8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4718

#89 0x0000555557529ad1 in st_select_lex_unit::exec_inner (this=0x62c0001d46d8)

    at /home/wx/mariadb-11.3.0/sql/sql_union.cc:2389

#90 0x0000555557528545 in st_select_lex_unit::exec (this=0x62c0001d46d8)

    at /home/wx/mariadb-11.3.0/sql/sql_union.cc:2292

#91 0x0000555557514186 in mysql_union (thd=0x62c0001d0288, lex=0x62c0001d45f8, result=0x62d000115e38,

    unit=0x62c0001d46d8, setup_tables_done_option=0) at /home/wx/mariadb-11.3.0/sql/sql_union.cc:45

#92 0x00005555572acea8 in handle_select (thd=0x62c0001d0288, lex=0x62c0001d45f8, result=0x62d000115e38,

    setup_tables_done_option=0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:618

#93 0x00005555571ce583 in execute_sqlcom_select (thd=0x62c0001d0288, all_tables=0x6290000f70c0)

    at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:6013

#94 0x00005555571becf6 in mysql_execute_command (thd=0x62c0001d0288, is_called_from_prepared_stmt=false)

    at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:3912

#95 0x00005555571d95e2 in mysql_parse (thd=0x62c0001d0288,

    rawbuf=0x6290000f52a8 "WITH RECURSIVE x ( x ) AS ( SELECT 'x' EXCEPT SELECT x + 1 FROM x GROUP BY - 'x' >= x IS NOT NULL = ( x = 1 AND x * 1 / ( SELECT 1 AS x UNION SELECT 1.000000 ORDER BY x IN ( SELECT x FROM ( SELECT DIS"...,

    length=882, parser_state=0x7fffd163c870) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:7734

#96 0x00005555571b1237 in dispatch_command (command=COM_QUERY, thd=0x62c0001d0288,

    packet=0x6290000eb289 "WITH RECURSIVE x ( x ) AS ( SELECT 'x' EXCEPT SELECT x + 1 FROM x GROUP BY - 'x' >= x IS NOT NULL = ( x = 1 AND x * 1 / ( SELECT 1 AS x UNION SELECT 1.000000 ORDER BY x IN ( SELECT x FROM ( SELECT DIS"...,

    packet_length=882, blocking=true) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1893

#97 0x00005555571adf7c in do_command (thd=0x62c0001d0288, blocking=true)

    at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1406

#98 0x000055555768e557 in do_handle_one_connection (connect=0x61100006f608, put_in_cache=true)

    at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1445

#99 0x000055555768deb4 in handle_one_connection (arg=0x61100006f608)

    at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1347

#100 0x00005555582fa350 in pfs_spawn_thread (arg=0x618000005108)

    at /home/wx/mariadb-11.3.0/storage/perfschema/pfs.cc:2201

#101 0x00007ffff7115609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0

#102 0x00007ffff6ce8133 in clone () from /lib/x86_64-linux-gnu/libc.so.6

Attachments

Issue Links

relates to

MDEV-32981 SQL Simplification for the SQL Query

Closed

MDEV-32913 Crash when query uses recursive reference to CTE in ORDER BY clause

Stalled

Activity

People

Assignee:: Oleg Smirnov

Reporter:: Xin Wen

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2023-11-07 02:17

Updated:: 2024-02-16 04:02

Git Integration

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