[MDEV-20851] optimizer_switch 'mrr=on' causes failing queries Created: 2019-10-17  Updated: 2020-06-29  Resolved: 2020-06-29

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.6, 10.4.8, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Jens Van Deynse Assignee: Igor Babaev
Resolution: Duplicate Votes: 2
Labels: optimizer
Environment:

Linux (CentOS), Windows (10)

Attachments: File FinalData.sql     File FinalQuery.sql    
Issue Links:
Duplicate
duplicates MDEV-20056 Assertion `!prebuilt->index->is_prima... Closed
Relates
relates to MDEV-19919 Assertion `!prebuilt->index->is_prima... Closed

 Description   

Since we upgraded from MariaDB 10.3.16 to MariaDB 10.4.6/10.4.8 we encounter an issue with one of our criteria queries.
After searching for what the cause might be we found out that it was due to an optimizer_switch, when deactivating it everything worked fine. The optimizer_switch causing the issue is 'mrr=on'.

After spending some time on it we were able to create a simpler database with 2 tables and fake data to replicate the issue. When doing this we noticed that the amount of rows in both databases seems to be of some relevance, when having too few row the query works but otherwise not. We could be wrong about this but it's just something we noticed.

In attachment I added the query that fails and the queries that can create a dataset to test it with.

Repro steps:

  1. Activate MariaDB server (version 10.4.6 - 10.4.8) and enable the optimizer_switch 'mrr=on'
  2. Create the database containing the 2 tables and the data
  3. Execute the query

Doing this will produce the following error:
/* SQL Error (1032): Can't find record in 't2' */

Expected result:
/* Affected rows: 0 Found rows: 2 Warnings: 0 Duration for 1 query: 0.015 sec. */

If you need any more information just ask and I'll see what we can do.

 Comments   
Comment by Alice Sherepa [ 2019-10-17 ]

Thanks a lot for the report and the testcase!
I repeated on MariaDB 10.4, seems to be the same problem as MDEV-20056 and MDEV-19919.

--source include/have_innodb.inc
 
set optimizer_switch='mrr=on';
 
 
 
CREATE TABLE t1 ( id int NOT NULL, c13 int NULL, PRIMARY KEY (id), INDEX c13 (c13)) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (351, NULL), (352, NULL), (353, NULL), (354, NULL), (355, NULL), (356, NULL), (357, NULL), (358, NULL), (359, NULL), (360, NULL), (361, NULL), (362, NULL), (363, NULL), (364, NULL), (365, NULL), (366, NULL), (367, NULL), (368, NULL), (369, NULL), (370, NULL), (371, NULL), (372, NULL), (373, NULL), (374, NULL), (375, NULL), (376, NULL), (377, NULL), (378, NULL), (379, NULL), (380, NULL), (381, NULL), (382, NULL), (383, NULL), (384, NULL), (385, NULL), (386, NULL), (387, NULL), (388, NULL), (389, NULL), (390, NULL), (391, NULL), (392, NULL), (393, NULL), (394, NULL), (395, NULL), (396, NULL), (397, NULL), (398, NULL), (399, NULL);
 
 
 
CREATE TABLE t2 ( id int NOT NULL, s1 int NOT NULL, c9 int NULL, PRIMARY KEY (id), INDEX (s1), INDEX (c9))ENGINE=InnoDB;
 
