[MDEV-29363] Server crashed with heap-use-after-free in calc_group_buffer(TMP_TABLE_PARAM*, st_order*) Created: 2022-08-23  Updated: 2024-02-08

Status: In Review
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.10.0, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

Type: Bug Priority: Critical
Reporter: Shihao Wen Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Attachments: HTML File 404_stack    
Issue Links:
Duplicate
is duplicated by MDEV-32424 Computing unoptimized subquery? SEGV ... In Review
is duplicated by MDEV-32722 Heap-Use-After-Free at /mariadb-11.3.... Closed
Problem/Incident
is caused by MDEV-21184 Assertion `used_tables_cache == 0' fa... Closed
Relates
relates to MDEV-32539 Server crash in Time_and_counter_trac... Closed

 Description   

SUMMARY: AddressSanitizer: heap-use-after-free /home/wsh/database_fuzz/mysql_fuzz/Mariadb_10.3/sql/sql_select.cc:25688 in calc_group_buffer(TMP_TABLE_PARAM*, st_order*)

poc:

CREATE TABLE v1200 ( v1201 TEXT NOT NULL ) ;
 
 ( ( SELECT v1201 FROM v1200 WHERE v1201 IN ( 'x' = v1201 ) ORDER BY v1201 + v1201 , v1201 + v1201 ) ) ;
 
 UPDATE v1200 SET v1201 = 82 WHERE v1201 = 39 ;
 
 INSERT INTO v1200 ( v1201 ) VALUES ( 8 ) , ( 71 ) ;
 
 SELECT DISTINCT ( ( 52851147.000000 OR NOT TRUE ) - 46 ) , 21 FROM v1200 WINDOW v1208 AS ( PARTITION BY FALSE ORDER BY ( SELECT DISTINCT 90 FROM ( SELECT DISTINCT 'x' , ( WITH RECURSIVE v1202 AS ( SELECT DISTINCT v1201 FROM v1200 ) SELECT v1201 FROM ( SELECT DISTINCT ( ( NOT ( 14419645.000000 AND v1201 = 0 ) ) = 2147483647 AND v1201 = 58 ) % 83 , ( v1201 = 85 OR v1201 > 'x' ) FROM v1200 WHERE v1201 = 79 AND ( EXISTS ( SELECT ( v1201 NOT IN ( v1201 ) AND v1201 NOT IN ( 55 ^ v1201 ) ) , v1201 + v1201 FROM v1200 GROUP BY v1201 HAVING ( v1201 != 1 AND v1201 = v1201 AND ( NOT ( 'x' = 'x' AND FALSE = 84 ) ) AND v1201 LIKE 'x' ) WINDOW v1209 AS ( ORDER BY v1201 - v1201 , ( 0 < v1201 AND v1201 = 64 ) ) ) OR v1201 = -1 OR v1201 = 8 ) ) AS v1204 NATURAL JOIN v1200 WHERE ( v1201 = 1 OR v1201 = 25453422.000000 ) NOT LIKE 'x' AND CASE v1201 * 2147483647 = -1 WHEN 64 THEN 'x' WHEN -1 THEN 'x' ELSE 255 END != 9 GROUP BY v1201 , v1201 ORDER BY v1201 DESC LIMIT 1 OFFSET 1 ) , 127 , 73570201.000000 FROM v1200 ) AS v1205 JOIN v1200 AS v1206 NATURAL JOIN v1200 AS v1207 JOIN v1200 ) DESC RANGE BETWEEN 36297935.000000 FOLLOWING AND 89149539.000000 FOLLOWING ) ;



 Comments   
Comment by Alice Sherepa [ 2022-08-23 ]

Thanks!
I repeated on 10.3-10.10:

CREATE TABLE t1 (i int);
 
INSERT INTO t1 VALUES (8),(71) ;
 
 
 
SELECT 1 FROM t1 window w AS 
(ORDER BY 
 (SELECT  (SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM t1 GROUP BY i  HAVING i=1  window w2 AS ( ORDER BY 1 ))))
 
);


Version: '10.3.37-MariaDB-debug-log'  
=================================================================
 
==404171==ERROR: AddressSanitizer: heap-use-after-free on address 0x6190000ee310 at pc 0x555b3caa0913 bp 0x7f25de6df210 sp 0x7f25de6df200
 
READ of size 8 at 0x6190000ee310 thread T27
 
    #0 0x555b3caa0912 in calc_group_buffer(TMP_TABLE_PARAM*, st_order*) /10.3/src/sql/sql_select.cc:24243
 
    #1 0x555b3caa0dbd in calc_group_buffer /10.3/src/sql/sql_select.cc:24314
 
    #2 0x555b3c9fd583 in JOIN::optimize_stage2() /10.3/src/sql/sql_select.cc:2440
 
    #3 0x555b3c9f8d7b in JOIN::optimize_inner() /10.3/src/sql/sql_select.cc:2018
 
    #4 0x555b3c9f3fae in JOIN::optimize() /10.3/src/sql/sql_select.cc:1534
 
    #5 0x555b3ca11688 in 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*) /10.3/src/sql/sql_select.cc:4360
 
    #6 0x555b3c9e79cc in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/src/sql/sql_select.cc:372
 
    #7 0x555b3c9586dc in execute_sqlcom_select /10.3/src/sql/sql_parse.cc:6340
 
    #8 0x555b3c946717 in mysql_execute_command(THD*) /10.3/src/sql/sql_parse.cc:3871
 
    #9 0x555b3c962439 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/src/sql/sql_parse.cc:7871
 
    #10 0x555b3c939316 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/src/sql/sql_parse.cc:1852
 
    #11 0x555b3c935e59 in do_command(THD*) /10.3/src/sql/sql_parse.cc:1398
 
    #12 0x555b3cd09ee4 in do_handle_one_connection(CONNECT*) /10.3/src/sql/sql_connect.cc:1403
 
    #13 0x555b3cd0979e in handle_one_connection /10.3/src/sql/sql_connect.cc:1308
 
    #14 0x555b3e33aa16 in pfs_spawn_thread /10.3/src/storage/perfschema/pfs.cc:1869
 
    #15 0x7f25f4e9e608 in start_thread /build/glibc-SzIz7B/glibc-2.31/nptl/pthread_create.c:477
 
    #16 0x7f25f4dc3132 in __clone (/lib/x86_64-linux-gnu/libc.so.6+0x11f132)
 
 
 
0x6190000ee310 is located 144 bytes inside of 1100-byte region [0x6190000ee280,0x6190000ee6cc)
 
freed by thread T27 here:
 
    #0 0x7f25f57f040f in __interceptor_free ../../../../src/libsanitizer/asan/asan_malloc_linux.cc:122
 
    #1 0x555b3e488a90 in free_memory /10.3/src/mysys/safemalloc.c:279
 
    #2 0x555b3e48804c in sf_free /10.3/src/mysys/safemalloc.c:197
 
    #3 0x555b3e456377 in my_free /10.3/src/mysys/my_malloc.c:223
 
    #4 0x555b3e432ae7 in free_root /10.3/src/mysys/my_alloc.c:430
 
    #5 0x555b3ca7c081 in free_tmp_table(THD*, TABLE*) /10.3/src/sql/sql_select.cc:19236
 
    #6 0x555b3ca51b3a in JOIN::cleanup(bool) /10.3/src/sql/sql_select.cc:13238
 
    #7 0x555b3ca50f0a in JOIN::join_free() /10.3/src/sql/sql_select.cc:13129
 
    #8 0x555b3ca7dfa7 in do_select /10.3/src/sql/sql_select.cc:19517
 
    #9 0x555b3ca103fe in JOIN::exec_inner() /10.3/src/sql/sql_select.cc:4171
 
    #10 0x555b3ca0dd7b in JOIN::exec() /10.3/src/sql/sql_select.cc:3965
 
    #11 0x555b3d2fe82c in subselect_single_select_engine::exec() /10.3/src/sql/item_subselect.cc:4023
 
    #12 0x555b3d2d9bbf in Item_subselect::exec() /10.3/src/sql/item_subselect.cc:791
 
    #13 0x555b3d2e4124 in Item_exists_subselect::val_int() /10.3/src/sql/item_subselect.cc:1729
 
    #14 0x555b3d18d188 in Item_in_optimizer::val_int() /10.3/src/sql/item_cmpfunc.cc:1556
 
    #15 0x555b3ce5b08f in Type_handler_int_result::Item_val_bool(Item*) const /10.3/src/sql/sql_type.cc:3292
 
    #16 0x555b3c6b3bbd in Item::val_bool() /10.3/src/sql/item.h:1219
 
    #17 0x555b3cac382d in Item::eval_const_cond() /10.3/src/sql/item.h:1227
 
    #18 0x555b3ca66d77 in Item::remove_eq_conds(THD*, Item::cond_result*, bool) /10.3/src/sql/sql_select.cc:16844
 
    #19 0x555b3ca64459 in optimize_cond /10.3/src/sql/sql_select.cc:16400
 
    #20 0x555b3c9f6997 in JOIN::optimize_inner() /10.3/src/sql/sql_select.cc:1779
 
    #21 0x555b3c9f3fae in JOIN::optimize() /10.3/src/sql/sql_select.cc:1534
 
    #22 0x555b3c8e69c5 in st_select_lex::optimize_unflattened_subqueries(bool) /10.3/src/sql/sql_lex.cc:4123
 
    #23 0x555b3cdf6c23 in JOIN::optimize_constant_subqueries() /10.3/src/sql/opt_subselect.cc:5445
 
    #24 0x555b3c9f5b92 in JOIN::optimize_inner() /10.3/src/sql/sql_select.cc:1733
 
    #25 0x555b3c9f3fae in JOIN::optimize() /10.3/src/sql/sql_select.cc:1534
 
    #26 0x555b3ca11688 in 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*) /10.3/src/sql/sql_select.cc:4360
 
    #27 0x555b3c9e79cc in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/src/sql/sql_select.cc:372
 
    #28 0x555b3c9586dc in execute_sqlcom_select /10.3/src/sql/sql_parse.cc:6340
 
    #29 0x555b3c946717 in mysql_execute_command(THD*) /10.3/src/sql/sql_parse.cc:3871
 
 
 
previously allocated by thread T27 here:
 
    #0 0x7f25f57f0808 in __interceptor_malloc ../../../../src/libsanitizer/asan/asan_malloc_linux.cc:144
 
    #1 0x555b3e487a00 in sf_malloc /10.3/src/mysys/safemalloc.c:118
 
    #2 0x555b3e455880 in my_malloc /10.3/src/mysys/my_malloc.c:101
 
    #3 0x555b3e4318ed in alloc_root /10.3/src/mysys/my_alloc.c:251
 
    #4 0x555b3e43314c in memdup_root /10.3/src/mysys/my_alloc.c:494
 
    #5 0x555b3d01b6a5 in Field::make_new_field(st_mem_root*, TABLE*, bool) /10.3/src/sql/field.cc:2347
 
    #6 0x555b3ca68d6d in create_tmp_field_from_field(THD*, Field*, st_mysql_const_lex_string*, TABLE*, Item_field*) /10.3/src/sql/sql_select.cc:17177
 
    #7 0x555b3ca6accc in create_tmp_field(THD*, TABLE*, Item*, Item::Type, Item***, Field**, Field**, bool, bool, bool, bool) /10.3/src/sql/sql_select.cc:17464
 
    #8 0x555b3ca6e6c2 in create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, st_mysql_const_lex_string const*, bool, bool) /10.3/src/sql/sql_select.cc:17922
 
    #9 0x555b3ca08ca7 in JOIN::create_postjoin_aggr_table(st_join_table*, List<Item>*, st_order*, bool, bool, bool) /10.3/src/sql/sql_select.cc:3478
 
    #10 0x555b3ca045d6 in JOIN::make_aggr_tables_info() /10.3/src/sql/sql_select.cc:3077
 
    #11 0x555b3c9fff27 in JOIN::optimize_stage2() /10.3/src/sql/sql_select.cc:2712
 
    #12 0x555b3c9f8d7b in JOIN::optimize_inner() /10.3/src/sql/sql_select.cc:2018
 
    #13 0x555b3c9f3fae in JOIN::optimize() /10.3/src/sql/sql_select.cc:1534
 
    #14 0x555b3c8e69c5 in st_select_lex::optimize_unflattened_subqueries(bool) /10.3/src/sql/sql_lex.cc:4123
 
    #15 0x555b3cdf6c23 in JOIN::optimize_constant_subqueries() /10.3/src/sql/opt_subselect.cc:5445
 
    #16 0x555b3c9f5b92 in JOIN::optimize_inner() /10.3/src/sql/sql_select.cc:1733
 
    #17 0x555b3c9f3fae in JOIN::optimize() /10.3/src/sql/sql_select.cc:1534
 
    #18 0x555b3c8e69c5 in st_select_lex::optimize_unflattened_subqueries(bool) /10.3/src/sql/sql_lex.cc:4123
 
    #19 0x555b3cdf6c23 in JOIN::optimize_constant_subqueries() /10.3/src/sql/opt_subselect.cc:5445
 
    #20 0x555b3c9f5b92 in JOIN::optimize_inner() /10.3/src/sql/sql_select.cc:1733
 
    #21 0x555b3c9f3fae in JOIN::optimize() /10.3/src/sql/sql_select.cc:1534
 
    #22 0x555b3ca11688 in 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*) /10.3/src/sql/sql_select.cc:4360
 
    #23 0x555b3c9e79cc in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/src/sql/sql_select.cc:372
 
    #24 0x555b3c9586dc in execute_sqlcom_select /10.3/src/sql/sql_parse.cc:6340
 
    #25 0x555b3c946717 in mysql_execute_command(THD*) /10.3/src/sql/sql_parse.cc:3871
 
    #26 0x555b3c962439 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/src/sql/sql_parse.cc:7871
 
    #27 0x555b3c939316 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/src/sql/sql_parse.cc:1852
 
    #28 0x555b3c935e59 in do_command(THD*) /10.3/src/sql/sql_parse.cc:1398
 
    #29 0x555b3cd09ee4 in do_handle_one_connection(CONNECT*) /10.3/src/sql/sql_connect.cc:1403
 
 
 
Thread T27 created by T0 here:
 
    #0 0x7f25f571d815 in __interceptor_pthread_create ../../../../src/libsanitizer/asan/asan_interceptors.cc:208
 
    #1 0x555b3e33ae07 in spawn_thread_v1 /10.3/src/storage/perfschema/pfs.cc:1919
 
    #2 0x555b3c65b39e in inline_mysql_thread_create /10.3/src/include/mysql/psi/mysql_thread.h:1275
 
    #3 0x555b3c674158 in create_thread_to_handle_connection(CONNECT*) /10.3/src/sql/mysqld.cc:6668
 
    #4 0x555b3c6748f3 in create_new_thread /10.3/src/sql/mysqld.cc:6738
 
    #5 0x555b3c675a85 in handle_connections_sockets() /10.3/src/sql/mysqld.cc:6996
 
    #6 0x555b3c673449 in mysqld_main(int, char**) /10.3/src/sql/mysqld.cc:6290
 
    #7 0x555b3c659b9c in main /10.3/src/sql/main.cc:25
 
    #8 0x7f25f4cc8082 in __libc_start_main ../csu/libc-start.c:308
 
 
 
SUMMARY: AddressSanitizer: heap-use-after-free /10.3/src/sql/sql_select.cc:24243 in calc_group_buffer(TMP_TABLE_PARAM*, st_order*)
 
Shadow bytes around the buggy address:
 
  0x0c3280015c10: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
  0x0c3280015c20: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
  0x0c3280015c30: f7 f7 f7 f7 f7 f7 f7 f7 f7 04 fa fa fa fa fa fa
 
  0x0c3280015c40: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c3280015c50: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
=>0x0c3280015c60: fd fd[fd]fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280015c70: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280015c80: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280015c90: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280015ca0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280015cb0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
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
 
==404171==ABORTING
 
----------SERVER LOG END-------------

Comment by Alice Sherepa [ 2023-11-07 ]

test case from MDEV-32722:

CREATE TABLE x ( x VARCHAR ( 1 ) ) ;
 
INSERT INTO x ( x ) VALUES ( 'x' ) , ( NULL ) , ( 'x' ) , ( NULL ) ;
 
SELECT x AS x , x AS x FROM x GROUP BY x , x HAVING ( ( x >= ( 1.000000 % FALSE AND x = x AND x = 1 ) AND x < 1 ) = 1 AND x = ( SELECT x FROM x AS x GROUP BY x ORDER BY x * 1 , x ASC , x DESC ) ) ORDER BY x / 1 ;


Version: '10.4.32-MariaDB-debug-log'  e5a5573f782723e40c372f38346a60b1ccc644d6
 
=================================================================
 
==368124==ERROR: AddressSanitizer: heap-use-after-free on address 0x6190000f2ef0 at pc 0x5631b4af5326 bp 0x7f9776d26840 sp 0x7f9776d26830
 
READ of size 8 at 0x6190000f2ef0 thread T27
 
    #0 0x5631b4af5325 in calc_group_buffer(TMP_TABLE_PARAM*, st_order*) /10.4/src/sql/sql_select.cc:25418
 
    #1 0x5631b4af57d0 in calc_group_buffer /10.4/src/sql/sql_select.cc:25489
 
    #2 0x5631b4a4b118 in JOIN::optimize_stage2() /10.4/src/sql/sql_select.cc:2856
 
    #3 0x5631b4a463c1 in JOIN::optimize_inner() /10.4/src/sql/sql_select.cc:2414
 
    #4 0x5631b4a3f0cc in JOIN::optimize() /10.4/src/sql/sql_select.cc:1731
 
    #5 0x5631b4916c75 in st_select_lex::optimize_unflattened_subqueries(bool) /10.4/src/sql/sql_lex.cc:4233
 
    #6 0x5631b4e8b594 in JOIN::optimize_unflattened_subqueries() /10.4/src/sql/opt_subselect.cc:5610
 
    #7 0x5631b4a4c44a in JOIN::optimize_stage2() /10.4/src/sql/sql_select.cc:2962
 
    #8 0x5631b4a463c1 in JOIN::optimize_inner() /10.4/src/sql/sql_select.cc:2414
 
    #9 0x5631b4a3f0cc in JOIN::optimize() /10.4/src/sql/sql_select.cc:1731
 
    #10 0x5631b4a600b6 in 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*) /10.4/src/sql/sql_select.cc:4832
 
    #11 0x5631b4a30b1d in handle_select(THD*, LEX*, select_result*, unsigned long) /10.4/src/sql/sql_select.cc:442
 
    #12 0x5631b499c885 in execute_sqlcom_select /10.4/src/sql/sql_parse.cc:6475
 
    #13 0x5631b4989ffc in mysql_execute_command(THD*) /10.4/src/sql/sql_parse.cc:3978
 
    #14 0x5631b49a5dc4 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.4/src/sql/sql_parse.cc:8013
 
    #15 0x5631b497c186 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.4/src/sql/sql_parse.cc:1857
 
    #16 0x5631b4978cb1 in do_command(THD*) /10.4/src/sql/sql_parse.cc:1378
 
    #17 0x5631b4d885b4 in do_handle_one_connection(CONNECT*) /10.4/src/sql/sql_connect.cc:1420
 
    #18 0x5631b4d87e58 in handle_one_connection /10.4/src/sql/sql_connect.cc:1324
 
    #19 0x5631b5a2447d in pfs_spawn_thread /10.4/src/storage/perfschema/pfs.cc:1869
 
    #20 0x7f978d8f3608 in start_thread /build/glibc-BHL3KM/glibc-2.31/nptl/pthread_create.c:477
 
    #21 0x7f978d4c4132 in __clone (/lib/x86_64-linux-gnu/libc.so.6+0x11f132)
 
 
 
0x6190000f2ef0 is located 368 bytes inside of 1100-byte region [0x6190000f2d80,0x6190000f31cc)
 
freed by thread T27 here:
 
    #0 0x7f978def140f in __interceptor_free ../../../../src/libsanitizer/asan/asan_malloc_linux.cc:122
 
    #1 0x5631b65aba5a in free_memory /10.4/src/mysys/safemalloc.c:279
 
    #2 0x5631b65ab016 in sf_free /10.4/src/mysys/safemalloc.c:197
 
    #3 0x5631b6579c0e in my_free /10.4/src/mysys/my_malloc.c:222
 
    #4 0x5631b6556954 in free_root /10.4/src/mysys/my_alloc.c:437
 
    #5 0x5631b4acf69d in free_tmp_table(THD*, TABLE*) /10.4/src/sql/sql_select.cc:20209
 
    #6 0x5631b4aa3e81 in JOIN::cleanup(bool) /10.4/src/sql/sql_select.cc:14166
 
    #7 0x5631b4a5f02f in JOIN::destroy() /10.4/src/sql/sql_select.cc:4654
 
    #8 0x5631b4c5ba3b in st_select_lex::cleanup() /10.4/src/sql/sql_union.cc:2160
 
    #9 0x5631b53a018c in subselect_single_select_engine::prepare(THD*) /10.4/src/sql/item_subselect.cc:3806
 
    #10 0x5631b537a30c in Item_subselect::fix_fields(THD*, Item**) /10.4/src/sql/item_subselect.cc:289
 
    #11 0x5631b46e55e4 in Item::fix_fields_if_needed(THD*, Item**) /10.4/src/sql/item.h:966
 
    #12 0x5631b52a50cd in Item_func::fix_fields(THD*, Item**) /10.4/src/sql/item_func.cc:355
 
    #13 0x5631b46e55e4 in Item::fix_fields_if_needed(THD*, Item**) /10.4/src/sql/item.h:966
 
    #14 0x5631b46e561e in Item::fix_fields_if_needed_for_scalar(THD*, Item**) /10.4/src/sql/item.h:970
 
    #15 0x5631b481db32 in Item::fix_fields_if_needed_for_bool(THD*, Item**) /10.4/src/sql/item.h:974
 
    #16 0x5631b5242565 in Item_cond::fix_fields(THD*, Item**) /10.4/src/sql/item_cmpfunc.cc:4848
 
    #17 0x5631b46e55e4 in Item::fix_fields_if_needed(THD*, Item**) /10.4/src/sql/item.h:966
 
    #18 0x5631b52a50cd in Item_func::fix_fields(THD*, Item**) /10.4/src/sql/item_func.cc:355
 
    #19 0x5631b46e55e4 in Item::fix_fields_if_needed(THD*, Item**) /10.4/src/sql/item.h:966
 
    #20 0x5631b46e561e in Item::fix_fields_if_needed_for_scalar(THD*, Item**) /10.4/src/sql/item.h:970
 
    #21 0x5631b481db32 in Item::fix_fields_if_needed_for_bool(THD*, Item**) /10.4/src/sql/item.h:974
 
    #22 0x5631b5242565 in Item_cond::fix_fields(THD*, Item**) /10.4/src/sql/item_cmpfunc.cc:4848
 
    #23 0x5631b46e55e4 in Item::fix_fields_if_needed(THD*, Item**) /10.4/src/sql/item.h:966
 
    #24 0x5631b52a50cd in Item_func::fix_fields(THD*, Item**) /10.4/src/sql/item_func.cc:355
 
    #25 0x5631b4945c1f in st_select_lex::pushdown_from_having_into_where(THD*, Item*) /10.4/src/sql/sql_lex.cc:10412
 
    #26 0x5631b4a43c4c in JOIN::optimize_inner() /10.4/src/sql/sql_select.cc:2160
 
    #27 0x5631b4a3f0cc in JOIN::optimize() /10.4/src/sql/sql_select.cc:1731
 
    #28 0x5631b4a600b6 in 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*) /10.4/src/sql/sql_select.cc:4832
 
    #29 0x5631b4a30b1d in handle_select(THD*, LEX*, select_result*, unsigned long) /10.4/src/sql/sql_select.cc:442
 
 
 
previously allocated by thread T27 here:
 
    #0 0x7f978def1808 in __interceptor_malloc ../../../../src/libsanitizer/asan/asan_malloc_linux.cc:144
 
    #1 0x5631b65aa9ca in sf_malloc /10.4/src/mysys/safemalloc.c:118
 
    #2 0x5631b6579117 in my_malloc /10.4/src/mysys/my_malloc.c:101
 
    #3 0x5631b655575a in alloc_root /10.4/src/mysys/my_alloc.c:258
 
    #4 0x5631b4b1a1b6 in Field::operator new(unsigned long, st_mem_root*) /10.4/src/sql/field.h:636
 
    #5 0x5631b4f03a94 in Type_handler_double::make_table_field(st_mysql_const_lex_string const*, Record_addr const&, Type_all_attributes const&, TABLE*) const /10.4/src/sql/sql_type.cc:3261
 
    #6 0x5631b4f02d29 in Type_handler::make_and_init_table_field(st_mysql_const_lex_string const*, Record_addr const&, Type_all_attributes const&, TABLE*) const /10.4/src/sql/sql_type.cc:3156
 
    #7 0x5631b46c5f35 in Item::tmp_table_field_from_field_type(TABLE*) /10.4/src/sql/item.h:809
 
    #8 0x5631b4abe155 in Item_result_field::create_tmp_field_ex(TABLE*, Tmp_field_src*, Tmp_field_param const*) /10.4/src/sql/sql_select.cc:18431
 
    #9 0x5631b4abe7d5 in create_tmp_field(TABLE*, Item*, Item***, Field**, Field**, bool, bool, bool, bool) /10.4/src/sql/sql_select.cc:18510
 
    #10 0x5631b4ac1ad6 in create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, st_mysql_const_lex_string const*, bool, bool) /10.4/src/sql/sql_select.cc:18894
 
    #11 0x5631b4a56a81 in JOIN::create_postjoin_aggr_table(st_join_table*, List<Item>*, st_order*, bool, bool, bool) /10.4/src/sql/sql_select.cc:3916
 
    #12 0x5631b4a522dd in JOIN::make_aggr_tables_info() /10.4/src/sql/sql_select.cc:3504
 
    #13 0x5631b4a4db6d in JOIN::optimize_stage2() /10.4/src/sql/sql_select.cc:3128
 
    #14 0x5631b4a463c1 in JOIN::optimize_inner() /10.4/src/sql/sql_select.cc:2414
 
    #15 0x5631b4a3f0cc in JOIN::optimize() /10.4/src/sql/sql_select.cc:1731
 
    #16 0x5631b4916c75 in st_select_lex::optimize_unflattened_subqueries(bool) /10.4/src/sql/sql_lex.cc:4233
 
    #17 0x5631b4e8b681 in JOIN::optimize_constant_subqueries() /10.4/src/sql/opt_subselect.cc:5644
 
    #18 0x5631b4a42442 in JOIN::optimize_inner() /10.4/src/sql/sql_select.cc:2068
 
    #19 0x5631b4a3f0cc in JOIN::optimize() /10.4/src/sql/sql_select.cc:1731
 
    #20 0x5631b4a600b6 in 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*) /10.4/src/sql/sql_select.cc:4832
 
    #21 0x5631b4a30b1d in handle_select(THD*, LEX*, select_result*, unsigned long) /10.4/src/sql/sql_select.cc:442
 
    #22 0x5631b499c885 in execute_sqlcom_select /10.4/src/sql/sql_parse.cc:6475
 
    #23 0x5631b4989ffc in mysql_execute_command(THD*) /10.4/src/sql/sql_parse.cc:3978
 
    #24 0x5631b49a5dc4 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.4/src/sql/sql_parse.cc:8013
 
    #25 0x5631b497c186 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.4/src/sql/sql_parse.cc:1857
 
    #26 0x5631b4978cb1 in do_command(THD*) /10.4/src/sql/sql_parse.cc:1378
 
    #27 0x5631b4d885b4 in do_handle_one_connection(CONNECT*) /10.4/src/sql/sql_connect.cc:1420
 
    #28 0x5631b4d87e58 in handle_one_connection /10.4/src/sql/sql_connect.cc:1324
 
    #29 0x5631b5a2447d in pfs_spawn_thread /10.4/src/storage/perfschema/pfs.cc:1869
 
 
 
Thread T27 created by T0 here:
 
    #0 0x7f978de1e815 in __interceptor_pthread_create ../../../../src/libsanitizer/asan/asan_interceptors.cc:208
 
    #1 0x5631b5a2486e in spawn_thread_v1 /10.4/src/storage/perfschema/pfs.cc:1919
 
    #2 0x5631b4673f71 in inline_mysql_thread_create /10.4/src/include/mysql/psi/mysql_thread.h:1275
 
    #3 0x5631b468c103 in create_thread_to_handle_connection(CONNECT*) /10.4/src/sql/mysqld.cc:6289
 
    #4 0x5631b468c89e in create_new_thread(CONNECT*) /10.4/src/sql/mysqld.cc:6359
 
    #5 0x5631b468cd84 in handle_accepted_socket(st_mysql_socket, st_mysql_socket) /10.4/src/sql/mysqld.cc:6457
 
    #6 0x5631b468dc40 in handle_connections_sockets() /10.4/src/sql/mysqld.cc:6615
 
    #7 0x5631b468b808 in mysqld_main(int, char**) /10.4/src/sql/mysqld.cc:5947
 
    #8 0x5631b4671f3c in main /10.4/src/sql/main.cc:25
 
    #9 0x7f978d3c9082 in __libc_start_main ../csu/libc-start.c:308
 
 
 
SUMMARY: AddressSanitizer: heap-use-after-free /10.4/src/sql/sql_select.cc:25418 in calc_group_buffer(TMP_TABLE_PARAM*, st_order*)
 
Shadow bytes around the buggy address:
 
  0x0c3280016580: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
 
  0x0c3280016590: f7 f7 f7 f7 f7 f7 f7 f7 f7 04 fa fa fa fa fa fa
 
  0x0c32800165a0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c32800165b0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c32800165c0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
=>0x0c32800165d0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd[fd]fd
 
  0x0c32800165e0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c32800165f0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280016600: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280016610: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280016620: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
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
 
==368124==ABORTING
 
----------SERVER LOG END-------------

Comment by Yuchen Pei [ 2023-12-08 ]

Of the three testcases, including the one in the description and the
two in Alice's comments, only the last one[1] is reproducible at 10.6
bc5e904043d4c50fb0cc0cbdce4149866bf4e6e0.

[1] https://jira.mariadb.org/browse/MDEV-29363?focusedCommentId=273524&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-273524

Comment by Yuchen Pei [ 2024-01-11 ]

I could reduce the testcase down to (11. 0 c2da55ac01b5a50961ef4576edce7668ed791776)

CREATE TABLE t ( x VARCHAR ( 1 ) ) ;
 
INSERT INTO t VALUES ( 'y' ) , ( NULL ) ;
 
SELECT x FROM t GROUP BY x HAVING x >= ( x = x ) AND x = ( SELECT x FROM t GROUP BY x ORDER BY x );

If I try to further reduce it, e.g. removing GROUP BY x ORDER BY x from the inner select, I get an assertion failure:

Version: '11.0.5-MariaDB-debug-log'  socket: '/home/ycp/source/mariadb-server/mdev-29363/build/mysql-test/var/tmp/mysqld.1.sock'  port: 16000  Source distribution
 
mariadbd: /home/ycp/source/mariadb-server/mdev-29363/src/sql/sql_select.cc:4696: bool JOIN::save_explain_data(Explain_query*, bool, bool, bool, bool): Assertion `select_lex->select_number == (0x7fffffff * 2U + 1U) || !output || !output->get_select(select_lex->select_number) || output->get_select(select_lex->select_number)->select_lex == select_lex' failed.

