Alice Sherepa added a comment - 2017-09-12 09:35 Don Lindsay, thanks for the report. It is reproducible on Mariadb 5.5,10.0,10.1,10.2,10.3, with innodb and myisam test case CREATE TABLE `bugs` (`bug_id` int NOT NULL PRIMARY KEY , `product_id` int NOT NULL ); INSERT INTO `bugs` VALUES (45199,1184);   CREATE TABLE `maintainer` (`product_id` int NOT NULL ,`userid` int NOT NULL , PRIMARY KEY (`product_id`,`userid`)); INSERT INTO `maintainer` VALUES (1184,103),(1184,624),(1184,1577),(1184,1582);   CREATE TABLE `product_groups` (`id` int NOT NULL PRIMARY KEY ,` name ` varchar (64)); CREATE TABLE `products` (`id` int NOT NULL PRIMARY KEY , ` name ` varchar (64));   CREATE TABLE `profiles` ( `userid` int NOT NULL PRIMARY KEY , `login_name` varchar (255)); INSERT INTO `profiles` VALUES (103, 'foo' ),(624, 'foo' ),(1577, 'foo' ),(1582, 'foo' );   select ( select login_name from profiles where userId = ( select userid from maintainer where product_id = bugs.product_id union select userid from maintainer where product_id = ( select id from products where name = ( select name from product_groups where id = bugs.product_id)) limit 1 ) ) from bugs where (bugs.bug_id=45199); explain extended select ( select login_name from profiles where userId = ( select userid from maintainer where product_id = bugs.product_id union select userid from maintainer where product_id = ( select id from products where name = ( select name from product_groups where id = bugs.product_id)) limit 1 ) ) from bugs where (bugs.bug_id=45199); + ------+--------------------+------------+--------+---------------+---------+---------+-------+------+----------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+--------------------+------------+--------+---------------+---------+---------+-------+------+----------+-----------------------------------------------------+ | 1 | PRIMARY | bugs | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | | | 2 | DEPENDENT SUBQUERY | profiles | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where | | 3 | SUBQUERY | maintainer | ref | PRIMARY | PRIMARY | 4 | const | 4 | 100.00 | Using index | | 4 | DEPENDENT UNION | maintainer | ref | PRIMARY | PRIMARY | 4 | func | 2 | 100.00 | Using where ; Using index | | 5 | DEPENDENT SUBQUERY | products | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 6 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | | NULL | UNION RESULT | <union3,4> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | | + ------+--------------------+------------+--------+---------------+---------+---------+-------+------+----------+-----------------------------------------------------+ 7 rows in set , 3 warnings (0.00 sec)   Note (Code 1276): Field or reference 'test.bugs.product_id' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.bugs.product_id' of SELECT #6 was resolved in SELECT #1 Note (Code 1003): select <expr_cache><>(( select `test`.`profiles`.`login_name` from `test`.`profiles` where `test`.`profiles`.`userid` = <expr_cache><>(( select `test`.`maintainer`.`userid` from `test`.`maintainer` where `test`.`maintainer`.`product_id` = 1184 union select `test`.`maintainer`.`userid` from `test`.`maintainer` where `test`.`maintainer`.`product_id` = <expr_cache><>(( select `test`.`products`.`id` from `test`.`products` where `test`.`products`.` name ` = ( select NULL from `test`.`product_groups` where 0))) limit 1)))) AS `( select login_name from profiles where userId = ( select userid from maintainer where product_id = bugs.product_id union select userid from maintainer where product_id = ( select id from products where name = ( select name from product_` from `test`.`bugs` where 1 stack trace from 10.2.9-debug Thread 1 (Thread 0x7fa0c828a700 (LWP 19408)): #0 0x0000007ec49f4d71 in join_read_next_same (info=0x7fa058096a58) at /home/alice/git/10.2/sql/sql_select.cc:19378 #1 0x0000007ec49f322b in sub_select (join=0x7fa05808c110, join_tab=0x7fa058096990, end_of_records=false) at /home/alice/git/10.2/sql/sql_select.cc:18582 #2 0x0000007ec49f26f2 in do_select (join=0x7fa05808c110, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18107 #3 0x0000007ec49cd2ad in JOIN::exec_inner (this=0x7fa05808c110) at /home/alice/git/10.2/sql/sql_select.cc:3483 #4 0x0000007ec49cc74e in JOIN::exec (this=0x7fa05808c110) at /home/alice/git/10.2/sql/sql_select.cc:3278 #5 0x0000007ec4a69fdb in st_select_lex_unit::exec (this=0x7fa058077a70) at /home/alice/git/10.2/sql/sql_union.cc:1005 #6 0x0000007ec4cc162d in subselect_union_engine::exec (this=0x7fa058010670) at /home/alice/git/10.2/sql/item_subselect.cc:3888 #7 0x0000007ec4cb64af in Item_subselect::exec (this=0x7fa05807cea0) at /home/alice/git/10.2/sql/item_subselect.cc:716 #8 0x0000007ec4cb818d in Item_singlerow_subselect::val_int (this=0x7fa05807cea0) at /home/alice/git/10.2/sql/item_subselect.cc:1318 #9 0x0000007ec48c036f in Item::val_int_result (this=0x7fa05807cea0) at /home/alice/git/10.2/sql/item.h:1178 #10 0x0000007ec4c20cd5 in Item_cache_int::cache_value (this=0x7fa05809fb40) at /home/alice/git/10.2/sql/item.cc:9552 #11 0x0000007ec4c2aa9c in Item_cache_wrapper::cache (this=0x7fa05809fa50) at /home/alice/git/10.2/sql/item.cc:8275 #12 0x0000007ec4c1cf3a in Item_cache_wrapper::val_int (this=0x7fa05809fa50) at /home/alice/git/10.2/sql/item.cc:8329 #13 0x0000007ec4c31240 in Arg_comparator::compare_int_signed (this=0x7fa05807d0f0) at /home/alice/git/10.2/sql/item_cmpfunc.cc:989 #14 0x0000007ec4c44254 in Arg_comparator::compare (this=0x7fa05807d0f0) at /home/alice/git/10.2/sql/item_cmpfunc.h:87 #15 0x0000007ec4c3356f in Item_func_eq::val_int (this=0x7fa05807d030) at /home/alice/git/10.2/sql/item_cmpfunc.cc:1783 #16 0x0000007ec49f352c in evaluate_join_record (join=0x7fa05808b8e0, join_tab=0x7fa058094420, error=0) at /home/alice/git/10.2/sql/sql_select.cc:18658 #17 0x0000007ec49f315e in sub_select (join=0x7fa05808b8e0, join_tab=0x7fa058094420, end_of_records=false) at /home/alice/git/10.2/sql/sql_select.cc:18563 #18 0x0000007ec49f26f2 in do_select (join=0x7fa05808b8e0, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18107 #19 0x0000007ec49cd2ad in JOIN::exec_inner (this=0x7fa05808b8e0) at /home/alice/git/10.2/sql/sql_select.cc:3483 #20 0x0000007ec49cc74e in JOIN::exec (this=0x7fa05808b8e0) at /home/alice/git/10.2/sql/sql_select.cc:3278 #21 0x0000007ec4cc13e6 in subselect_single_select_engine::exec (this=0x7fa058046b20) at /home/alice/git/10.2/sql/item_subselect.cc:3861 #22 0x0000007ec4cb64af in Item_subselect::exec (this=0x7fa05807d1d0) at /home/alice/git/10.2/sql/item_subselect.cc:716 #23 0x0000007ec4cb82a5 in Item_singlerow_subselect::val_str (this=0x7fa05807d1d0, str=0x7fa0580a08a8) at /home/alice/git/10.2/sql/item_subselect.cc:1335 #24 0x0000007ec48c03a2 in Item::str_result (this=0x7fa05807d1d0, tmp=0x7fa0580a08a8) at /home/alice/git/10.2/sql/item.h:1179 #25 0x0000007ec4c223c0 in Item_cache_str::cache_value (this=0x7fa0580a07a0) at /home/alice/git/10.2/sql/item.cc:9923 #26 0x0000007ec4c2aa9c in Item_cache_wrapper::cache (this=0x7fa0580a06b0) at /home/alice/git/10.2/sql/item.cc:8275 #27 0x0000007ec4c1d22e in Item_cache_wrapper::val_str (this=0x7fa0580a06b0, str=0x7fa0c8287a60) at /home/alice/git/10.2/sql/item.cc:8383 #28 0x0000007ec4c18e09 in Item::send (this=0x7fa0580a06b0, protocol=0x7fa058001030, buffer=0x7fa0c8287a60) at /home/alice/git/10.2/sql/item.cc:6889 #29 0x0000007ec4c1c99e in Item_cache_wrapper::send (this=0x7fa0580a06b0, protocol=0x7fa058001030, buffer=0x7fa0c8287a60) at /home/alice/git/10.2/sql/item.cc:8181 #30 0x0000007ec48bc1db in Protocol::send_result_set_row (this=0x7fa058001030, row_items=0x7fa058004ec0) at /home/alice/git/10.2/sql/protocol.cc:979 #31 0x0000007ec493ddb6 in select_send::send_data (this=0x7fa05808b120, items=...) at /home/alice/git/10.2/sql/sql_class.cc:2774 #32 0x0000007ec49f5cfa in end_send (join=0x7fa05808b170, join_tab=0x0, end_of_records=false) at /home/alice/git/10.2/sql/sql_select.cc:19726 #33 0x0000007ec49f2447 in do_select (join=0x7fa05808b170, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18064 #34 0x0000007ec49cd2ad in JOIN::exec_inner (this=0x7fa05808b170) at /home/alice/git/10.2/sql/sql_select.cc:3483 #35 0x0000007ec49cc74e in JOIN::exec (this=0x7fa05808b170) at /home/alice/git/10.2/sql/sql_select.cc:3278 #36 0x0000007ec49cd91e in mysql_select (thd=0x7fa058000a98, tables=0x7fa05807d360, wild_num=0, fields=..., conds=0x7fa05807dad0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fa05808b120, unit=0x7fa058004638, select_lex=0x7fa058004d90) at /home/alice/git/10.2/sql/sql_select.cc:3678 #37 0x0000007ec49c21c9 in handle_select (thd=0x7fa058000a98, lex=0x7fa058004570, result=0x7fa05808b120, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:373 #38 0x0000007ec498e1b0 in execute_sqlcom_select (thd=0x7fa058000a98, all_tables=0x7fa05807d360) at /home/alice/git/10.2/sql/sql_parse.cc:6446 #39 0x0000007ec4983eb1 in mysql_execute_command (thd=0x7fa058000a98) at /home/alice/git/10.2/sql/sql_parse.cc:3460 #40 0x0000007ec4991b29 in mysql_parse (thd=0x7fa058000a98, rawbuf=0x7fa05801e440 "select (\n select login_name from profiles where userId = (\n select userid from maintainer where product_id = bugs.product_id\n union\n select userid from maintainer where product_id = (\n "..., length=353, parser_state=0x7fa0c8289240, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7886 #41 0x0000007ec497f84b in dispatch_command (command=COM_QUERY, thd=0x7fa058000a98, packet=0x7fa058007e89 "", packet_length=353, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1812 #42 0x0000007ec497e1be in do_command (thd=0x7fa058000a98) at /home/alice/git/10.2/sql/sql_parse.cc:1360 #43 0x0000007ec4acab66 in do_handle_one_connection (connect=0x7ec778a4f8) at /home/alice/git/10.2/sql/sql_connect.cc:1354 #44 0x0000007ec4aca8e6 in handle_one_connection (arg=0x7ec778a4f8) at /home/alice/git/10.2/sql/sql_connect.cc:1260 #45 0x00007fa0cba5a6ba in start_thread (arg=0x7fa0c828a700) at pthread_create.c:333 #46 0x00007fa0caeef3dd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109 error log 70912 11:10:17 [ERROR] mysqld got signal 11 ; Server version: 10.2.9-MariaDB-debug   Thread pointer: 0x7fa058000a98 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 = 0x7fa0c8289ec0 thread_stack 0x49000 /data/bld/10.2/bin/mysqld(my_print_stacktrace+0x38)[0x7ec5364a15] /data/bld/10.2/bin/mysqld(handle_fatal_signal+0x3a3)[0x7ec4bf125e] /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fa0cba64390] /data/bld/10.2/bin/mysqld(+0x61bd71)[0x7ec49f4d71] /data/bld/10.2/bin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x3b0)[0x7ec49f322b] mysys/stacktrace.c:267(my_print_stacktrace)[0x7ec49f26f2] sql/sql_select.cc:19378(join_read_next_same(READ_RECORD*))[0x7ec49cd2ad] sql/sql_select.cc:3279(JOIN::exec())[0x7ec49cc74e] sql/sql_union.cc:1006(st_select_lex_unit::exec())[0x7ec4a69fdb] sql/item_subselect.cc:3888(subselect_union_engine::exec())[0x7ec4cc162d] sql/item_subselect.cc:716(Item_subselect::exec())[0x7ec4cb64af] sql/item_subselect.cc:1318(Item_singlerow_subselect::val_int())[0x7ec4cb818d] /data/bld/10.2/bin/mysqld(+0x4e736f)[0x7ec48c036f] /data/bld/10.2/bin/mysqld(_ZN14Item_cache_int11cache_valueEv+0x55)[0x7ec4c20cd5] /data/bld/10.2/bin/mysqld(_ZN18Item_cache_wrapper5cacheEv+0x68)[0x7ec4c2aa9c] /data/bld/10.2/bin/mysqld(_ZN18Item_cache_wrapper7val_intEv+0xee)[0x7ec4c1cf3a] /data/bld/10.2/bin/mysqld(_ZN14Arg_comparator18compare_int_signedEv+0x78)[0x7ec4c31240] /data/bld/10.2/bin/mysqld(_ZN14Arg_comparator7compareEv+0x64)[0x7ec4c44254] /data/bld/10.2/bin/mysqld(_ZN12Item_func_eq7val_intEv+0x49)[0x7ec4c3356f] /data/bld/10.2/bin/mysqld(+0x61a52c)[0x7ec49f352c] /data/bld/10.2/bin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x2e3)[0x7ec49f315e] /data/bld/10.2/bin/mysqld(+0x6196f2)[0x7ec49f26f2] /data/bld/10.2/bin/mysqld(_ZN4JOIN10exec_innerEv+0xac1)[0x7ec49cd2ad] /data/bld/10.2/bin/mysqld(_ZN4JOIN4execEv+0xcc)[0x7ec49cc74e] /data/bld/10.2/bin/mysqld(_ZN30subselect_single_select_engine4execEv+0x620)[0x7ec4cc13e6] /data/bld/10.2/bin/mysqld(_ZN14Item_subselect4execEv+0x141)[0x7ec4cb64af] sql/item.h:1178(Item::val_int_result())[0x7ec4cb82a5] sql/sql_select.cc:18107(do_select(JOIN*, Procedure*))[0x7ec48c03a2] sql/item.cc:9923(Item_cache_str::cache_value())[0x7ec4c223c0] sql/item.cc:8276(Item_cache_wrapper::cache())[0x7ec4c2aa9c] sql/item.cc:8384(Item_cache_wrapper::val_str(String*))[0x7ec4c1d22e] sql/item.cc:6889(Item::send(Protocol*, String*))[0x7ec4c18e09] sql/item.cc:8182(Item_cache_wrapper::send(Protocol*, String*))[0x7ec4c1c99e] sql/protocol.cc:979(Protocol::send_result_set_row(List<Item>*))[0x7ec48bc1db] sql/sql_class.cc:2774(select_send::send_data(List<Item>&))[0x7ec493ddb6] sql/sql_select.cc:19726(end_send(JOIN*, st_join_table*, bool))[0x7ec49f5cfa] sql/sql_select.cc:18064(do_select(JOIN*, Procedure*))[0x7ec49f2447] sql/sql_select.cc:3483(JOIN::exec_inner())[0x7ec49cd2ad] sql/sql_select.cc:3279(JOIN::exec())[0x7ec49cc74e] sql/sql_select.cc:3680(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*))[0x7ec49cd91e] sql/sql_select.cc:373(handle_select(THD*, LEX*, select_result*, unsigned long))[0x7ec49c21c9] sql/sql_parse.cc:6446(execute_sqlcom_select(THD*, TABLE_LIST*))[0x7ec498e1b0] sql/sql_parse.cc:3460(mysql_execute_command(THD*))[0x7ec4983eb1] sql/sql_parse.cc:7886(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x7ec4991b29] sql/sql_parse.cc:1814(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x7ec497f84b] sql/sql_parse.cc:1360(do_command(THD*))[0x7ec497e1be] sql/sql_connect.cc:1354(do_handle_one_connection(CONNECT*))[0x7ec4acab66] sql/sql_connect.cc:1261(handle_one_connection)[0x7ec4aca8e6] /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fa0cba5a6ba] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fa0caeef3dd]   Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7fa05801e440): select ( select login_name from profiles where userId = ( select userid from maintainer where product_id = bugs.product_id union select userid from maintainer where product_id = ( select id from products where name = (select name from product_groups where id = bugs.product_id)) limit 1 ) ) as aa from bugs where (bugs.bug_id=45199) Connection ID (thread ID): 8 Status: NOT_KILLED on mysql 5.7.18, 5.6.23: + ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ( select login_name from profiles where userId = ( select userid from maintainer where product_id = bugs.product_id union select userid from maintainer where product_id = ( select id from products where name = ( select name from product_ | + ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | foo | + ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,18 sec)     + ----+--------------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ----+--------------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------------+ | 1 | PRIMARY | bugs | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 2 | DEPENDENT SUBQUERY | profiles | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where | | 3 | DEPENDENT SUBQUERY | maintainer | NULL | ref | PRIMARY | PRIMARY | 4 | const | 4 | 100.00 | Using index | | 4 | DEPENDENT UNION | maintainer | NULL | ref | PRIMARY | PRIMARY | 4 | func | 4 | 100.00 | Using where ; Using index | | 5 | DEPENDENT SUBQUERY | products | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 6 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | | NULL | UNION RESULT | <union3,4> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | + ----+--------------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------------+ 7 rows in set , 4 warnings (0,05 sec)   Warning (Code 1681): 'EXTENDED' is deprecated and will be removed in a future release. Note (Code 1276): Field or reference 'test.bugs.product_id' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.bugs.product_id' of SELECT #6 was resolved in SELECT #1 Note (Code 1003): /* select#1 */ select ( /* select#2 */ select `test`.`profiles`.`login_name` from `test`.`profiles` where (`test`.`profiles`.`userid` = ( /* select#3 */ select `test`.`maintainer`.`userid` from `test`.`maintainer` where (`test`.`maintainer`.`product_id` = '1184' ) union /* select#4 */ select `test`.`maintainer`.`userid` from `test`.`maintainer` where (`test`.`maintainer`.`product_id` = ( /* select#5 */ select `test`.`products`.`id` from `test`.`products` where (`test`.`products`.` name ` = ( /* select#6 */ select NULL from `test`.`product_groups` where multiple equal( '1184' , NULL ))))) limit 1))) AS `( select login_name from profiles where userId = ( select userid from maintainer where product_id = bugs.product_id union select userid from maintainer where product_id = ( select id from products where name = ( select name from product_` from `test`.`bugs` where 1