INSERT INTO t2 VALUES (351, 83, 351), (352, 83, 352), (353, 83, 353), (354, 83, 354), (355, 64, 355), (356, 64, 356), (357, 68, 357), (358, 68, 358), (359, 68, 359), (360, 68, 360), (361, 77, 361), (362, 77, 362), (363, 77, 363), (364, 77, 364), (365, 77, 365), (366, 77, 366), (367, 77, 367), (368, 91, 368), (369, 91, 369), (370, 91, 370), (371, 91, 371), (372, 96, 372), (373, 96, 373), (374, 96, 374), (375, 96, 375), (376, 96, 376), (377, 96, 377), (378, 96, 378), (379, 96, 379), (380, 96, 380), (381, 96, 381), (382, 97, 382), (383, 97, 383), (384, 97, 384), (385, 97, 385), (386, 97, 386), (387, 97, 387), (388, 97, 388), (389, 97, 389), (390, 97, 390), (391, 98, 391), (392, 98, 392), (393, 98, 393), (394, 98, 394), (395, 98, 395), (396, 98, 396), (397, 98, 397), (398, 98, 398), (399, 98, 399), (400, 98, 400), (401, 99, 401), (504, 109, 504), (505, 1, 505), (506, 1, 506), (507, 1, 507), (508, 1, 508), (509, 1, 509), (510, 97, 510), (511, 10, 511), (512, 10, 512), (513, 100, 513), (514, 100, 514), (515, 114, 515), (516, 114, 516), (517, 116, 517), (518, 113, 518), (519, 83, 519), (520, 101, 520), (521, 101, 521), (522, 114, 522), (523, 124, 523), (524, 127, 524), (525, 116, 525), (526, 126, 526), (527, 115, 527), (528, 7, 528), (2444, 84, 2444), (2445, 84, 2445), (2446, 84, 2446), (2447, 84, 2447), (2448, 64, 2448), (2449, 64, 2449), (2450, 88, 2450), (2451, 87, 2451), (2452, 87, 2452), (2453, 89, 2453), (2454, 89, 2454), (2455, 90, 2455), (2456, 90, 2456), (2457, 90, 2457), (2458, 91, 2458), (2459, 91, 2459), (2460, 93, 2460), (2461, 93, 2461), (2462, 93, 2462), (2463, 93, 2463), (2464, 94, 2464), (2465, 94, 2465), (2466, 96, 2466), (2467, 96, 2467), (2468, 96, 2468), (2469, 96, 2469), (2470, 96, 2470), (2471, 97, 2471), (2472, 97, 2472), (2473, 97, 2473), (2474, 97, 2474), (2475, 97, 2475), (2476, 98, 2476), (2477, 98, 2477), (2478, 98, 2478), (2479, 98, 2479), (2480, 98, 2480), (2481, 99, 2481), (2482, 99, 2482), (2483, 99, 2483), (2484, 99, 2484), (2485, 99, 2485), (2486, 100, 2486), (2487, 100, 2487), (2488, 100, 2488), (2489, 100, 2489), (2490, 100, 2490), (3142, 38, 3142), (3143, 58, 3143), (3144, 64, 3144), (3145, 64, 3145), (3146, 64, 3146), (3147, 64, 3147), (3148, 64, 3148), (3149, 64, 3149), (3150, 65, 3150), (3151, 65, 3151), (3152, 68, 3152), (3153, 68, 3153), (3154, 68, 3154), (3155, 68, 3155), (3156, 93, 3156), (3157, 93, 3157), (3158, 93, 3158), (3159, 93, 3159), (3160, 94, 3160), (3161, 94, 3161), (3162, 96, 3162), (3163, 96, 3163), (3164, 96, 3164), (3165, 96, 3165), (3166, 96, 3166), (3167, 97, 3167), (3168, 97, 3168), (3169, 97, 3169), (3170, 97, 3170), (3171, 97, 3171), (3172, 98, 3172), (3173, 98, 3173), (3174, 98, 3174), (3175, 98, 3175), (3176, 98, 3176), (3177, 99, 3177), (3178, 99, 3178), (3179, 99, 3179), (3180, 99, 3180), (3181, 99, 3181), (3182, 100, 3182), (3183, 100, 3183), (3184, 100, 3184), (3185, 100, 3185), (3186, 100, 3186), (3187, 101, 3187), (3188, 101, 3188), (3189, 101, 3189), (3190, 101, 3190), (3191, 101, 3191), (3192, 102, 3192), (3193, 102, 3193), (3356, 96, 3356), (3357, 97, 3357), (3358, 97, 3358), (3359, 97, 3359), (3360, 97, 3360), (3361, 97, 3361), (3362, 98, 3362), (3363, 98, 3363), (3364, 98, 3364), (3365, 98, 3365), (3366, 98, 3366), (3367, 99, 3367), (3368, 99, 3368), (3369, 99, 3369), (3370, 99, 3370), (3371, 99, 3371), (3372, 100, 3372), (3373, 100, 3373), (3374, 100, 3374), (3375, 100, 3375), (3376, 100, 3376), (3377, 101, 3377), (3378, 101, 3378), (3379, 101, 3379), (3380, 101, 3380), (3381, 101, 3381), (3382, 102, 3382), (3383, 102, 3383), (3384, 102, 3384), (3385, 102, 3385), (3386, 102, 3386), (3387, 103, 3387), (3388, 84, 3388), (3389, 105, 3389), (3390, 105, 3390), (3391, 105, 3391), (3392, 105, 3392), (3393, 105, 3393), (3394, 105, 3394), (3395, 133, 3395), (3396, 1, 3396), (3397, 20, 3397), (3398, 153, 3398), (3399, 153, 3399), (3400, 154, 3400), (3401, 154, 3401), (3402, 155, 3402), (3403, 155, 3403), (3404, 155, 3404), (3405, 155, 3405), (3406, 156, 3406), (5569, 23, 5569), (5570, 23, 5570), (5571, 25, 5571), (5572, 26, 5572), (5573, 26, 5573), (5574, 28, 5574), (5575, 30, 5575), (5576, 43, 5576), (5577, 64, 5577), (5578, 67, 5578), (5579, 67, 5579), (5580, 67, 5580), (5581, 67, 5581), (5582, 67, 5582), (5583, 68, 5583), (5584, 68, 5584), (5585, 67, 5585), (5586, 91, 5586), (5587, 96, 5587), (5588, 96, 5588), (5589, 96, 5589), (5590, 97, 5590), (5591, 97, 5591), (5592, 98, 5592), (5593, 98, 5593), (5594, 98, 5594), (5595, 98, 5595), (5596, 99, 5596), (5597, 99, 5597), (5598, 99, 5598), (5599, 100, 5599), (5600, 100, 5600), (5601, 101, 5601), (5602, 101, 5602), (5603, 102, 5603), (5604, 102, 5604), (5605, 102, 5605), (5606, 103, 5606), (5607, 133, 5607), (5608, 116, 5608), (5609, 116, 5609), (5610, 116, 5610), (5611, 117, 5611), (5612, 119, 5612), (5613, 120, 5613), (5614, 122, 5614), (5615, 122, 5615), (5616, 123, 5616), (5617, 123, 5617), (5618, 123, 5618), (5768, 96, 5768), (5769, 96, 5769), (5770, 97, 5770), (5771, 97, 5771), (5772, 97, 5772), (5773, 98, 5773), (5774, 99, 5774), (5775, 99, 5775), (5776, 100, 5776), (5777, 100, 5777), (5778, 100, 5778);
 
 
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id
 