Comment by Yuchen Pei [ 2024-01-12 ]

NOTE: the traces below are a bit wrong (missing some layers), will fix.

Further investigation tells me that the problem could be that during
create_tmp_table(), the field_varstring is created (1) and
assigned as the result_field of the group by item (2).

11.0 c2da55ac01b5a50961ef4576edce7668ed791776

 
  else
 
  {
 
    bool tmp_maybe_null= param->modify_item() ? maybe_null() :
 
                                                field->maybe_null();
 
    result= field->create_tmp_field(root, new_table, tmp_maybe_null); // (1)
 
    if (result && ! param->modify_item())
 
      result->field_name= *new_name;
 
  }
 
  if (result && param->modify_item())
 
    result_field= result; // (2)

alloc_root > memdup_root > Field::make_new_field > Field::create_tmp_field > create_tmp_field > create_tmp_table > JOIN::make_aggr_tables_info > JOIN::optimize_inner > JOIN::optimize > JOIN::optimize > mysql_select > handle_select > mysql_execute_command > mysql_parse

Then it was freed in free_tmp_table without resetting the
result_field.

free_memory > sf_free > my_free > root_free > free_root > JOIN::cleanup > JOIN::destroy > subselect_single_select_engine::prepare > Item::fix_fields_if_needed > Item::fix_fields_if_needed > st_select_lex::pushdown_from_having_into_where > JOIN::optimize > mysql_select > handle_select > mysql_execute_command > mysql_parse

