Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.4.7
-
None
-
centos7 and fedora30
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')
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
I am not seeing the assertion. Just confirming that MariaDB needs to be compiled with debug enabled to see the assertion?
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