MySQL Bugs: #52435: GROUP_CONCAT fails to return row when value NULL

Bug #52435	GROUP_CONCAT fails to return row when value NULL
Submitted:	29 Mar 2010 11:46	Modified:	30 Mar 2010 10:03
Reporter:	Hameer Abbasi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S1 (Critical)
Version:	5.1.44-community	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	group_concat, missing rows

Description:
MySQL fails to return rows in which GROUP_CONCAT returns NULL values (in any column), or even in which, on the processing of the column, GROUP_CONCAT returns NULL.

How to repeat:
This is the code that caused the bug for me, but I trimmed it out a bit:
Create three tables:

CREATE  TABLE IF NOT EXISTS `BookStore`.`Books` (
  `bookID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `title` CHAR(150) NOT NULL ,
  `subtitle` CHAR(250) NULL DEFAULT NULL ,
  `publisherID` BIGINT UNSIGNED NOT NULL ,
  `categoryID` SMALLINT UNSIGNED NOT NULL ,
  `edition` CHAR(50) NULL DEFAULT NULL ,
  `volume` SMALLINT UNSIGNED NULL DEFAULT NULL ,
  `pyear` DECIMAL(4,0) UNSIGNED NOT NULL ,
  `isbn10` CHAR(13) NULL DEFAULT NULL ,
  `isbn13` CHAR(17) NULL DEFAULT NULL ,
  `price` DECIMAL(12,2) UNSIGNED NOT NULL ,
  PRIMARY KEY (`bookID`) ,
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `BookStore`.`Authors` (
  `authorID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` CHAR(150) NOT NULL ,
  `email` CHAR(255) NULL DEFAULT NULL ,
  `address` CHAR(250) NULL DEFAULT NULL ,
  `bio` TEXT NULL DEFAULT NULL ,
  PRIMARY KEY (`authorID`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `BookStore`.`BooksAuthors` (
  `bookID` BIGINT UNSIGNED NOT NULL ,
  `authorID` BIGINT UNSIGNED NOT NULL ,
  PRIMARY KEY (`bookID`, `authorID`) ,
  INDEX `fk_Books_bookID` (`bookID` ASC) ,
  INDEX `fk_Authors_authorID` (`authorID` ASC) ,
  CONSTRAINT `fk_Books_bookID`
    FOREIGN KEY (`bookID` )
    REFERENCES `BookStore`.`Books` (`bookID` )
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_Authors_authorID`
    FOREIGN KEY (`authorID` )
    REFERENCES `BookStore`.`Authors` (`authorID` )
    ON DELETE RESTRICT
    ON UPDATE RESTRICT)
ENGINE = InnoDB;

Then, add a book, but no author related to the book in the BooksAuthors table. Then, execute the following SELECT statement:

SELECT Books.bookID AS bookID,
       Books.title AS title,
       Books.subtitle AS subtitle,
       GROUP_CONCAT(Authors.name SEPARATOR ', ') AS authors,
       Books.edition AS edition,
       Books.volume AS volume,
       Books.pyear AS pyear,
       Books.isbn10 AS isbn10,
       Books.isbn13 AS isbn13,
       Books.price AS price
FROM Books, Authors, BooksAuthors, Publishers, Categories
WHERE Books.bookID = BooksAuthors.bookID
AND Authors.authorID = BooksAuthors.authorID;

You'll not see the book's data returned. I even created a special function:

DROP FUNCTION IF EXISTS `BookStore`.`GROUP_CONCAT_SPECIAL`;
CREATE FUNCTION `BookStore`.`GROUP_CONCAT_SPECIAL`(oBookID BIGINT UNSIGNED)
RETURNS TEXT
BEGIN
    SELECT GROUP_CONCAT(Authors.name SEPARATOR ', ')
    INTO @authorExists
    FROM Books, BooksAuthors, Authors
    WHERE books.bookID = booksauthors.bookID
    AND authors.authorID = booksauthors.authorID
    AND books.bookID = oBookID;
    
    IF NOT(@authorExists IS NULL OR TRIM(@authorExists) = '')  THEN
      RETURN @authorExists;
    ELSE
      RETURN 'None';
    END IF;      
END;

But it seems, that even when this function returns 'None', the row isn't displayed, but the function seems to work fine, returning 'None', when a book with no author is returned.

Suggested fix:
MySQL should return the missing rows.

Oh, and yes, the rows ARE returned if there is an author assigned to the book.

Wasn't it more appropriate to use the CONCAT_WS() function here?. GROUP_CONCAT() with no GROUP BY does not make sense to me. GROUP_CONCAT() is an aggregate function, refer 
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
and 
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

Peter
(not a MySQL person).

I'm sorry, i have another view that causes the bug, thanks peter for the tip. Here's the code:

SELECT Books.bookID AS bookID,
       Books.title AS title,
       Books.subtitle AS subtitle,
       GROUP_CONCAT_SPECIAL(Books.bookID) AS authors,
       Publishers.name AS publisher,
       Categories.name AS category,
       Books.edition AS edition,
       Books.volume AS volume,
       Books.pyear AS pyear,
       Books.isbn10 AS isbn10,
       Books.isbn13 AS isbn13,
       Books.price AS price
FROM Books, Authors, BooksAuthors, Publishers, Categories
WHERE Books.bookID = BooksAuthors.bookID
AND Authors.authorID = BooksAuthors.authorID
AND Books.publisherID = Publishers.publisherID
AND Books.categoryID = Categories.CategoryID
GROUP BY Books.bookID;

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You are using INNER JOIN which requires corresponding row is in each table. Read at http://dev.mysql.com/doc/refman/5.1/en/join.html about JOIN syntax.

The can someone help me with creating an OUTER JOIN with more than two tables? An example would suffice.