[MDEV-24819] Wrong result or ASAN use-after-poison errors upon SELECT with GROUP BY Created: 2021-02-09  Updated: 2023-11-28

Status: In Review
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: 11.0-sel

Issue Links:
Relates
relates to MDEV-29179 Condition pushdown from HAVING into W... Closed
relates to MDEV-27572 Wrong result with DISTINCT and indexes Confirmed

 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (1,3),(2,6),(3,9),(4,NULL),(5,NULL);
 
 
 
ANALYZE TABLE t1; # optional, fails either way
 
SELECT a, pk FROM t1 WHERE pk != 0 AND (a < 0 OR pk <= 0) GROUP BY a, pk;
 
 
 
# Cleanup
 
DROP TABLE t1;

10.5 ffc5d064 ASAN

 
==1352396==ERROR: AddressSanitizer: use-after-poison on address 0x6210000d4af5 at pc 0x55c540c0bb39 bp 0x7ff9e84d9220 sp 0x7ff9e84d9210
 
READ of size 1 at 0x6210000d4af5 thread T13
 
    #0 0x55c540c0bb38 in row_mysql_store_col_in_innobase_format(dfield_t*, unsigned char*, unsigned long, unsigned char const*, unsigned long, unsigned long) /data/src/10.5/storage/innobase/row/row0mysql.cc:434
 
    #1 0x55c540c9387d in row_sel_convert_mysql_key_to_innobase(dtuple_t*, unsigned char*, unsigned long, dict_index_t*, unsigned char const*, unsigned long) /data/src/10.5/storage/innobase/row/row0sel.cc:2602
 
    #2 0x55c540877d6f in ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function) /data/src/10.5/storage/innobase/handler/ha_innodb.cc:8741
 
    #3 0x55c53fd75a6a in handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) /data/src/10.5/sql/handler.h:3798
 
    #4 0x55c53fd45927 in handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) /data/src/10.5/sql/handler.cc:3123
 
    #5 0x55c53fd60869 in handler::read_range_first(st_key_range const*, st_key_range const*, bool, bool) /data/src/10.5/sql/handler.cc:6197
 
    #6 0x55c54016ba9c in QUICK_RANGE_SELECT::get_next_prefix(unsigned int, unsigned int, unsigned char*) /data/src/10.5/sql/opt_range.cc:12688
 
    #7 0x55c54017f118 in QUICK_GROUP_MIN_MAX_SELECT::next_prefix() /data/src/10.5/sql/opt_range.cc:15519
 
    #8 0x55c54017d81d in QUICK_GROUP_MIN_MAX_SELECT::get_next() /data/src/10.5/sql/opt_range.cc:15261
 
    #9 0x55c5401a0549 in rr_quick /data/src/10.5/sql/records.cc:403
 
    #10 0x55c53f37006d in READ_RECORD::read_record() /data/src/10.5/sql/records.h:80
 
    #11 0x55c53f67ab6b in sub_select(JOIN*, st_join_table*, bool) /data/src/10.5/sql/sql_select.cc:20707
 
    #12 0x55c53f678a09 in do_select /data/src/10.5/sql/sql_select.cc:20222
 
    #13 0x55c53f605378 in JOIN::exec_inner() /data/src/10.5/sql/sql_select.cc:4466
 
    #14 0x55c53f602963 in JOIN::exec() /data/src/10.5/sql/sql_select.cc:4246
 
    #15 0x55c53f606d37 in 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*) /data/src/10.5/sql/sql_select.cc:4719
 
    #16 0x55c53f5d8575 in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.5/sql/sql_select.cc:417
 
    #17 0x55c53f541b71 in execute_sqlcom_select /data/src/10.5/sql/sql_parse.cc:6281
 
    #18 0x55c53f530a6c in mysql_execute_command(THD*) /data/src/10.5/sql/sql_parse.cc:3977
 
    #19 0x55c53f54ce6b in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /data/src/10.5/sql/sql_parse.cc:8062
 
    #20 0x55c53f523158 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.5/sql/sql_parse.cc:1889
 
    #21 0x55c53f51fa81 in do_command(THD*) /data/src/10.5/sql/sql_parse.cc:1370
 
    #22 0x55c53f96235b in do_handle_one_connection(CONNECT*, bool) /data/src/10.5/sql/sql_connect.cc:1410
 
    #23 0x55c53f961cbf in handle_one_connection /data/src/10.5/sql/sql_connect.cc:1312
 
    #24 0x55c540670200 in pfs_spawn_thread /data/src/10.5/storage/perfschema/pfs.cc:2201
 
    #25 0x7ff9f7a4c608 in start_thread /build/glibc-ZN95T4/glibc-2.31/nptl/pthread_create.c:477
 
    #26 0x7ff9f7622292 in __clone (/lib/x86_64-linux-gnu/libc.so.6+0x122292)
 
 
 
0x6210000d4af5 is located 501 bytes inside of 4196-byte region [0x6210000d4900,0x6210000d5964)
 
allocated by thread T13 here:
 
    #0 0x7ff9f7f3abc8 in malloc (/lib/x86_64-linux-gnu/libasan.so.5+0x10dbc8)
 
    #1 0x55c54130e87c in sf_malloc /data/src/10.5/mysys/safemalloc.c:121
 
    #2 0x55c5412dbc81 in my_malloc /data/src/10.5/mysys/my_malloc.c:90
 
    #3 0x55c5412b7a78 in alloc_root /data/src/10.5/mysys/my_alloc.c:244
 
    #4 0x55c54017af82 in QUICK_GROUP_MIN_MAX_SELECT::init() /data/src/10.5/sql/opt_range.cc:14919
 
    #5 0x55c540179dcf in TRP_GROUP_MIN_MAX::make_quick(PARAM*, bool, st_mem_root*) /data/src/10.5/sql/opt_range.cc:14760
 
    #6 0x55c540129386 in SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool) /data/src/10.5/sql/opt_range.cc:3054
 
    #7 0x55c53f60743d in get_quick_record_count /data/src/10.5/sql/sql_select.cc:4763
 
    #8 0x55c53f60e394 in make_join_statistics /data/src/10.5/sql/sql_select.cc:5494
 
    #9 0x55c53f5ece6b in JOIN::optimize_inner() /data/src/10.5/sql/sql_select.cc:2255
 
    #10 0x55c53f5e634b in JOIN::optimize() /data/src/10.5/sql/sql_select.cc:1627
 
    #11 0x55c53f606b42 in 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*) /data/src/10.5/sql/sql_select.cc:4705
 
    #12 0x55c53f5d8575 in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.5/sql/sql_select.cc:417
 
    #13 0x55c53f541b71 in execute_sqlcom_select /data/src/10.5/sql/sql_parse.cc:6281
 
    #14 0x55c53f530a6c in mysql_execute_command(THD*) /data/src/10.5/sql/sql_parse.cc:3977
 
    #15 0x55c53f54ce6b in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /data/src/10.5/sql/sql_parse.cc:8062
 
    #16 0x55c53f523158 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.5/sql/sql_parse.cc:1889
 
    #17 0x55c53f51fa81 in do_command(THD*) /data/src/10.5/sql/sql_parse.cc:1370
 
    #18 0x55c53f96235b in do_handle_one_connection(CONNECT*, bool) /data/src/10.5/sql/sql_connect.cc:1410
 
    #19 0x55c53f961cbf in handle_one_connection /data/src/10.5/sql/sql_connect.cc:1312
 
    #20 0x55c540670200 in pfs_spawn_thread /data/src/10.5/storage/perfschema/pfs.cc:2201
 
    #21 0x7ff9f7a4c608 in start_thread /build/glibc-ZN95T4/glibc-2.31/nptl/pthread_create.c:477
 
 
 
Thread T13 created by T0 here:
 
    #0 0x7ff9f7e67805 in pthread_create (/lib/x86_64-linux-gnu/libasan.so.5+0x3a805)
 
    #1 0x55c54066b1a4 in my_thread_create /data/src/10.5/storage/perfschema/my_thread.h:38
 
    #2 0x55c5406705f3 in pfs_spawn_thread_v1 /data/src/10.5/storage/perfschema/pfs.cc:2252
 
    #3 0x55c53f2134fe in inline_mysql_thread_create /data/src/10.5/include/mysql/psi/mysql_thread.h:1323
 
    #4 0x55c53f229512 in create_thread_to_handle_connection(CONNECT*) /data/src/10.5/sql/mysqld.cc:6028
 
    #5 0x55c53f229b91 in create_new_thread(CONNECT*) /data/src/10.5/sql/mysqld.cc:6087
 
    #6 0x55c53f229eee in handle_accepted_socket(st_mysql_socket, st_mysql_socket) /data/src/10.5/sql/mysqld.cc:6152
 
    #7 0x55c53f22ab0d in handle_connections_sockets() /data/src/10.5/sql/mysqld.cc:6279
 
    #8 0x55c53f228d1f in mysqld_main(int, char**) /data/src/10.5/sql/mysqld.cc:5674
 
    #9 0x55c53f211d9c in main /data/src/10.5/sql/main.cc:25
 
    #10 0x7ff9f75270b2 in __libc_start_main (/lib/x86_64-linux-gnu/libc.so.6+0x270b2)
 
 
 
SUMMARY: AddressSanitizer: use-after-poison /data/src/10.5/storage/innobase/row/row0mysql.cc:434 in row_mysql_store_col_in_innobase_format(dfield_t*, unsigned char*, unsigned long, unsigned char const*, unsigned long, unsigned long)
 
Shadow bytes around the buggy address:
 
  0x0c4280012900: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c4280012910: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c4280012920: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c4280012930: 00 00 00 00 f7 00 02 f7 00 02 f7 00 00 00 00 00
 
  0x0c4280012940: 00 00 00 00 00 00 00 00 00 00 00 f7 04 f7 00 00
 
=>0x0c4280012950: 00 00 00 f7 01 f7 05 f7 00 00 00 00 00 f7[05]f7
 
  0x0c4280012960: 01 f7 00 00 00 00 00 00 00 00 f7 f7 f7 f7 f7 f7
 
  0x0c4280012970: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
  0x0c4280012980: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
  0x0c4280012990: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
  0x0c42800129a0: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
Shadow byte legend (one shadow byte represents 8 application bytes):
 
  Addressable:           00
 
  Partially addressable: 01 02 03 04 05 06 07 
  Heap left redzone:       fa
 
  Freed heap region:       fd
 
  Stack left redzone:      f1
 
  Stack mid redzone:       f2
 
  Stack right redzone:     f3
 
  Stack after return:      f5
 
  Stack use after scope:   f8
 
  Global redzone:          f9
 
  Global init order:       f6
 
  Poisoned by user:        f7
 
  Container overflow:      fc
 
  Array cookie:            ac
 
  Intra object redzone:    bb
 
  ASan internal:           fe
 
  Left alloca redzone:     ca
 
  Right alloca redzone:    cb
 
  Shadow gap:              cc
 
==1352396==ABORTING

Reproducible on 10.5, 10.6.
No obvious immediate problem on a non-ASAN build.
No obvious failures with MyISAM instead of InnoDB.
Upd: See comments for an alternative test case affecting MyISAM and Aria and causing problems on non-ASAN builds
Not reproducible on 10.4.
The commit below introduced in 10.5 a change of plan which leads to the failure. I don't know if the plan and the problem are possible to achieve in 10.4 by adjusting the test case.

commit b3ab3105fdb34dae6c2d4270751bc0694c3d9df8
 
Author: Monty
 
Date:   Wed Mar 4 19:52:19 2020 +0200
 
 
 
    Removed double calls to records_in_range from distinct and group by
 
    
    Fixed by moving testing of get_best_group_min_max() after range testing.

