Comment 10 for bug 957409

Hi Igor,

As said above, the problem described in the bug is reproducible on maria-5.3 up to and including revno 3458, and but not on revno 3459 and further. Revision 3459 is your fix for https://bugs.launchpad.net/maria/+bug/953649. Could you please take a look and advise whether it is expected to fix the problem(s) described here, too, or it just changed something so that test cases provided here don't trigger the issue any longer, while it might still exist?

I hope you will be able to say just looking at it, but I will also attach a test case which is not minimal, but is considerably smaller than the initial one. I run it as "perl ./mtr test_oom", without any custom parameters.

The offending query in it is

SELECT COUNT(*) FROM (
SELECT * FROM sc_member_product_detail
UNION
SELECT * FROM sc_member_product_detail
) tmp, sc_member m
WHERE m.id = tmp.member_id;

COUNT is not important, it is here just for convenience as sometimes the query returns thousands of rows instead of expected 1.
With derived_with_keys=off everything works fine.
Replacing the subquery with a view does not eliminate a problem.

The issue appears in different ways, with the same or similar data and query it can be a crash, or out of memory error, or 'Using too big key for internal temp tables', or even a ridiculous result. However, valgrind warnings are all similar if not identical:

Conditional jump or move depends on uninitialised value(s)
at 0xC3D2DA: alloc_root (my_alloc.c:199)
by 0x795D18: create_internal_tmp_table(st_table*, st_key*, st_maria_columndef*, st_maria_columndef**, unsigned long long) (sql_select.cc:14459)
by 0x8FA01C: mysql_derived_create(THD*, st_lex*, TABLE_LIST*) (sql_derived.cc:814)
by 0x8F8EDA: mysql_handle_single_derived(st_lex*, TABLE_LIST*, unsigned int) (sql_derived.cc:176)
by 0x78AC56: st_join_table::preread_init() (sql_select.cc:9897)
by 0x797C19: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:15387)
by 0x798614: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:15629)
by 0x797E5E: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:15432)
by 0x79762C: do_select(JOIN*, List<Item>*, st_table*, Procedure*) (sql_select.cc:15093)
by 0x778E32: JOIN::exec() (sql_select.cc:2731)
by 0x7796CC: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned lon
g long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:2952)
by 0x770294: handle_select(THD*, st_lex*, select_result*, unsigned long) (sql_select.cc:285)
by 0x6FEB03: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:5151)
by 0x6F5BC0: mysql_execute_command(THD*) (sql_parse.cc:2284)
by 0x7014DE: mysql_parse(THD*, char*, unsigned int, char const**) (sql_parse.cc:6152)
by 0x6F336F: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1228)
Conditional jump or move depends on uninitialised value(s)
at 0xC3D349: alloc_root (my_alloc.c:209)
by 0x795D18: create_internal_tmp_table(st_table*, st_key*, st_maria_columndef*, st_maria_columndef**, unsigned long long) (sql_select.cc:14459)
by 0x8FA01C: mysql_derived_create(THD*, st_lex*, TABLE_LIST*) (sql_derived.cc:814)
by 0x8F8EDA: mysql_handle_single_derived(st_lex*, TABLE_LIST*, unsigned int) (sql_derived.cc:176)
by 0x78AC56: st_join_table::preread_init() (sql_select.cc:9897)
by 0x797C19: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:15387)
by 0x798614: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:15629)
by 0x797E5E: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:15432)
by 0x79762C: do_select(JOIN*, List<Item>*, st_table*, Procedure*) (sql_select.cc:15093)
by 0x778E32: JOIN::exec() (sql_select.cc:2731)

etc.

EXPLAIN:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY m index PRIMARY,member_photo PRIMARY 4 NULL 68 100.00 Using index
1 PRIMARY <derived2> ref key0 key0 4 test.m.id 10 100.00
2 DERIVED sc_member_product_detail ALL NULL NULL NULL NULL 195 100.00
3 UNION sc_member_product_detail ALL NULL NULL NULL NULL 195 100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 select count(0) AS `COUNT(*)` from (select `test`.`sc_member_product_detail`.`id` AS `id`,`test`.`sc_member_product_detail`.`created_on` AS `created_on`,`test`.`sc_member_product_detail`.`last_updated_on` AS `last_updated_on`,`test`.`sc_member_product_detail`.`member_id` AS `member_id`,`test`.`sc_member_product_detail`.`product_id` AS `product_id`,`test`.`sc_member_product_detail`.`plateform_id` AS `plateform_id`,`test`.`sc_member_product_detail`.`view_type` AS `view_type`,`test`.`sc_member_product_detail`.`note` AS `note`,`test`.`sc_member_product_detail`.`title_review` AS `title_review`,`test`.`sc_member_product_detail`.`blog_lnk` AS `blog_lnk`,`test`.`sc_member_product_detail`.`review` AS `review`,`test`.`sc_member_product_detail`.`review_status` AS `review_status`,`test`.`sc_member_product_detail`.`date_review` AS `date_review`,`test`.`sc_member_product_detail`.`date_note` AS `date_note`,`test`.`sc_member_product_detail`.`lock_com` AS `lock_com`,`test`.`sc_member_product_detail`.`is_express` AS `is_express`,`test`.`sc_member_product_detail`.`product_type` AS `product_type` from `test`.`sc_member_product_detail` union select `test`.`sc_member_product_detail`.`id` AS `id`,`test`.`sc_member_product_detail`.`created_on` AS `created_on`,`test`.`sc_member_product_detail`.`last_updated_on` AS `last_updated_on`,`test`.`sc_member_product_detail`.`member_id` AS `member_id`,`test`.`sc_member_product_detail`.`product_id` AS `product_id`,`test`.`sc_member_product_detail`.`plateform_id` AS `plateform_id`,`test`.`sc_member_product_detail`.`view_type` AS `view_type`,`test`.`sc_member_product_detail`.`note` AS `note`,`test`.`sc_member_product_detail`.`title_review` AS `title_review`,`test`.`sc_member_product_detail`.`blog_lnk` AS `blog_lnk`,`test`.`sc_member_product_detail`.`review` AS `review`,`test`.`sc_member_product_detail`.`review_status` AS `review_status`,`test`.`sc_member_product_detail`.`date_review` AS `date_review`,`test`.`sc_member_product_detail`.`date_note` AS `date_note`,`test`.`sc_member_product_detail`.`lock_com` AS `lock_com`,`test`.`sc_member_product_detail`.`is_express` AS `is_express`,`test`.`sc_member_product_detail`.`product_type` AS `product_type` from `test`.`sc_member_product_detail`) `tmp` join `test`.`sc_member` `m` where (`tmp`.`member_id` = `test`.`m`.`id`)