Then the result_field of the group by was accessed, causing
use-after-free.

    Item *group_item= *group->item;
 
    Field *field= group_item->get_tmp_table_field();

calc_group_buffer > calc_group_buffer > JOIN::optimize_stage2 > JOIN::optimize > Item_subselect::exec > Arg_comparator::compare_string > Item_func_eq::val_int > Item_func_or_sum::walk > Item_func_or_sum::walk > Item_cond::walk > JOIN::optimize > mysql_select > handle_select > mysql_execute_command > mysql_parse

So naively, one idea would be to reset the result_field during
freeing.

Comment by Sergei Petrunia [ 2024-01-16 ]

Summary of today's discussion on #optimizer on Slack:

Subquery Misuse

The immediate cause of the crash is these actions with subquery #2:

  1. First, it is optimized.
    This among other things sets up a grouping temp table and pointers to its fields.
  2. Then, it is cleaned up
    This destroys its JOIN object and the temp.table. But Item objects still have pointers to the temp table.
  3. Then, it is prepared again. But the pointers to the old temp.table are not overwritten.
  4. Then, the optimizer tries to execute it, and accesses freed memory.

The wrong part here is that we do steps #2 and #3.

Why do we do them? Because of the following transformations of the HAVING clause:

Equality Propagation Doesn't Update Attributes