Plan after the change (failing)

 
explain extended SELECT a, pk FROM t1 WHERE pk != 0 AND (a < 0 OR pk <= 0) GROUP BY a, pk;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	SIMPLE	t1	range	PRIMARY,a	a	9	NULL	1	100.00	Using where; Using index for group-by
 
Warnings:
 
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`pk` AS `pk` from `test`.`t1` where `test`.`t1`.`pk` <> 0 and (`test`.`t1`.`a` < 0 or `test`.`t1`.`pk` <= 0) group by `test`.`t1`.`a`,`test`.`t1`.`pk`

Plan before the change (not failing)

 
explain extended SELECT a, pk FROM t1 WHERE pk != 0 AND (a < 0 OR pk <= 0) GROUP BY a, pk;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	SIMPLE	t1	index	PRIMARY,a	a	5	NULL	5	100.00	Using where; Using index
 
Warnings:
 
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`pk` AS `pk` from `test`.`t1` where `test`.`t1`.`pk` <> 0 and (`test`.`t1`.`a` < 0 or `test`.`t1`.`pk` <= 0) group by `test`.`t1`.`a`,`test`.`t1`.`pk`



 Comments   
Comment by Elena Stepanova [ 2021-12-12 ]

The test case below causes the same or very similar failure with InnoDB, but also fails with MyISAM and Aria, unlike the one in the description:

