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

Aggregate functions does not work when it is called from views

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
    • 10.11
    • None

    Description

      Aggregate functions does not work when it is called from views. In the example below result of aggregate function is NULL and statistics of calls did not change.

      Test:

      --source include/default_optimizer_switch.inc
       
      flush status;
      show  status like "%custom_aggregate%";
      delimiter |;
       
      create aggregate function f1(x INT) returns int
      begin
        declare tot_sum int default 0;
        declare continue handler for not found return tot_sum;
        loop
          fetch group next row;
          set tot_sum= tot_sum + x;
        end loop;
      end|
       
      delimiter ;|
       
      create table t1 (sal int(10),id int(10));
      INSERT INTO t1 (sal,id) VALUES (5000,1);
      INSERT INTO t1 (sal,id) VALUES (2000,2);
      INSERT INTO t1 (sal,id) VALUES (1000,3);
       
      select f1(sal) from t1 where id>= 1;
      show  status like "%custom_aggregate%";
      create or replace view v1 as select f1(sal) from t1 where id>= 1;
      select * from v1;
      show  status like "%custom_aggregate%";
       
      drop view v1;
      drop table t1;
      drop function f1;
      

      Result:

      select f1(sal) from t1 where id>= 1;
      f1(sal)
      8000
      show  status like "%custom_aggregate%";
      Variable_name	Value
      Feature_custom_aggregate_functions	1
      create or replace view v1 as select f1(sal) from t1 where id>= 1;
      select * from v1;
      f1(sal)
      NULL
      show  status like "%custom_aggregate%";
      Variable_name	Value
      Feature_custom_aggregate_functions	1
      

      Expected result:

      select f1(sal) from t1 where id>= 1;
      f1(sal)
      8000
      show  status like "%custom_aggregate%";
      Variable_name	Value
      Feature_custom_aggregate_functions	1
      create or replace view v1 as select f1(sal) from t1 where id>= 1;
      select * from v1;
      f1(sal)
      8000
      show  status like "%custom_aggregate%";
      Variable_name	Value
      Feature_custom_aggregate_functions	2
      

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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