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

Wrong result set from a query aggregation and HAVING over SF call

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.7, 10.8, 10.9, 10.10
    • 10.11
    • Optimizer

    Description

      The query with HAVING from the following result 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;
       
      INSERT INTO t1 SELECT * FROM t1;
      ALTER TABLE t1 ADD KEY(t, u);
      ANALYZE TABLE t1;
      SELECT * FROM series, seq_calls;
      SELECT next_seq_value() r, MIN(u) FROM t1 GROUP BY t HAVING r = 1;
       
      DROP TABLE t1;
      DROP FUNCTION next_seq_value;
      DROP TABLE series, seq_calls;
      

      returns an empty result set.

      5.5, 10.0, 10.1  resturn the correct result:
      

      MariaDB [test]> SELECT next_seq_value() r, MIN(u) FROM t1 GROUP BY t HAVING r = 1;
      ------------+

      r MIN(u)

      ------------+

      1 10
      1 12
      1 16
      1 18

      ------------+

       

      Attachments

        Activity

          People

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