title: MySQL not respecting 'HAVING' where aliases are used?
body: <p>I've got the following table structure:</p>
  
  <pre><code>CREATE TABLE reservations (
      id int auto_increment primary key,
      minDate date,
      maxDate date
  );
  
  CREATE TABLE stays (
      id int auto_increment primary key,
      reservation_id int,
      theDate date
  );
  
  INSERT INTO reservations VALUES (null, CURDATE(), CURDATE());
  INSERT INTO stays VALUES (null, 1, CURDATE());
  </code></pre>
  
  <p>It's for a booking system that records reservations (a general container) and stays (someone for each night).</p>
  
  <p>I try to run the following query to extract all reservations that have a different number of days in the database (e.g. the reservation says there should be 2 nights, but there's only 1 in the database, etc)</p>
  
  <pre><code>SELECT
      reservations.id   AS 'Reservation ID',
      reservations.minDate,
      reservations.maxDate,
      DATEDIFF(reservations.maxDate, reservations.minDate) + 1  AS 'numNights',
      COUNT(DISTINCT stays.id)  AS 'numStays'
  FROM
      reservations
      LEFT JOIN stays ON reservations.id = stays.reservation_id
  GROUP BY
      reservations.id
  HAVING
      `numNights` != `numStays`
  ORDER BY
      reservations.minDate
  </code></pre>
  
  <p>This works perfectly on my Windows version of MySQL (xampp), and production CentOS server, but is broken on a testing machine running version 5.6.19-0ubuntu0.14.04.1. On the broken machine, it's pulling back all rows, even though the numNights and numStays columns match.</p>
  
  <p>If I replace the aliases in the HAVING clause with the expressions used in the SELECT part, then it works fine, but I can't understand why it doesn't like the aliases in the HAVING clause (on this version)?</p>
  
  <p>Btw, it's definitely not a no-quote/quote/double-quote/backtick issue, I've tried all combinations. I might have thought it was a charset encoding issue, but DATEDIFF() and COUNT() should be returning the same type of integers back, right? And that wouldn't explain why expressions work in the HAVING part.</p>
  
  <p>I have an <a href="http://www.sqlfiddle.com/#!2/854a3/2" rel="nofollow" title="SQL Fiddle">SQL Fiddle</a> set up for experimenting as well... it works fine on that too. So now I'm at a loss</p>