[MDEV-31143] Crash for query using ROWNUM() over view with ORDER BY Created: 2023-04-27  Updated: 2023-05-02  Resolved: 2023-05-01

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.6, 10.8, 10.9, 10.10
Fix Version/s: 10.11.3, 11.0.2, 10.6.13, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Blocker
Reporter: Alice Sherepa Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Relates
relates to MDEV-31073 Server crash, assertion `table != 0 &... Closed

 Description   

regression after 5bada1246de48ef4a18fa30388f, repeatable after MDEV-31073 fix:

CREATE TABLE t1 (id int NOT NULL PRIMARY KEY);
 
SELECT t1.id AS fld1 FROM sys.schema_index_statistics , t1 WHERE rownum() = 1 GROUP BY fld1;


Version: '10.6.13-MariaDB-debug-log'  
230428 11:26:28 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.6.13-MariaDB-debug-log source revision: 2eb7bf1ec301d43a7e72b8720f6ee153dc52b6c9
 
 
 
sql/signal_handler.cc:241(handle_fatal_signal)[0x564018b2e716]
 
sigaction.c:0(__restore_rt)[0x7fef4eb59420]
 
maria/ha_maria.cc:1106(ha_maria::scan_time())[0x564019102df4]
 
sql/opt_range.cc:2714(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool))[0x564018f39b67]
 
sql/sql_select.cc:5113(get_quick_record_count(THD*, SQL_SELECT*, TABLE*, Bitmap<64u> const*, unsigned long long))[0x56401838665b]
 
sql/sql_select.cc:5840(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x56401838d400]
 
sql/sql_select.cc:2509(JOIN::optimize_inner())[0x56401836b171]
 
sql/sql_select.cc:1848(JOIN::optimize())[0x5640183641cf]
 
sql/sql_select.cc:5055(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*))[0x564018385d68]
 
sql/sql_select.cc:559(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564018355b9f]
 
sql/sql_parse.cc:6273(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5640182b6e8e]
 
sql/sql_parse.cc:3949(mysql_execute_command(THD*, bool))[0x5640182a57fb]
 
sql/sql_parse.cc:8036(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x5640182c21b9]
 
sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x56401829803a]
 
sql/sql_parse.cc:1409(do_command(THD*, bool))[0x564018294d85]
 
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x564018710df9]
 
sql/sql_connect.cc:1320(handle_one_connection)[0x564018710756]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5640193a8d44]
 
nptl/pthread_create.c:478(start_thread)[0x7fef4eb4d609]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fef4e71e133]
 
 
 
Query (0x62b0000c42a8): SELECT t1.id AS fld1 FROM sys.schema_index_statistics , t1 WHERE rownum() = 1 GROUP BY fld1


CREATE TABLE t1 (id int NOT NULL PRIMARY KEY);
 
CREATE  VIEW  v1 AS SELECT  sys.format_time(tb.sum_timer_fetch) FROM performance_schema.table_io_waits_summary_by_index_usage tb ORDER BY tb.sum_timer_wait;
 
 
 
SELECT t1.id AS fld1 FROM v1, t1 WHERE rownum() = 1 GROUP BY fld1;



 Comments   
Comment by Oleksandr Byelkin [ 2023-04-28 ]

It is temporary table of derived table (made from sys.schema_index_statistics?) has myisam handler but have no file reference (as vell as many other uninitialized fields)

Comment by Oleksandr Byelkin [ 2023-04-28 ]

Fix applied to MDEV-31073 does not work here bacause it is not special case for materialization for rownum as it was there (several tables and actually ORDE BY in the outer most statement) but some part of the code definetly does not expect that sys.schema_index_statistics can be merged (can it really?)

Comment by Oleksandr Byelkin [ 2023-04-28 ]

actually information schema AFAIK always shoud be matherialyzed derved

Comment by Igor Babaev [ 2023-04-30 ]

The following test case causes a crash of the same kind as reported one.

create table t1 (id int primary key);
 
insert into t1 values (3), (7), (1);
 
 
 
create table t2 (a int);
 
insert into t2 values (2), (4);
 
 
 
create view v as select a from t2 order by a;
 
 
 
set big_tables= 1;
 
select t1.id from v, t1 where rownum() = 1 group by t1.id;
 
 
 
set big_tables=default;
 
 
 
drop view 
drop table t1, t2


sql/signal_handler.cc:241(handle_fatal_signal)[0x557794bd49b1]
 
/lib64/libpthread.so.0(+0xf890)[0x7f6280744890]
 
maria/ha_maria.cc:1106(ha_maria::scan_time())[0x557794e78aaf]
 
sql/opt_range.cc:2714(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool))[0x557794d85b17]
 
sql/sql_select.cc:5113(get_quick_record_count(THD*, SQL_SELECT*, TABLE*, Bitmap<64u> const*, unsigned long long))[0x5577948853bc]
 
sql/sql_select.cc:5841(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x557794887cd3]
 
sql/sql_select.cc:2509(JOIN::optimize_inner())[0x55779487ba5b]
 
sql/sql_select.cc:1848(JOIN::optimize())[0x5577948792a2]
 
sql/sql_select.cc:5055(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*))[0x557794885085]
 
sql/sql_select.cc:571(handle_select(THD*, LEX*, select_result*, unsigned long))[0x557794873d82]
 
sql/sql_parse.cc:6273(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5577948331e0]
 
sql/sql_parse.cc:3949(mysql_execute_command(THD*, bool))[0x55779482a58b]
 
sql/sql_parse.cc:8036(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x557794837f5b]
 
sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x5577948245ce]
 
sql/sql_parse.cc:1410(do_command(THD*, bool))[0x557794822e74]
 
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5577949eabc7]
 
sql/sql_connect.cc:1320(handle_one_connection)[0x5577949ea931]

Comment by Oleksandr Byelkin [ 2023-04-30 ]

ok to push

Comment by Igor Babaev [ 2023-05-01 ]

A fix for this bug was pushed into 10.6. It should be merged upstream as it is.

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