[MDEV-23056] server crash on select with where clause that calls a function doing a subquery - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 10.4.13
Fix Version/s: N/A
Component/s: Data Manipulation - Subquery
Labels:
None
Environment:
docker container using official docker image mariadb/server:10.4
docker runs on linux Ubuntu 16.04.6 LTS

Description

Shell commands to prepare environment:

$ docker pull mariadb/server:10.4
$ docker run --name bugreport -e MYSQL_ROOT_PASSWORD=mypass -d mariadb/server:10.4
$ docker exec bugreport hostname -I
--> 172.17.0.2
$ sudo mysql -h 172.17.0.2 -u root -p

Now at mariadb/mysql> prompt:

(you can source the attached bug-reproduce.sql file which contains all followig commands)

mysql> select version();

+----------------------------------------+

| version()                              |

+----------------------------------------+

| 10.4.13-MariaDB-1:10.4.13+maria~bionic |

+----------------------------------------+

1 row in set (0,00 sec)

mysql> create database if not exists test;

Query OK, 1 row affected (0,00 sec)

mysql> use test;

Database changed

mysql> create table if not exists workstation (

  id            INT NOT NULL AUTO_INCREMENT,

  prodlineid    INT NOT NULL DEFAULT 1,

  primary key (id)

) engine=innodb;

Query OK, 0 rows affected (0,26 sec)

mysql> create table if not exists process_workstation (

  processid                             INT NOT NULL,

  workstationid                         INT NOT NULL,

  unique index (processid, workstationid),

  index (workstationid),

  constraint foreign key (workstationid) references workstation(id) on delete cascade on update cascade

) ENGINE = InnoDB;

Query OK, 0 rows affected (0,23 sec)

