Search code examples
mysqlgroup-bysql-order-byfilesort

SELECT query returns no result without ORDER BY clause

I have this query:

SELECT  `Stocks`.`id` AS  `Stocks.id` ,  `Locations`.`id` AS  `Locations.id`  
FROM  `rowiusnew`.`c_stocks` AS  `Stocks` 
LEFT JOIN  `rowiusnew`.`g_locations` AS  `Locations` ON ( `Locations`.`ref_id` =  `Stocks`.`id` AND `Locations`.`ref_type` =  'stock' ) 
GROUP BY  `Stocks`.`id` 
HAVING `Locations.id` IS NOT NULL

This returns 0 results.

When I add

ORDER BY Locations.id

to the exactly same query, I correctly get 3 results. Noteworthy: When I discard the GROUP BY clause, I get the same 3 results. The grouping is necessary for the complete query with additional joins; this is the simplified one to demonstrate the problem.

My question is: Why do I not get a result with the original query?

Note that there are two conditions in the JOIN ON clause. Changing or removing the braces or changing the order of these conditions does not change the outcome.

Usually, you would suspect that the field id in g_locations is sometimes NULL, thus the ORDER BY clause makes the correct referenced result be displayed "at the top" of the group dataset. This is not the case; the id field is correctly set up as a primary key field and uses auto_increment.

The EXPLAIN statement shows that filesort is used instead of the index in those cases when I actually get a result. The original query looks like this:

original query

The modified, working query looks like this:

https://i.sstatic.net/6Pk6w.png

Below is the table definitions:

CREATE TABLE IF NOT EXISTS `c_stocks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_stock_type` int(10) unsigned DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`locality` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `StockType_idx` (`id_stock_type`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `g_locations` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`ref_type` enum('stock','object','branch') DEFAULT NULL,
`ref_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UniqueLocation` (`ref_type`,`ref_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

The ref_id field features a long comment that I omitted in this definition.

Solution

  • After being unable to reproduce the error on SQLFiddle.com and also on my second computer, I realized that there must be a bug involved.

    Indeed, my used version 5.6.12 suffers from this bug:

    Some LEFT JOIN queries with GROUP BY could return incorrect results. (Bug #68897, Bug #16620047)

    See the change log of MySQL 5.6.13: http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-13.html

    An upgrade to 5.6.17 solved my problem. I am not getting the results I expect, independent of ORDER clauses and aggregate functions.