CREATE TABLE t1 (a INT, b VARCHAR(1), KEY (a), KEY(b,a)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (4,'n'),(1,'h'),(NULL,'w');
 
 
 
SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7;
 
 
 
# Cleanup
 
DROP TABLE t1;

10.5 88b33980

 
==48380==ERROR: AddressSanitizer: use-after-poison on address 0x6210000912a8 at pc 0x55962df96d2b bp 0x7f36fd5eb960 sp 0x7f36fd5eb950
 
READ of size 1 at 0x6210000912a8 thread T5
 
    #0 0x55962df96d2a in _mi_pack_key /data/src/10.5/storage/myisam/mi_key.c:295
 
    #1 0x55962e03d574 in mi_rkey /data/src/10.5/storage/myisam/mi_rkey.c:62
 
    #2 0x55962df2fe2b in ha_myisam::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) /data/src/10.5/storage/myisam/ha_myisam.cc:1969
 
    #3 0x55962cbc7591 in handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) /data/src/10.5/sql/handler.cc:3137
 
    #4 0x55962cbe25d5 in handler::read_range_first(st_key_range const*, st_key_range const*, bool, bool) /data/src/10.5/sql/handler.cc:6217
 
    #5 0x55962cfecc8c in QUICK_RANGE_SELECT::get_next_prefix(unsigned int, unsigned int, unsigned char*) /data/src/10.5/sql/opt_range.cc:12742
 
    #6 0x55962d0002bc in QUICK_GROUP_MIN_MAX_SELECT::next_prefix() /data/src/10.5/sql/opt_range.cc:15573
 
    #7 0x55962cffe9c1 in QUICK_GROUP_MIN_MAX_SELECT::get_next() /data/src/10.5/sql/opt_range.cc:15315
 
    #8 0x55962d021933 in rr_quick /data/src/10.5/sql/records.cc:403
 
    #9 0x55962c1dc231 in READ_RECORD::read_record() /data/src/10.5/sql/records.h:80
 
    #10 0x55962c4f2f0f in join_init_read_record(st_join_table*) /data/src/10.5/sql/sql_select.cc:21845
 
    #11 0x55962c4ebfe9 in sub_select(JOIN*, st_join_table*, bool) /data/src/10.5/sql/sql_select.cc:20877
 
    #12 0x55962c4ea2ab in do_select /data/src/10.5/sql/sql_select.cc:20414
 
    #13 0x55962c475e75 in JOIN::exec_inner() /data/src/10.5/sql/sql_select.cc:4516
 
    #14 0x55962c47347f in JOIN::exec() /data/src/10.5/sql/sql_select.cc:4296
 
    #15 0x55962c4778c4 in 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*) /data/src/10.5/sql/sql_select.cc:4773
 
    #16 0x55962c448db4 in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.5/sql/sql_select.cc:444
 
    #17 0x55962c3b0ba3 in execute_sqlcom_select /data/src/10.5/sql/sql_parse.cc:6314
 
    #18 0x55962c39fb44 in mysql_execute_command(THD*) /data/src/10.5/sql/sql_parse.cc:4005
 
    #19 0x55962c3bbefd in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /data/src/10.5/sql/sql_parse.cc:8100
 
    #20 0x55962c391db8 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.5/sql/sql_parse.cc:1891
 
    #21 0x55962c38e6f7 in do_command(THD*) /data/src/10.5/sql/sql_parse.cc:1370
 
    #22 0x55962c7d986a in do_handle_one_connection(CONNECT*, bool) /data/src/10.5/sql/sql_connect.cc:1418
 
    #23 0x55962c7d9083 in handle_one_connection /data/src/10.5/sql/sql_connect.cc:1312
 
    #24 0x55962d445c76 in pfs_spawn_thread /data/src/10.5/storage/perfschema/pfs.cc:2201
 
    #25 0x7f3706f4a608 in start_thread /build/glibc-eX1tMB/glibc-2.31/nptl/pthread_create.c:477
 
    #26 0x7f3706b1d292 in __clone (/lib/x86_64-linux-gnu/libc.so.6+0x122292)
 
 
 
0x6210000912a8 is located 424 bytes inside of 4196-byte region [0x621000091100,0x621000092164)
 
allocated by thread T5 here:
 
    #0 0x7f3707613bc8 in malloc (/lib/x86_64-linux-gnu/libasan.so.5+0x10dbc8)
 
    #1 0x55962e0fbb43 in sf_malloc /data/src/10.5/mysys/safemalloc.c:121
 
    #2 0x55962e0c9347 in my_malloc /data/src/10.5/mysys/my_malloc.c:90
 
    #3 0x55962e0a50fb in alloc_root /data/src/10.5/mysys/my_alloc.c:244
 
    #4 0x55962cffc126 in QUICK_GROUP_MIN_MAX_SELECT::init() /data/src/10.5/sql/opt_range.cc:14973
 
    #5 0x55962cffaf73 in TRP_GROUP_MIN_MAX::make_quick(PARAM*, bool, st_mem_root*) /data/src/10.5/sql/opt_range.cc:14814
 
    #6 0x55962cfaa2a5 in SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool) /data/src/10.5/sql/opt_range.cc:3055
 
    #7 0x55962c477fca in get_quick_record_count /data/src/10.5/sql/sql_select.cc:4817
 
    #8 0x55962c47ee98 in make_join_statistics /data/src/10.5/sql/sql_select.cc:5544
 
    #9 0x55962c45d933 in JOIN::optimize_inner() /data/src/10.5/sql/sql_select.cc:2296
 
    #10 0x55962c456e2f in JOIN::optimize() /data/src/10.5/sql/sql_select.cc:1668
 
    #11 0x55962c4776cf in 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*) /data/src/10.5/sql/sql_select.cc:4759
 
    #12 0x55962c448db4 in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.5/sql/sql_select.cc:444
 
    #13 0x55962c3b0ba3 in execute_sqlcom_select /data/src/10.5/sql/sql_parse.cc:6314
 
    #14 0x55962c39fb44 in mysql_execute_command(THD*) /data/src/10.5/sql/sql_parse.cc:4005
 
    #15 0x55962c3bbefd in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /data/src/10.5/sql/sql_parse.cc:8100
 
    #16 0x55962c391db8 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.5/sql/sql_parse.cc:1891
 
    #17 0x55962c38e6f7 in do_command(THD*) /data/src/10.5/sql/sql_parse.cc:1370
 
    #18 0x55962c7d986a in do_handle_one_connection(CONNECT*, bool) /data/src/10.5/sql/sql_connect.cc:1418
 
    #19 0x55962c7d9083 in handle_one_connection /data/src/10.5/sql/sql_connect.cc:1312
 
    #20 0x55962d445c76 in pfs_spawn_thread /data/src/10.5/storage/perfschema/pfs.cc:2201
 
    #21 0x7f3706f4a608 in start_thread /build/glibc-eX1tMB/glibc-2.31/nptl/pthread_create.c:477
 
 
 
Thread T5 created by T0 here:
 
    #0 0x7f3707540805 in pthread_create (/lib/x86_64-linux-gnu/libasan.so.5+0x3a805)
 
    #1 0x55962d440c24 in my_thread_create /data/src/10.5/storage/perfschema/my_thread.h:48
 
    #2 0x55962d446069 in pfs_spawn_thread_v1 /data/src/10.5/storage/perfschema/pfs.cc:2252
 
    #3 0x55962c07e652 in inline_mysql_thread_create /data/src/10.5/include/mysql/psi/mysql_thread.h:1323
 
    #4 0x55962c094991 in create_thread_to_handle_connection(CONNECT*) /data/src/10.5/sql/mysqld.cc:6010
 
    #5 0x55962c095010 in create_new_thread(CONNECT*) /data/src/10.5/sql/mysqld.cc:6069
 
    #6 0x55962c09536d in handle_accepted_socket(st_mysql_socket, st_mysql_socket) /data/src/10.5/sql/mysqld.cc:6134
 
    #7 0x55962c095fbf in handle_connections_sockets() /data/src/10.5/sql/mysqld.cc:6261
 
    #8 0x55962c09418d in mysqld_main(int, char**) /data/src/10.5/sql/mysqld.cc:5656
 
    #9 0x55962c07d11c in main /data/src/10.5/sql/main.cc:25
 
    #10 0x7f3706a220b2 in __libc_start_main (/lib/x86_64-linux-gnu/libc.so.6+0x270b2)
 
 
 
SUMMARY: AddressSanitizer: use-after-poison /data/src/10.5/storage/myisam/mi_key.c:295 in _mi_pack_key
 
Shadow bytes around the buggy address:
 
  0x0c428000a200: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c428000a210: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c428000a220: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c428000a230: 00 00 00 00 f7 00 02 f7 00 02 f7 00 00 00 00 00
 
  0x0c428000a240: 00 00 00 00 00 00 00 00 00 00 00 f7 04 f7 00 00
 
=>0x0c428000a250: 00 00 00 f7 06[f7]06 f7 00 00 00 00 00 00 00 00
 
  0x0c428000a260: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
  0x0c428000a270: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
  0x0c428000a280: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
  0x0c428000a290: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
  0x0c428000a2a0: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
Shadow byte legend (one shadow byte represents 8 application bytes):
 
  Addressable:           00
 
  Partially addressable: 01 02 03 04 05 06 07 
  Heap left redzone:       fa
 
  Freed heap region:       fd
 
  Stack left redzone:      f1
 
  Stack mid redzone:       f2
 
  Stack right redzone:     f3
 
  Stack after return:      f5
 
  Stack use after scope:   f8
 
  Global redzone:          f9
 
  Global init order:       f6
 
  Poisoned by user:        f7
 
  Container overflow:      fc
 
  Array cookie:            ac
 
  Intra object redzone:    bb
 
  ASan internal:           fe
 
  Left alloca redzone:     ca
 
  Right alloca redzone:    cb
 
  Shadow gap:              cc
 
==48380==ABORTING
 
211212 22:24:06 [ERROR] mysqld got signal 6 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.5.14-MariaDB-debug-log
 
key_buffer_size=1048576
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=153
 
thread_count=1
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 63752 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x62b000069288
 
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 = 0x7f36fd5eecd0 thread_stack 0x100000
 
??:0(__interceptor_tcgetattr)[0x7f3707572d30]
 
mysys/stacktrace.c:212(my_print_stacktrace)[0x55962e0d998f]
 
sql/signal_handler.cc:225(handle_fatal_signal)[0x55962cbaba6d]
 
sigaction.c:0(__restore_rt)[0x7f3706f563c0]
 
??:0(gsignal)[0x7f3706a4118b]
 
??:0(abort)[0x7f3706a20859]
 
??:0(__sanitizer_set_report_fd)[0x7f37076316a2]
 
??:0(__sanitizer_get_module_and_offset_for_pc)[0x7f370763c24c]
 
??:0(__sanitizer_ptr_cmp)[0x7f370761d8ec]
 
??:0(__asan_on_error)[0x7f370761d363]
 
??:0(__asan_report_load1)[0x7f370761de4b]
 
myisam/mi_key.c:295(_mi_pack_key)[0x55962df96d2b]
 
myisam/mi_rkey.c:62(mi_rkey)[0x55962e03d575]
 
myisam/ha_myisam.cc:1969(ha_myisam::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x55962df2fe2c]
 
sql/handler.cc:3137(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x55962cbc7592]
 
sql/handler.cc:6217(handler::read_range_first(st_key_range const*, st_key_range const*, bool, bool))[0x55962cbe25d6]
 
sql/opt_range.cc:12742(QUICK_RANGE_SELECT::get_next_prefix(unsigned int, unsigned int, unsigned char*))[0x55962cfecc8d]
 
sql/opt_range.cc:15573(QUICK_GROUP_MIN_MAX_SELECT::next_prefix())[0x55962d0002bd]
 
sql/opt_range.cc:15315(QUICK_GROUP_MIN_MAX_SELECT::get_next())[0x55962cffe9c2]
 
sql/records.cc:403(rr_quick(READ_RECORD*))[0x55962d021934]
 
sql/records.h:80(READ_RECORD::read_record())[0x55962c1dc232]
 
sql/sql_select.cc:21845(join_init_read_record(st_join_table*))[0x55962c4f2f10]
 
sql/sql_select.cc:20877(sub_select(JOIN*, st_join_table*, bool))[0x55962c4ebfea]
 
sql/sql_select.cc:20414(do_select(JOIN*, Procedure*))[0x55962c4ea2ac]
 
sql/sql_select.cc:4516(JOIN::exec_inner())[0x55962c475e76]
 
sql/sql_select.cc:4297(JOIN::exec())[0x55962c473480]
 
sql/sql_select.cc:4775(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*))[0x55962c4778c5]
 
