CODEWITHSUNDEEP
mysqlgroup-bysql-order-byfilesort
Tom Arrow
Tom Arrow

Reputation: 43

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.

Upvotes: 2

Views: 363

Answers (2)

Strayt Jakket
Strayt Jakket

Reputation: 1

Remove

having locations.id is not null

instead use 

where locations.id is not null

locations.id is not null is not a problem for the grouping - you don't want them included at all.

Also, you need to do something with the locations.id since it isn't in the group by clause. Do you want "max" locations.id?

so your query now becomes:

SELECT  `Stocks`.`id` AS  `Stocks.id` ,  max(`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' ) 
WHERE `Locations.id` IS NOT NULL
GROUP BY  `Stocks`.`id`

Make those changes and it should work better for you.

FYI: I think that by putting in the order by clause, you are allowing the engine to guess what you want for the locations.id, otherwise it has no clue. In something other than MYSQL, it wouldn't run at all.

Upvotes: 0

Tom Arrow
Tom Arrow

Reputation: 43

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.

Upvotes: 1

Related Questions