SQL - empty result set for checking if date is past the current date-CodePudding

I have a query in MariaDB 10.3 database where there is a field called "expiration_date" that stores a unix timestamp, but if there is no data in the field the default is set to "0".

I'm trying to use a WHERE clause to check the current date against the expiration_date to filter out any records that are past the expiration_date. Below is what I have.

SELECT entry_id, title, (CASE WHEN expiration_date = "0" THEN CURDATE()   INTERVAL 1 DAY ELSE FROM_UNIXTIME(expiration_date, "%Y-%m-%d") END) AS expiration_date
    FROM channel_titles
    WHERE CURDATE() < expiration_date

This returns and empty result set... what am I missing?

CodePudding user response：

You're trying to use an alias of expiration_date from your CASE statement in your WHERE clause.

Two problems with this:

You cannot use column aliases in the WHERE clause. Refer to this post here.

WHERE happens before SELECT in the execution chain.

Your alias matches an actual column name in your table, so your WHERE clause is not throwing an error regarding your alias, its comparing the current date to the expiration_date column in the table, thus, throwing off your expected result.

Solutions:

If you want to use the alias in your WHERE clause, there are a few options for you to force SQL to handle the SELECT before the WHERE clause.

You can use a subquery (or subselect) to force logical order of operation by using parentheses:

  SELECT 
  a.entry_id, 
  a.title,
  a.expiration_date
  FROM
     (SELECT 
      entry_id, 
      title, 
      (CASE WHEN expiration_date = 0 THEN CURDATE()   INTERVAL 1 DAY ELSE FROM_UNIXTIME(expiration_date, '%Y-%m-%d') END) AS expiration_date
      FROM channel_titles
     ) a
  WHERE CURDATE() < a.expiration_date

You can declare your alias in a Common Table Expression (CTE), then SELECT it FROM the CTE:

WITH cte AS (SELECT 
             entry_id, 
             title, 
            (CASE WHEN expiration_date = 0 THEN CURDATE()   INTERVAL 1 DAY ELSE FROM_UNIXTIME(expiration_date, '%Y-%m-%d') END) AS expiration_date
             FROM channel_titles)
  SELECT 
      entry_id, 
      title, 
      expiration_date
      FROM cte
  WHERE CURDATE() < expiration_date

You can disregard using your alias entirely in your WHERE clause and plug in the logic from your SELECT statement directly into your WHERE clause. However, this may appear redundant from a readability perspective; also, extra processing should be considered when using this approach as well, but if you have a small data set this method will work just fine:

SELECT 
  entry_id, 
  title, 
  (CASE WHEN expiration_date = 0 THEN CURDATE()   INTERVAL 1 DAY ELSE FROM_UNIXTIME(expiration_date, '%Y-%m-%d') END) AS expiration_date
  FROM channel_titles
WHERE CURDATE() < (CASE WHEN expiration_date = 0 THEN CURDATE()   INTERVAL 1 DAY ELSE FROM_UNIXTIME(expiration_date, '%Y-%m-%d') END)

Input:

entry_id	title	expiration_date	expiration_date_date
1	test1	1695513600	2023-09-24
2	test2	0	2022-09-15
3	test3	1662768000	2022-09-10

Output:

entry_id	title	expiration_date
1	test1	2023-09-24
2	test2	2022-09-15

db<>fiddle here.

CodePudding user response：

There's a very simple solution to this and it only requires you to change two things from your original query:

The first part is your column (CASE expression) alias - you should define your alias with something not similar to any of the column names present in the table. From your query, you have a column expiration_datein your table and you also set an alias for your CASE expression with expiration_date as well and since you're using WHERE, the query will definitely do the lookup based on your table expiration_date column instead of your CASE expression. Rename that alias to something like exp_date... but doing WHERE exp_date ... will return you an error. Refer to the second point below.
The second part is your WHERE - since you're doing lookup from a CASE expression (or perhaps custom generated value/column) with newly assigned alias of exp_date, you can't use it in WHERE.. well unless you make the query as a subquery then do the WHERE outside.. but you don't need to. You only need to change WHERE to HAVING and you should be able to use the exp_date and get your result.

So, with those two changes, your query should be something like this:

SELECT entry_id, title, 
     (CASE WHEN expiration_date = "0" THEN CURDATE()   INTERVAL 1 DAY ELSE 
         FROM_UNIXTIME(expiration_date, "%Y-%m-%d") END) AS exp_date
    FROM channel_titles
    HAVING CURDATE() < exp_date;

demo fiddle