CODEWITHSUNDEEP
mysqlsqldatabasecoalesce
James Haskell
James Haskell

Reputation: 113

SQL COALESCE function skipping non-null values

I am trying to fill a report page with data of various dates. Here is my simplified query:

SELECT COALESCE(topics.created_at, tm.created_at, 'Unknown') AS created_at
FROM topics
INNER JOIN topics_media AS tm ON tm.fk_topic = topics.id
ORDER BY created_at DESC
LIMIT 1

For certain rows, this statement is returning 'Unknown' for data that I know exists.

After running the same statement but with IFNULL:

SELECT IFNULL(topics.created_at, tm.created_at) AS created_at 
FROM topics
INNER JOIN topics_media AS tm ON tm.fk_topic = topics.id
ORDER BY created_at DESC
LIMIT 1

A date is returned here from tm.created_at (i.e. '2022-01-27 00:00:00'), whereas with the previous statement using COALESCE, 'Unknown' is returned as if tm.created_at was NULL.

I would like to use COALESCE for other rows that truly don't have the data but not when something should be returned so why does it behave like this?

Upvotes: 0

Views: 792

Answers (1)

Akina
Akina

Reputation: 42622

Your ORDER BY uses output column as a criteria.

One of the alternative in this column's expression COALESCE(topics.created_at, tm.created_at, 'Unknown') has string type, hence the column is string and it is sorting as string.

Non-NULL values (which are DATETIME) starts from a digit. Alternative value starts from a letter. Ordering type is DESC. Hence if alternative value is present in at least one row then this row will be the most first in the output.

If you want to ensure then remove LIMIT clause and look at the whole rowset.

Upvotes: 2

Related Questions