WHERE t2.s1 = 97
 
  AND t1.id IN (382, 383)
 
  AND t2.c9 IN (SELECT t2.c9 FROM t1 JOIN t2 ON t1.id = t2.id);

10.4 899c843f11bc40b

 
#3  <signal handler called>
 
#4  0x00007ff63e947428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
 
#5  0x00007ff63e94902a in __GI_abort () at abort.c:89
 
#6  0x00007ff63e93fbd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x559ff3294118 "!prebuilt->index->is_primary()", file=file@entry=0x559ff3293048 "/10.4/storage/innobase/row/row0sel.cc", line=line@entry=3942, function=function@entry=0x559ff3297240 <row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned long const*)::__PRETTY_FUNCTION__> "ICP_RESULT row_search_idx_cond_check(byte*, row_prebuilt_t*, const rec_t*, const ulint*)") at assert.c:92
 
#7  0x00007ff63e93fc82 in __GI___assert_fail (assertion=0x559ff3294118 "!prebuilt->index->is_primary()", file=0x559ff3293048 "/10.4/storage/innobase/row/row0sel.cc", line=3942, function=0x559ff3297240 <row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned long const*)::__PRETTY_FUNCTION__> "ICP_RESULT row_search_idx_cond_check(byte*, row_prebuilt_t*, const rec_t*, const ulint*)") at assert.c:101
 
#8  0x0000559ff2bbe352 in row_search_idx_cond_check (mysql_rec=0x7ff5e8068470 "\375~\001", prebuilt=0x7ff5e8080938, rec=0x7ff63354443f "\200", offsets=0x7ff6380c1950) at /10.4/storage/innobase/row/row0sel.cc:3942
 
#9  0x0000559ff2bbfcaa in row_search_mvcc (buf=0x7ff5e8068470 "\375~\001", mode=PAGE_CUR_GE, prebuilt=0x7ff5e8080938, match_mode=1, direction=0) at /10.4/storage/innobase/row/row0sel.cc:4414
 
#10 0x0000559ff29d4a00 in ha_innobase::index_read (this=0x7ff5e8067af8, buf=0x7ff5e8068470 "\375~\001", key_ptr=0x7ff5e81ed6ec "~\001", key_len=4, find_flag=HA_READ_KEY_EXACT) at /10.4/storage/innobase/handler/ha_innodb.cc:9306
 
#11 0x0000559ff29d5d23 in ha_innobase::rnd_pos (this=0x7ff5e8067af8, buf=0x7ff5e8068470 "\375~\001", pos=0x7ff5e81ed6ec "~\001") at /10.4/storage/innobase/handler/ha_innodb.cc:9807
 
#12 0x0000559ff2641e69 in handler::ha_rnd_pos (this=0x7ff5e8067af8, buf=0x7ff5e8068470 "\375~\001", pos=0x7ff5e81ed6ec "~\001") at /10.4/sql/handler.cc:2858
 
#13 0x0000559ff24b83d3 in Mrr_ordered_rndpos_reader::get_next (this=0x7ff5e8067f88, range_info=0x7ff6380c2a08) at /10.4/sql/multi_range_read.cc:885
 
#14 0x0000559ff24b9e5c in DsMrr_impl::dsmrr_next (this=0x7ff5e8067f60, range_info=0x7ff6380c2a08) at /10.4/sql/multi_range_read.cc:1520
 
