[MDEV-20629] SELECT with WHERE IN and ORDER BY does not return rows Created: 2019-09-19  Updated: 2019-09-24  Resolved: 2019-09-19

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.4.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: richardeaxon Assignee: Alice Sherepa
Resolution: Duplicate Votes: 0
Labels: None
Environment:

centos7 and fedora30

Attachments: File s.sql    
Issue Links:
Relates
relates to MDEV-19919 Assertion `!prebuilt->index->is_prima... Closed

 Description   

Query that worked on 10.2 no longer works after upgrading to 10.4.7. Simple test case attached. I can recreate the issue on 10.4.7 and 10.4.8 on centos7 and fedora30 using the Mariab official RPM's (not vendor built RPM's). The following RPM's are installed:

MariaDB-common-10.4.8-1.fc30.x86_64
MariaDB-server-10.4.8-1.fc30.x86_64
MariaDB-oqgraph-engine-10.4.8-1.fc30.x86_64
MariaDB-client-10.4.8-1.fc30.x86_64

Using the attached table and dataset execute the following:

MariaDB [prod_sync_re]> EXPLAIN
 
    -> SELECT * FROM services
 
    ->          WHERE service_id='189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ('S', 'R')
 
    ->          ORDER BY seq;
 
 
 
 
 
SELECT * FROM services
 
         WHERE service_id='189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ('S', 'R')
 
         ORDER BY seq;
 
         +------+-------------+----------+------------+------------------------+------------------------+---------+-------+---------+------------------------------------------------------------------------+
 
| id   | select_type | table    | type       | possible_keys          | key                    | key_len | ref   | rows    | Extra                                                                  |
 
+------+-------------+----------+------------+------------------------+------------------------+---------+-------+---------+------------------------------------------------------------------------+
 
|    1 | SIMPLE      | services | ref|filter | service_id,provisioned | service_id|provisioned | 108|1   | const | 3 (22%) | Using index condition; Using where; Using filesort; Using rowid filter |
 
+------+-------------+----------+------------+------------------------+------------------------+---------+-------+---------+------------------------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [prod_sync_re]>
 
MariaDB [prod_sync_re]>
 
MariaDB [prod_sync_re]> SELECT * FROM services
 
    ->          WHERE service_id='189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ('S', 'R')
 
    ->          ORDER BY seq;
 
Empty set (0.000 sec)

EXPLAIN says 3 rows are returned but none are displayed.

Doing any of the following shows the rows:

Removing the ORDER BY
Removing the provisioned index
Change the IN to IN ('S')
Delete any of the following columns: service_supplier_id, billing_group_id, service_descrip
Remove the IN from the WHERE clause and do a post query filter using HAVING provisioned IN ('S', 'R')

 Comments   
Comment by Alice Sherepa [ 2019-09-19 ]

Thank you for the report and the test case.
I reproduced on 10.4, it seems to be the same bug as MDEV-19919:

10.4 b9dea911bf8e3d4

 
Version: '10.4.9-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.
 
190919 12:42:50 [ERROR] mysqld got signal 6 ;
 
 
stdlib/abort.c:91(__GI_abort)[0x7f0cc9d5e02a]
 
assert/assert.c:92(__assert_fail_base)[0x7f0cc9d54bd7]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x2dc82)[0x7f0cc9d54c82]
 
row/row0sel.cc:3942(row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned long const*))[0x563782d0b508]
 
row/row0sel.cc:5257(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long))[0x563782d0f821]
 
handler/ha_innodb.cc:9306(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function))[0x563782b147cf]
 
handler/ha_innodb.cc:9807(ha_innobase::rnd_pos(unsigned char*, unsigned char*))[0x563782b15b19]
 
sql/handler.cc:2858(handler::ha_rnd_pos(unsigned char*, unsigned char*))[0x563782774818]
 
sql/records.cc:543(rr_from_pointers(READ_RECORD*))[0x563782923abd]
 
sql/records.h:69(READ_RECORD::read_record())[0x563782356093]
 
sql/sql_select.cc:21108(join_init_read_record(st_join_table*))[0x56378248b4ee]
 
sql/sql_select.cc:20163(sub_select(JOIN*, st_join_table*, bool))[0x5637824890b1]
 
sql/sql_select.cc:19704(do_select(JOIN*, Procedure*))[0x563782488583]
 
sql/sql_select.cc:4390(JOIN::exec_inner())[0x56378245d2c1]
 
sql/sql_select.cc:4173(JOIN::exec())[0x56378245c3f0]
 
sql/sql_select.cc:4606(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*))[0x56378245db13]
 
sql/sql_select.cc:424(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56378244d8cb]
 
sql/sql_parse.cc:6352(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5637824138f3]
 
sql/sql_parse.cc:3894(mysql_execute_command(THD*))[0x563782408ea4]
 
sql/sql_parse.cc:7912(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x563782417ad2]
 
sql/sql_parse.cc:1843(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5637824029ec]
 
sql/sql_parse.cc:1359(do_command(THD*))[0x563782401024]
 
sql/sql_connect.cc:1412(do_handle_one_connection(CONNECT*))[0x56378258be09]
 
sql/sql_connect.cc:1317(handle_one_connection)[0x56378258bb32]
 
perfschema/pfs.cc:1864(pfs_spawn_thread)[0x563782fc36a1]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f0ccb24b6ba]
 
x86_64/clone.S:111(clone)[0x7f0cc9e2e41d]
 
 
 
Query (0x7f0c6c013118): SELECT * FROM services WHERE service_id='189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ('S', 'R') ORDER BY seq

Comment by richardeaxon [ 2019-09-19 ]

I am not seeing the assertion. Just confirming that MariaDB needs to be compiled with debug enabled to see the assertion?

Comment by Alice Sherepa [ 2019-09-20 ]

yes, the assertion on debug version

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