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

Wrong result upon GROUP BY on a table with indexed virtual column after INSERT IGNORE

    XMLWordPrintable

Details

    • Unexpected results
    • Q2/2026 Server Development

    Description

      CREATE TABLE t (a INT PRIMARY KEY, va TINYINT AS (a), KEY(va));
      		 
      INSERT IGNORE INTO t (a) VALUES (100),(150),(200);
      		 
       
      		 
      SELECT a, COUNT(*) FROM t GROUP BY a;
      		 
      SELECT * FROM t;
      		 
       
      		 
      DROP TABLE t;

      12.2 de72d02bffe6639ef0b7911966a3f85e3eba052f

      		 
      SELECT a, COUNT(*) FROM t GROUP BY a;
      		 
      a	COUNT(*)
      		 
      100	1
      		 
      150	2
      		 
      SELECT * FROM t;
      		 
      a	va
      		 
      100	100
      		 
      150	127
      		 
      200	127

      The result of the first SELECT is obviously wrong, it should return 3 rows, without COUNT=1 for each.

      The failure started happening after this commit in 12.1.1:

      commit 8cdee25952763a0401e4c2a4d61e92c13499bdc6
      		 
      Author: Yuchen Pei
      		 
      Date:   Wed Jun 4 11:43:30 2025 +1000
      		 
       
      		 
          MDEV-36132 Substitute vcol expressions with indexed vcol fields in ORDER BY and GROUP BY

      Note that the bug also affects mysql:

      select version();
      		 
      version()
      		 
      9.6.0-debug
      		 
      CREATE TABLE t (a INT, va1 TINYINT AS (a), va2 TINYINT AS (a + 1), KEY(va1), KEY(va2));
      		 
      INSERT IGNORE INTO t (a) VALUES (200),(300);
      		 
      Warnings:
      		 
      Warning 1264    Out of range value for column 'va1' at row 1
      		 
      Warning 1264    Out of range value for column 'va2' at row 1
      		 
      Warning 1264    Out of range value for column 'va1' at row 2
      		 
      Warning 1264    Out of range value for column 'va2' at row 2
      		 
      select  count(*) from t group by a;
      		 
      count(*)
      		 
      1
      		 
      1
      		 
      select  count(*) from t group by a + 1;
      		 
      count(*)
      		 
      2
      		 
      select  count(*) from t use index() group by a + 1;
      		 
      count(*)
      		 
      1
      		 
      1

      Attachments

        1. mdev_38752.result
          11 kB
        2. mdev_38752.result.diff
          13 kB
        3. mdev_38752.test
          6 kB
        4. mdev_38752.test.diff
          3 kB

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-39526 The supertype check in virtual column substitution is too strict

          • Major - Major loss of function.
          • Open
          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-36132 Optimizer support for functional indexes: handle GROUP/ORDER BY

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18153 Assertion `0' or Assertion `btr_validate_index(index, 0)' failed in row_upd_sec_index_entry or error code 126: Index is corrupted upon UPDATE with TIME_ROUND_FRACTIONAL

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-38685 Temp table corrupted error, Assertion `length <= copy->to_length - 2', ASAN errors with GROUP BY and indexed vcol

          • Critical - Crashes, loss of data, severe memory leak.
          • In Review
          split to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-39525 WHERE virtual column index substitution causes wrong results when truncation happens

          • Critical - Crashes, loss of data, severe memory leak.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-39526 The supertype check in virtual column substitution is too strict

          • Major - Major loss of function.
          • Open

          Activity

            People

              ycp Yuchen Pei
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              10 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.