Varun Gupta (Inactive) added a comment - 2017-10-10 11:15 Output of explain extended I see in 5.5 explain extended select ( select login_name from profiles where userId = ( select userid from maintainer where product_id = bugs.product_id union select userid from maintainer1 where product_id = ( select id from products where name = ( select name from product_groups where id = bugs.product_id)) limit 1 ) ) as x from bugs where (bugs.bug_id=45199); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY bugs system PRIMARY NULL NULL NULL 1 100.00 2 DEPENDENT SUBQUERY profiles eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where 3 SUBQUERY maintainer ref PRIMARY PRIMARY 4 const 3 100.00 Using index 4 DEPENDENT UNION maintainer1 ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 6 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1276 Field or reference 'test.bugs.product_id' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'test.bugs.product_id' of SELECT #6 was resolved in SELECT #1 Note 1003 select <expr_cache><>((select `test`.`profiles`.`login_name` from `test`.`profiles` where (`test`.`profiles`.`userid` = <expr_cache><>((select `test`.`maintainer`.`userid` from `test`.`maintainer` where (`test`.`maintainer`.`product_id` = 1184) union select `test`.`maintainer1`.`userid` from `test`.`maintainer1` where (`test`.`maintainer1`.`product_id` = (select NULL from `test`.`products` where 0)) limit 1))))) AS `x` from dual where 1