HAVING 
  x >= ( x = x ) AND x = ( SELECT x FROM t GROUP BY x ORDER BY x );

Initially Item_func_ge("x >= (x = x)") has with_subquery()= false.
All "x" are Item_ref objects referring to Item_field object(s).

Then, equality propagation code replaces them with references to
Item_subselect object, so the Item_func_ge becomes:

"t.x >= ((subquery#2) = (subquery#2))"

But we still have

Item_func_ge->with_subquery()==false

Check Item_func::build_equal_items - it calls update_used_tables(). But it doesn't update
with_subquery attribute.

Pushdown Attempts to Push Subselect

Since Item_func_ge object has with_subquery()=false, Item_func::excl_dep_on_grouping_fields returns true for it, despite
its intent to return false when subqueries are used:

  
  bool excl_dep_on_grouping_fields(st_select_lex *sel)
 
  {
 
    if (has_rand_bit() || with_subquery())
 
      return false;

Then, we try to push Item_func_ge object from HAVING into WHERE.
This involves doing cleanup() and then fix_fields() for the Item_subselect object, which causes the Subquery Misuse described above.

Comment by Sergei Petrunia [ 2024-01-17 ]

Take-aways from the optimizer call:

Two possible solutions

1. Make update_used_tables() re-compute with_subquery().

Currently update_used_tables() only updates used_tables() and const_item().
Make it re-compute other attributes.

an Item has two sets of attributes:

  item_base_t base_flags;
 
  item_with_t with_flags;

subquery is in the second:

{
 
  NONE=             0,
 
  SP_VAR=      (1<<0), // If Item contains a stored procedure variable
 
  WINDOW_FUNC= (1<<1), // If item contains a window func
 
  FIELD=       (1<<2), // If any item except Item_sum contains a field.
 
  SUM_FUNC=    (1<<3), // If item contains a sum func
 
  SUBQUERY=    (1<<4), // If item containts a sub query
 
  ROWNUM_FUNC= (1<<5), // If ROWNUM function was used
 
  PARAM=       (1<<6)  // If user parameter was used
 
};

We could make update_used_tables() recompute one or both sets of attributes...

Issues with this:

  • It adds a lot of CPU overhead.
  • Is this change too intrusive to make in a stable version (this MDEV is for 10.4!)?
  • Which attributes to recompute? All of them?

2. Just recompute with_subuqery().

We can just re-compute the with_subquery() attribute in pushdown code:

diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
 
index 9492ff087ae..9692f661fd4 100644
 
--- a/sql/sql_lex.cc
 
+++ b/sql/sql_lex.cc
 
@@ -8964,6 +8964,12 @@ st_select_lex::check_cond_extraction_for_grouping_fields(THD *thd, Item *cond)
 
   {
 
     int fl= cond->excl_dep_on_grouping_fields(this) && !cond->is_expensive() ?
 
       MARKER_FULL_EXTRACTION : MARKER_NO_EXTRACTION;
 
+  
+    if (fl == MARKER_FULL_EXTRACTION)
 
+    {
 
+      if (cond->walk(&Item::is_subquery_processor, 0, 0))
 
+        fl= MARKER_NO_EXTRACTION;
 
+    }
 
     cond->set_extraction_flag(fl);
 
   }
 
 }