sql/sql_select.cc:444(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55962c448db5]
 
sql/sql_parse.cc:6314(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55962c3b0ba4]
 
sql/sql_parse.cc:4005(mysql_execute_command(THD*))[0x55962c39fb45]
 
sql/sql_parse.cc:8100(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55962c3bbefe]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55962c391db9]
 
sql/sql_parse.cc:1370(do_command(THD*))[0x55962c38e6f8]
 
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x55962c7d986b]
 
sql/sql_connect.cc:1314(handle_one_connection)[0x55962c7d9084]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55962d445c77]
 
nptl/pthread_create.c:478(start_thread)[0x7f3706f4a609]
 
??:0(clone)[0x7f3706b1d293]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x62b0000382a8): SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7
 
 
 
Connection ID (thread ID): 4
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
 
 
 
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
 
information that should help you find out what is causing the crash.
 
Writing a core file...
 
Working directory at /dev/shm/var_auto_JVEa/mysqld.1/data
 
Resource Limits:
 
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        unlimited            unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             385674               385674               processes 
Max open files            1024                 1024                 files     
Max locked memory         67108864             67108864             bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       385674               385674               signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
Core pattern: |/usr/share/apport/apport %p %s %c %d %P %E

Comment by Elena Stepanova [ 2021-12-29 ]

The test case above also returns a wrong (empty) result when it doesn't fail with ASAN errors:

10.5 89a0364f

 
SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7;
 
b	a

The expected result is two rows.

Similar failures (both wrong results and ASAN errors) have also been seen in tests on different queries with DISTINCT instead of GROUP BY.

Comment by Oleg Smirnov [ 2022-07-22 ]

Optimizer trace:

| EXPLAIN FORMAT=JSON SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7 | {
 
  "steps": [
 
    {
 
      "join_preparation": {
 
        "select_id": 1,
 
        "steps": [
 
          {
 
            "expanded_query": "select t1.b AS b,t1.a AS a from t1 where t1.b <> 'p' or t1.a = 4 group by t1.b,t1.a having t1.a <= 7"
 
          }
 
        ]
 
      }
 
    },
 
    {
 
      "join_optimization": {
 
        "select_id": 1,
 
        "steps": [
 
          {
 
            "condition_processing": {
 
              "condition": "WHERE",
 
              "original_condition": "t1.b <> 'p' or t1.a = 4",
 
              "steps": [
 
                {
 
                  "transformation": "equality_propagation",
 
                  "resulting_condition": "t1.b <> 'p' or multiple equal(4, t1.a)"
 
                },
 
                {
 
                  "transformation": "constant_propagation",
 
                  "resulting_condition": "t1.b <> 'p' or multiple equal(4, t1.a)"
 
                },
 
                {
 
                  "transformation": "trivial_condition_removal",
 
                  "resulting_condition": "t1.b <> 'p' or multiple equal(4, t1.a)"
 
                }
 
              ]
 
            }
 
          },
 
          {
 
            "condition_processing": {
 
              "condition": "HAVING",
 
              "original_condition": "t1.a <= 7",
 
              "steps": [
 
                {
 
                  "transformation": "equality_propagation",
 
                  "resulting_condition": "t1.a <= 7"
 
                },
 
                {
 
                  "transformation": "constant_propagation",
 
                  "resulting_condition": "t1.a <= 7"
 
                },
 
                {
 
                  "transformation": "trivial_condition_removal",
 
                  "resulting_condition": "t1.a <= 7"
 
                }
 
              ]
 
            }
 
          },
 
          {
 
            "table_dependencies": [
 
              {
 
                "table": "t1",
 
                "row_may_be_null": false,
 
                "map_bit": 0,
 
                "depends_on_map_bits": []
 
              }
 
            ]
 
          },
 
          {
 
            "ref_optimizer_key_uses": []
 
          },
 
          {
 
            "rows_estimation": [
 
              {
 
                "table": "t1",
 
                "range_analysis": {
 
                  "table_scan": {
 
                    "rows": 4,
 
                    "cost": 3.8
 
                  },
 
                  "potential_range_indexes": [
 
                    {
 
                      "index": "a",
 
                      "usable": true,
 
                      "key_parts": ["a"]
 
                    },
 
                    {
 
                      "index": "b",
 
                      "usable": true,
 
                      "key_parts": ["b", "a"]
 
                    }
 
                  ],
 
                  "best_covering_index_scan": {
 
                    "index": "b",
 
                    "cost": 1.800732377,
 
                    "chosen": true
 
                  },
 
                  "setup_range_conditions": [],
 
                  "analyzing_range_alternatives": {
 
                    "range_scan_alternatives": [
 
                      {
 
                        "index": "a",
 
                        "ranges": ["(NULL) < (a) <= (7)"],
 
                        "rowid_ordered": false,
 
                        "using_mrr": false,
 
                        "index_only": false,
 
                        "rows": 2,
 
                        "cost": 2.545268538,
 
                        "chosen": false,
 
                        "cause": "cost"
 
                      }
 
                    ],
 
                    "analyzing_roworder_intersect": {
 
                      "cause": "too few roworder scans"
 
                    },
 
                    "analyzing_index_merge_union": [
 
                      {
 
                        "indexes_to_merge": [
 
                          {
 
                            "range_scan_alternatives": [
 
                              {
 
                                "index": "a",
 
                                "ranges": ["(NULL) < (a) <= (7)"],
 
                                "rowid_ordered": false,
 
                                "using_mrr": false,
 
                                "index_only": true,
 
                                "rows": 2,
 
                                "cost": 0.545268538,
 
                                "chosen": true
 
                              },
 
                              {
 
                                "index": "b",
 
                                "ranges": ["(NULL) < (b) < (p)", "(p) < (b)"],
 
                                "rowid_ordered": false,
 
                                "using_mrr": false,
 
                                "index_only": true,
 
                                "rows": 3,
 
                                "cost": 0.870549283,
 
                                "chosen": false,
 
                                "cause": "cost"
 
                              }
 
                            ],
 
                            "index_to_merge": "a",
 
                            "cumulated_cost": 0.545268538
 
                          },
 
                          {
 
                            "range_scan_alternatives": [
 
                              {
 
                                "index": "a",
 
                                "ranges": ["(4) <= (a) <= (4)"],
 
                                "rowid_ordered": true,
 
                                "using_mrr": false,
 
                                "index_only": true,
 
                                "rows": 1,
 
                                "cost": 0.345134269,
 
                                "chosen": true
 
                              }
 
                            ],
 
                            "index_to_merge": "a",
 
                            "cumulated_cost": 0.890402807
 
                          }
 
                        ],
 
                        "cost_of_reading_ranges": 0.890402807,
 
                        "cost_sort_rowid_and_read_disk": 2.084375,
 
                        "use_roworder_index_merge": true,
 
                        "cause": "cost"
 
                      }
 
                    ]
 
                  },
 
                  "group_index_range": {
 
                    "potential_group_range_indexes": [
 
                      {
 
                        "index": "a",
 
                        "usable": false,
 
                        "cause": "not covering"
 
                      },
 
                      {
 
                        "index": "b",
 
                        "covering": true,
 
                        "ranges": ["(NULL) < (a) <= (7)"],
 
                        "rows": 4,
 
                        "cost": 1.6
 
                      }
 
                    ]
 
                  },
 
                  "best_group_range_summary": {
 
                    "type": "index_group",
 
                    "index": "b",
 
                    "min_max_arg": null,
 
                    "min_aggregate": false,
 
                    "max_aggregate": false,
 
                    "distinct_aggregate": false,
 
                    "rows": 4,
 
                    "cost": 1.6,
 
                    "key_parts_used_for_access": ["b", "a"],
 
                    "ranges": ["(NULL) < (a) <= (7)"],
 
                    "chosen": true
 
                  },
 
                  "chosen_range_access_summary": {
 
                    "range_access_plan": {
 
                      "type": "index_group",
 
                      "index": "b",
 
                      "min_max_arg": null,
 
                      "min_aggregate": false,
 
                      "max_aggregate": false,
 
                      "distinct_aggregate": false,
 
                      "rows": 4,
 
                      "cost": 1.6,
 
                      "key_parts_used_for_access": ["b", "a"],
 
                      "ranges": ["(NULL) < (a) <= (7)"]
 
                    },
 
                    "rows_for_plan": 4,
 
                    "cost_for_plan": 1.6,
 
                    "chosen": true
 
                  }
 
                }
 
              },
 
              {
 
                "table": "t1",
 
                "rowid_filters": [
 
                  {
 
                    "key": "a",
 
                    "build_cost": 0.149131482,
 
                    "rows": 2
 
                  }
 
                ]
 
              }
 
            ]
 
          },
 
          {
 
            "considered_execution_plans": [
 
              {
 
                "plan_prefix": [],
 
                "table": "t1",
 
                "best_access_path": {
 
                  "considered_access_paths": [
 
                    {
 
                      "access_type": "index_merge",
 
                      "resulting_rows": 4,
 
                      "cost": 1.6,
 
                      "chosen": true,
 
                      "use_tmp_table": true
 
                    }
 
                  ],
 
                  "chosen_access_method": {
 
                    "type": "index_merge",
 
                    "records": 4,
 
                    "cost": 1.6,
 
                    "uses_join_buffering": false
 
                  }
 
                },
 
                "rows_for_plan": 4,
 
                "cost_for_plan": 2.4,
 
                "cost_for_sorting": 4,
 
                "estimated_join_cardinality": 4
 
              }
 
            ]
 
          },
 
          {
 
            "best_join_order": ["t1"]
 
          },
 
          {
 
            "attaching_conditions_to_tables": {
 
              "original_condition": "(t1.b <> 'p' or t1.a = 4) and t1.a <= 7",
 
              "attached_conditions_computation": [],
 
              "attached_conditions_summary": [
 
                {
 
                  "table": "t1",
 
                  "attached": "(t1.b <> 'p' or t1.a = 4) and t1.a <= 7"
 
                }
 
              ]
 
            }
 
          }
 
        ]
 
      }
 
    },
 
    {
 
      "join_execution": {
 
        "select_id": 1,
 
        "steps": []
 
      }
 
    }
 
  ]
 
} |                                 0 |                       0 |