Varun Gupta (Inactive) added a comment - 2017-10-10 15:10 - edited Having discussion with [~psergey] and [~sanja] , in MariaDB UNION runtime does not support one part of UNION being correlated and another one being uncorrelated. So we should have both of them correlated and re-execution every time

Varun Gupta (Inactive) added a comment - 2017-10-13 14:13 So stating some observations, the main issue I see is that the third select(with the maintainer table) has to be a DEPENDENT SUBQUERY In the function st_select_lex::mark_as_dependent we set the above select to be dependent // Select is dependent of outer select s->uncacheable= (s->uncacheable & ~UNCACHEABLE_UNITED) | UNCACHEABLE_DEPENDENT_GENERATED; And then after JOIN::optimize for the maintainer table, there is call to st_select_lex::update_correlated_cache if (!is_correlated) uncacheable&= ~UNCACHEABLE_DEPENDENT;

Rasmus Johansson (Inactive) added a comment - 2019-01-03 10:52 - edited varun Can you check again what to do about this?

Varun Gupta (Inactive) added a comment - 2019-01-06 17:51 New Patch http://lists.askmonty.org/pipermail/commits/2019-January/013251.html

Oleksandr Byelkin added a comment - 2019-01-07 11:50 OK to push if it pass --ps-protocol tests