[MDEV-19680] Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index) || (!(ptr >= table->record[0] && ptr < table->record[0] + table->s->reclength)))' or alike failed upon SELECT with mix of functions from simple view Created: 2019-06-03  Updated: 2023-10-04

Status: Stalled
Project: MariaDB Server
Component/s: Server
Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-21384 Assertion `marked_for_read()' or `!ta... Closed
Relates
relates to MDEV-19984 Assertion `!table || (!table->read_se... Confirmed
relates to MDEV-20010 Equal on two RANK window functions cr... Closed
relates to MDEV-21384 Assertion `marked_for_read()' or `!ta... Closed
relates to MDEV-23291 SUM column from a derived table retur... Closed

 Description   

Note: We have plenty of open bugs with the same assertion failure, but they either use virtual columns, or in some other way essentially different scenarios. Unfortunately, the assertion is generic and happens in various cases, it's impossible to distinguish them.

CREATE TABLE t1 (a INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (1),(2);
 
CREATE VIEW v1 AS SELECT * FROM t1;
 
 
 
SELECT ISNULL( BENCHMARK( 1, MIN(a) ) ) FROM v1;
 
 
 
# Cleanup
 
DROP VIEW v1;
 
DROP TABLE t1;

10.3 7060b032

 
mysqld: /data/src/10.3/sql/field.cc:4267: virtual longlong Field_long::val_int(): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index) || (!(ptr >= table->record[0] && ptr < table->record[0] + table->s->reclength)))' failed.
 
190603 20:00:48 [ERROR] mysqld got signal 6 ;
 
 
 
#6  0x00007f8a5c99de67 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x55f3962f10f8 "!table || (!table->read_set || bitmap_is_set(table->read_set, field_index) || (!(ptr >= table->record[0] && ptr < table->record[0] + table->s->reclength)))", file=file@entry=0x55f3962f0ef0 "/data/src/10.3/sql/field.cc", line=line@entry=4267, function=function@entry=0x55f3962f3620 <Field_long::val_int()::__PRETTY_FUNCTION__> "virtual longlong Field_long::val_int()") at assert.c:92
 
#7  0x00007f8a5c99df12 in __GI___assert_fail (assertion=0x55f3962f10f8 "!table || (!table->read_set || bitmap_is_set(table->read_set, field_index) || (!(ptr >= table->record[0] && ptr < table->record[0] + table->s->reclength)))", file=0x55f3962f0ef0 "/data/src/10.3/sql/field.cc", line=4267, function=0x55f3962f3620 <Field_long::val_int()::__PRETTY_FUNCTION__> "virtual longlong Field_long::val_int()") at assert.c:101
 
#8  0x000055f3958ebd97 in Field_long::val_int (this=0x7f8a44006b00) at /data/src/10.3/sql/field.cc:4267
 
#9  0x000055f39593f4a2 in Item_field::val_int (this=0x7f8a44014e78) at /data/src/10.3/sql/item.cc:3432
 
#10 0x000055f39594df91 in Item_direct_ref::val_int (this=0x7f8a44016b68) at /data/src/10.3/sql/item.cc:8485
 
#11 0x000055f39595810b in Item_direct_view_ref::val_int (this=0x7f8a44016b68) at /data/src/10.3/sql/item.h:5294
 
#12 0x000055f39594d3d6 in Item_ref::val_int_result (this=0x7f8a44016b68) at /data/src/10.3/sql/item.cc:8279
 
#13 0x000055f395951ec1 in Item_cache_int::cache_value (this=0x7f8a44016d50) at /data/src/10.3/sql/item.cc:9875
 
#14 0x000055f395a067e2 in Item_sum_min::add (this=0x7f8a44012ad0) at /data/src/10.3/sql/item_sum.cc:2465
 
#15 0x000055f395a0d9f7 in Aggregator_simple::add (this=0x7f8a44018730) at /data/src/10.3/sql/item_sum.h:706
 
#16 0x000055f3956af9c1 in Item_sum::aggregator_add (this=0x7f8a44012ad0) at /data/src/10.3/sql/item_sum.h:551
 