Comment by Oleg Smirnov [ 2022-07-25 ]

Analysis on the following test case (MariaDB 10.5) for InnoDB:

CREATE TABLE t1 (a INT, b VARCHAR(1), KEY (a), KEY(b,a)) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (4,'n'),(1,'h'),(NULL,'w');
 
 
SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7;

Explain plan looks like this:

MariaDB [mdev24819]> EXPLAIN SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7;
 
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                 |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
 
|    1 | SIMPLE      | t1    | range | a,b           | b    | 9       | NULL | 4    | Using where; Using index for group-by |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
 
1 row in set (4.408 sec)

Optimizer chooses to use index "b" for group by as described in the "Extra" field and constructs QUICK_GROUP_MIN_MAX_SELECT object for index=1:

opt_range.cc make_quick() lines 14835-14849

 
QUICK_SELECT_I *
 
TRP_GROUP_MIN_MAX::make_quick(PARAM *param, bool retrieve_full_rows,
 
                              MEM_ROOT *parent_alloc)
 
{
 
  QUICK_GROUP_MIN_MAX_SELECT *quick;
 
  DBUG_ENTER("TRP_GROUP_MIN_MAX::make_quick");
 
 
 
  quick= new QUICK_GROUP_MIN_MAX_SELECT(param->table,
 
                                        param->thd->lex->current_select->join,
 
                                        have_min, have_max, 
                                        have_agg_distinct, min_max_arg_part,
 
                                        group_prefix_len, group_key_parts,
 
                                        used_key_parts, index_info, index,
 
                                        read_cost, records, key_infix_len,
 
                                        key_infix, parent_alloc, is_index_scan);

At the same time QUICK_RANGE_SELECT* QUICK_GROUP_MIN_MAX_SELECT::quick_prefix_select member is initialized which means using the ranges optimization (I may be wrong in this suggestion). By the way, are "Using index for group-by" and "ranges" access combineable?

This quick_prefix_select ranges element seem to be related to another index ("a"). If you try debugging this piece of code:

opt_range.cc QUICK_RANGE_SELECT::get_next_prefix() lines 12768-...

 
uint count= ranges.elements - (uint)(cur_range - (QUICK_RANGE**) ranges.buffer);
 
    if (count == 0)
 
    {
 
      /* Ranges have already been used up before. None is left for read. */
 
      last_range= 0;
 
      DBUG_RETURN(HA_ERR_END_OF_FILE);
 
    }
 
    last_range= *(cur_range++);
 
 
 
    key_range start_key, end_key;
 
    last_range->make_min_endpoint(&start_key, prefix_length, keypart_map);
 
    last_range->make_max_endpoint(&end_key, prefix_length, keypart_map);
 
 
 
    result= file->read_range_first(last_range->min_keypart_map ? &start_key : 0,
 
				   last_range->max_keypart_map ? &end_key : 0,
 
                                   MY_TEST(last_range->flag & EQ_RANGE),
 
				   TRUE);

you'll see last_range->min_length=5 and last_range->min_key is initialized with 5 bytes. This doesn't correspond to the length of key in index "b" which is shown in the EXPLAIN plan as 9.
But the handler file is initialized for using index "b" (file->active_index=1) and expects key_len=9. When it is fed with a wrong 5-byte min_key it either doesn't retrieve any rows (in case of no sanitizer) or crashes in case of using the sanitizer.

Comment by Oleg Smirnov [ 2022-07-25 ]

One more observation: 

TRP_GROUP_MIN_MAX {
 
SEL_ARG  *index_tree; /* The SEL_ARG sub-tree corresponding to index_info. */
 
KEY *index_info;
 
}

Actually during the execution it turns out index_info correlates to index "b" and index_tree probably relates to index "a". So QUICK_RANGE_SELECT object is constructed with inconsistent attributes (param_idx=1 while index_tree relates to idx=0):

opt_range.cc TRP_GROUP_MIN_MAX line 14866-...

 
quick->quick_prefix_select= get_quick_select(param, param_idx,
 
                                                   index_tree,
 
                                                   HA_MRR_USE_DEFAULT_IMPL, 0,
 
                                                   &quick->alloc);

Comment by Sergei Petrunia [ 2022-07-25 ]

Some more observations:

Condition pushdown from HAVING into WHERE puts "t1.a <=7" into the WHERE:

  $6 = 0x555557aff480 <dbug_item_print_buf> "(t1.b <> 'p' or multiple equal(4, t1.a)) and t1.a <= 7"

TODO: this is not visible in the optimizer trace!

Then, range optimizer constructs these range trees:

(gdb) p dbug_print_sel_arg(tree->keys[0])
 
  $18 = 0x7fff6c980d08 "SEL_ARG(NULL<a<=7)"
 
 
 
(gdb) p dbug_print_sel_arg(tree->keys[1])
 
  $22 = 0x7fff6c980d08 "SEL_ARG(NULL<a<=7)"

The same one-element tree for both indexes, the root SEL_ARG has left==right==next_key_part==NULL.

Comment by Sergei Petrunia [ 2022-07-25 ]

So QUICK_RANGE_SELECT object is constructed with inconsistent attributes (param_idx=1 while index_tree relates to idx=0)

No, I think it's for the right index but for second key part:

(gdb) print param_idx
 
  $81 = 1
 
(gdb) p dbug_print_sel_arg(index_tree)
 
  $84 = 0x7fff6c980d08 "SEL_ARG(NULL<a<=7)"
 
(gdb) p index_tree->part
 
  $86 = 1 '\001'

Comment by Sergei Petrunia [ 2022-07-25 ]

The constructed QUICK_RANGE_SELECT object is not usable, as its "ranges" start from keypart=2.

If I take a query which uses loose index scan and but can't use ranges to read rows, for example:

create table t10 ( .... key(a,b) );
 
explain select max(b) from t10 where b between 3 and 5 group by a;

I see that in TRP_GROUP_MIN_MAX::make_quick() we get here

  if (range_tree)
 
  {
 
    DBUG_ASSERT(quick_prefix_records > 0);
 
    if (quick_prefix_records == HA_POS_ERROR)
 
      quick->quick_prefix_select= NULL; /* Can't construct a quick select. */

and indeed quick_prefix_records=HA_POS_ERROR, that is, (-1).
however for the problem query I see that quick_prefix_records=3, as if it was possible to read records!

Comment by Sergei Petrunia [ 2022-07-25 ]

The quick_prefix_records comes from this code in get_best_group_min_max(): 

    if (tree)
 
    {
 
      if ((cur_index_tree= tree->keys[cur_param_idx]))
 
      {
 
        cur_quick_prefix_records= param->quick_rows[cur_index];

how do we get param->quick_rows[cur_index=1]==3 if we can't use that index to read rows?

Easy, it gets assigned when evaluating index_merge:

  #0  check_quick_select (param=0x7ffff010ca20, idx=1, index_only=true, tree=0x7fff6c98d140, update_tbl_stats=false, mrr_flags=0x7ffff010c4d4, bufsize=0x7ffff010c4d8, cost=0x7ffff010c5c0, is_ror_scan=0x7ffff010c4d2) at sql/opt_range.cc:11654
 
  #1  0x0000555555db15e8 in get_key_scans_params (param=0x7ffff010ca20, tree=0x7fff6c98d030, index_read_must_be_used=true, for_range_access=false, read_time=0.5452685382972231) at sql/opt_range.cc:7489
 
  #2  0x0000555555dab1ed in get_best_disjunct_quick (param=0x7ffff010ca20, imerge=0x7fff6c98cfc0, read_time=1.6005492828806833, named_trace=false) at sql/opt_range.cc:5178
 
  #3  0x0000555555da605a in SQL_SELECT::test_quick_select (this=0x7fff6c993bb0, thd=0x7fff6c000d78, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false, remove_false_parts_of_where=true, only_single_index_range_scan=false) at sql/opt_range.cc:3005

Comment by Sergei Petrunia [ 2022-07-25 ]

Should check_quick_select() not update param->quick_rows when it is invoked for index_merge branches ?

It actually already has update_tbl_stats and it doesn't update param->table->opt_range[keynr].rows for example...

Comment by Sergei Petrunia [ 2022-07-25 ]

making check_quick_select() not update param->quick_rows when update_tbl_stats==false breaks index_merge test cases.

What if instead we make get_best_group_min_max() look at param->table->opt_range? This seems to work:

diff --git a/sql/opt_range.cc b/sql/opt_range.cc
 
index 391a04c2a1a..e2dbab4cff8 100644
 
--- a/sql/opt_range.cc
 
+++ b/sql/opt_range.cc
 
@@ -14167,7 +14167,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time)
 
     {
 
       if ((cur_index_tree= tree->keys[cur_param_idx]))
 
       {
 
-        cur_quick_prefix_records= param->quick_rows[cur_index];
 
+        cur_quick_prefix_records= param->table->opt_range[cur_index].rows;
 
         if (unlikely(cur_index_tree && thd->trace_started()))
 
         {
 
           Json_writer_array trace_range(thd, "ranges");

oleg.smirnov any thoughts?
Also please file an MDEV that condition pushdown from HAVING to WHERE is not traced and fix it....

Comment by Oleg Smirnov [ 2022-07-26 ]

But with this patch cur_quick_prefix_records is being assigned to an unreasonably large value 0x02131f9e98e0.

And the explain changes to :

MariaDB [mdev24819]> EXPLAIN SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7;
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
|    1 | SIMPLE      | t1    | index | a,b           | b    | 9       | NULL | 3    | Using where; Using index |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

We no longer have "Using index for group-by" so probably the initial problem is not fixed.
By the way, the last explain plan ("Using index") is generated by the previous version of MariaDB 10.4 (and probably earlier versions) and MySQL 5.5-8.0. But starting from 10.5 the explain plan has changed to "Using index for group-by".

I believe it would be good to have cur_quick_prefix_records = HA_POS_ERROR in that place, then the explain plan remains the same and the result of the query is correct.

Comment by Sergei Petrunia [ 2022-07-26 ]

The fix I've suggested needs to be adjusted:

-        cur_quick_prefix_records= param->quick_rows[cur_index];
 
+        if (param->table->opt_range_keys.is_set(cur_index)  
+          cur_quick_prefix_records= param->table->opt_range[cur_index].rows;
 
+        else
 
+          cur_quick_prefix_records= HA_POS_ERROR;

Comment by Oleg Smirnov [ 2022-07-28 ]

Filed a separate MDEV-29179 for missing HAVING pushdown, otherwise ready for review.

Comment by Alice Sherepa [ 2022-12-15 ]

please check also MDEV-27572 (probably the same bug)

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