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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2, 10.2.9
    • Fix Version/s: 10.2.11
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      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

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            M-A-X M-A-X
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: