[MDEV-33221] Crash on FULLTEXT+UNION search with paritioned table Created: 2024-01-11  Updated: 2024-01-12

Status: Confirmed
Project: MariaDB Server
Component/s: Full-text Search, Partitioning
Affects Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3, 11.2.2
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4

Type: Bug Priority: Major
Reporter: Sebastian Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu


 Description   

The following query crashes MariaDB (signal 11) when partitioned table is used with UNION+FULLTEXT query. Same doesn't happen when table is not partitioned.

SELECT gb_id 
FROM gen_idx_birth6 
WHERE MATCH(gb_names_a ) AGAINST ( 'kosk' IN BOOLEAN MODE )
 
  AND MATCH(gb_names_b ) AGAINST ( 'kwit' IN BOOLEAN MODE )
 
UNION ALL
 
SELECT gb_id
 
FROM gen_idx_birth6
 
WHERE MATCH(gb_names_b ) AGAINST ( 'kosk' IN BOOLEAN MODE )
 
  AND MATCH(gb_names_a ) AGAINST ( 'kwit' IN BOOLEAN MODE )

Table:

CREATE TABLE `gen_idx_birth6` (
 
  `region` int(11) NOT NULL,
 
  `gb_id` bigint(6) NOT NULL,
 
  `gb_names_a` varchar(100) DEFAULT NULL,
 
  `gb_names_b` varchar(30) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_polish_ci
 
PARTITION BY LIST (`region`)
 
(
 
PARTITION p7 VALUES IN (7) ENGINE=InnoDB
 
);
 
 
 
ALTER TABLE `gen_idx_birth6`
 
  ADD PRIMARY KEY (`gb_id`,`region`) USING BTREE;
 
ALTER TABLE `gen_idx_birth6` ADD FULLTEXT KEY `ix_names_b` (`gb_names_b`);
 
ALTER TABLE `gen_idx_birth6` ADD FULLTEXT KEY `ix_names_a` (`gb_names_a`);
 
 
 
ALTER TABLE `gen_idx_birth6`
 
  MODIFY `gb_id` bigint(6) NOT NULL AUTO_INCREMENT;
 
COMMIT;



 Comments   
Comment by Marko Mäkelä [ 2024-01-11 ]

I compiled the mariadb-11.2.2 release tag from source code and was unable to reproduce a crash with the following:

--source include/have_innodb.inc
 
--source include/have_partition.inc
 
 
 
CREATE TABLE gen_idx_birth6 (
 
  region int NOT NULL,
 
  gb_id bigint NOT NULL,
 
  gb_names_a varchar(100) DEFAULT NULL,
 
  gb_names_b varchar(30) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_polish_ci
 
PARTITION BY LIST (region)
 
(
 
PARTITION p7 VALUES IN (7) ENGINE=InnoDB
 
);
 
 
 
ALTER TABLE gen_idx_birth6
 
  ADD PRIMARY KEY (gb_id,region) USING BTREE;
 
ALTER TABLE gen_idx_birth6 ADD FULLTEXT KEY ix_names_b (gb_names_b);
 
ALTER TABLE gen_idx_birth6 ADD FULLTEXT KEY ix_names_a (gb_names_a);
 
 
 
ALTER TABLE gen_idx_birth6
 
  MODIFY gb_id bigint(6) NOT NULL AUTO_INCREMENT;
 
COMMIT;
 
 
 
SELECT gb_id
 
FROM gen_idx_birth6
 
WHERE MATCH(gb_names_a ) AGAINST ( 'kosk' IN BOOLEAN MODE )
 
  AND MATCH(gb_names_b ) AGAINST ( 'kwit' IN BOOLEAN MODE )
 
UNION ALL
 
SELECT gb_id
 
FROM gen_idx_birth6
 
WHERE MATCH(gb_names_b ) AGAINST ( 'kosk' IN BOOLEAN MODE )
 
  AND MATCH(gb_names_a ) AGAINST ( 'kwit' IN BOOLEAN MODE );
 
 
 
DROP TABLE gen_idx_birth6;

I think that this may depend on the actual data that is stored in the table.

Can you post stack traces for the crash?

Comment by Alice Sherepa [ 2024-01-11 ]

Thanks for the report!
I repeated on 10.4-11.4:

240111 10:07:31 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.4.33-MariaDB-debug-log source revision: 9322ef03e339ee8fcea25231c73c2f63d56c0d49
 
 
 
sql/signal_handler.cc:235(handle_fatal_signal)[0x5642439688f9]
 
sigaction.c:0(__restore_rt)[0x7f5c6d79c420]
 
handler/ha_innodb.cc:18257(innobase_fts_retrieve_ranking(st_ft_info*))[0x5642443883c2]
 
sql/ha_partition.cc:6736(partition_ft_get_relevance(st_ft_info*))[0x564244194515]
 
sql/item_func.cc:6298(Item_func_match::val_real())[0x564243b0a4a2]
 
sql/sql_type.cc:4633(Type_handler_real_result::Item_val_bool(Item*) const)[0x56424372ae70]
 
sql/item.h:1475(Item::val_bool())[0x564242ede4fc]
 
sql/item_cmpfunc.cc:5382(Item_cond_and::val_int())[0x564243a67f4c]
 
sql/sql_select.cc:21017(evaluate_join_record(JOIN*, st_join_table*, int))[0x5642432f3bfc]
 
sql/sql_select.cc:20922(sub_select(JOIN*, st_join_table*, bool))[0x5642432f2f36]
 
sql/sql_select.cc:20443(do_select(JOIN*, Procedure*))[0x5642432f0cdc]
 
sql/sql_select.cc:4625(JOIN::exec_inner())[0x56424327e8e6]
 
sql/sql_select.cc:4408(JOIN::exec())[0x56424327bf16]
 
sql/sql_union.cc:1603(st_select_lex_unit::exec())[0x564243476816]
 
sql/sql_union.cc:42(mysql_union(THD*, LEX*, select_result*, st_select_lex_unit*, unsigned long))[0x564243465f1c]
 
sql/sql_select.cc:432(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564243250631]
 
sql/sql_parse.cc:6523(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5642431b796d]
 
sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x5642431a4f8d]
 
sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5642431c0ee9]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564243197095]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x564243193bc0]
 
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x5642435a8c3c]
 
sql/sql_connect.cc:1324(handle_one_connection)[0x5642435a84e0]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x56424424726a]
 
nptl/pthread_create.c:478(start_thread)[0x7f5c6d790609]
 
 
 
Query (0x62b0000a1290): SELECT * FROM t1 WHERE match(a) against ('kosk') AND match(b) against ('kwit')
 
UNION 
SELECT * FROM t1 WHERE match(b) against ('kosk') AND match(a) against ('kwit')


--source include/have_innodb.inc
 
--source include/have_partition.inc
 
 
 
CREATE TABLE t1 ( i int, a varchar(100), b varchar(30), fulltext KEY (b), fulltext KEY (a))
 
 PARTITION BY LIST (i) (PARTITION p7 VALUES IN (7) ENGINE = InnoDB);
 
INSERT INTO t1 VALUES (7,'kwit 1','kosk 1'),(7,'kwit 2','kosk 2'),(7,'kwit 3','kosk 3');
 
 
 
SELECT * FROM t1 WHERE match(a) against ('kosk') AND match(b) against ('kwit')
 
UNION 
SELECT * FROM t1 WHERE match(b) against ('kosk') AND match(a) against ('kwit');
 
 
 
DROP TABLE t1;

Also without UNION:

--source include/have_innodb.inc
 
--source include/have_partition.inc
 
 
 
CREATE TABLE t1 ( i int, a varchar(100), b varchar(30), fulltext KEY (b), fulltext KEY (a))  ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 5;
 
INSERT INTO t1 VALUES (7,'kwit 1','kosk 1'),(7,'kwit 2','kosk 2'),(7,'kwit 3','kosk 3');
 
 
 
SELECT * FROM t1 WHERE match(b) against ('kosk') AND match(a) against ('kwit');
 
 
 
DROP TABLE t1;


240111 10:17:35 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.4.33-MariaDB-debug-log source revision: 9322ef03e339ee8fcea25231c73c2f63d56c0d49
 
 
 
sql/signal_handler.cc:235(handle_fatal_signal)[0x5640545e08f9]
 
sigaction.c:0(__restore_rt)[0x7f3dba3d9420]
 
handler/ha_innodb.cc:18257(innobase_fts_retrieve_ranking(st_ft_info*))[0x5640550003c2]
 
sql/ha_partition.cc:6736(partition_ft_get_relevance(st_ft_info*))[0x564054e0c515]
 
sql/item_func.cc:6298(Item_func_match::val_real())[0x5640547824a2]
 
sql/sql_type.cc:4633(Type_handler_real_result::Item_val_bool(Item*) const)[0x5640543a2e70]
 
sql/item.h:1475(Item::val_bool())[0x564053b564fc]
 
sql/item_cmpfunc.cc:5382(Item_cond_and::val_int())[0x5640546dff4c]
 
sql/sql_select.cc:21017(evaluate_join_record(JOIN*, st_join_table*, int))[0x564053f6bbfc]
 
sql/sql_select.cc:20922(sub_select(JOIN*, st_join_table*, bool))[0x564053f6af36]
 
sql/sql_select.cc:20443(do_select(JOIN*, Procedure*))[0x564053f68cdc]
 
sql/sql_select.cc:4625(JOIN::exec_inner())[0x564053ef68e6]
 
sql/sql_select.cc:4408(JOIN::exec())[0x564053ef3f16]
 
sql/sql_select.cc:4848(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*))[0x564053ef80f2]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564053ec8968]
 
sql/sql_parse.cc:6523(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564053e2f96d]
 
sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x564053e1cf8d]
 
sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564053e38ee9]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564053e0f095]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x564053e0bbc0]
 
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x564054220c3c]
 
sql/sql_connect.cc:1324(handle_one_connection)[0x5640542204e0]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x564054ebf26a]
 
nptl/pthread_create.c:478(start_thread)[0x7f3dba3cd609]
 
 
 
Query (0x62b0000a1290): SELECT * FROM t1 WHERE match(b) against ('kosk') AND match(a) against ('kwit')

Generated at Thu Feb 08 10:37:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.