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

CREATE VIEW extraction changes COUNT(*) result for ordered grouped VAR_SAMP(... ) IS NULL query

    XMLWordPrintable

Details

    • Not for Release Notes

    Description

      Under semantic-equivalent rewriting, extracting a grouped VAR_SAMP subquery into a view causes a stable COUNT( * ) result mismatch on MariaDB when the grouped result is ordered and then filtered by IS NULL.
      The source and mutated queries are logically equivalent, but return different final values.

      CREATE TABLE t1_case06 (
        c1 INT NOT NULL,
        c6 VARCHAR(10) NOT NULL
      ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
       
      INSERT INTO t1_case06 VALUES
        (2323, 'sample_s'),
        (3269, 'sample_S'),
        (143, 'sample_3Bs');
       
      CREATE INDEX idx_c6 ON t1_case06 (c6);
       
      -- Source Original SQL
      SELECT COUNT(*) AS col_1
      FROM (
        SELECT c6, VAR_SAMP(c1) AS v
        FROM t1_case06
        GROUP BY c6
        ORDER BY c6 DESC
      ) q
      WHERE v IS NULL;
       
      -- View SQL
      CREATE VIEW v_case06 AS
      SELECT c6, VAR_SAMP(c1) AS v
      FROM t1_case06
      GROUP BY c6;
       
      -- Mutated SQL
      SELECT COUNT(*) AS col_1
      FROM (
        SELECT c6, v
        FROM v_case06
        ORDER BY c6 DESC
      ) q
      WHERE v IS NULL;
      

      Observed result - Query A (original):

      col_1
      0

      Observed result - Query B (mutated):

      col_1
      1

      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.