[MDEV-33121] Assertion Failed at /mariadb-11.3.0/sql/filesort.cc:663 Created: 2023-12-26  Updated: 2023-12-27

Status: Confirmed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 11.1, 11.2, 11.3, 11.3.0
Fix Version/s: 11.1, 11.2, 11.3

Type: Bug Priority: Major
Reporter: Xin Wen Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: regression-11.1
Environment:

Ubuntu 20.04

Issue Links:
Relates
relates to MDEV-15178 Filesort::make_sortorder: Assertion `... Closed
relates to MDEV-28883 Re-design the upper level of handling... Closed
relates to MDEV-29428 Incorrect result for delete with "ord... Closed

 Description   

Run these queries in debug build:

CREATE TABLE v0 ( v1 INT , v2 INT ) ;
 
DELETE FROM v0 ORDER BY COUNT( v2 ) ;

Will trigger Assertion Failed.
GDB info:

#0  0x00007ffff6c0c00b in raise () from /lib/x86_64-linux-gnu/libc.so.6
 
#1  0x00007ffff6beb859 in abort () from /lib/x86_64-linux-gnu/libc.so.6
 
#2  0x00007ffff6beb729 in ?? () from /lib/x86_64-linux-gnu/libc.so.6
 
#3  0x00007ffff6bfcfd6 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
 
#4  0x0000555557afd502 in Filesort::make_sortorder (this=0x7fffd0e244e0, thd=0x62c0001e0288, join=0x0, first_table_bit=0)
 
    at /home/wx/mariadb-11.3.0/sql/filesort.cc:663
 
#5  0x0000555557af94f8 in filesort (thd=0x62c0001e0288, table=0x61900006b908, filesort=0x7fffd0e244e0, tracker=0x6290000f72b8, join=0x0, first_table_bit=0)
 
    at /home/wx/mariadb-11.3.0/sql/filesort.cc:230
 
#6  0x00005555570c30fa in Sql_cmd_delete::delete_from_single_table (this=0x6290000f6510, thd=0x62c0001e0288) at /home/wx/mariadb-11.3.0/sql/sql_delete.cc:659
 
#7  0x00005555570ce6fe in Sql_cmd_delete::execute_inner (this=0x6290000f6510, thd=0x62c0001e0288) at /home/wx/mariadb-11.3.0/sql/sql_delete.cc:1800
 
#8  0x00005555573a7f0d in Sql_cmd_dml::execute (this=0x6290000f6510, thd=0x62c0001e0288) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:33350
 
#9  0x00005555571c1637 in mysql_execute_command (thd=0x62c0001e0288, is_called_from_prepared_stmt=false) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:4361
 
#10 0x00005555571d95e2 in mysql_parse (thd=0x62c0001e0288, rawbuf=0x6290000f52a8 "DELETE FROM v0 ORDER BY COUNT( v2 )", length=35, parser_state=0x7fffd0e25870)
 
    at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:7734
 
#11 0x00005555571b1237 in dispatch_command (command=COM_QUERY, thd=0x62c0001e0288, packet=0x6290000eb289 "DELETE FROM v0 ORDER BY COUNT( v2 )", 
    packet_length=35, blocking=true) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1893
 
#12 0x00005555571adf7c in do_command (thd=0x62c0001e0288, blocking=true) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1406
 
#13 0x000055555768e557 in do_handle_one_connection (connect=0x611000053e08, put_in_cache=true) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1445
 
#14 0x000055555768deb4 in handle_one_connection (arg=0x611000053e08) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1347
 
#15 0x00005555582fa350 in pfs_spawn_thread (arg=0x618000006908) at /home/wx/mariadb-11.3.0/storage/perfschema/pfs.cc:2201
 
#16 0x00007ffff7115609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
 
#17 0x00007ffff6ce8133 in clone () from /lib/x86_64-linux-gnu/libc.so.6



 Comments   
Comment by Alice Sherepa [ 2023-12-27 ]

Thank you!
I repeated as described on 11.1-11.3, with InnoDB, not Myisam. earlier versions- " Invalid use of group function"

mariadbd: /11.2/src/sql/filesort.cc:663: Sort_keys* Filesort::make_sortorder(THD*, JOIN*, table_map): Assertion `pos->field != __null || pos->item != __null' failed.
 
231227 12:29:57 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 11.2.3-MariaDB-debug-log source revision: fef31a26f3f4dc3c1c8dabf84c9afc93fe1ebd01
 
 
 
sql/signal_handler.cc:238(handle_fatal_signal)[0x561690b8492e]
 
sigaction.c:0(__restore_rt)[0x7f56cce2f420]
 
sql/filesort.cc:618(Filesort::make_sortorder(THD*, JOIN*, unsigned long long))[0x561690b70932]
 
sql/filesort.cc:230(filesort(THD*, TABLE*, Filesort*, Filesort_tracker*, JOIN*, unsigned long long))[0x561690b6c928]
 
sql/sql_delete.cc:656(Sql_cmd_delete::delete_from_single_table(THD*))[0x56169012adc5]
 
sql/sql_delete.cc:1804(Sql_cmd_delete::execute_inner(THD*))[0x5616901364c0]
 
sql/sql_select.cc:33604(Sql_cmd_dml::execute(THD*))[0x561690413f23]
 
sql/sql_parse.cc:4408(mysql_execute_command(THD*, bool))[0x56169022be2a]
 
sql/sql_parse.cc:7857(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x56169024396d]
 
sql/sql_parse.cc:1895(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x56169021ba30]
 
sql/sql_parse.cc:1406(do_command(THD*, bool))[0x56169021877a]
 
