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

Window functions: interplay between window function and other constructs

Details

    • 10.2.0-9, 10.2.0-10, 10.2.0-11

    Description

      Study (and fix) the interplay between window functions and other SQL constructs, like ORDER BY ... LIMIT, DISTINCT, etc.

      Known things:

      • LIMIT should be applied AFTER the window functions are computed (that is, window function computation should see rows that are cut off by LIMIT)
      • DISTINCT must not be converted into GROUP BY when window functions are present
      • need to check window functions inside derived table and/or union. Item_window_func::update_field() is not implemented, is this ok?

      Attachments

        Issue Links

          is part of

          Task - A task that needs to be done. MDEV-6115 window functions as in the SQL standard

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia added a comment -

            Example from Peter:

            create table t1 (s1 int, s2 char(5));
            		 
            insert into t1 values (1,'a');
            		 
            insert into t1 values (null,null);
            		 
            insert into t1 values (1,null);
            		 
            insert into t1 values (null,'a');
            		 
            insert into t1 values (2,'b');
            		 
            insert into t1 values (-1,'');


            select *,
            		 
                     row_number() over (order by s1)
            		 
                     - row_number() over (order by s1) as X from t1;

            This crashes, because "split_sum_func" process is not done correctly for window functions.

            • The fact that split_sum_func is not invoked for the Item_func_minus is a trivial typo.
            • But if I make it to be invoked, it does nothing
            • What should Item::split_sum_func do when it is invoked for a Item_window_func that's an argument of an expression? Should we create an Item_direct_ref? Or just inject the item into the select list?
            psergei Sergei Petrunia added a comment - Example from Peter: create table t1 (s1 int, s2 char(5)); insert into t1 values (1,'a'); insert into t1 values (null,null); insert into t1 values (1,null); insert into t1 values (null,'a'); insert into t1 values (2,'b'); insert into t1 values (-1,''); select *, row_number() over (order by s1) - row_number() over (order by s1) as X from t1; This crashes, because "split_sum_func" process is not done correctly for window functions. The fact that split_sum_func is not invoked for the Item_func_minus is a trivial typo. But if I make it to be invoked, it does nothing What should Item::split_sum_func do when it is invoked for a Item_window_func that's an argument of an expression? Should we create an Item_direct_ref? Or just inject the item into the select list?
            psergei Sergei Petrunia added a comment -

            The above is now resolved.

            psergei Sergei Petrunia added a comment - The above is now resolved.
            psergei Sergei Petrunia added a comment -

            The problem with DISTINCT being converted into GROUP BY is resolved

            psergei Sergei Petrunia added a comment - The problem with DISTINCT being converted into GROUP BY is resolved
            psergei Sergei Petrunia added a comment -

            We seem to still have a problem with queries that have aggregates but do not have GROUP BY clause.

            This is not critical because aggregages w/o GROUP BY means the query output is just one row. It is not very meaningful to compute window functions over a resultset that has just one row (although this is allowed and should be fixed).

            psergei Sergei Petrunia added a comment - We seem to still have a problem with queries that have aggregates but do not have GROUP BY clause. This is not critical because aggregages w/o GROUP BY means the query output is just one row. It is not very meaningful to compute window functions over a resultset that has just one row (although this is allowed and should be fixed).
            psergei Sergei Petrunia added a comment -

            Marking as fixed (in 10.2.1 version)

            psergei Sergei Petrunia added a comment - Marking as fixed (in 10.2.1 version)

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.