mysql> create function proc_on_ws(process_id int, ws_id int, prodline_id int) returns int

  return

    exists(

        select * from process_workstation pw

        where pw.workstationid = ws_id and pw.processid = process_id

  or (

        not isnull(prodline_id) and

        not isnull(process_id) and

        not exists(

                select * from workstation ws, process_workstation pw

                where ws.prodlineid = prodline_id and

                      pw.workstationid = ws.id and pw.processid = process_id

);

Query OK, 0 rows affected (0,03 sec)

mysql> insert ignore into  workstation (id) values

        (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),

        (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),

        (30),(31),(32),(33),(34),(35),(36),(37),(38),(39),

        (40),(41),(42),(43),(44),(45),(46),(47),(48),(49),

        (50),(51),(52),(53),(54),(55),(56),(57),(58),(59),

        (60),(61),(62),(63),(64),(65),(66),(67),(68),(69),

        (70),(71),(72),(73),(74),(75),(76),(77),(78),(79),

        (80),(81),(82),(83),(84),(85),(86),(87),(88),(89)

Query OK, 80 rows affected (0,08 sec)

Records: 80  Duplicates: 0  Warnings: 0

-- This query crash the server:

mysql> select nextws.id from workstation nextws

where nextws.prodlineid = 1 AND proc_on_ws( 7 , nextws.id, nextws.prodlineid);

ERROR 2013 (HY000): Lost connection to MySQL server during query

Logs:

Back to shell, the output of
$ docker logs bugreport 2>&1
is attached as bug-mariadb.logs

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

bug-mariadb.logs
12 kB
2020-06-30 12:16
bug-reproduce.sql
2 kB
2020-06-30 12:16

Issue Links

duplicates

MDEV-20290 Server crash in st_select_lex::cleanup or Assertion `sl->join == 0' failed in reinit_stmt_before_use

Closed

Activity

Ascending order - Click to sort in descending order

L. Schwarz created issue - 2020-06-30 12:29

Alice Sherepa made changes - 2020-06-30 13:16

Field	Original Value	New Value
Link		This issue duplicates ~~MDEV-20290~~ [ ~~MDEV-20290~~ ]

Alice Sherepa added a comment - 2020-06-30 13:24

Thanks a lot for the report and the test case!
I tried the debug version of 10.4,10.5, it turned out to be the same bug as ~~MDEV-20290~~.
I am closing the case here and will add a note to check the case after ~~MDEV-20290~~ will be fixed.

mariadbd: /git/10.5/sql/sql_prepare.cc:3018: void reinit_stmt_before_use(THD*, LEX*): Assertion `sl->join == 0' failed.

200630 15:14:06 [ERROR] mysqld got signal 6 ;

linux/raise.c:51(__GI_raise)[0x7fd6212a8e97]

stdlib/abort.c:81(__GI_abort)[0x7fd6212aa801]

assert/assert.c:89(__assert_fail_base)[0x7fd62129a39a]

??:0(__assert_fail)[0x7fd62129a412]

sql/sql_prepare.cc:3021(reinit_stmt_before_use(THD*, LEX*))[0x564818da8fa9]

sql/sp_head.cc:3474(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x564818afecd8]

sql/sp_head.cc:4148(sp_instr_freturn::execute(THD*, unsigned int*))[0x564818b03a4c]

sql/sp_head.cc:1435(sp_head::execute(THD*, bool))[0x564818af1465]

sql/sp_head.cc:2163(sp_head::execute_function(THD*, Item**, unsigned int, Field*, sp_rcontext**, Query_arena*))[0x564818af5993]

sql/item.cc:2847(Item_sp::execute_impl(THD*, Item**, unsigned int))[0x564819579373]

sql/item.cc:2759(Item_sp::execute(THD*, bool*, Item**, unsigned int))[0x5648195788f3]

sql/item_func.cc:6500(Item_func_sp::execute())[0x56481969d489]

sql/item_func.h:3418(Item_func_sp::val_int())[0x5648196ad350]

sql/sql_type.cc:4915(Type_handler_int_result::Item_val_bool(Item*) const)[0x5648192d85e4]

sql/item.h:1481(Item::val_bool())[0x564818e8a771]

sql/item_cmpfunc.cc:5433(Item_cond_and::val_int())[0x564818e8a0bf]

sql/sql_select.cc:20701(evaluate_join_record(JOIN*, st_join_table*, int))[0x564818e87a6a]

sql/sql_select.cc:20642(sub_select(JOIN*, st_join_table*, bool))[0x564818e14a8d]

sql/sql_select.cc:20137(do_select(JOIN*, Procedure*))[0x564818e1208a]

sql/sql_select.cc:4447(JOIN::exec_inner())[0x564818e16144]

sql/sql_select.cc:4229(JOIN::exec())[0x564818de82b1]

sql/sql_select.cc:4654(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x564818d58a4e]

sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564818d47d1c]

sql/sql_parse.cc:6211(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564818d63ba5]

sql/sql_parse.cc:3939(mysql_execute_command(THD*))[0x564818d3a695]

sql/sql_parse.cc:7995(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564818d36f13]

sql/sql_parse.cc:1877(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564819164ad9]

sql/sql_parse.cc:1355(do_command(THD*))[0x564819164432]

sql/sql_connect.cc:1411(do_handle_one_connection(CONNECT*, bool))[0x564819e27caf]

nptl/pthread_create.c:463(start_thread)[0x7fd6221a56db]

x86_64/clone.S:97(clone)[0x7fd62138b88f]

Query (0x62b0000a12a8): select nextws.id from workstation nextws where nextws.prodlineid = 1 AND proc_on_ws( 7 , nextws.id, nextws.prodlineid)

Alice Sherepa added a comment - 2020-06-30 13:24 Thanks a lot for the report and the test case! I tried the debug version of 10.4,10.5, it turned out to be the same bug as MDEV-20290 . I am closing the case here and will add a note to check the case after MDEV-20290 will be fixed. mariadbd: /git/10.5/sql/sql_prepare.cc:3018: void reinit_stmt_before_use(THD*, LEX*): Assertion `sl->join == 0' failed. 200630 15:14:06 [ERROR] mysqld got signal 6 ; linux/raise.c:51(__GI_raise)[0x7fd6212a8e97] stdlib/abort.c:81(__GI_abort)[0x7fd6212aa801] assert/assert.c:89(__assert_fail_base)[0x7fd62129a39a] ??:0(__assert_fail)[0x7fd62129a412] sql/sql_prepare.cc:3021(reinit_stmt_before_use(THD*, LEX*))[0x564818da8fa9] sql/sp_head.cc:3474(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x564818afecd8] sql/sp_head.cc:4148(sp_instr_freturn::execute(THD*, unsigned int*))[0x564818b03a4c] sql/sp_head.cc:1435(sp_head::execute(THD*, bool))[0x564818af1465] sql/sp_head.cc:2163(sp_head::execute_function(THD*, Item**, unsigned int, Field*, sp_rcontext**, Query_arena*))[0x564818af5993] sql/item.cc:2847(Item_sp::execute_impl(THD*, Item**, unsigned int))[0x564819579373] sql/item.cc:2759(Item_sp::execute(THD*, bool*, Item**, unsigned int))[0x5648195788f3] sql/item_func.cc:6500(Item_func_sp::execute())[0x56481969d489] sql/item_func.h:3418(Item_func_sp::val_int())[0x5648196ad350] sql/sql_type.cc:4915(Type_handler_int_result::Item_val_bool(Item*) const)[0x5648192d85e4] sql/item.h:1481(Item::val_bool())[0x564818e8a771] sql/item_cmpfunc.cc:5433(Item_cond_and::val_int())[0x564818e8a0bf] sql/sql_select.cc:20701(evaluate_join_record(JOIN*, st_join_table*, int))[0x564818e87a6a] sql/sql_select.cc:20642(sub_select(JOIN*, st_join_table*, bool))[0x564818e14a8d] sql/sql_select.cc:20137(do_select(JOIN*, Procedure*))[0x564818e1208a] sql/sql_select.cc:4447(JOIN::exec_inner())[0x564818e16144] sql/sql_select.cc:4229(JOIN::exec())[0x564818de82b1] sql/sql_select.cc:4654(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x564818d58a4e] sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564818d47d1c] sql/sql_parse.cc:6211(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564818d63ba5] sql/sql_parse.cc:3939(mysql_execute_command(THD*))[0x564818d3a695] sql/sql_parse.cc:7995(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564818d36f13] sql/sql_parse.cc:1877(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564819164ad9] sql/sql_parse.cc:1355(do_command(THD*))[0x564819164432] sql/sql_connect.cc:1411(do_handle_one_connection(CONNECT*, bool))[0x564819e27caf] nptl/pthread_create.c:463(start_thread)[0x7fd6221a56db] x86_64/clone.S:97(clone)[0x7fd62138b88f] Query (0x62b0000a12a8): select nextws.id from workstation nextws where nextws.prodlineid = 1 AND proc_on_ws( 7 , nextws.id, nextws.prodlineid)

Alice Sherepa made changes - 2020-06-30 13:27

Fix Version/s		N/A [ 14700 ]
Resolution		Duplicate [ 3 ]
Status	Open [ 1 ]	Closed [ 6 ]

Alice Sherepa made changes - 2020-06-30 13:27

Affects Version/s		10.4.13 [ 24223 ]
Affects Version/s	10.4.3 [ 23230 ]

Sergei Golubchik made changes - 2021-12-06 21:51

Workflow

MariaDB v3 [ 110706 ]

MariaDB v4 [ 158039 ]

MariaDB Server

server crash on select with where clause that calls a function doing a subquery

Details

Description

Shell commands to prepare environment:

Now at mariadb/mysql> prompt:

Logs:

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration