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

Wrong result set from a query with HAVING containing a subquery with SF call.

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
    • 10.3
    • Optimizer

    Description

      The query with HAVING from the following test case

      SET @@sql_mode = '';
       
      CREATE TABLE series (
        val INT(10) UNSIGNED NOT NULL
      );
      INSERT INTO series VALUES(1);
      CREATE TABLE seq_calls (
        c INT
      );
      INSERT INTO seq_calls VALUES(0);
       
      DELIMITER |;
      CREATE FUNCTION next_seq_value() RETURNS INT
      BEGIN
       DECLARE next_val INT;
       SELECT val INTO next_val FROM series;
       UPDATE series SET val=mod(val + 1, 2);
       UPDATE seq_calls SET c=c+1;
       RETURN next_val;
      END;
      |
      DELIMITER ;|
       
      CREATE TABLE t1 (t INT, u INT, KEY(t));
      INSERT INTO t1 VALUES(10, 10), (11, 11), (12, 12), (12, 13),(14, 15), (15, 16),
                           (16, 17), (17, 18);
      ANALYZE TABLE t1;
       
      SELECT * FROM series, seq_calls;
      SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t)
      GROUP BY t HAVING (SELECT 1 FROM t1 WHERE r = 1 LIMIT 1)
      ORDER BY t1.u;
       
      DROP TABLE t1;
      DROP FUNCTION next_seq_value;
      DROP TABLE series, seq_calls;
      

      returns a wrong result set:

      MariaDB [test]> SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t)
          -> GROUP BY t HAVING (SELECT 1 FROM t1 WHERE r = 1 LIMIT 1)
          -> ORDER BY t1.u;
      +------+------+
      | t    | r    |
      +------+------+
      |   10 |    1 |
      |   11 |    1 |
      |   12 |    1 |
      |   14 |    1 |
      |   15 |    1 |
      |   16 |    1 |
      |   17 |    1 |
      +------+------+
      

      The expected result set is:

      MariaDB [test]> SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t)
          -> GROUP BY t HAVING (SELECT 1 FROM t1 WHERE r = 1 LIMIT 1)
          -> ORDER BY t1.u;
      +------+------+
      | t    | r    |
      +------+------+
      |   10 |    1 |
      |   12 |    1 |
      |   15 |    1 |
      |   17 |    1 |
      +------+------+
      

      This bug is a regression as 5.5,10.0,10.1 return the right result set

      Attachments

        Activity

          People

            monty Michael Widenius
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.