[MDEV-26825] Bogus error for query with two usage of CTE referring another CTE Created: 2021-10-14  Updated: 2021-11-17  Resolved: 2021-11-17

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.3.31, 10.6.3, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.2.42, 10.3.33, 10.4.23, 10.5.14, 10.6.6

Type: Bug Priority: Major
Reporter: Walter van der Geest Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None
Environment:

debian, windows

Attachments: Text File error.log    
Issue Links:
Relates
relates to MDEV-26025 Server crashes while executing query ... Closed

 Description   

When a double encapsulated CTE query calls a function which reads a table which has been aliased in the CTE query, the optimizer cannot find the table anymore and ends with an error 1146 Table db.t1 doesn't exist.

Script to reproduce:

CREATE TABLE t1 (id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY);
 
/* Generate 10'000 entries */
 
INSERT INTO t1(id) SELECT @row := @row + 1 AS id FROM 
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t,
 
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2, 
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3, 
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4, 
(SELECT @row:=0) numbers;
 
 
 
CREATE OR REPLACE FUNCTION check_my_id(in_id INT) RETURNS INTEGER
 
READS SQL DATA
 
RETURN (SELECT id FROM t1 WHERE t1.id = in_id);
 
 
 
 
 
WITH c1 AS (SELECT id	FROM t1 a WHERE check_my_id(id) = id GROUP BY id),
 
	   c2 AS (SELECT pt.id FROM c1 pt GROUP BY pt.id)
 
SELECT id FROM c2 GROUP BY id
 
UNION ALL
 
SELECT id FROM c2;

If the table alias (a) is removed in the CTE c1, the query works.

When I try on my real query (and not in the abbreviated example above), I sometimes get the error message and sometimes mysqld crashes with the error log attached.

 Comments   
Comment by Alice Sherepa [ 2021-10-14 ]

About the crash - it might be the same problem as MDEV-26025 - could you please try 10.6.4?

Comment by Alice Sherepa [ 2021-10-14 ]

Thank you for the report!

I repeated as described on 10.2-10.6. --getting error 1146: Table 'test.t1' doesn't exist