sql/sql_connect.cc:1417(do_handle_one_connection(CONNECT*, bool))[0x5616906faaa5]
 
sql/sql_connect.cc:1321(handle_one_connection)[0x5616906fa402]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x56169136426a]
 
nptl/pthread_create.c:478(start_thread)[0x7f56cce23609]
 
 
 
Query (0x6290001092a8): DELETE FROM v0 ORDER BY COUNT( v2 )

with update

mariadbd: /11.3/src/sql/filesort.cc:663: Sort_keys* Filesort::make_sortorder(THD*, JOIN*, table_map): Assertion `pos->field != __null || pos->item != __null' failed.
 
231227 12:48:51 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 11.3.2-MariaDB-debug-log source revision: 63fb478f88e0061d149f5cdd3c4d21d4a35c7bd9
 
 
 
/lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f0d1741dfd6]
 
sql/filesort.cc:618(Filesort::make_sortorder(THD*, JOIN*, unsigned long long))[0x556dc4f54022]
 
sql/filesort.cc:230(filesort(THD*, TABLE*, Filesort*, Filesort_tracker*, JOIN*, unsigned long long))[0x556dc4f50018]
 
sql/sql_update.cc:712(Sql_cmd_update::update_single_table(THD*))[0x556dc49a1fdb]
 
sql/sql_update.cc:3076(Sql_cmd_update::execute_inner(THD*))[0x556dc49b90c3]
 
sql/sql_select.cc:33587(Sql_cmd_dml::execute(THD*))[0x556dc47f7741]
 
sql/sql_parse.cc:4375(mysql_execute_command(THD*, bool))[0x556dc460e1fd]
 
sql/sql_parse.cc:7798(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x556dc46265f9]
 
sql/sql_parse.cc:1895(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x556dc45fdbad]
 
sql/sql_parse.cc:1406(do_command(THD*, bool))[0x556dc45fa8f2]
 
sql/sql_connect.cc:1417(do_handle_one_connection(CONNECT*, bool))[0x556dc4ade581]
 
sql/sql_connect.cc:1321(handle_one_connection)[0x556dc4addede]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x556dc574bde0]
 
nptl/pthread_create.c:478(start_thread)[0x7f0d17938609]
 
 
 
Query (0x6290001092a8): update  v0 set v1=1 ORDER BY COUNT( v2 )

Comment by Xin Wen [ 2023-12-27 ]

If a WHERE clause is added, it will trigger another assertion failure.

CREATE TABLE v0 ( v1 INT ) ;
DELETE FROM v0 WHERE v1 = 1 ORDER BY COUNT(v1) ;

GDB info:
#4 0x000055555732ffd1 in simple_remove_const (order=0x6290000f68c8, where=0x6290000f6408)
at /home/wx/mariadb-11.3.0/sql/sql_select.cc:16848
16848 DBUG_ASSERT(!order->item[0]->with_sum_func()); // should never happen

#0 0x00007ffff6c0c00b in raise () from /lib/x86_64-linux-gnu/libc.so.6
#1 0x00007ffff6beb859 in abort () from /lib/x86_64-linux-gnu/libc.so.6
#2 0x00007ffff6beb729 in ?? () from /lib/x86_64-linux-gnu/libc.so.6
#3 0x00007ffff6bfcfd6 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
#4 0x000055555732ffd1 in simple_remove_const (order=0x6290000f68c8, where=0x6290000f6408)
at /home/wx/mariadb-11.3.0/sql/sql_select.cc:16848
#5 0x00005555570c2101 in Sql_cmd_delete::delete_from_single_table (this=0x6290000f6928, thd=0x62c0001d0288)
at /home/wx/mariadb-11.3.0/sql/sql_delete.cc:557
#6 0x00005555570ce6fe in Sql_cmd_delete::execute_inner (this=0x6290000f6928, thd=0x62c0001d0288)
at /home/wx/mariadb-11.3.0/sql/sql_delete.cc:1800
#7 0x00005555573a7f0d in Sql_cmd_dml::execute (this=0x6290000f6928, thd=0x62c0001d0288)
at /home/wx/mariadb-11.3.0/sql/sql_select.cc:33350
#8 0x00005555571c1637 in mysql_execute_command (thd=0x62c0001d0288, is_called_from_prepared_stmt=false)
at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:4361
#9 0x00005555571d95e2 in mysql_parse (thd=0x62c0001d0288,
rawbuf=0x6290000f52a8 "DELETE FROM v0 WHERE v1 = 1 ORDER BY COUNT(v1)", length=46,
parser_state=0x7fffd163c870) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:7734
#10 0x00005555571b1237 in dispatch_command (command=COM_QUERY, thd=0x62c0001d0288,
packet=0x6290000eb289 "DELETE FROM v0 WHERE v1 = 1 ORDER BY COUNT(v1)", packet_length=46, blocking=true)
at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1893
#11 0x00005555571adf7c in do_command (thd=0x62c0001d0288, blocking=true)
at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1406
#12 0x000055555768e557 in do_handle_one_connection (connect=0x6110000429c8, put_in_cache=true)
at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1445
#13 0x000055555768deb4 in handle_one_connection (arg=0x6110000429c8)
at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1347
#14 0x00005555582fa350 in pfs_spawn_thread (arg=0x618000007908)
at /home/wx/mariadb-11.3.0/storage/perfschema/pfs.cc:2201
#15 0x00007ffff7115609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
#16 0x00007ffff6ce8133 in clone () from /lib/x86_64-linux-gnu/libc.so.6

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