As extension of this patch, we could make sure that any attempt to call with_subquery() after equality propagation (or even at join optimization start?) will cause ASAN error.

Comment by Yuchen Pei [ 2024-01-19 ]

Here's a (the?) bad commit at which the bug is reproducible, but not
at its parent:

commit 1c97cd339e9513b152727f386573c8c048db0281 (HEAD)
 
Author: Igor Babaev <igor@askmonty.org>
 
Date:   Fri Jan 3 11:15:00 2020 -0800
 
 
 
    MDEV-21184 Assertion `used_tables_cache == 0' failed in Item_func::fix_fields
 
               with condition_pushdown_from_having
 
    
    This bug could manifest itself for queries with GROUP BY and HAVING clauses
 
    when the HAVING clause was a conjunctive condition that depended
 
    exclusively on grouping fields and at least one conjunct contained an
 
    equality of the form fld=sq where fld is a grouping field and sq is a
 
    constant subquery.
 
    In this case the optimizer tries to perform a pushdown of the HAVING
 
    condition into WHERE. To construct the pushable condition the optimizer
 
    first transforms all multiple equalities in HAVING into simple equalities.
 
    This has to be done for a proper processing of the pushed conditions
 
    in WHERE. The multiple equalities at all AND/OR levels must be converted
 
    to simple equalities because any multiple equality may refer to a multiple
 
    equality at the upper level.
 
    Before this patch the conversion was performed like this:
 
      multiple_equality(x,f1,...,fn) => x=f1 and ... and x=fn.
 
    When an equality item for x=fi was constructed both the items for x and fi
 
    were cloned. If x happened to be a constant subquery that could not be
 
    cloned the conversion failed. If the conversions of multiple equalities
 
    previously performed had succeeded then the whole condition became in an
 
    inconsistent state that could cause different failures.
 
    The solution provided by the patch is:
 
    1. to use a different conversion rule if x is a constant
 
      multiple_equality(x,f1,...,fn) => f1=x and f2=f1 and ... and fn=f1
 
    2. not to clone x if it's a constant.
 
    Such conversions cannot fail and besides the result of the conversion
 
    preserves the equivalence of f1,...,fn that can be used for other
 
    optimizations.
 
    This patch also made sure that expensive predicates are not pushed from
 
    HAVING to WHERE.
 
 
 
 mysql-test/main/derived_cond_pushdown.result |   8 +-
 
 mysql-test/main/having_cond_pushdown.result  | 148 ++++++++++++++++++++++++++++++
 
 mysql-test/main/having_cond_pushdown.test    |  39 ++++++++
 
 sql/item.cc                                  |  14 ++-
 
 sql/item.h                                   |   7 +-
 
 sql/item_cmpfunc.cc                          | 105 ++++++++++++---------
 
 sql/item_cmpfunc.h                           |   3 +-
 
 sql/sql_lex.cc                               |  14 ++-
 
 8 files changed, 280 insertions(+), 58 deletions(-)

Comment by Yuchen Pei [ 2024-01-19 ]

ok, so the reason that the bad commit caused the regression was
because before that commit, the test case would skip pushdown from
HAVING to WHERE altogether, because it would try to clone the
subquery, which returns 0.

Item_subselect::build_clone > Item_equal::create_pushable_equalities > Item_equal::multiple_equality_transformer > Item_func::transform > Item_equal::transform > Item_cond::transform > st_select_lex::build_pushable_cond_for_having_pushdown > st_select_lex::pushdown_from_having_into_where

Now, the commit highlights that the having2where pushdown can handle
the case where one of the multiple equality items is a const subquery,
like in our test case. In the current version it ensures that there is
only one such item in the expanded equalities for the pushdown, and it
sets the MARKER_IMMUTABLE flag so that later the subquery does not get
unfixed:

bool Item_equal::create_pushable_equalities(THD *thd,
 
                                            List<Item> *equalities,
 
                                            Pushdown_checker checker,
 
                                            uchar *arg,
 
                                            bool clone_const)
 
{
 
//  [... 29 lines elided]
 
    if (!clone_const)
 
    {
 
      /*
 
        Also set IMMUTABLE_FL for any sub-items of the right_item.
 
        This is needed to prevent Item::cleanup_excluding_immutables_processor
 
        from peforming cleanup of the sub-items and so creating an item tree
 
        where a fixed item has non-fixed items inside it.
 
      */
 
      int16 new_flag= MARKER_IMMUTABLE;
 
      right_item->walk(&Item::set_extraction_flag_processor, false,
 
                       (void*)&new_flag);
 
    }
 
//  [... 20 lines elided]
 
}
 

bool Item::cleanup_excluding_immutables_processor (void *arg)
 
{
 
  if (!(get_extraction_flag() == MARKER_IMMUTABLE))
 
    return cleanup_processor(arg);  // (1)
 
  else
 
  {
 
    clear_extraction_flag();  // (2)
 
    return false;
 
  }
 
}

However, the effect is one time, as can be seen above. If there are
two or more items in attach_to_conds sharing the same subquery (as
is the case in our test case), when calling
cleanup_excluding_immutables_processor on the first item, it goes to
(2) and clear the immutable flag of the subquery, and when calling the
same processor on the second item, it goes to (1) and unfixes the
subquery. The subsequent call of fix_fields thus fixes the
subquery and destroys the JOIN etc.

It makes sense to not include the Item_func_ge in the
attach_to_conds, because the following commit that predated the
commit by igor says "Subqueries can't be pushed into WHERE". I
assume that the special case handled in igor's commit should be the
only exception to this rule, which is handled by the "smart" iterator
Item_equal_iterator that skips the const item (i.e. the subquery
in our case) when excl_dep_on_grouping_fields is called on it.

commit 694d1a50bd7754c5357dde184f87d63b7032ee5e
 
Author: Galina Shalygina <galina.shalygina@mariadb.com>
 
Date:   Fri Apr 5 22:55:20 2019 +0300
 
 
 
    MDEV-19185: Pushdown constant function defined with subquery
 
 
 
    The bug occurs because of the wrong pushdown of constant function
 
    defined with subquery from HAVING into WHERE. Subqueries can't be
 
    pushed into WHERE.
 
 
 
    To fix it with_subquery() call is added to check if the function contains
 
    subquery.
 
 
 
 mysql-test/main/having_cond_pushdown.result | 16 ++++++++++++++++
 
 mysql-test/main/having_cond_pushdown.test   | 17 +++++++++++++++++
 
 sql/item.cc                                 |  5 ++---
 
 sql/item_func.h                             |  2 +-
 
 4 files changed, 36 insertions(+), 4 deletions(-)

Comment by Yuchen Pei [ 2024-01-24 ]

The latest idea was to avoid creating extra pointers to items with
subquery during equality substitution (e.g. in calls to
build_equal_items()). I have a PoC patch doing that:

bb-11.0-mdev-29363-no-propagate-with-subquery 2bb75adaf1e680d6f07c0a5ec80b748f77fd1334
 
MDEV-29363 Do not change an item to another with subquery when propagating equal fields

It also contains tests from MDEV-32424 (currently marked duplicate to
this ticket) and MDEV-32608 (marked up to MDEV-32424). Another ticket,
MDEV-32539 was marked as a duplicate to MDEV-32424, but the assertion
failure still happens with the PoC patch.

Further look into MDEV-32539 seems to indicate it could be related to
this ticket, as it contains multiple references to a subquery
SELECT MIN(f) FROM t2, and destroys and rebuilds the join of the
subquery like in the case of this ticket. It does not result in
similar SEGV though. It is not clear what could have caused the
assertion failure - to be further looked into.

The case in MDEV-32539 also implies that we may want to update the
with_flags of Item_equal's to apply the idea mentioned at the
beginning of this comment, which currently does not happen in
Item_equal::fix_fields().

Comment by Yuchen Pei [ 2024-02-08 ]

Hi psergei, following up on the discussions on Monday, I have adopted the new fix the exclude subquery items from participating in multiple equalities. It also fixes test cases in MDEV-32424, MDEV-32608, MDEV-32539. Ptal thanks

upstream/bb-11.0-mdev-29363 9f7035507f41544f9c319ac89b0d42843468e8b5
 
MDEV-29363 avoid adding items with subqueries to item_equal
 
 
 
The optimizer constructs multiple equalities, and swap them in during
 
equality propagation. In doing so, it may create multiple pointers to
 
the same item. When the item contain subqueries, bad things can
 
happen. For example, in pushdown of HAVING to WHERE, if the HAVING
 
conditions contain multiple equalities with a const subquery, the
 
subquery will not be excluded from pushdown into WHERE. And if there
 
are multiple pointers to the same subquery, it may unfix the subquery
 
and destroy and rebuild its join, which could cause further
 
use-after-free if there are external pointers to items in the
 
join (e.g. tmp table fields as in the main case in MDEV-29363).

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