Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14520

Custom aggregate functions work incorrectly with WITH ROLLUP clause

Details

    • 10.3.6-1

    Description

      Note: The agg_sum function definition is taken as is from main.aggregate_functions test.

      --delimiter |
      create aggregate function agg_sum(x INT) returns INT
      begin
      declare z int default 0;
      declare continue handler for not found return z;
      loop
      fetch group next row;
      set z= z+x;
      end loop;
      end|
      --delimiter ;
       
      create table t1 (i int);
      insert into t1 values (1),(2),(2),(3);
      select i, agg_sum(i) from t1 group by i with rollup;
      --echo #
      --echo # Compare with
      select i, sum(i) from t1 group by i with rollup;
       
      # Cleanup
      drop function agg_sum;
      drop table t1;
      

      Actual result with agg_sum

      select i, agg_sum(i) from t1 group by i with rollup;
      i	agg_sum(i)
      1	1
      2	8
      3	6
      NULL	6
      

      Expected result with agg_sum, actual result with sum

      select i, sum(i) from t1 group by i with rollup;
      i	sum(i)
      1	1
      2	4
      3	3
      NULL	8
      

      Attachments

        Issue Links

          Activity

            Currently the main reason for the incorrect results in case of use of rollup in custom aggregate functions that in the function JOIN::rollup_make_fields, it says that create copies of item_sum items for each sum level, currently copy_or_same() function is missing from custom aggregate functions. This function would basically create a copy of custom aggregate functions Item_sum_sp object when we do rollup

            varun Varun Gupta (Inactive) added a comment - Currently the main reason for the incorrect results in case of use of rollup in custom aggregate functions that in the function JOIN::rollup_make_fields, it says that create copies of item_sum items for each sum level, currently copy_or_same() function is missing from custom aggregate functions. This function would basically create a copy of custom aggregate functions Item_sum_sp object when we do rollup
            varun Varun Gupta (Inactive) added a comment - Patch http://lists.askmonty.org/pipermail/commits/2018-May/012556.html

            One comment then OK to push once buildbot clears the tests:

            +Item* Item_sum_sp::copy_or_same(THD *thd)
            +{
            +  Item_sum_sp *copy_item= new (thd->mem_root) Item_sum_sp(thd, this);
            +  copy_item->init_result_field(thd, max_length, maybe_null, &null_value, &name);
            +  return copy_item;
            +}
            +
            

            init_result_field should take &copy_item->null_value and &copy_item->name, otherwise we're using the previous item's fields null_value. There is no apparent bug as the with rollup execution happens after the parent's item values have been computed, but you are reusing the parent item's null_value field instead of the child's.

            cvicentiu Vicențiu Ciorbaru added a comment - One comment then OK to push once buildbot clears the tests: +Item* Item_sum_sp::copy_or_same(THD *thd) +{ + Item_sum_sp *copy_item= new (thd->mem_root) Item_sum_sp(thd, this); + copy_item->init_result_field(thd, max_length, maybe_null, &null_value, &name); + return copy_item; +} + init_result_field should take &copy_item->null_value and &copy_item->name , otherwise we're using the previous item's fields null_value. There is no apparent bug as the with rollup execution happens after the parent's item values have been computed, but you are reusing the parent item's null_value field instead of the child's.

            OK to push once the comment has been addressed.

            cvicentiu Vicențiu Ciorbaru added a comment - OK to push once the comment has been addressed.

            People

              varun Varun Gupta (Inactive)
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.