On debug version, when alias is not used - there is the assertion `!file->keyread_enabled()' failed in TABLE::init:

 
 
--source include/have_sequence.inc
 
CREATE TABLE t1 (id int primary key);
 
 
 
insert into t1 select seq from seq_1_to_10000;
 
 
 
CREATE  FUNCTION check_my_id(in_id INT) RETURNS INTEGER
 
RETURN (SELECT id FROM t1 WHERE t1.id = in_id);
 
 
 
WITH c1 AS (SELECT id FROM t1  WHERE check_my_id(id) = id GROUP BY id),
 
     c2 AS (SELECT pt.id FROM c1 pt GROUP BY pt.id)
 
SELECT id FROM c2 GROUP BY id
 
UNION ALL
 
SELECT id FROM c2;

10.2 1d71dacd519a8ea51e7c8a92

 
 
 
#3  <signal handler called>
 
#4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#5  0x00007f5bab876859 in __GI_abort () at abort.c:79
 
#6  0x00007f5bab876729 in __assert_fail_base (fmt=0x7f5baba0c588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x557d55e0ed67 "!file->keyread_enabled()", file=0x557d55e0d7b8 "/10.2/src/sql/table.cc", line=4687, function=<optimized out>) at assert.c:92
 
#7  0x00007f5bab887f36 in __GI___assert_fail (assertion=0x557d55e0ed67 "!file->keyread_enabled()", file=0x557d55e0d7b8 "/10.2/src/sql/table.cc", line=4687, function=0x557d55e0ece8 "void TABLE::init(THD*, TABLE_LIST*)") at assert.c:101
 
#8  0x0000557d5545fd63 in TABLE::init (this=0x7f5b5809db80, thd=0x7f5b58000d90, tl=0x7f5b58161ee8) at /10.2/src/sql/table.cc:4687
 
#9  0x0000557d552d9cee in open_table (thd=0x7f5b58000d90, table_list=0x7f5b58161ee8, ot_ctx=0x7f5ba5bbae40) at /10.2/src/sql/sql_base.cc:1678
 
#10 0x0000557d552dd58c in open_and_process_table (thd=0x7f5b58000d90, tables=0x7f5b58161ee8, counter=0x7f5ba5bbaed4, flags=0, prelocking_strategy=0x7f5ba5bbaf58, has_prelocking_list=false, ot_ctx=0x7f5ba5bbae40) at /10.2/src/sql/sql_base.cc:3589
 
#11 0x0000557d552de481 in open_tables (thd=0x7f5b58000d90, options=..., start=0x7f5ba5bbaeb8, counter=0x7f5ba5bbaed4, flags=0, prelocking_strategy=0x7f5ba5bbaf58) at /10.2/src/sql/sql_base.cc:4062
 
#12 0x0000557d552dfad6 in open_and_lock_tables (thd=0x7f5b58000d90, options=..., tables=0x7f5b58161ee8, derived=true, flags=0, prelocking_strategy=0x7f5ba5bbaf58) at /10.2/src/sql/sql_base.cc:4861
 
#13 0x0000557d552a55b9 in open_and_lock_tables (thd=0x7f5b58000d90, tables=0x7f5b58161ee8, derived=true, flags=0) at /10.2/src/sql/sql_base.h:508
 
#14 0x0000557d5529f341 in sp_instr::exec_open_and_lock_tables (this=0x7f5b58162508, thd=0x7f5b58000d90, tables=0x7f5b58161ee8) at /10.2/src/sql/sp_head.cc:3196
 
#15 0x0000557d5529ef2f in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f5b58162548, thd=0x7f5b58000d90, nextp=0x7f5ba5bbb0cc, open_tables=true, instr=0x7f5b58162508) at /10.2/src/sql/sp_head.cc:3090
 
#16 0x0000557d552a04bb in sp_instr_freturn::execute (this=0x7f5b58162508, thd=0x7f5b58000d90, nextp=0x7f5ba5bbb0cc) at /10.2/src/sql/sp_head.cc:3611
 
#17 0x0000557d5529a5a1 in sp_head::execute (this=0x7f5b5815e7f8, thd=0x7f5b58000d90, merge_da_on_success=true) at /10.2/src/sql/sp_head.cc:1326
 
#18 0x0000557d5529bad6 in sp_head::execute_function (this=0x7f5b5815e7f8, thd=0x7f5b58000d90, argp=0x7f5b58014038, argcount=1, return_value_fld=0x7f5b5809c760) at /10.2/src/sql/sp_head.cc:1954
 
#19 0x0000557d55664e8c in Item_func_sp::execute_impl (this=0x7f5b58013fa8, thd=0x7f5b58000d90) at /10.2/src/sql/item_func.cc:6752
 
#20 0x0000557d55664c1a in Item_func_sp::execute (this=0x7f5b58013fa8) at /10.2/src/sql/item_func.cc:6685
 
#21 0x0000557d5566982c in Item_func_sp::val_int (this=0x7f5b58013fa8) at /10.2/src/sql/item_func.h:2412
 
#22 0x0000557d5561540a in Arg_comparator::compare_int_signed (this=0x7f5b58015328) at /10.2/src/sql/item_cmpfunc.cc:967
 
#23 0x0000557d556296aa in Arg_comparator::compare (this=0x7f5b58015328) at /10.2/src/sql/item_cmpfunc.h:87
 
#24 0x0000557d55617afd in Item_func_eq::val_int (this=0x7f5b58015268) at /10.2/src/sql/item_cmpfunc.cc:1806
 
#25 0x0000557d553c02d0 in evaluate_join_record (join=0x7f5b5809c080, join_tab=0x7f5b5803d208, error=0) at /10.2/src/sql/sql_select.cc:18990
 
#26 0x0000557d553bfef3 in sub_select (join=0x7f5b5809c080, join_tab=0x7f5b5803d208, end_of_records=false) at /10.2/src/sql/sql_select.cc:18895
 
#27 0x0000557d553bf454 in do_select (join=0x7f5b5809c080, procedure=0x0) at /10.2/src/sql/sql_select.cc:18439
 
#28 0x0000557d55398f65 in JOIN::exec_inner (this=0x7f5b5809c080) at /10.2/src/sql/sql_select.cc:3651
 
#29 0x0000557d5539840c in JOIN::exec (this=0x7f5b5809c080) at /10.2/src/sql/sql_select.cc:3446
 
#30 0x0000557d553995e6 in mysql_select (thd=0x7f5b58000d90, tables=0x7f5b58013618, wild_num=0, fields=..., conds=0x7f5b58015268, og_num=1, order=0x0, group=0x7f5b580154d8, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7f5b5809bfb0, unit=0x7f5b58012d78, select_lex=0x7f5b58012980) at /10.2/src/sql/sql_select.cc:3849
 
#31 0x0000557d5531f390 in mysql_derived_fill (thd=0x7f5b58000d90, lex=0x7f5b580048c8, derived=0x7f5b58016310) at /10.2/src/sql/sql_derived.cc:1107
 
#32 0x0000557d5531d259 in mysql_handle_single_derived (lex=0x7f5b580048c8, derived=0x7f5b58016310, phases=96) at /10.2/src/sql/sql_derived.cc:192
 
#33 0x0000557d553af992 in st_join_table::preread_init (this=0x7f5b5803e380) at /10.2/src/sql/sql_select.cc:12141
 
#34 0x0000557d553bfd53 in sub_select (join=0x7f5b5809c990, join_tab=0x7f5b5803e380, end_of_records=false) at /10.2/src/sql/sql_select.cc:18863
 
#35 0x0000557d553bf454 in do_select (join=0x7f5b5809c990, procedure=0x0) at /10.2/src/sql/sql_select.cc:18439
 
#36 0x0000557d55398f65 in JOIN::exec_inner (this=0x7f5b5809c990) at /10.2/src/sql/sql_select.cc:3651
 
#37 0x0000557d5539840c in JOIN::exec (this=0x7f5b5809c990) at /10.2/src/sql/sql_select.cc:3446
 
#38 0x0000557d553995e6 in mysql_select (thd=0x7f5b58000d90, tables=0x7f5b58016310, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0x7f5b58016a50, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7f5b5809c8c0, unit=0x7f5b58015a58, select_lex=0x7f5b58015660) at /10.2/src/sql/sql_select.cc:3849
 
#39 0x0000557d5531f390 in mysql_derived_fill (thd=0x7f5b58000d90, lex=0x7f5b580048c8, derived=0x7f5b58016ce0) at /10.2/src/sql/sql_derived.cc:1107
 
#40 0x0000557d5531d259 in mysql_handle_single_derived (lex=0x7f5b580048c8, derived=0x7f5b58016ce0, phases=96) at /10.2/src/sql/sql_derived.cc:192
 
#41 0x0000557d553af992 in st_join_table::preread_init (this=0x7f5b58042e68) at /10.2/src/sql/sql_select.cc:12141
 
#42 0x0000557d553bfd53 in sub_select (join=0x7f5b5809ba58, join_tab=0x7f5b58042e68, end_of_records=false) at /10.2/src/sql/sql_select.cc:18863
 
#43 0x0000557d553bf454 in do_select (join=0x7f5b5809ba58, procedure=0x0) at /10.2/src/sql/sql_select.cc:18439
 
#44 0x0000557d55398f65 in JOIN::exec_inner (this=0x7f5b5809ba58) at /10.2/src/sql/sql_select.cc:3651
 
#45 0x0000557d5539840c in JOIN::exec (this=0x7f5b5809ba58) at /10.2/src/sql/sql_select.cc:3446
 
#46 0x0000557d5543fc0b in st_select_lex_unit::exec (this=0x7f5b58004988) at /10.2/src/sql/sql_union.cc:1074
 
#47 0x0000557d5543c236 in mysql_union (thd=0x7f5b58000d90, lex=0x7f5b580048c8, result=0x7f5b5809b930, unit=0x7f5b58004988, setup_tables_done_option=0) at /10.2/src/sql/sql_union.cc:42
 
#48 0x0000557d5538d656 in handle_select (thd=0x7f5b58000d90, lex=0x7f5b580048c8, result=0x7f5b5809b930, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:351
 
#49 0x0000557d55357d9a in execute_sqlcom_select (thd=0x7f5b58000d90, all_tables=0x7f5b58016ce0) at /10.2/src/sql/sql_parse.cc:6271
 
#50 0x0000557d5534e90e in mysql_execute_command (thd=0x7f5b58000d90) at /10.2/src/sql/sql_parse.cc:3582
 
#51 0x0000557d5535bb56 in mysql_parse (thd=0x7f5b58000d90, rawbuf=0x7f5b58012708 "WITH c1 AS (SELECT id FROM t1  WHERE check_my_id(id) = id GROUP BY id),\nc2 AS (SELECT pt.id FROM c1 pt GROUP BY pt.id)\nSELECT id FROM c2 GROUP BY id\nUNION ALL\nSELECT id FROM c2", length=176, parser_state=0x7f5ba5bbd560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
 
#52 0x0000557d55349db1 in dispatch_command (command=COM_QUERY, thd=0x7f5b58000d90, packet=0x7f5b58008b61 "", packet_length=176, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
 
#53 0x0000557d553488ac in do_command (thd=0x7f5b58000d90) at /10.2/src/sql/sql_parse.cc:1381
 
#54 0x0000557d554a467b in do_handle_one_connection (connect=0x557d58779c30) at /10.2/src/sql/sql_connect.cc:1336
 
#55 0x0000557d554a43e0 in handle_one_connection (arg=0x557d58779c30) at /10.2/src/sql/sql_connect.cc:1241
 
#56 0x0000557d55ccedbe in pfs_spawn_thread (arg=0x557d5875d020) at /10.2/src/storage/perfschema/pfs.cc:1869
 
#57 0x00007f5babd98609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#58 0x00007f5bab973293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

if there is no primary key in table t1 :

--source include/have_sequence.inc
 
 
 
CREATE TABLE t1 (id int);
 
 
 
insert into t1 select seq from seq_1_to_10000;
 
 
 
CREATE  FUNCTION check_my_id(in_id INT) RETURNS INTEGER
 
RETURN (SELECT id FROM t1 WHERE t1.id = in_id);
 
 
 
WITH c1 AS (SELECT id FROM t1  WHERE check_my_id(id) = id GROUP BY id),
 
     c2 AS (SELECT pt.id FROM c1 pt GROUP BY pt.id)
 
SELECT id FROM c2 GROUP BY id
 
UNION ALL
 
SELECT id FROM c2;

10.2 1d71dacd519a8ea51e7c8a92

 
 
 
Version: '10.2.41-MariaDB-debug-log' 
mysqld: /10.2/src/sql/handler.cc:2665: int handler::ha_rnd_next(uchar*): Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed.
 
211014 11:42:33 [ERROR] mysqld got signal 6 ;
 
 
 
:0(__GI___assert_fail)[0x7fd1fac43f36]
 
sql/handler.cc:2667(handler::ha_rnd_next(unsigned char*))[0x5582e3128439]
 
sql/records.cc:492(rr_sequential(READ_RECORD*))[0x5582e329bf77]
 
sql/sql_select.cc:19821(join_init_read_record(st_join_table*))[0x5582e2f10151]
 
sql/sql_select.cc:18892(sub_select(JOIN*, st_join_table*, bool))[0x5582e2f0de90]
 
sql/sql_select.cc:18439(do_select(JOIN*, Procedure*))[0x5582e2f0d454]
 
sql/sql_select.cc:3651(JOIN::exec_inner())[0x5582e2ee6f65]
 
sql/sql_select.cc:3447(JOIN::exec())[0x5582e2ee640c]
 
sql/item_subselect.cc:4003(subselect_single_select_engine::exec())[0x5582e31f6cb3]
 
sql/item_subselect.cc:770(Item_subselect::exec())[0x5582e31eb5fd]
 
sql/item_subselect.cc:1391(Item_singlerow_subselect::val_int())[0x5582e31ed439]
 
sql/item.cc:6456(Item::save_in_field(Field*, bool))[0x5582e3148bc0]
 
sql/sp_head.cc:436(sp_eval_expr(THD*, Field*, Item**))[0x5582e2de6234]
 
sql/sp_rcontext.cc:161(sp_rcontext::set_return_value(THD*, Item**))[0x5582e2df69a1]
 
sql/sp_head.cc:3641(sp_instr_freturn::exec_core(THD*, unsigned int*))[0x5582e2dee566]
 
sql/sp_head.cc:3094(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x5582e2decf5b]
 
sql/sp_head.cc:3611(sp_instr_freturn::execute(THD*, unsigned int*))[0x5582e2dee4bb]
 
sql/sp_head.cc:1326(sp_head::execute(THD*, bool))[0x5582e2de85a1]
 
sql/sp_head.cc:1954(sp_head::execute_function(THD*, Item**, unsigned int, Field*))[0x5582e2de9ad6]
 
sql/item_func.cc:6752(Item_func_sp::execute_impl(THD*))[0x5582e31b2e8c]
 
sql/item_func.cc:6685(Item_func_sp::execute())[0x5582e31b2c1a]
 
sql/item_func.h:2412(Item_func_sp::val_int())[0x5582e31b782c]
 
sql/item_cmpfunc.cc:967(Arg_comparator::compare_int_signed())[0x5582e316340a]
 
sql/item_cmpfunc.h:87(Arg_comparator::compare())[0x5582e31776aa]
 
sql/item_cmpfunc.cc:1806(Item_func_eq::val_int())[0x5582e3165afd]
 
sql/sql_select.cc:18990(evaluate_join_record(JOIN*, st_join_table*, int))[0x5582e2f0e2d0]
 
sql/sql_select.cc:18895(sub_select(JOIN*, st_join_table*, bool))[0x5582e2f0def3]
 
sql/sql_select.cc:18439(do_select(JOIN*, Procedure*))[0x5582e2f0d454]
 
sql/sql_select.cc:3651(JOIN::exec_inner())[0x5582e2ee6f65]
 
sql/sql_select.cc:3447(JOIN::exec())[0x5582e2ee640c]
 
sql/sql_select.cc:3851(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*))[0x5582e2ee75e6]
 
sql/sql_derived.cc:1107(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x5582e2e6d390]
 
sql/sql_derived.cc:192(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x5582e2e6b259]
 
sql/sql_select.cc:12140(st_join_table::preread_init())[0x5582e2efd992]
 
sql/sql_select.cc:18863(sub_select(JOIN*, st_join_table*, bool))[0x5582e2f0dd53]
 
sql/sql_select.cc:18439(do_select(JOIN*, Procedure*))[0x5582e2f0d454]
 
sql/sql_select.cc:3651(JOIN::exec_inner())[0x5582e2ee6f65]
 
sql/sql_select.cc:3447(JOIN::exec())[0x5582e2ee640c]
 
sql/sql_select.cc:3851(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*))[0x5582e2ee75e6]
 
sql/sql_derived.cc:1107(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x5582e2e6d390]
 
sql/sql_derived.cc:192(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x5582e2e6b259]
 
sql/sql_select.cc:12140(st_join_table::preread_init())[0x5582e2efd992]
 
sql/sql_select.cc:18863(sub_select(JOIN*, st_join_table*, bool))[0x5582e2f0dd53]
 
sql/sql_select.cc:18439(do_select(JOIN*, Procedure*))[0x5582e2f0d454]
 
sql/sql_select.cc:3651(JOIN::exec_inner())[0x5582e2ee6f65]
 
sql/sql_select.cc:3447(JOIN::exec())[0x5582e2ee640c]
 
sql/sql_union.cc:1075(st_select_lex_unit::exec())[0x5582e2f8dc0b]
 
sql/sql_union.cc:42(mysql_union(THD*, LEX*, select_result*, st_select_lex_unit*, unsigned long))[0x5582e2f8a236]
 
sql/sql_select.cc:351(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5582e2edb656]
 
sql/sql_parse.cc:6271(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5582e2ea5d9a]
 
sql/sql_parse.cc:3582(mysql_execute_command(THD*))[0x5582e2e9c90e]
 
sql/sql_parse.cc:7793(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5582e2ea9b56]
 
sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5582e2e97db1]
 
sql/sql_parse.cc:1381(do_command(THD*))[0x5582e2e968ac]
 
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x5582e2ff267b]
 
sql/sql_connect.cc:1242(handle_one_connection)[0x5582e2ff23e0]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x5582e381cdbe]
 
nptl/pthread_create.c:478(start_thread)[0x7fd1fb154609]
 
x86_64/clone.S:97(__GI___clone)[0x7fd1fad2f293]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fd1a0012708): WITH c1 AS (SELECT id FROM t1  WHERE check_my_id(id) = id GROUP BY id),
 
c2 AS (SELECT pt.id FROM c1 pt GROUP BY pt.id)
 
SELECT id FROM c2 GROUP BY id
 
UNION ALL
 
SELECT id FROM c2

Comment by Walter van der Geest [ 2021-10-14 ]

Tried with 10.6.4 - same result.

Comment by Alice Sherepa [ 2021-10-14 ]

I've got some cases, that are derived from the reported test case, but they all are not quite the same as the one, that is in the attached error log. 

 
 
CREATE TABLE t1 (id int primary key);
 
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 
 
 
CREATE FUNCTION f() RETURNS int RETURN (SELECT 1 FROM t1 limit 1);
 
 
create  procedure sp1() 
WITH 
cte1 AS (SELECT 1 id FROM t1 a LEFT JOIN t1 t2 ON a.id=t2.id and f() ),
 
cte2 AS (SELECT 1 FROM cte1)
 
(SELECT  1 FROM cte2)  UNION  (SELECT 1 FROM cte2);
 
 
 
--error 1146
 
CALL sp1();
 
CALL sp1();

Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed in handler::ha_index_first

10.2 1d71dacd519a8ea51e7c8

 
#3  <signal handler called>
 
#4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#5  0x00007f1291558859 in __GI_abort () at abort.c:79
 
#6  0x00007f1291558729 in __assert_fail_base (fmt=0x7f12916ee588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x564891919d10 "table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2", file=0x5648919189b0 "/10.2/src/sql/handler.cc", line=2805, function=<optimized out>) at assert.c:92
 
#7  0x00007f1291569f36 in __GI___assert_fail (assertion=0x564891919d10 "table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2", file=0x5648919189b0 "/10.2/src/sql/handler.cc", line=2805, function=0x564891919f08 "int handler::ha_index_first(uchar*)") at assert.c:101
 
#8  0x00005648910285b6 in handler::ha_index_first (this=0x7f127008bac8, buf=0x7f12701911d0 "\377") at /10.2/src/sql/handler.cc:2805
 
#9  0x0000564890e0f4c3 in join_read_first (tab=0x7f1270053af8) at /10.2/src/sql/sql_select.cc:19884
 
#10 0x0000564890e0ce90 in sub_select (join=0x7f1270047458, join_tab=0x7f1270053af8, end_of_records=false) at /10.2/src/sql/sql_select.cc:18892
 
#11 0x0000564890e0c454 in do_select (join=0x7f1270047458, procedure=0x0) at /10.2/src/sql/sql_select.cc:18439
 
#12 0x0000564890de5f65 in JOIN::exec_inner (this=0x7f1270047458) at /10.2/src/sql/sql_select.cc:3651
 
#13 0x0000564890de540c in JOIN::exec (this=0x7f1270047458) at /10.2/src/sql/sql_select.cc:3446
 
#14 0x0000564890e8cc0b in st_select_lex_unit::exec (this=0x7f127018dc78) at /10.2/src/sql/sql_union.cc:1074
 
#15 0x0000564890e89236 in mysql_union (thd=0x7f1270000d90, lex=0x7f127018dbb8, result=0x7f127003af10, unit=0x7f127018dc78, setup_tables_done_option=0) at /10.2/src/sql/sql_union.cc:42
 
#16 0x0000564890dda656 in handle_select (thd=0x7f1270000d90, lex=0x7f127018dbb8, result=0x7f127003af10, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:351
 
#17 0x0000564890da4d9a in execute_sqlcom_select (thd=0x7f1270000d90, all_tables=0x7f127009d568) at /10.2/src/sql/sql_parse.cc:6271
 
#18 0x0000564890d9b90e in mysql_execute_command (thd=0x7f1270000d90) at /10.2/src/sql/sql_parse.cc:3582
 
#19 0x0000564890cec8fa in sp_instr_stmt::exec_core (this=0x7f1270094650, thd=0x7f1270000d90, nextp=0x7f128757f5ac) at /10.2/src/sql/sp_head.cc:3331
 
#20 0x0000564890cebf5b in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f1270094690, thd=0x7f1270000d90, nextp=0x7f128757f5ac, open_tables=false, instr=0x7f1270094650) at /10.2/src/sql/sp_head.cc:3094
 
#21 0x0000564890cec59f in sp_instr_stmt::execute (this=0x7f1270094650, thd=0x7f1270000d90, nextp=0x7f128757f5ac) at /10.2/src/sql/sp_head.cc:3247
 
#22 0x0000564890ce75a1 in sp_head::execute (this=0x7f1270037678, thd=0x7f1270000d90, merge_da_on_success=true) at /10.2/src/sql/sp_head.cc:1326
 
#23 0x0000564890ce97bf in sp_head::execute_procedure (this=0x7f1270037678, thd=0x7f1270000d90, args=0x7f12700056e0) at /10.2/src/sql/sp_head.cc:2202
 
#24 0x0000564890d99ce8 in do_execute_sp (thd=0x7f1270000d90, sp=0x7f1270037678) at /10.2/src/sql/sql_parse.cc:2981
 
#25 0x0000564890da2693 in mysql_execute_command (thd=0x7f1270000d90) at /10.2/src/sql/sql_parse.cc:5622
 
#26 0x0000564890da8b56 in mysql_parse (thd=0x7f1270000d90, rawbuf=0x7f1270012708 "CALL sp1()", length=10, parser_state=0x7f1287580560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
 
#27 0x0000564890d96db1 in dispatch_command (command=COM_QUERY, thd=0x7f1270000d90, packet=0x7f1270008b61 "CALL sp1()", packet_length=10, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
 
#28 0x0000564890d958ac in do_command (thd=0x7f1270000d90) at /10.2/src/sql/sql_parse.cc:1381
 
#29 0x0000564890ef167b in do_handle_one_connection (connect=0x5648936b43c0) at /10.2/src/sql/sql_connect.cc:1336
 
#30 0x0000564890ef13e0 in handle_one_connection (arg=0x5648936b43c0) at /10.2/src/sql/sql_connect.cc:1241
 
#31 0x000056489171bdbe in pfs_spawn_thread (arg=0x5648936975d0) at /10.2/src/storage/perfschema/pfs.cc:1869
 
#32 0x00007f1291a7a609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#33 0x00007f1291655293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

---------------------------------------------
2)

CREATE TABLE t1 (id int primary key);
 
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 
 
 
CREATE FUNCTION f() RETURNS int RETURN (SELECT 1 FROM t1 limit 1);
 
 
create  procedure sp1() 
WITH 
cte1 AS (SELECT 1 id FROM t1 a LEFT JOIN t1 t2 ON a.id=t2.id where f() ),
 
cte2 AS (SELECT 1 FROM cte1)
 
(SELECT  1 FROM cte2)  UNION  (SELECT 1 FROM cte2);
 
 
 
--error 1146
 
CALL sp1();
 
CALL sp1();

10.2 1d71dacd519a8ea51e7c8

 
#3  <signal handler called>
 
#4  0x000055fbfc0494de in setup_on_expr (thd=0x7fd804000d90, table=0x7fd80409f920, is_update=false) at /10.2/src/sql/sql_base.cc:7890
 
#5  0x000055fbfc0496db in setup_on_expr (thd=0x7fd804000d90, table=0x7fd80409eb28, is_update=false) at /10.2/src/sql/sql_base.cc:7915
 
#6  0x000055fbfc0496db in setup_on_expr (thd=0x7fd804000d90, table=0x7fd80409e470, is_update=false) at /10.2/src/sql/sql_base.cc:7915
 
#7  0x000055fbfc049ac6 in setup_conds (thd=0x7fd804000d90, tables=0x7fd80409e470, leaves=..., conds=0x7fd80403b8d0) at /10.2/src/sql/sql_base.cc:8014
 
#8  0x000055fbfc0f0113 in setup_without_group (thd=0x7fd804000d90, ref_pointer_array=..., tables=0x7fd80409e470, leaves=..., fields=..., all_fields=..., conds=0x7fd80403b8d0, order=0x0, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x7fd80403b7af, reserved=0x7fd80409dd34) at /10.2/src/sql/sql_select.cc:649
 
#9  0x000055fbfc0f0bb9 in JOIN::prepare (this=0x7fd80403b4c8, tables_init=0x7fd80409e470, wild_num=0, conds_init=0x7fd804094290, og_num=0, order_init=0x0, skip_order_by=true, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fd80409da70, unit_arg=0x7fd80418db38) at /10.2/src/sql/sql_select.cc:812
 
#10 0x000055fbfc19fd16 in st_select_lex_unit::prepare (this=0x7fd80418db38, thd_arg=0x7fd804000d90, sel_result=0x7fd80403acc8, additional_options=268435456) at /10.2/src/sql/sql_union.cc:597
 
#11 0x000055fbfc19e21c in mysql_union (thd=0x7fd804000d90, lex=0x7fd80418da78, result=0x7fd80403acc8, unit=0x7fd80418db38, setup_tables_done_option=0) at /10.2/src/sql/sql_union.cc:40
 
#12 0x000055fbfc0ef656 in handle_select (thd=0x7fd804000d90, lex=0x7fd80418da78, result=0x7fd80403acc8, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:351
 
#13 0x000055fbfc0b9d9a in execute_sqlcom_select (thd=0x7fd804000d90, all_tables=0x7fd80409d440) at /10.2/src/sql/sql_parse.cc:6271
 
#14 0x000055fbfc0b090e in mysql_execute_command (thd=0x7fd804000d90) at /10.2/src/sql/sql_parse.cc:3582
 
#15 0x000055fbfc0018fa in sp_instr_stmt::exec_core (this=0x7fd8040943c0, thd=0x7fd804000d90, nextp=0x7fd8153825ac) at /10.2/src/sql/sp_head.cc:3331
 
#16 0x000055fbfc000f5b in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fd804094400, thd=0x7fd804000d90, nextp=0x7fd8153825ac, open_tables=false, instr=0x7fd8040943c0) at /10.2/src/sql/sp_head.cc:3094
 
#17 0x000055fbfc00159f in sp_instr_stmt::execute (this=0x7fd8040943c0, thd=0x7fd804000d90, nextp=0x7fd8153825ac) at /10.2/src/sql/sp_head.cc:3247
 
#18 0x000055fbfbffc5a1 in sp_head::execute (this=0x7fd804037678, thd=0x7fd804000d90, merge_da_on_success=true) at /10.2/src/sql/sp_head.cc:1326
 
#19 0x000055fbfbffe7bf in sp_head::execute_procedure (this=0x7fd804037678, thd=0x7fd804000d90, args=0x7fd8040056e0) at /10.2/src/sql/sp_head.cc:2202
 
#20 0x000055fbfc0aece8 in do_execute_sp (thd=0x7fd804000d90, sp=0x7fd804037678) at /10.2/src/sql/sql_parse.cc:2981
 
#21 0x000055fbfc0b7693 in mysql_execute_command (thd=0x7fd804000d90) at /10.2/src/sql/sql_parse.cc:5622
 
#22 0x000055fbfc0bdb56 in mysql_parse (thd=0x7fd804000d90, rawbuf=0x7fd804012708 "CALL sp1()", length=10, parser_state=0x7fd815383560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
 
#23 0x000055fbfc0abdb1 in dispatch_command (command=COM_QUERY, thd=0x7fd804000d90, packet=0x7fd804008b61 "CALL sp1()", packet_length=10, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
 
#24 0x000055fbfc0aa8ac in do_command (thd=0x7fd804000d90) at /10.2/src/sql/sql_parse.cc:1381
 
#25 0x000055fbfc20667b in do_handle_one_connection (connect=0x55fbff9063c0) at /10.2/src/sql/sql_connect.cc:1336
 
#26 0x000055fbfc2063e0 in handle_one_connection (arg=0x55fbff9063c0) at /10.2/src/sql/sql_connect.cc:1241
 
#27 0x000055fbfca30dbe in pfs_spawn_thread (arg=0x55fbff8e95d0) at /10.2/src/storage/perfschema/pfs.cc:1869
 
#28 0x00007fd81b85f609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#29 0x00007fd81b43a293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

-------------------------------------
3)

 
 
CREATE TABLE t1 (id int primary key);
 
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 
 
 
CREATE FUNCTION f() RETURNS int RETURN (SELECT 1 FROM t1 limit 1);
 
 
create  procedure sp1() 
WITH 
cte1 AS (SELECT 1 id FROM t1 a  JOIN t1 t2 ON a.id=t2.id where f() ),
 
cte2 AS (SELECT 1 FROM cte1)
 
(SELECT  1 FROM cte2)  UNION  (SELECT 1 FROM cte2);
 
 
 
--error 1146
 
CALL sp1();
 
CALL sp1();

10.2 1d71dacd519a8ea51e7c8

 
#3  <signal handler called>
 
#4  0x00005649d622bc47 in optimizer_flag (thd=0x0, flag=134217728) at /10.2/src/sql/sql_select.h:2225
 
#5  0x00005649d62b5531 in TABLE::actual_n_key_parts (this=0x7f7634123630, keyinfo=0x7f7634191718) at /10.2/src/sql/table.cc:7318
 
#6  0x00005649d61ed9c1 in add_key_part (keyuse_array=0x7f763403ba18, key_field=0x7f7634049278) at /10.2/src/sql/sql_select.cc:5539
 
#7  0x00005649d61eec22 in update_ref_and_keys (thd=0x7f7634000d90, keyuse=0x7f763403ba18, join_tab=0x7f763404a148, tables=2, cond=0x7f763403ab70, normal_tables=18446744073709551615, select_lex=0x7f763409da48, sargables=0x7f76461860b8) at /10.2/src/sql/sql_select.cc:5893
 
#8  0x00005649d61e9562 in make_join_statistics (join=0x7f763403b728, tables_list=..., keyuse_array=0x7f763403ba18) at /10.2/src/sql/sql_select.cc:4139
 
#9  0x00005649d61e0b09 in JOIN::optimize_inner (this=0x7f763403b728) at /10.2/src/sql/sql_select.cc:1597
 
#10 0x00005649d61df000 in JOIN::optimize (this=0x7f763403b728) at /10.2/src/sql/sql_select.cc:1127
 
#11 0x00005649d628e61d in st_select_lex_unit::optimize (this=0x7f763418dc78) at /10.2/src/sql/sql_union.cc:988
 
#12 0x00005649d628e855 in st_select_lex_unit::exec (this=0x7f763418dc78) at /10.2/src/sql/sql_union.cc:1022
 
#13 0x00005649d628b236 in mysql_union (thd=0x7f7634000d90, lex=0x7f763418dbb8, result=0x7f763403af28, unit=0x7f763418dc78, setup_tables_done_option=0) at /10.2/src/sql/sql_union.cc:42
 
#14 0x00005649d61dc656 in handle_select (thd=0x7f7634000d90, lex=0x7f763418dbb8, result=0x7f763403af28, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:351
 
#15 0x00005649d61a6d9a in execute_sqlcom_select (thd=0x7f7634000d90, all_tables=0x7f763409d418) at /10.2/src/sql/sql_parse.cc:6271
 
#16 0x00005649d619d90e in mysql_execute_command (thd=0x7f7634000d90) at /10.2/src/sql/sql_parse.cc:3582
 
#17 0x00005649d60ee8fa in sp_instr_stmt::exec_core (this=0x7f76340943b8, thd=0x7f7634000d90, nextp=0x7f76461875ac) at /10.2/src/sql/sp_head.cc:3331
 
#18 0x00005649d60edf5b in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f76340943f8, thd=0x7f7634000d90, nextp=0x7f76461875ac, open_tables=false, instr=0x7f76340943b8) at /10.2/src/sql/sp_head.cc:3094
 
#19 0x00005649d60ee59f in sp_instr_stmt::execute (this=0x7f76340943b8, thd=0x7f7634000d90, nextp=0x7f76461875ac) at /10.2/src/sql/sp_head.cc:3247
 
#20 0x00005649d60e95a1 in sp_head::execute (this=0x7f7634037678, thd=0x7f7634000d90, merge_da_on_success=true) at /10.2/src/sql/sp_head.cc:1326
 
#21 0x00005649d60eb7bf in sp_head::execute_procedure (this=0x7f7634037678, thd=0x7f7634000d90, args=0x7f76340056e0) at /10.2/src/sql/sp_head.cc:2202
 
#22 0x00005649d619bce8 in do_execute_sp (thd=0x7f7634000d90, sp=0x7f7634037678) at /10.2/src/sql/sql_parse.cc:2981
 
#23 0x00005649d61a4693 in mysql_execute_command (thd=0x7f7634000d90) at /10.2/src/sql/sql_parse.cc:5622
 
#24 0x00005649d61aab56 in mysql_parse (thd=0x7f7634000d90, rawbuf=0x7f7634012708 "CALL sp1()", length=10, parser_state=0x7f7646188560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
 
#25 0x00005649d6198db1 in dispatch_command (command=COM_QUERY, thd=0x7f7634000d90, packet=0x7f7634008b61 "CALL sp1()", packet_length=10, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
 
#26 0x00005649d61978ac in do_command (thd=0x7f7634000d90) at /10.2/src/sql/sql_parse.cc:1381
 
#27 0x00005649d62f367b in do_handle_one_connection (connect=0x5649d95543c0) at /10.2/src/sql/sql_connect.cc:1336
 
#28 0x00005649d62f33e0 in handle_one_connection (arg=0x5649d95543c0) at /10.2/src/sql/sql_connect.cc:1241
 
#29 0x00005649d6b1ddbe in pfs_spawn_thread (arg=0x5649d95375d0) at /10.2/src/storage/perfschema/pfs.cc:1869
 
#30 0x00007f764c664609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#31 0x00007f764c23f293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

------------------------
4) 

CREATE TABLE t1 (id int primary key);
 
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 
 
 
CREATE FUNCTION f() RETURNS int RETURN (SELECT 1 FROM t1 limit 1);
 
 
create  procedure sp1() 
WITH 
cte1 AS (SELECT 1 id FROM t1 a  JOIN t1 t2 ON t2.id<>0 where f() ),
 
cte2 AS (SELECT 1 FROM cte1)
 
(SELECT  1 FROM cte2)  UNION  (SELECT 1 FROM cte2);
 
 
 
--error 1146
 
CALL sp1();
 
CALL sp1();

10.2 1d71dacd519a8ea51e7c8

 
#3  <signal handler called>
 
#4  make_select (head=0x7fc1bc123630, const_tables=0, read_tables=0, conds=0x7fc1bc03ab68, filesort=0x0, allow_null_cond=true, error=0x7fc1d843d05c) at /10.2/src/sql/opt_range.cc:1178
 
#5  0x000056050778fbed in make_join_statistics (join=0x7fc1bc03b720, tables_list=..., keyuse_array=0x7fc1bc03ba10) at /10.2/src/sql/sql_select.cc:4494
 
#6  0x0000560507785b09 in JOIN::optimize_inner (this=0x7fc1bc03b720) at /10.2/src/sql/sql_select.cc:1597
 
#7  0x0000560507784000 in JOIN::optimize (this=0x7fc1bc03b720) at /10.2/src/sql/sql_select.cc:1127
 
#8  0x000056050783361d in st_select_lex_unit::optimize (this=0x7fc1bc18dc78) at /10.2/src/sql/sql_union.cc:988
 
#9  0x0000560507833855 in st_select_lex_unit::exec (this=0x7fc1bc18dc78) at /10.2/src/sql/sql_union.cc:1022
 
#10 0x0000560507830236 in mysql_union (thd=0x7fc1bc000d90, lex=0x7fc1bc18dbb8, result=0x7fc1bc03af20, unit=0x7fc1bc18dc78, setup_tables_done_option=0) at /10.2/src/sql/sql_union.cc:42
 
#11 0x0000560507781656 in handle_select (thd=0x7fc1bc000d90, lex=0x7fc1bc18dbb8, result=0x7fc1bc03af20, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:351
 
#12 0x000056050774bd9a in execute_sqlcom_select (thd=0x7fc1bc000d90, all_tables=0x7fc1bc09d380) at /10.2/src/sql/sql_parse.cc:6271
 
#13 0x000056050774290e in mysql_execute_command (thd=0x7fc1bc000d90) at /10.2/src/sql/sql_parse.cc:3582
 
#14 0x00005605076938fa in sp_instr_stmt::exec_core (this=0x7fc1bc094298, thd=0x7fc1bc000d90, nextp=0x7fc1d843e5ac) at /10.2/src/sql/sp_head.cc:3331
 
#15 0x0000560507692f5b in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fc1bc0942d8, thd=0x7fc1bc000d90, nextp=0x7fc1d843e5ac, open_tables=false, instr=0x7fc1bc094298) at /10.2/src/sql/sp_head.cc:3094
 
#16 0x000056050769359f in sp_instr_stmt::execute (this=0x7fc1bc094298, thd=0x7fc1bc000d90, nextp=0x7fc1d843e5ac) at /10.2/src/sql/sp_head.cc:3247
 
#17 0x000056050768e5a1 in sp_head::execute (this=0x7fc1bc037678, thd=0x7fc1bc000d90, merge_da_on_success=true) at /10.2/src/sql/sp_head.cc:1326
 
#18 0x00005605076907bf in sp_head::execute_procedure (this=0x7fc1bc037678, thd=0x7fc1bc000d90, args=0x7fc1bc0056e0) at /10.2/src/sql/sp_head.cc:2202
 
#19 0x0000560507740ce8 in do_execute_sp (thd=0x7fc1bc000d90, sp=0x7fc1bc037678) at /10.2/src/sql/sql_parse.cc:2981
 
#20 0x0000560507749693 in mysql_execute_command (thd=0x7fc1bc000d90) at /10.2/src/sql/sql_parse.cc:5622
 
#21 0x000056050774fb56 in mysql_parse (thd=0x7fc1bc000d90, rawbuf=0x7fc1bc012708 "CALL sp1()", length=10, parser_state=0x7fc1d843f560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
 
#22 0x000056050773ddb1 in dispatch_command (command=COM_QUERY, thd=0x7fc1bc000d90, packet=0x7fc1bc008b61 "CALL sp1()", packet_length=10, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
 
#23 0x000056050773c8ac in do_command (thd=0x7fc1bc000d90) at /10.2/src/sql/sql_parse.cc:1381
 
#24 0x000056050789867b in do_handle_one_connection (connect=0x56050a5103c0) at /10.2/src/sql/sql_connect.cc:1336
 
#25 0x00005605078983e0 in handle_one_connection (arg=0x56050a5103c0) at /10.2/src/sql/sql_connect.cc:1241
 
#26 0x00005605080c2dbe in pfs_spawn_thread (arg=0x56050a4f35d0) at /10.2/src/storage/perfschema/pfs.cc:1869
 
#27 0x00007fc1dd975609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#28 0x00007fc1dd550293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

5) 

CREATE TABLE t1 (id int primary key);
 
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 
 
 
CREATE FUNCTION f() RETURNS int RETURN (SELECT 1 FROM t1 limit 1);
 
 
create  procedure sp1() 
WITH 
cte1 AS (SELECT f() FROM t1 a JOIN t1 t2 ON t2.id>0 and a.id>1),
 
cte2 AS (SELECT 1 id FROM cte1)
 
(SELECT  id FROM cte2 )  UNION  all (SELECT id FROM cte2 p1);
 
 
 
--error 1146
 
CALL sp1();
 
CALL sp1();

Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed in handler::ha_index_read_map

10.2 1d71dacd519a8ea51e7c8

 
#3  <signal handler called>
 
#4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#5  0x00007fc1dad49859 in __GI_abort () at abort.c:79
 
#6  0x00007fc1dad49729 in __assert_fail_base (fmt=0x7fc1daedf588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x5576ba926d10 "table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2", file=0x5576ba9259b0 "/10.2/src/sql/handler.cc", line=2714, function=<optimized out>) at assert.c:92
 
#7  0x00007fc1dad5af36 in __GI___assert_fail (assertion=0x5576ba926d10 "table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2", file=0x5576ba9259b0 "/10.2/src/sql/handler.cc", line=2714, function=0x5576ba926db8 "int handler::ha_index_read_map(uchar*, const uchar*, key_part_map, ha_rkey_function)") at assert.c:101
 
#8  0x00005576ba034a54 in handler::ha_index_read_map (this=0x7fc1c41911c8, buf=0x7fc1c403a0a0 "\377", key=0x7fc1c4058658 "\001", keypart_map=1, find_flag=HA_READ_AFTER_KEY) at /10.2/src/sql/handler.cc:2714
 
#9  0x00005576ba03bba8 in handler::read_range_first (this=0x7fc1c41911c8, start_key=0x7fc1c41912b0, end_key=0x0, eq_range_arg=false, sorted=true) at /10.2/src/sql/handler.cc:5483
 
#10 0x00005576b9f4bb9b in handler::multi_range_read_next (this=0x7fc1c41911c8, range_info=0x7fc1d4d8d1e0) at /10.2/src/sql/multi_range_read.cc:298
 
#11 0x00005576b9f4bcdd in Mrr_simple_index_reader::get_next (this=0x7fc1c4191760, range_info=0x7fc1d4d8d1e0) at /10.2/src/sql/multi_range_read.cc:335
 
#12 0x00005576b9f4e926 in DsMrr_impl::dsmrr_next (this=0x7fc1c4191620, range_info=0x7fc1d4d8d1e0) at /10.2/src/sql/multi_range_read.cc:1411
 
#13 0x00005576ba68e1ba in ha_myisam::multi_range_read_next (this=0x7fc1c41911c8, range_info=0x7fc1d4d8d1e0) at /10.2/src/storage/myisam/ha_myisam.cc:2486
 
#14 0x00005576ba19629b in QUICK_RANGE_SELECT::get_next (this=0x7fc1c40583f0) at /10.2/src/sql/opt_range.cc:11517
 
#15 0x00005576ba1a7d4d in rr_quick (info=0x7fc1c40554c0) at /10.2/src/sql/records.cc:373
 
#16 0x00005576b9e1c151 in join_init_read_record (tab=0x7fc1c40553f8) at /10.2/src/sql/sql_select.cc:19821
 
#17 0x00005576b9e19e90 in sub_select (join=0x7fc1c4047458, join_tab=0x7fc1c40553f8, end_of_records=false) at /10.2/src/sql/sql_select.cc:18892
 
#18 0x00005576b9e19454 in do_select (join=0x7fc1c4047458, procedure=0x0) at /10.2/src/sql/sql_select.cc:18439
 
#19 0x00005576b9df2f65 in JOIN::exec_inner (this=0x7fc1c4047458) at /10.2/src/sql/sql_select.cc:3651
 
#20 0x00005576b9df240c in JOIN::exec (this=0x7fc1c4047458) at /10.2/src/sql/sql_select.cc:3446
 
#21 0x00005576b9e99c0b in st_select_lex_unit::exec (this=0x7fc1c418dc78) at /10.2/src/sql/sql_union.cc:1074
 
#22 0x00005576b9e96236 in mysql_union (thd=0x7fc1c4000d90, lex=0x7fc1c418dbb8, result=0x7fc1c403af18, unit=0x7fc1c418dc78, setup_tables_done_option=0) at /10.2/src/sql/sql_union.cc:42
 
#23 0x00005576b9de7656 in handle_select (thd=0x7fc1c4000d90, lex=0x7fc1c418dbb8, result=0x7fc1c403af18, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:351
 
#24 0x00005576b9db1d9a in execute_sqlcom_select (thd=0x7fc1c4000d90, all_tables=0x7fc1c409d810) at /10.2/src/sql/sql_parse.cc:6271
 
#25 0x00005576b9da890e in mysql_execute_command (thd=0x7fc1c4000d90) at /10.2/src/sql/sql_parse.cc:3582
 
#26 0x00005576b9cf98fa in sp_instr_stmt::exec_core (this=0x7fc1c411fb08, thd=0x7fc1c4000d90, nextp=0x7fc1d4d8e5ac) at /10.2/src/sql/sp_head.cc:3331
 
#27 0x00005576b9cf8f5b in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fc1c411fb48, thd=0x7fc1c4000d90, nextp=0x7fc1d4d8e5ac, open_tables=false, instr=0x7fc1c411fb08) at /10.2/src/sql/sp_head.cc:3094
 
#28 0x00005576b9cf959f in sp_instr_stmt::execute (this=0x7fc1c411fb08, thd=0x7fc1c4000d90, nextp=0x7fc1d4d8e5ac) at /10.2/src/sql/sp_head.cc:3247
 
#29 0x00005576b9cf45a1 in sp_head::execute (this=0x7fc1c4037678, thd=0x7fc1c4000d90, merge_da_on_success=true) at /10.2/src/sql/sp_head.cc:1326
 
#30 0x00005576b9cf67bf in sp_head::execute_procedure (this=0x7fc1c4037678, thd=0x7fc1c4000d90, args=0x7fc1c40056e0) at /10.2/src/sql/sp_head.cc:2202
 
#31 0x00005576b9da6ce8 in do_execute_sp (thd=0x7fc1c4000d90, sp=0x7fc1c4037678) at /10.2/src/sql/sql_parse.cc:2981
 
#32 0x00005576b9daf693 in mysql_execute_command (thd=0x7fc1c4000d90) at /10.2/src/sql/sql_parse.cc:5622
 
#33 0x00005576b9db5b56 in mysql_parse (thd=0x7fc1c4000d90, rawbuf=0x7fc1c4012708 "CALL sp1()", length=10, parser_state=0x7fc1d4d8f560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
 
#34 0x00005576b9da3db1 in dispatch_command (command=COM_QUERY, thd=0x7fc1c4000d90, packet=0x7fc1c4008b61 "", packet_length=10, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
 
#35 0x00005576b9da28ac in do_command (thd=0x7fc1c4000d90) at /10.2/src/sql/sql_parse.cc:1381
 
#36 0x00005576b9efe67b in do_handle_one_connection (connect=0x5576bc7a43c0) at /10.2/src/sql/sql_connect.cc:1336
 
#37 0x00005576b9efe3e0 in handle_one_connection (arg=0x5576bc7a43c0) at /10.2/src/sql/sql_connect.cc:1241
 
#38 0x00005576ba728dbe in pfs_spawn_thread (arg=0x5576bc7875d0) at /10.2/src/storage/perfschema/pfs.cc:1869
 
#39 0x00007fc1db26b609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#40 0x00007fc1dae46293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Comment by Igor Babaev [ 2021-10-27 ]

Let's consider the query:

with c1 as (select id from t1 where id = id * id group by id), 
        c2 as (select id from c1 as pt group by id)
 
select id from c2 as s1 union select id from c2 as s2;

If in debugger we look at the list LEX::query_tables right after parsing we see that it contains the TABLE_LIST objects for
s1,t1(1),pt(1),s2,pt(2),t1(2). These objects are linked into a chain through the field TABLE_LIST::next_global with the first element for s1 and the last element for t1(2). At the same time we see that LEX::query_tables_last does not point to the address of the TABLE_LIST::next_global for the last element. Rather it points to the TABLE_LIST::next_global for the previous element pt(2). This inconsistency is not good though it does not cause any problem for the execution of the query.

Comment by Igor Babaev [ 2021-10-28 ]

Yet if we try to execute the query

with c1 as (select id from t1 where f(id)=id group by id), 
        c2 as (select id from c1 as pt group by id)
 
select id from c2 as s1 union select id from c2 as s2;

that uses the stored function

create  function f(in_id int) returns integer
 
return (select id from t1 where t1.id = in_id);

the above inconsistency brings us to an assertion failure.
Let's figure out how it happens.
So after parsing LEX::query_tables starts the chain of TABLE_LIST objects in the following order:
first the object for s1, then for t1(1), pt(1), s2, pt(2) and lastly the object for t1(2). Yet LEX::query_tables_last points to
TABLE_LIST::next_global in the object for pt(2) rather than for in the last of object of the chain. Bare in mind that at this moment all names to CTE (s1, pt(1), s2, pt(2) has been already resolved.
1. when executing open_tables()
1.1. for each elements from the chain LEX::query_tables open_and_process_table() is called
1.1.1. open_table() is called for t1(1)
1.1.2. open_table() is called for t1(2)
1.2 . open_and_process_routine() is called for each element from the list thd->lex->sroutines_list (with only one element)
1.2.1 open_table() is called for t1(SF) used in the stored function.
1..2.2. add_used_tables_to_table_list inserts the TABLE_LIST object for t1(SF) after the TABLE_LIST for pt(2) as the last element in the chain LEX::query_tables. The chain looks like this now s1, then for t1(1), pt(1), s2, pt(2), t1(SF).
2. lock_tables() is called for the chain s1, t1(1), pt(1), s2, pt(2), t1(SF). It locks TABLE_LIST::table for t1(1), but does not lock TABLE_LIST::table for t1(2) as the latter is out of the chain.
3. st_select_lex_unit::optimize() is called for the query. It sets TABLE_LIST::table->file->keyread for t1(1) and TABLE_LIST::table->file->keyread for t1(2) to 0 (the number of the index to be used when scanning the tables t1(1) and t1(2)).
4. st_select_lex_unit::exec() is called for the query. The execution comes to the evaluation of the condition f(id)=id.
4.1. open_table() is called for t1(SF) It sees that TABLE_LIST::table for t1(2) is not locked for usage and takes it to be used for t1(SF). When calling TABLE::init() it is noticed that TABLE_LIST::table->file->keyread for t1(2) is set to 0 while it should have been set to 64.

Comment by Igor Babaev [ 2021-10-28 ]

Let's consider a slightly different query when the specification of c1 uses an alias for the table reference t1.

with c1 as (select id from t1 as r where f(id)=id group by id), 
        c2 as (select id from c1 as pt group by id)
 
select id from c2 as s1 union select id from c2 as s2;

After parsing we have the same inconsistency as for the previous query. LEX::query_tables points to the chain of TABLE_LIST objects for s1,r(1),pt(1),s2,pt(2),r(2) while LEX::query_tables_last points to TABLE_LIST::next_global for pt(2).
When opening table for t1(SF) we come to the lines: 

1686     /*
 
1687       No table in the locked tables list. In case of explicit LOCK TABLES
 
1688       this can happen if a user did not include the table into the list.
 
1689       In case of pre-locked mode locked tables list is generated automatically,
 
1690       so we may only end up here if the table did not exist when
 
1691       locked tables list was created.
 
1692     */
 
1693     if (thd->locked_tables_mode == LTM_PRELOCKED)
 
1694       my_error(ER_NO_SUCH_TABLE, MYF(0), table_list->db, table_list->alias);
 
1695     else
 
1696       my_error(ER_TABLE_NOT_LOCKED, MYF(0), alias);

because alias for t1(SF) differs from 'r'.
Here the server reports the error message:

Table 'test.t1' doesn't exist

Comment by Igor Babaev [ 2021-10-29 ]

Let's figure out how we get the above mentioned inconsistency for the query

with c1 as (select id from t1 where id = id * id group by id), 
        c2 as (select id from c1 as pt group by id)
 
select id from c2 as s1 union select id from c2 as s2;

At the moment when we have just finished parsing the query remaining still within the parser we call LEX::resolve_references_to_cte() with the chain LEX::query_tables containing TABLE_LIST objects for t1(1), pt(1), s1, s2. pt() and s1 are resolved against the selects specifying c1 and c2 correspondingly without any problem. Yet to resolve s2 we have to build a new copy of the select specifying c2. So we call With_element::clone_parsed_spec() with LEX for the main query as the first parameter old_lex and the TABLE_LIST object for s2 as the second parameter. At this moment s2 is already resolved against CTE c2.The function takes the specification. When parsing is finished LEX::query_tables contains only the TABLE_LIST object for pt(2). LEX::resolve_references_to_cte() is called for this chain. Yet it fails to resolve pt(2). Then we see that the chain pointing to the TABLE_LIST object for pt(2) is appended to the chain t1(1), pt(1), s1, s2. pt(2). After this LEX::resolve_references_to_cte() is called for the chain starting from pt(2). The call resolves pt(2) against c1, sees that this is the second usage of c1 and calls With_element::clone_parsed_spec to create a copy of the specification of c1 for pt(2). This call parses the specification of c1, gets the chain containing the TABLE_LIST object for t1(2) and calls LEX::resolve_references_to_cte() that does not resolve anything. After this the chain with the TABLE_LIST object for t1(2) is inserted after the TABLE_LIST object for pt(1) and in this way it appears in the chain LEX->query_tables for the main query. The function also updates LEX::query_tables_last, but it does it in the LEX structure created for the copy of the specification of c2. So LEX::query_tables_last used for the main query is not updated and remains the same as it was after the last update.

Comment by Oleksandr Byelkin [ 2021-11-16 ]

OK to push

Comment by Igor Babaev [ 2021-11-17 ]

A fix for this bug was pushed into 10.2. It should be merged upstream as it is.

Generated at Thu Feb 08 09:48:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.