#17 0x000055f3956af8ac in Item_sum::reset_and_add (this=0x7f8a44012ad0) at /data/src/10.3/sql/item_sum.h:441
 
#18 0x000055f3956a49c0 in init_sum_functions (func_ptr=0x7f8a44016fb0, end_ptr=0x7f8a44016fb8) at /data/src/10.3/sql/sql_select.cc:24401
 
#19 0x000055f39569b724 in end_send_group (join=0x7f8a44015c48, join_tab=0x7f8a44017f38, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:20819
 
#20 0x000055f395698571 in evaluate_join_record (join=0x7f8a44015c48, join_tab=0x7f8a44017ba0, error=0) at /data/src/10.3/sql/sql_select.cc:19652
 
#21 0x000055f395697e30 in sub_select (join=0x7f8a44015c48, join_tab=0x7f8a44017ba0, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:19432
 
#22 0x000055f395697358 in do_select (join=0x7f8a44015c48, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:18972
 
#23 0x000055f39566f85d in JOIN::exec_inner (this=0x7f8a44015c48) at /data/src/10.3/sql/sql_select.cc:4042
 
#24 0x000055f39566eca0 in JOIN::exec (this=0x7f8a44015c48) at /data/src/10.3/sql/sql_select.cc:3836
 
#25 0x000055f39566ff41 in mysql_select (thd=0x7f8a44000b00, tables=0x7f8a44012ef0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f8a44015c20, unit=0x7f8a440049c8, select_lex=0x7f8a44005140) at /data/src/10.3/sql/sql_select.cc:4241
 
#26 0x000055f395661ea4 in handle_select (thd=0x7f8a44000b00, lex=0x7f8a44004900, result=0x7f8a44015c20, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:373
 
#27 0x000055f39562ca31 in execute_sqlcom_select (thd=0x7f8a44000b00, all_tables=0x7f8a44012ef0) at /data/src/10.3/sql/sql_parse.cc:6562
 
#28 0x000055f395623010 in mysql_execute_command (thd=0x7f8a44000b00) at /data/src/10.3/sql/sql_parse.cc:3818
 
#29 0x000055f395630988 in mysql_parse (thd=0x7f8a44000b00, rawbuf=0x7f8a44012808 "SELECT ISNULL( BENCHMARK( 1, MIN(a) ) ) FROM v1", length=47, parser_state=0x7f8a56b9c5f0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:8105
 
#30 0x000055f39561d9ca in dispatch_command (command=COM_QUERY, thd=0x7f8a44000b00, packet=0x7f8a44008c61 "", packet_length=47, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1858
 
#31 0x000055f39561c3ed in do_command (thd=0x7f8a44000b00) at /data/src/10.3/sql/sql_parse.cc:1403
 
#32 0x000055f395785135 in do_handle_one_connection (connect=0x55f397fb6ef0) at /data/src/10.3/sql/sql_connect.cc:1402
 
#33 0x000055f395784eac in handle_one_connection (arg=0x55f397fb6ef0) at /data/src/10.3/sql/sql_connect.cc:1308
 
#34 0x000055f39605d7e2 in pfs_spawn_thread (arg=0x55f397fd1df0) at /data/src/10.3/storage/perfschema/pfs.cc:1862
 
#35 0x00007f8a5e5124a4 in start_thread (arg=0x7f8a56b9d700) at pthread_create.c:456
 
#36 0x00007f8a5ca5ad0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

5.5 6660c072

 
mysqld: /data/src/5.5/sql/field.cc:3662: virtual longlong Field_long::val_int(): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed.
 
190603 20:01:45 [ERROR] mysqld got signal 6 ;
 
 
 
#6  0x00007f7dddf3de67 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x564d14bb56c0 "!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))", file=file@entry=0x564d14bb55cc "/data/src/5.5/sql/field.cc", line=line@entry=3662, function=function@entry=0x564d14bb7740 <Field_long::val_int()::__PRETTY_FUNCTION__> "virtual longlong Field_long::val_int()") at assert.c:92
 