#15 0x0000559ff29e8266 in ha_innobase::multi_range_read_next (this=0x7ff5e8067af8, range_info=0x7ff6380c2a08) at /10.4/storage/innobase/handler/ha_innodb.cc:20153
 
#16 0x0000559ff27d8050 in QUICK_RANGE_SELECT::get_next (this=0x7ff5e807f2d0) at /10.4/sql/opt_range.cc:12160
 
#17 0x0000559ff27eb7f9 in rr_quick (info=0x7ff5e81a59b8) at /10.4/sql/records.cc:361
 
#18 0x0000559ff222c347 in READ_RECORD::read_record (this=0x7ff5e81a59b8) at /10.4/sql/records.h:69
 
#19 0x0000559ff235ef98 in join_init_read_record (tab=0x7ff5e81a58f0) at /10.4/sql/sql_select.cc:21129
 
#20 0x0000559ff24b00f2 in JOIN_TAB_SCAN::open (this=0x7ff5e81aafb8) at /10.4/sql/sql_join_cache.cc:3349
 
#21 0x0000559ff24ae437 in JOIN_CACHE::join_matching_records (this=0x7ff5e81aac28, skip_last=false) at /10.4/sql/sql_join_cache.cc:2252
 
#22 0x0000559ff24adf3c in JOIN_CACHE::join_records (this=0x7ff5e81aac28, skip_last=false) at /10.4/sql/sql_join_cache.cc:2088
 
#23 0x0000559ff235c7ac in sub_select_cache (join=0x7ff5e8079238, join_tab=0x7ff5e81a58f0, end_of_records=true) at /10.4/sql/sql_select.cc:19966
 
#24 0x0000559ff235c9b7 in sub_select (join=0x7ff5e8079238, join_tab=0x7ff5e81a5548, end_of_records=true) at /10.4/sql/sql_select.cc:20137
 
#25 0x0000559ff235c0e3 in do_select (join=0x7ff5e8079238, procedure=0x0) at /10.4/sql/sql_select.cc:19728
 
#26 0x0000559ff2330fbd in JOIN::exec_inner (this=0x7ff5e8079238) at /10.4/sql/sql_select.cc:4386
 
#27 0x0000559ff23300fa in JOIN::exec (this=0x7ff5e8079238) at /10.4/sql/sql_select.cc:4168
 
#28 0x0000559ff233180f in mysql_select (thd=0x7ff5e8000b00, tables=0x7ff5e8013868, wild_num=1, fields=..., conds=0x7ff5e8015a68, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7ff5e8018fe8, unit=0x7ff5e8004a30, select_lex=0x7ff5e80132a8) at /10.4/sql/sql_select.cc:4600
 
#29 0x0000559ff23216f4 in handle_select (thd=0x7ff5e8000b00, lex=0x7ff5e8004968, result=0x7ff5e8018fe8, setup_tables_done_option=0) at /10.4/sql/sql_select.cc:420
 
#30 0x0000559ff22e7e5b in execute_sqlcom_select (thd=0x7ff5e8000b00, all_tables=0x7ff5e8013868) at /10.4/sql/sql_parse.cc:6352
 
#31 0x0000559ff22dd57b in mysql_execute_command (thd=0x7ff5e8000b00) at /10.4/sql/sql_parse.cc:3894
 
#32 0x0000559ff22ebfaf in mysql_parse (thd=0x7ff5e8000b00, rawbuf=0x7ff5e8013128 "SELECT * FROM t1 JOIN t2 ON t1.id = t2.id\nWHERE t2.s1 = 97\nAND t1.id IN (382, 383)\nAND t2.c9 IN (SELECT t2.c9 FROM t1 JOIN t2 ON t1.id = t2.id)", length=143, parser_state=0x7ff6380c3ff0, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:7912
 
#33 0x0000559ff22d7248 in dispatch_command (command=COM_QUERY, thd=0x7ff5e8000b00, packet=0x7ff5e819dfe1 "", packet_length=143, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1841
 
#34 0x0000559ff22d58a9 in do_command (thd=0x7ff5e8000b00) at /10.4/sql/sql_parse.cc:1359
 
#35 0x0000559ff245e5a5 in do_handle_one_connection (connect=0x559ff5f96230) at /10.4/sql/sql_connect.cc:1412
 
#36 0x0000559ff245e2ce in handle_one_connection (arg=0x559ff5f96230) at /10.4/sql/sql_connect.cc:1316
 
#37 0x0000559ff2e639eb in pfs_spawn_thread (arg=0x559ff5f14870) at /10.4/storage/perfschema/pfs.cc:1862
 
#38 0x00007ff63fe366ba in start_thread (arg=0x7ff6380c5700) at pthread_create.c:333
 
#39 0x00007ff63ea1941d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Comment by Alice Sherepa [ 2020-06-29 ]

Not reproducible after commit 4d4b2867a2526872a7b476 by Igor Babaev

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