[MDEV-24191] A bug occurs when an operation is put in the OVER query. Mysqld will be restart Created: 2020-11-11  Updated: 2020-11-11  Resolved: 2020-11-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.3.14
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: lee sang eui Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

client : mysql connector 5.1.46

server : kernel 3.10.0-957.10.1.el7.x86_64
CentOS Linux release 7.6.1810 (Core)

mariaDb : 10.3.14-MariaDB-log
Galera 25.3.26(r3857) by Codership

Issue Links:
Duplicate
duplicates MDEV-14791 Crash with order by expression contai... Closed

 Description   

Description:

The actual mysqld-err.log is as follows.

bash
 
Thread pointer: 0x7fbbec183678
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7fbbe5637d30 thread_stack 0x49000
 
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x5604930a4dfe]
 
/usr/sbin/mysqld(handle_fatal_signal+0x357)[0x560492b44cc7]
 
sigaction.c:0(__restore_rt)[0x7fbcdd0cc5d0]
 
/usr/sbin/mysqld(_ZN4Item17save_int_in_fieldEP5Fieldb+0x25)[0x560492b67775]
 
/usr/sbin/mysqld(_ZN4Item13save_in_fieldEP5Fieldb+0x36)[0x560492b58f66]
 
/usr/sbin/mysqld(_Z19compute_window_funcP3THDR4ListI16Item_window_funcERS1_I14Cursor_managerEP5TABLEP9SORT_INFO+0x37a)[0x560492aa39ea]
 
/usr/sbin/mysqld(_ZN18Window_func_runner4execEP3THDP5TABLEP9SORT_INFO+0x99)[0x560492aa3cf9]
 
/usr/sbin/mysqld(_ZN17Window_funcs_sort4execEP4JOINb+0x7e)[0x560492aa3e2e]
 
/usr/sbin/mysqld(_ZN24Window_funcs_computation4execEP4JOINb+0x2a)[0x560492aa451a]
 
/usr/sbin/mysqld(_ZN7AGGR_OP8end_sendEv+0xe7)[0x5604929c3267]
 
/usr/sbin/mysqld(_Z24sub_select_postjoin_aggrP4JOINP13st_join_tableb+0x39)[0x5604929c3489]
 
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1cc)[0x5604929c4f2c]
 
/usr/sbin/mysqld(+0x4b543c)[0x56049287743c]
 
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x239b)[0x560492967edb]
 
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x22b)[0x56049296ecab]
 
/usr/sbin/mysqld(+0x5ad639)[0x56049296f639]
 
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1299)[0x560492970f89]
 
/usr/sbin/mysqld(_Z10do_commandP3THD+0x13e)[0x56049297288e]
 
/usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1aa)[0x560492a48b6a]
 
/usr/sbin/mysqld(handle_one_connection+0x3d)[0x560492a48c8d]
 
pthread_create.c:0(start_thread)[0x7fbcdd0c4dd5]
 
/lib64/libc.so.6(clone+0x6d)[0x7fbcdb466ead]

The actual query is as follows.

SELECT REQ_GRP_ID
 
FROM TB_REQ_MAIN
 
WHERE REQ_GRP_ID ='REQ_GRP_000000440'
 
    ORDER BY ROW_NUMBER() OVER (ORDER BY CREATE_DTM) + 1;

The problem is ORDER BY ROW_NUMBER() OVER (ORDER BY RM.CREATE_DTM)`+1` part.

In the actual application, it occurs as follows

[2020-11-11 17:21:53] [08S01] Communications link failure
 
[2020-11-11 17:21:53] The last packet successfully received from the server was 149 milliseconds ago. The last packet sent successfully to the server was 149 milliseconds ago.
 
[2020-11-11 17:21:53] java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

How to repeat:

SELECT REQ_GRP_ID
 
FROM TB_REQ_MAIN
 
WHERE REQ_GRP_ID ='REQ_GRP_000000440'
 
    ORDER BY ROW_NUMBER() OVER (ORDER BY CREATE_DTM) + 1;

Suggested fix:
SELECT REQ_GRP_ID
FROM TB_REQ_MAIN
WHERE REQ_GRP_ID ='REQ_GRP_000000440'
ORDER BY ROW_NUMBER() OVER (ORDER BY CREATE_DTM);

 Comments   
Comment by Alice Sherepa [ 2020-11-11 ]

Thanks! I repeated on 10.3.14

10.3.14

 
sql/item.cc:10687(Item::save_int_in_field(Field*, bool))[0x564f95bdb4fc]
 
sql/item.cc:6863(Item::save_in_field(Field*, bool))[0x564f95bce9cd]
 
sql/sql_list.h:431(compute_window_func(THD*, List<Item_window_func>&, List<Cursor_manager>&, TABLE*, SORT_INFO*))[0x564f95b27712]
 
sql/sql_list.h:431(Window_func_runner::exec(THD*, TABLE*, SORT_INFO*))[0x564f95b278fc]
 
sql/sql_window.cc:2959(Window_funcs_sort::exec(JOIN*, bool))[0x564f95b279e6]
 
sql/sql_window.cc:3083(Window_funcs_computation::exec(JOIN*, bool))[0x564f95b27a92]
 
sql/sql_select.cc:27398(AGGR_OP::end_send())[0x564f95a44104]
 
sql/sql_select.cc:19113(sub_select_postjoin_aggr(JOIN*, st_join_table*, bool))[0x564f95a44301]
 
sql/sql_select.cc:18938(do_select)[0x564f95a50f6b]
 
sql/sql_select.cc:3835(JOIN::exec())[0x564f95a512bc]
 
sql/sql_select.cc:4241(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*))[0x564f95a5010a]
 
sql/sql_select.cc:385(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564f95a514cc]
 
sql/sql_parse.cc:6549(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564f95918779]
 
sql/sql_parse.cc:3821(mysql_execute_command(THD*))[0x564f95a01a69]
 
sql/sql_class.h:1820(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564f95a03d19]
 
sql/sql_parse.cc:1857(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564f95a05e95]
 
sql/sql_parse.cc:1405(do_command(THD*))[0x564f95a0663e]
 
sql/sql_connect.cc:1402(do_handle_one_connection(CONNECT*))[0x564f95ace36f]
 
sql/sql_connect.cc:1310(handle_one_connection)[0x564f95ace494]
 
nptl/pthread_create.c:487(start_thread)[0x7f72e9ea7fa3]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f72e9a814cf]
 
Query (0x7f7278011870): SELECT i FROM t WHERE i =1     ORDER BY ROW_NUMBER() OVER (ORDER BY j) + 1

It is the same problem as MDEV-14791, the query works as expected in 10.3.15,
please upgrade MariaDB server.

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