#7  0x00007f7dddf3df12 in __GI___assert_fail (assertion=0x564d14bb56c0 "!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))", file=0x564d14bb55cc "/data/src/5.5/sql/field.cc", line=3662, function=0x564d14bb7740 <Field_long::val_int()::__PRETTY_FUNCTION__> "virtual longlong Field_long::val_int()") at assert.c:101
 
#8  0x0000564d145a592d in Field_long::val_int (this=0x7f7dd8c56f98) at /data/src/5.5/sql/field.cc:3662
 
#9  0x0000564d145dbe99 in Item_field::val_int (this=0x7f7dd8cc4f50) at /data/src/5.5/sql/item.cc:2636
 
#10 0x0000564d145e8e2b in Item_direct_ref::val_int (this=0x7f7dd8d82d20) at /data/src/5.5/sql/item.cc:7462
 
#11 0x0000564d145f274d in Item_direct_view_ref::val_int (this=0x7f7dd8d82d20) at /data/src/5.5/sql/item.h:3416
 
#12 0x0000564d145e81d4 in Item_ref::val_int_result (this=0x7f7dd8d82d20) at /data/src/5.5/sql/item.cc:7238
 
#13 0x0000564d145ecc3f in Item_cache_int::cache_value (this=0x7f7dd8d82ef0) at /data/src/5.5/sql/item.cc:8855
 
#14 0x0000564d1466f00f in Item_sum_min::add (this=0x7f7dd8cc4340) at /data/src/5.5/sql/item_sum.cc:2117
 
#15 0x0000564d14675093 in Aggregator_simple::add (this=0x7f7dd8d9a310) at /data/src/5.5/sql/item_sum.h:671
 
#16 0x0000564d14483b29 in Item_sum::aggregator_add (this=0x7f7dd8cc4340) at /data/src/5.5/sql/item_sum.h:520
 
#17 0x0000564d14483a14 in Item_sum::reset_and_add (this=0x7f7dd8cc4340) at /data/src/5.5/sql/item_sum.h:417
 
#18 0x0000564d1447b491 in init_sum_functions (func_ptr=0x7f7dd8d82fb0, end_ptr=0x7f7dd8d82fb8) at /data/src/5.5/sql/sql_select.cc:21967
 
#19 0x0000564d14472e03 in end_send_group (join=0x7f7dd8cc5ad8, join_tab=0x7f7dd8d99e80, end_of_records=false) at /data/src/5.5/sql/sql_select.cc:18425
 
#20 0x0000564d14470276 in evaluate_join_record (join=0x7f7dd8cc5ad8, join_tab=0x7f7dd8d99b60, error=0) at /data/src/5.5/sql/sql_select.cc:17347
 
#21 0x0000564d1446fb30 in sub_select (join=0x7f7dd8cc5ad8, join_tab=0x7f7dd8d99b60, end_of_records=false) at /data/src/5.5/sql/sql_select.cc:17130
 
#22 0x0000564d1446f38f in do_select (join=0x7f7dd8cc5ad8, fields=0x7f7dd8cc5eb8, table=0x0, procedure=0x0) at /data/src/5.5/sql/sql_select.cc:16792
 
#23 0x0000564d1444cb1b in JOIN::exec (this=0x7f7dd8cc5ad8) at /data/src/5.5/sql/sql_select.cc:2912
 
#24 0x0000564d1444d2f1 in mysql_select (thd=0x7f7dd9a6f060, rref_pointer_array=0x7f7dd9a72d08, tables=0x7f7dd8cc4768, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f7dd8cc5ab8, unit=0x7f7dd9a72388, select_lex=0x7f7dd9a72a68) at /data/src/5.5/sql/sql_select.cc:3133
 
#25 0x0000564d144438d4 in handle_select (thd=0x7f7dd9a6f060, lex=0x7f7dd9a722d8, result=0x7f7dd8cc5ab8, setup_tables_done_option=0) at /data/src/5.5/sql/sql_select.cc:311
 
#26 0x0000564d1441c7a0 in execute_sqlcom_select (thd=0x7f7dd9a6f060, all_tables=0x7f7dd8cc4768) at /data/src/5.5/sql/sql_parse.cc:4684
 
#27 0x0000564d144156a1 in mysql_execute_command (thd=0x7f7dd9a6f060) at /data/src/5.5/sql/sql_parse.cc:2222
 
