Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-3340

Filtering not working properly when using window functions in embedded query

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 1.1.7
    • Icebox
    • ExeMgr
    • None

    Description

      Filtering does not work properly in the columnstore when there is an embedded query using window functions.

      See the example below: we are trying to compute a moving average and then filter out NULL values. The query works well in InnoDB or TokuDB. But not with the columnstore which keeps the NULL values. We have seen the same behaviour in version 1.2.3.

      CREATE TABLE `bug_cs` (
      `a` tinyint unsigned,
      `b` decimal(5,2) DEFAULT NULL
      ) ENGINE=columnstore;

      INSERT INTO `bug_cs`
      (`a`,
      `b`)
      VALUES (1,2.05), (2,5.44),(3,3.04), (4,4.03),(5,2.45),(6,9.02),(7,4.23),(8,3.02),(9,2.84),(10,8.3);

      select * from
      (
      SELECT
      `a`,
      case when ROW_NUMBER() OVER `w_1`>3 then AVG(`b`) OVER `w_0` else null end as `mavg`
      FROM bug_cs
      WINDOW
      `w_1` as ( ORDER BY `a` ASC),
      `w_0` as ( ORDER BY `a` ASC rows between 3 preceding and current row)
      ) t
      where `mavg` is not null;

      Attachments

        Activity

          People

            leonid.fedorov Leonid Fedorov
            Girod Emmanuel
            Votes:
            1 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.