Unexpected result - MySQL SELECT based on date comparisons

Question

I am encountering some unexpected behaviour with a MySQL select query. I am running the query:

SELECT `refno`, `subdomain`, `toplevels`, `renew_until`, `expiry_date`, 
(YEAR(`renew_until`) - YEAR(`expiry_date`)) AS `renew_for` FROM `testing_names` 
WHERE `expiry_date` >= DATE(NOW()) AND `renew_for` >= 0

which returns (as expected):

|  refno  |  subdomain  |  toplevels  |  renew_until  |  expiry_date  |  renew_for  |
|-----------------------------------------------------------------------------------|
|  5      |  domain1    |  com        |  2014-02-02   |  2014-02-02   |  0          |
|  45     |  domain2    |  net        |  2014-01-27   |  2013-01-27   |  1          |

However the following query (note the different comparison on renew_for) returns an empty set:

SELECT `refno`, `subdomain`, `toplevels`, `renew_until`, `expiry_date`, 
(YEAR(`renew_until`) - YEAR(`expiry_date`)) AS `renew_for` FROM `testing_names` 
WHERE `expiry_date` >= DATE(NOW()) AND `renew_for` > 0

In this scenario, I was expecting row #45; what is wrong with my query? Am I utilising renew_for in the correct way?

score 2 · Accepted Answer · answered Feb 06 '12 at 12:27

Other RDBMS won't allow this syntax: column aliases should not be available in the WHERE clause.
MySQL does it's own thing though with erratic results:

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

So, change the WHERE condition to this

WHERE `expiry_date` >= DATE(NOW()) AND (YEAR(`renew_until`) - YEAR(`expiry_date`)) > 0

Or use a derived table

SELECT *
FROM
   (
    SELECT 
        `refno`, `subdomain`, `toplevels`, `renew_until`, `expiry_date`, 
         (YEAR(`renew_until`) - YEAR(`expiry_date`)) AS `renew_for`
    FROM `testing_names` 
    ) T
WHERE `expiry_date` >= DATE(NOW()) AND `renew_for` > 0

score 1 · Answer 2 · edited May 23 '17 at 09:59

From taking a further look, it seems that using HAVING will do the job in MySQL, with the simplest syntax; but as far as I understand, it's another case of MySQL doing its own thing ( WHERE vs HAVING ); so if keeping close to the SQL standard is a priority, @gbn's solutions are probably best.

Using HAVING:

SELECT `refno`, `subdomain`, `toplevels`, `renew_until`, `expiry_date`,
(YEAR(`renew_until`) - YEAR(`expiry_date`)) AS `renew_for` FROM `testing_names`
WHERE `expiry_date` >= DATE(NOW()) HAVING `renew_for` > 0

Unexpected result - MySQL SELECT based on date comparisons

2 Answers2