#28 0x0000564d1441f363 in mysql_parse (thd=0x7f7dd9a6f060, rawbuf=0x7f7dd8cc4078 "SELECT ISNULL( BENCHMARK( 1, MIN(a) ) ) FROM v1", length=47, parser_state=0x7f7ddf9b2620) at /data/src/5.5/sql/sql_parse.cc:5929
 
#29 0x0000564d14412c1f in dispatch_command (command=COM_QUERY, thd=0x7f7dd9a6f060, packet=0x7f7dd9bea061 "", packet_length=47) at /data/src/5.5/sql/sql_parse.cc:1067
 
#30 0x0000564d14411e2f in do_command (thd=0x7f7dd9a6f060) at /data/src/5.5/sql/sql_parse.cc:793
 
#31 0x0000564d145174f9 in do_handle_one_connection (thd_arg=0x7f7dd9a6f060) at /data/src/5.5/sql/sql_connect.cc:1268
 
#32 0x0000564d14517268 in handle_one_connection (arg=0x7f7dd9a6f060) at /data/src/5.5/sql/sql_connect.cc:1184
 
#33 0x0000564d1489bbd8 in pfs_spawn_thread (arg=0x7f7dd9b92300) at /data/src/5.5/storage/perfschema/pfs.cc:1015
 
#34 0x00007f7ddf5e44a4 in start_thread (arg=0x7f7ddf9b3700) at pthread_create.c:456
 
#35 0x00007f7dddffad0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Also reproducible with Aria. Not reproducible with InnoDB.
No obvious problem on a non-debug build.

 Comments   
Comment by Alice Sherepa [ 2019-07-10 ]

The same without view, with derived table:

CREATE TABLE t1 (a INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
SELECT ISNULL( BENCHMARK( 1, MIN(a) ) ) FROM (SELECT * FROM t1) dt;

10.4 70c2bde931246ea4966

 
#5  0x00007f8c6ea2302a in __GI_abort () at abort.c:89
 
#6  0x00007f8c6ea19bd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x55e31d9a6d10 "marked_for_read()", file=file@entry=0x55e31d9a6b18 "/10.4/sql/field.cc", line=line@entry=4257, function=function@entry=0x55e31d9a9300 <Field_long::val_int()::__PRETTY_FUNCTION__> "virtual longlong Field_long::val_int()") at assert.c:92
 
#7  0x00007f8c6ea19c82 in __GI___assert_fail (assertion=0x55e31d9a6d10 "marked_for_read()", file=0x55e31d9a6b18 "/10.4/sql/field.cc", line=4257, function=0x55e31d9a9300 <Field_long::val_int()::__PRETTY_FUNCTION__> "virtual longlong Field_long::val_int()") at assert.c:101
 
#8  0x000055e31cf2b3bc in Field_long::val_int (this=0x7f8c1400a480) at /10.4/sql/field.cc:4257
 
#9  0x000055e31cf7e086 in Item_field::val_int (this=0x7f8c14016c80) at /10.4/sql/item.cc:3196
 
#10 0x000055e31cf8ce33 in Item_direct_ref::val_int (this=0x7f8c14017090) at /10.4/sql/item.cc:8310
 
#11 0x000055e31cf96e29 in Item_direct_view_ref::val_int (this=0x7f8c14017090) at /10.4/sql/item.h:5683
 
#12 0x000055e31cf8c1d2 in Item_ref::val_int_result (this=0x7f8c14017090) at /10.4/sql/item.cc:8090
 
#13 0x000055e31cf90e3f in Item_cache_int::cache_value (this=0x7f8c14017278) at /10.4/sql/item.cc:9712
 
#14 0x000055e31d04d852 in Item_sum_min::add (this=0x7f8c14013858) at /10.4/sql/item_sum.cc:2465
 
#15 0x000055e31d054d01 in Aggregator_simple::add (this=0x7f8c14018dd8) at /10.4/sql/item_sum.h:715
 
#16 0x000055e31ccb10bf in Item_sum::aggregator_add (this=0x7f8c14013858) at /10.4/sql/item_sum.h:558
 
#17 0x000055e31ccb0faa in Item_sum::reset_and_add (this=0x7f8c14013858) at /10.4/sql/item_sum.h:443
 
#18 0x000055e31cca4c9d in init_sum_functions (func_ptr=0x7f8c140175a8, end_ptr=0x7f8c140175b0) at /10.4/sql/sql_select.cc:25123
 
#19 0x000055e31cc9b85e in end_send_group (join=0x7f8c140160a8, join_tab=0x7f8c140185d0, end_of_records=false) at /10.4/sql/sql_select.cc:21504
 
#20 0x000055e31cc986ca in evaluate_join_record (join=0x7f8c140160a8, join_tab=0x7f8c14018228, error=0) at /10.4/sql/sql_select.cc:20334
 
#21 0x000055e31cc97fb0 in sub_select (join=0x7f8c140160a8, join_tab=0x7f8c14018228, end_of_records=false) at /10.4/sql/sql_select.cc:20114
 
#22 0x000055e31cc974e0 in do_select (join=0x7f8c140160a8, procedure=0x0) at /10.4/sql/sql_select.cc:19652
 
#23 0x000055e31cc6d7b3 in JOIN::exec_inner (this=0x7f8c140160a8) at /10.4/sql/sql_select.cc:4391
 
#24 0x000055e31cc6c960 in JOIN::exec (this=0x7f8c140160a8) at /10.4/sql/sql_select.cc:4173
 
#25 0x000055e31cc6e005 in mysql_select (thd=0x7f8c14000b00, tables=0x7f8c140150f8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f8c14016080, unit=0x7f8c14004a30, select_lex=0x7f8c140131f8) at /10.4/sql/sql_select.cc:4605
 
#26 0x000055e31cc5e5ce in handle_select (thd=0x7f8c14000b00, lex=0x7f8c14004968, result=0x7f8c14016080, setup_tables_done_option=0) at /10.4/sql/sql_select.cc:425
 
#27 0x000055e31cc27f0a in execute_sqlcom_select (thd=0x7f8c14000b00, all_tables=0x7f8c140150f8) at /10.4/sql/sql_parse.cc:6340
 
#28 0x000055e31cc1dd58 in mysql_execute_command (thd=0x7f8c14000b00) at /10.4/sql/sql_parse.cc:3882
 
#29 0x000055e31cc2bce2 in mysql_parse (thd=0x7f8c14000b00, rawbuf=0x7f8c14013108 "SELECT ISNULL( BENCHMARK( 1, MIN(a) ) ) FROM (SELECT * FROM t1) dt", length=66, parser_state=0x7f8c62f69000, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:7892
 
#30 0x000055e31cc17f51 in dispatch_command (command=COM_QUERY, thd=0x7f8c14000b00, packet=0x7f8c1419dca1 "", packet_length=66, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1827
 
#31 0x000055e31cc166e3 in do_command (thd=0x7f8c14000b00) at /10.4/sql/sql_parse.cc:1360
 
#32 0x000055e31cd90926 in do_handle_one_connection (connect=0x55e31fdaff60) at /10.4/sql/sql_connect.cc:1404
 
#33 0x000055e31cd9064f in handle_one_connection (arg=0x55e31fdaff60) at /10.4/sql/sql_connect.cc:1306
 
#34 0x000055e31d6bbfa7 in pfs_spawn_thread (arg=0x55e31fd2e710) at /10.4/storage/perfschema/pfs.cc:1862
 
#35 0x00007f8c6f8626ba in start_thread (arg=0x7f8c62f6a700) at pthread_create.c:333
 
#36 0x00007f8c6eaf341d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Comment by Varun Gupta (Inactive) [ 2019-12-26 ]

The issue here is for column a the read_set map is not set. This is because the benchmark function always returns a NOT NULL value, so ISNULL functional item becomes a const item and the read_set in case of view is not set.
The read_set needs to be set because when aggregate functions are inside other items, split_sum_func is called which adds the aggregate function to the JOIN::all_fields list. So the approach to fix this would be to go through the JOIN::all_fields list and set the read_set for column a.

Comment by Varun Gupta (Inactive) [ 2019-12-26 ]

Patch
http://lists.askmonty.org/pipermail/commits/2019-December/014115.html

Comment by Sergei Petrunia [ 2019-12-26 ]

Ok to push.

Comment by Igor Babaev [ 2020-01-02 ]

I've reopened the bug, because the fix is basically incorrect (actually it's a pure hack). See my analysis in comments.

Comment by Igor Babaev [ 2020-01-02 ]

The first question that should have been asked is:
Why the query

SELECT ISNULL( BENCHMARK(1, MIN(a))) FROM v1;

fails while the query

SELECT ISNULL( BENCHMARK(1, MIN(a))) FROM t1;

does not?
The answer is:
For the first query the function update_used_tables() is called that contains such optimization code 

    if (!args[0]->maybe_null && !arg_is_datetime_notnull_field())
 
    {
 
      used_tables_cache= 0;			/* is always false */
 
      const_item_cache= 1;
 
    }

.
There is no such code working for the second query, because the code of Item_func::fix_fields does not contain it. The following function makes the processing of both queries similar though they both fail:

+bool Item_func_isnull::fix_fields(THD *thd, Item **ref)
 
+{
 
+  if (Item_func::fix_fields(thd, ref))
 
+    return true;
 
+  if (!args[0]->maybe_null && !arg_is_datetime_notnull_field())
 
+  {
 
+    used_tables_cache= 0;                      /* is always false */
 
+    const_item_cache= 1;
 
+  } 
+  return false; 
+}

This failures disappear when we prohibit to split constant expressions with set functions (there is no need for this):

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
 
index 799c85a..a22bdd0 100644
 
--- a/sql/sql_base.cc
 
+++ b/sql/sql_base.cc
 
@@ -7639,7 +7639,7 @@ bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array,
 
       split_sum_func() must be called for Window Function items, see
 
       Item_window_func::split_sum_func.
 
     */
 
-    if (sum_func_list &&
 
+    if (sum_func_list && !item->const_item() &&
 
          ((item->with_sum_func() && item->type() != Item::SUM_FUNC_ITEM) ||
 
           item->with_window_func))
 
     {

After this both queries return the same result sets, but we still have a problem: the result sets contains more that one row.
To fix this we need to mark the selects as ones with implicit grouping: select_lex->implicit grouping must be set to true for them:

--- a/sql/sql_select.cc
 
+++ b/sql/sql_select.cc
 
@@ -1359,6 +1359,11 @@ JOIN::prepare(TABLE_LIST *tables_init,
 
       order= NULL;
 
   }
 
 
+  if (select_lex->with_aggr && !group_list)
 
+    implicit_grouping= true;
 
+  
   if (having && having->with_sum_func())
 
     having->split_sum_func2(thd, ref_ptrs, all_fields,
 
                             &having, SPLIT_SUM_SKIP_REGISTERED);

Here we use the new flag st_select_lex::with_aggr is set to true if we have aggregations in the given SELECT.

diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
 
index 16bb53c..6beb195 100644
 
--- a/sql/sql_lex.cc
 
+++ b/sql/sql_lex.cc
 
@@ -2445,6 +2445,7 @@ void st_select_lex::init_select()
 
   offset_limit= 0;      /* denotes the default offset = 0 */
 
   is_set_query_expr_tail= false;
 
   with_sum_func= 0;
 
+  with_aggr= false;
 
   with_all_modifier= 0;
 
   is_correlated= 0;
 
   cur_pos_in_select_list= UNDEF_POS;
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
 
index 9d22eb1..16bc9fa 100644
 
--- a/sql/sql_lex.h
 
+++ b/sql/sql_lex.h
 
@@ -1175,6 +1175,7 @@ class st_select_lex: public st_select_lex_node
 
   enum_parsing_place save_parsing_place;
 
   enum_parsing_place context_analysis_place; /* where we are in prepare */
 
   bool with_sum_func;   /* sum function indicator */
 
+  bool with_aggr;
 
 
   ulong table_join_options;
 
   uint in_sum_expr;
 
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
 
index 13a823f..1ed780c 100644
 
--- a/sql/item_sum.cc
 
+++ b/sql/item_sum.cc
 
@@ -319,6 +319,8 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
 
     aggr_sel->set_custom_agg_func_used(true);
 
   update_used_tables();
 
   thd->lex->in_sum_func= in_sum_func;
 
+  if (aggr_level == nest_level && !is_in_window_func())
 
+    curr_sel->with_aggr= true;
 
   return FALSE;
 
 }
 
 
@@ -387,6 +389,7 @@ bool Item_sum::register_sum_func(THD *thd, Item **ref)
 
     }
 
     aggr_sel->inner_sum_func_list= this;
 
     aggr_sel->with_sum_func= 1;
 
+    aggr_sel->with_aggr= true;
 
 
     /* 
       Mark Item_subselect(s) as containing aggregate function all the way up

The condition !is_in_window_func() is needed only for the code with window functions. It is supported with the following changes:

diff --git a/sql/item_sum.h b/sql/item_sum.h
 
index 7ef1b7e..51e5170 100644
 
--- a/sql/item_sum.h
 
+++ b/sql/item_sum.h
 
@@ -343,6 +343,8 @@ class Item_sum :public Item_func_or_sum
 
   /* TRUE if this is aggregate function of a window function */
 
   bool window_func_sum_expr_flag;
 
 
+  bool in_window_func;
 
+
 
 public:
 
 
   bool has_force_copy_fields() const { return force_copy_fields; }
 
@@ -587,6 +589,8 @@ class Item_sum :public Item_func_or_sum
 
   virtual void setup_caches(THD *thd) {};
 
 
   bool with_sum_func() const { return true; }
 
+  void mark_as_in_window_func() { in_window_func= true; }
 
+  bool is_in_window_func() { return in_window_func; }
 
 };
 
diff --git a/sql/item_windowfunc.cc b/sql/item_windowfunc.cc
 
index 5ecb951..365744c 100644
 
--- a/sql/item_windowfunc.cc
 
+++ b/sql/item_windowfunc.cc
 
@@ -115,6 +115,7 @@ Item_window_func::fix_fields(THD *thd, Item **ref)
 
     This will substitute *this (an Item_window_func object) with Item_sum
 
     object. Is this the intent?
 
   */
 
+  window_func()->mark_as_in_window_func();
 
   if (window_func()->fix_fields(thd, ref))
 
     return true;

Comment by Igor Babaev [ 2020-01-02 ]

After the above changes we have only one failing test in the main suite. The failure is due to a new explain output for one of the queries from distinct.test and we have

MariaDB [test]> SELECT ISNULL( BENCHMARK(1, MIN(a))) FROM v1;
 
+-------------------------------+
 
| ISNULL( BENCHMARK(1, MIN(a))) |
 
+-------------------------------+
 
|                             0 |
 
+-------------------------------+
 
1 row in set (0.002 sec)
 
 
 
MariaDB [test]> SELECT ISNULL( BENCHMARK(1, MIN(a))) FROM t1;
 
+-------------------------------+
 
| ISNULL( BENCHMARK(1, MIN(a))) |
 
+-------------------------------+
 
|                             0 |
 
+-------------------------------+
 
1 row in set (0.001 sec)

and we have this:

MariaDB [test]> SELECT ISNULL( BENCHMARK(1, MIN(a))) FROM v1 group by a;
 
+-------------------------------+
 
| ISNULL( BENCHMARK(1, MIN(a))) |
 
+-------------------------------+
 
|                             0 |
 
|                             0 |
 
+-------------------------------+
 
2 rows in set (0.003 sec)
 
 
 
MariaDB [test]> SELECT ISNULL( BENCHMARK(1, MIN(a))) FROM t1 group by a;
 
+-------------------------------+
 
| ISNULL( BENCHMARK(1, MIN(a))) |
 
+-------------------------------+
 
|                             0 |
 
|                             0 |
 
+-------------------------------+

Comment by Igor Babaev [ 2020-01-02 ]

We also should add an implementation for Item_func_isnotnull::fix_fields() and a test case where this function is tested.

Comment by Alice Sherepa [ 2021-03-03 ]

not reproducible on current 10.2-10.5

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