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

CREATE VIEW with `ALGORITHM=MERGE` and `ALGORITHM=TEMPTABLE` return different grouped `VAR_SAMP(...)` results under `HAVING ... IS NULL`

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.11, 11.8, 12.3, 11.4.10
    • N/A
    • Optimizer
    • Not for Release Notes

    Description

      Two views with the same definition but different view algorithms produce different results on MariaDB.
      The query groups by an indexed column, computes `VAR_SAMP(...)`, and filters with `HAVING ... IS NULL`.
      `TEMPTABLE` returns the expected `NULL` group, while `MERGE` incorrectly returns a non-`NULL` aggregate value.

      How to repeat:

      CREATE TABLE t (
        a INT,
        b CHAR(1),
        KEY (b)
      );
       
      INSERT INTO t VALUES
        (1, 'a'),
        (2, 'a'),
        (3, 'b');
       
      CREATE ALGORITHM=MERGE VIEW vm AS
      SELECT *
      FROM t;
       
      CREATE ALGORITHM=TEMPTABLE VIEW vt AS
      SELECT *
      FROM t;
       
      SELECT VAR_SAMP(a) AS v
      FROM vm
      GROUP BY b
      HAVING v IS NULL;
       
      SELECT VAR_SAMP(a) AS v
      FROM vt
      GROUP BY b
      HAVING v IS NULL;
      

      Observed result - Query A (`MERGE`):

      v
      0.5

      Observed result - Query B (`TEMPTABLE`):

      v
      NULL

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              pyuan Peiyuan Liu
              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.