[MDEV-21112] Can't find record when using LIMIT 1 with multiple joins Created: 2019-11-21  Updated: 2019-11-21  Resolved: 2019-11-21

Status: Closed
Project: MariaDB Server
Component/s: Server, Storage Engine - InnoDB
Affects Version/s: 10.4.6, 10.4.10
Fix Version/s: N/A

Type: Bug Priority: Blocker
Reporter: Ambroise Maupate Assignee: Alice Sherepa
Resolution: Duplicate Votes: 0
Labels: join, limit_examined_rows, select
Environment:

Server 1: mysql Ver 15.1 Distrib 10.4.10-MariaDB, for osx10.14 (x86_64) using readline 5.1
Server 2: mysql Ver 15.1 Distrib 10.4.6-MariaDB, for osx10.13 (x86_64) using readline 5.1

Attachments: File bug-1032_2019-11-21.sql.bz2    
Issue Links:
Duplicate
duplicates MDEV-19919 Assertion `!prebuilt->index->is_prima... Closed
PartOf
is part of MDEV-14551 Can't find record in table on multi-t... Closed

 Description   

Given data sources attached.

Following SQL statement returns 1032 Can't find record in 'nodes_sources':

SELECT 
n0_.title AS title_0, 
n0_.published_at AS published_at_1, 
n0_.meta_title AS meta_title_2, 
n0_.meta_keywords AS meta_keywords_3, 
n0_.meta_description AS meta_description_4, 
n0_.id AS id_5, 
n1_.over_title AS over_title_6, 
n1_.sub_title AS sub_title_7, 
n1_.sticky AS sticky_8, 
n1_.excerpt AS excerpt_9, 
n1_.content AS content_10, 
n2_.node_name AS node_name_11, 
n2_.visible AS visible_14, 
n2_.status AS status_15, 
n2_.id AS id_26, 
u3_.alias AS alias_27, 
u3_.id AS id_28, 
n0_.discr AS discr_29, 
n0_.node_id AS node_id_30, 
n0_.translation_id AS translation_id_31, 
n2_.nodeType_id AS nodeType_id_32, 
n2_.parent_node_id AS parent_node_id_33, 
u3_.ns_id AS ns_id_34
 
FROM ns_blogpost n1_ 
INNER JOIN nodes_sources n0_ ON n1_.id = n0_.id 
INNER JOIN nodes n2_ ON n0_.node_id = n2_.id
 
LEFT JOIN url_aliases u3_ ON n0_.id = u3_.ns_id 
WHERE n0_.id <> 59 
AND n2_.visible = 1 
AND n2_.status = 30
 
AND n0_.translation_id = 1 
AND n0_.published_at > "2019-11-20 11:30:00" 
ORDER BY n0_.published_at ASC 
LIMIT 1

You can test commenting the LIMIT 1 line and query will work again.

Then, using only one INNER JOIN, query is OK (with or without LIMIT):

SELECT n0_.title AS title_0, 
n0_.published_at AS published_at_1, 
n0_.meta_title AS meta_title_2, 
n0_.meta_keywords AS meta_keywords_3, 
n0_.meta_description AS meta_description_4, 
n0_.id AS id_5, 
n1_.over_title AS over_title_6, 
n1_.sub_title AS sub_title_7, 
n1_.sticky AS sticky_8, 
n1_.excerpt AS excerpt_9, 
n1_.content AS content_10, 
n0_.discr AS discr_29, 
n0_.node_id AS node_id_30, 
n0_.translation_id AS translation_id_31
 
FROM ns_blogpost n1_
 
INNER JOIN nodes_sources n0_ ON n1_.id = n0_.id 
WHERE n0_.id <> 59 
AND n0_.translation_id = 1 
AND n0_.published_at >= "2019-11-20 11:30:00" 
ORDER BY n0_.published_at ASC 
LIMIT 1



 Comments   
Comment by Alice Sherepa [ 2019-11-21 ]

Thanks for the report!
I checked on 10.4 Debug version it fails with the assertion `!prebuilt->index->is_primary()', which is, unfortunately, the same bug as MDEV-19919.

