title: SELECT query returns no result without ORDER BY clause
body: <p>I have this query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This returns 0 results.</p>
  
  <p>When I add</p>
  
  <pre><code>ORDER BY Locations.id
  </code></pre>
  
  <p>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.</p>
  
  <p>My question is: <strong>Why do I not get a result with the original query?</strong></p>
  
  <p>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.</p>
  
  <p>Usually, you would suspect that the field <code>id</code> in <code>g_locations</code> 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 <code>id</code> field is correctly set up as a primary key field and uses auto_increment.</p>
  
  <p>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:</p>
  
  <p><a href="https://i.stack.imgur.com/7Mkx5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7Mkx5.png" alt="original query"></a></p>
  
  <p>The modified, working query looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/6Pk6w.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6Pk6w.png" alt="http://i.stack.imgur.com/6Pk6w.png"></a></p>
  
  <p>Below is the table definitions:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The <code>ref_id</code> field features a long comment that I omitted in this definition.</p>