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

Wrong result upon JOIN with INDEX with no rows in joined table + GROUP BY + GROUP_CONCAT + HAVING + ORDER BY [by field from HAVING] + 1 row expected

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.9, 10.2(EOL)
    • 10.2.11
    • Optimizer
    • None
    • CentOS7 x64

    Description

      Test case:

      CREATE TABLE _authors (
        id MEDIUMINT(8) UNSIGNED AUTO_INCREMENT,
        name VARCHAR(100),
        some_field MEDIUMINT(8) UNSIGNED,
        PRIMARY KEY (id),
        index(some_field)
      );
       
      CREATE TABLE _books (
        id MEDIUMINT(8) UNSIGNED AUTO_INCREMENT,
        title VARCHAR(100),
        PRIMARY KEY (id)
      );
      CREATE TABLE _books2authors (
        author_id MEDIUMINT(8) DEFAULT 0,
        book_id MEDIUMINT(8) DEFAULT 0,
        index(author_id),
        index(book_id)
      );
       
      INSERT INTO _authors (name, some_field) VALUES 
      ('author1', 1),('author2', 2),('author3', 3);
       
      INSERT INTO _books (title) VALUES 
      ('book1'),('book2'),('book3');
       
      INSERT INTO _books2authors (author_id, book_id) VALUES 
      (2,1),(3,2),(3,3);
       
      SELECT 
      	A.id, GROUP_CONCAT(B.title ORDER BY B.title DESC SEPARATOR ',') AS books, 0.1 + some_field AS having_field
      FROM 
      	_authors A
      LEFT JOIN
      	_books2authors B2A 
      	FORCE INDEX(author_id) 
      	ON B2A.author_id = A.id
      LEFT JOIN 
      	_books B ON B.id = B2A.book_id
      WHERE 
      	1
      GROUP BY 
      	A.id
      HAVING 
      	having_field < 2
      ORDER BY
      	having_field ASC
      	;
      	
      DROP TABLE _authors, _books, _books2authors;
      

      Expected:
      id books having_field
      1 null 1,1

      Received empty result

      Attachments

        Activity

          M-A-X,
          Thanks for the report and test case.

          Reproducible on all 10.2 versions, with MyISAM and InnoDB. Not reproducible on 5.5-10.1 or MySQL 5.7.

          10.2 ANALYZE JSON

          ANALYZE
          {
            "query_block": {
              "select_id": 1,
              "r_loops": 1,
              "r_total_time_ms": 0.4398,
              "const_condition": "1",
              "having_condition": "having_field < 2",
              "filesort": {
                "sort_key": "0.1 + A.some_field",
                "r_loops": 1,
                "r_total_time_ms": 0.0373,
                "r_used_priority_queue": false,
                "r_output_rows": 0,
                "r_buffer_size": "210",
                "temporary_table": {
                  "read_sorted_file": {
                    "r_rows": 3,
                    "filesort": {
                      "sort_key": "A.`id`",
                      "r_loops": 1,
                      "r_total_time_ms": 0.035,
                      "r_used_priority_queue": false,
                      "r_output_rows": 3,
                      "r_buffer_size": "234",
                      "table": {
                        "table_name": "A",
                        "access_type": "ALL",
                        "r_loops": 1,
                        "rows": 3,
                        "r_rows": 3,
                        "r_total_time_ms": 0.0077,
                        "filtered": 100,
                        "r_filtered": 1
                      }
                    }
                  },
                  "table": {
                    "table_name": "B2A",
                    "access_type": "ref",
                    "possible_keys": ["author_id"],
                    "key": "author_id",
                    "key_length": "4",
                    "used_key_parts": ["author_id"],
                    "ref": ["test.A.id"],
                    "r_loops": 3,
                    "rows": 2,
                    "r_rows": 1,
                    "r_total_time_ms": 0.0212,
                    "filtered": 100,
                    "r_filtered": 100,
                    "attached_condition": "trigcond(B2A.author_id = A.`id`)"
                  },
                  "table": {
                    "table_name": "B",
                    "access_type": "eq_ref",
                    "possible_keys": ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "3",
                    "used_key_parts": ["id"],
                    "ref": ["test.B2A.book_id"],
                    "r_loops": 4,
                    "rows": 1,
                    "r_rows": 0.75,
                    "r_total_time_ms": 0.0119,
                    "filtered": 100,
                    "r_filtered": 100,
                    "attached_condition": "trigcond(B.`id` = B2A.book_id and trigcond(B2A.book_id is not null))"
                  }
                }
              }
            }
          }
          

          10.1 ANALYZE JSON

          ANALYZE
          {
            "query_block": {
              "select_id": 1,
              "r_loops": 1,
              "r_total_time_ms": 0.6897,
              "const_condition": "1",
              "having_condition": "(having_field < 2)",
              "filesort": {
                "r_loops": 1,
                "r_total_time_ms": 0.0493,
                "r_used_priority_queue": false,
                "r_output_rows": 1,
                "r_buffer_size": "273",
                "filesort": {
                  "r_loops": 1,
                  "r_total_time_ms": 0.053,
                  "r_used_priority_queue": false,
                  "r_output_rows": 3,
                  "r_buffer_size": "234",
                  "temporary_table": {
                    "table": {
                      "table_name": "A",
                      "access_type": "ALL",
                      "r_loops": 1,
                      "rows": 3,
                      "r_rows": 3,
                      "r_total_time_ms": 0.0073,
                      "filtered": 100,
                      "r_filtered": 100
                    },
                    "table": {
                      "table_name": "B2A",
                      "access_type": "ref",
                      "possible_keys": ["author_id"],
                      "key": "author_id",
                      "key_length": "4",
                      "used_key_parts": ["author_id"],
                      "ref": ["test.A.id"],
                      "r_loops": 3,
                      "rows": 2,
                      "r_rows": 1,
                      "r_total_time_ms": 0.0234,
                      "filtered": 100,
                      "r_filtered": 100,
                      "attached_condition": "trigcond((B2A.author_id = A.`id`))"
                    },
                    "table": {
                      "table_name": "B",
                      "access_type": "eq_ref",
                      "possible_keys": ["PRIMARY"],
                      "key": "PRIMARY",
                      "key_length": "3",
                      "used_key_parts": ["id"],
                      "ref": ["test.B2A.book_id"],
                      "r_loops": 4,
                      "rows": 1,
                      "r_rows": 0.75,
                      "r_total_time_ms": 0.0137,
                      "filtered": 100,
                      "r_filtered": 100,
                      "attached_condition": "trigcond(((B.`id` = B2A.book_id) and trigcond((B2A.book_id is not null))))"
                    }
                  }
                }
              }
            }
          }
          

          elenst Elena Stepanova added a comment - M-A-X , Thanks for the report and test case. Reproducible on all 10.2 versions, with MyISAM and InnoDB. Not reproducible on 5.5-10.1 or MySQL 5.7. 10.2 ANALYZE JSON ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.4398, "const_condition": "1", "having_condition": "having_field < 2", "filesort": { "sort_key": "0.1 + A.some_field", "r_loops": 1, "r_total_time_ms": 0.0373, "r_used_priority_queue": false, "r_output_rows": 0, "r_buffer_size": "210", "temporary_table": { "read_sorted_file": { "r_rows": 3, "filesort": { "sort_key": "A.`id`", "r_loops": 1, "r_total_time_ms": 0.035, "r_used_priority_queue": false, "r_output_rows": 3, "r_buffer_size": "234", "table": { "table_name": "A", "access_type": "ALL", "r_loops": 1, "rows": 3, "r_rows": 3, "r_total_time_ms": 0.0077, "filtered": 100, "r_filtered": 1 } } }, "table": { "table_name": "B2A", "access_type": "ref", "possible_keys": ["author_id"], "key": "author_id", "key_length": "4", "used_key_parts": ["author_id"], "ref": ["test.A.id"], "r_loops": 3, "rows": 2, "r_rows": 1, "r_total_time_ms": 0.0212, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(B2A.author_id = A.`id`)" }, "table": { "table_name": "B", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "3", "used_key_parts": ["id"], "ref": ["test.B2A.book_id"], "r_loops": 4, "rows": 1, "r_rows": 0.75, "r_total_time_ms": 0.0119, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(B.`id` = B2A.book_id and trigcond(B2A.book_id is not null))" } } } } } 10.1 ANALYZE JSON ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.6897, "const_condition": "1", "having_condition": "(having_field < 2)", "filesort": { "r_loops": 1, "r_total_time_ms": 0.0493, "r_used_priority_queue": false, "r_output_rows": 1, "r_buffer_size": "273", "filesort": { "r_loops": 1, "r_total_time_ms": 0.053, "r_used_priority_queue": false, "r_output_rows": 3, "r_buffer_size": "234", "temporary_table": { "table": { "table_name": "A", "access_type": "ALL", "r_loops": 1, "rows": 3, "r_rows": 3, "r_total_time_ms": 0.0073, "filtered": 100, "r_filtered": 100 }, "table": { "table_name": "B2A", "access_type": "ref", "possible_keys": ["author_id"], "key": "author_id", "key_length": "4", "used_key_parts": ["author_id"], "ref": ["test.A.id"], "r_loops": 3, "rows": 2, "r_rows": 1, "r_total_time_ms": 0.0234, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond((B2A.author_id = A.`id`))" }, "table": { "table_name": "B", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "3", "used_key_parts": ["id"], "ref": ["test.B2A.book_id"], "r_loops": 4, "rows": 1, "r_rows": 0.75, "r_total_time_ms": 0.0137, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(((B.`id` = B2A.book_id) and trigcond((B2A.book_id is not null))))" } } } } } }
          igor Igor Babaev added a comment -

          A fix for this bug was pushed into 10.2

          igor Igor Babaev added a comment - A fix for this bug was pushed into 10.2

          People

            igor Igor Babaev
            M-A-X M-A-X
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.