10.4 589a1235b64866c7bb

 
Version: '10.4.11-MariaDB-debug-log'  
mysqld: /10.4/storage/innobase/row/row0sel.cc:3942: ICP_RESULT row_search_idx_cond_check(byte*, row_prebuilt_t*, const rec_t*, const ulint*): Assertion `!prebuilt->index->is_primary()' failed.
 
191121 15:30:48 [ERROR] mysqld got signal 6 ;
 
 
 
assert/assert.c:92(__assert_fail_base)[0x7fe404f23bd7]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x2dc82)[0x7fe404f23c82]
 
row/row0sel.cc:3943(row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned long const*))[0x5613d84eec64]
 
row/row0sel.cc:5257(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long))[0x5613d84f2d73]
 
handler/ha_innodb.cc:9305(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function))[0x5613d83055b6]
 
sql/handler.h:3604(handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x5613d7f821a0]
 
sql/handler.cc:2883(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x5613d7f72430]
 
sql/handler.cc:5831(handler::read_range_first(st_key_range const*, st_key_range const*, bool, bool))[0x5613d7f7ad40]
 
sql/multi_range_read.cc:418(handler::multi_range_read_next(void**))[0x5613d7de6285]
 
sql/multi_range_read.cc:450(Mrr_simple_index_reader::get_next(void**))[0x5613d7de63bb]
 
sql/multi_range_read.cc:1520(DsMrr_impl::dsmrr_next(void**))[0x5613d7de9156]
 
handler/ha_innodb.cc:20375(ha_innobase::multi_range_read_next(void**))[0x5613d831912c]
 
sql/opt_range.cc:12160(QUICK_RANGE_SELECT::get_next())[0x5613d81083c8]
 
sql/records.cc:361(rr_quick(READ_RECORD*))[0x5613d811bb71]
 
sql/records.h:69(READ_RECORD::read_record())[0x5613d7b5a72d]
 
sql/sql_select.cc:21129(join_init_read_record(st_join_table*))[0x5613d7c8dbf0]
 
sql/sql_select.cc:21052(join_init_quick_read_record(st_join_table*))[0x5613d7c8d77c]
 
sql/sql_select.cc:20184(sub_select(JOIN*, st_join_table*, bool))[0x5613d7c8b7e1]
 
sql/sql_select.cc:20407(evaluate_join_record(JOIN*, st_join_table*, int))[0x5613d7c8bf5d]
 
sql/sql_select.cc:20187(sub_select(JOIN*, st_join_table*, bool))[0x5613d7c8b843]
 
sql/sql_select.cc:19725(do_select(JOIN*, Procedure*))[0x5613d7c8acd5]
 
sql/sql_select.cc:4386(JOIN::exec_inner())[0x5613d7c5fcb7]
 
sql/sql_select.cc:4169(JOIN::exec())[0x5613d7c5edf4]
 
sql/sql_select.cc:4602(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*))[0x5613d7c60509]
 
sql/sql_select.cc:420(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5613d7c503ee]
 
sql/sql_parse.cc:6357(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5613d7c16900]
 
sql/sql_parse.cc:3899(mysql_execute_command(THD*))[0x5613d7c0c020]
 
sql/sql_parse.cc:7898(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5613d7c1aa41]
 
sql/sql_parse.cc:1844(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5613d7c05c28]
 
sql/sql_parse.cc:1360(do_command(THD*))[0x5613d7c04289]
 
sql/sql_connect.cc:1412(do_handle_one_connection(CONNECT*))[0x5613d7d8d803]
 
sql/sql_connect.cc:1317(handle_one_connection)[0x5613d7d8d52c]
 
perfschema/pfs.cc:1864(pfs_spawn_thread)[0x5613d8792339]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fe40641a6ba]
 
x86_64/clone.S:111(clone)[0x7fe404ffd41d]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fe3ac013138): SELECT  n0_.title AS title_0,  n0_.published_at AS published_at_1,  n0_.meta_title AS meta_title_2,  n0_.meta_keywords AS meta_keywords_3,  n0_.meta_description AS meta_description_4,  n0_.id AS id_5,  n1_.over_title AS over_title_6,  n1_.sub_title AS sub_title_7,  n1_.sticky AS sticky_8,  n1_.excerpt AS excerpt_9,  n1_.content AS content_10,  n2_.node_name AS node_name_11,  n2_.visible AS visible_14,  n2_.status AS status_15,  n2_.id AS id_26,  u3_.alias AS alias_27,  u3_.id AS id_28,  n0_.discr AS discr_29,  n0_.node_id AS node_id_30,  n0_.translation_id AS translation_id_31,  n2_.nodeType_id AS nodeType_id_32,  n2_.parent_node_id AS parent_node_id_33,  u3_.ns_id AS ns_id_34 FROM ns_blogpost n1_  INNER JOIN nodes_sources n0_ ON n1_.id = n0_.id  INNER JOIN nodes n2_ ON n0_.node_id = n2_.id LEFT JOIN url_aliases u3_ ON n0_.id = u3_.ns_id  WHERE n0_.id <> 59  AND n2_.visible = 1  AND n2_.status = 30 AND n0_.translation_id = 1  AND n0_.published_at > "2019-11-20 11:30:00"  ORDER BY n0_.published_at ASC  LIMIT 1

Comment by Ambroise Maupate [ 2019-11-21 ]

Thanks @alice for finding the related existing issue.
I’m not deep enough into MariaDB core to find the underlying error.

I’ll watch for #MDEV-19919 advancements.

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