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

HAVING STDDEV_POP(x) IS NOT NULL incorrectly removes non-NULL singleton groups

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11, 11.4, 11.8, 12.3, 13.1, 12.3.2
    • 10.11, 11.4, 11.8, 12.3, 13.1
    • Optimizer
    • None

    Description

      Description

      MariaDB Server 12.3.2 incorrectly filters all three singleton groups when `STDDEV_POP IS NOT NULL` is used in HAVING alongside a projected SUM DISTINCT. Without HAVING, STDDEV_POP returns non-NULL 0 for every group. Adding SELECT DISTINCT also restores all groups.

      A derived-table filter reproduces the failure with default condition pushdown, while disabling `condition_pushdown_for_derived` restores the correct result. The direct HAVING query already demonstrates the incorrect result independently of that transformation.

      How to repeat

       
      CREATE TEMPORARY TABLE stddev_probe (id INT,k VARCHAR(10),x DOUBLE,txt LONGTEXT);
      INSERT INTO stddev_probe VALUES
          (1,'a',3.71,'one'),(2,'b',5.25,'two'),(3,'c',9.12,'three');
       
      SELECT STDDEV_POP(x) AS sd,SUM(DISTINCT x) AS s,txt
      FROM stddev_probe GROUP BY txt,k HAVING STDDEV_POP(x) IS NOT NULL;
      

      Expected result

      Three rows: `(0,3.71,'one'),(0,9.12,'three'),(0,5.25,'two')`, order unspecified. Each group has exactly one non-NULL x, whose population standard deviation is 0, not NULL.

      Actual result

      No rows.

      Attachments

        Activity

          People

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