Warm tip: This article is reproduced from serverfault.com, please click

Why is this CASE GREATEST returning NULL?

发布于 2020-12-02 12:29:26

I have a select query that looks like this, so that I know which date is the newest (dtStart) and which table the newest date comes from (TableOrigin).

WITH ranked_entity AS (
 SELECT
    table5.id,
    GREATEST(
        COALESCE(table1.dtEvaluationStart, '0000-00-00 00:00:00'), 
        COALESCE(table2.dtStart, '0000-00-00 00:00:00'), 
        COALESCE(table3.dtEvaluationStart, '0000-00-00 00:00:00'),
        COALESCE(table4.dtStart, '0000-00-00 00:00:00')) as dtStart,
    CASE GREATEST(
        COALESCE(table1.dtEvaluationStart, '0000-00-00 00:00:00'), 
        COALESCE(table2.dtStart, '0000-00-00 00:00:00'), 
        COALESCE(table3.dtEvaluationStart, '0000-00-00 00:00:00'),
        COALESCE(table4.dtStart, '0000-00-00 00:00:00')
        )
    WHEN table1.dtEvaluationStart THEN 'table1'
    WHEN table2.dtStart THEN 'table2'
    WHEN table3.dtEvaluationStart THEN 'table3'
    WHEN table4.dtStart THEN 'table4'
    END AS TableOrigin,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY dtStart DESC) AS rn
 FROM table5 
    LEFT JOIN table1 ON table5.id = table1.fid 
    LEFT JOIN table2 ON table5.id = table2.fid
    LEFT JOIN table3 ON table5.id = table3.fid 
    LEFT JOIN table4 ON table5.id = table4.fid
)
    SELECT * FROM ranked_entity WHERE rn = 1;

But sometimes the TableOrigin is NULL even though dtStart is set. Why is that so?

For example in one row the column dtStart has the value '2020-05-14 14:34:18' that is taken from table3, because it is the newest date out of the columns of the four tables. That means that the column TableOrigin should have the value 'table3' for this row. But instead TableOrigin is NULL. This doesn't happen with all the rows, in some rows the TableOrigin value is correct.

Sample Data:

table5
+-----+
| id  |
+-----+
| 198 |
| 197 |
+-----+

table1
+-----+---------------------+
| fid |  dtEvaluationStart  |
+-----+---------------------+
| 198 | 2018-01-11 13:59:17 |
| 197 | 2020-01-21 09:29:35 |
+-----+---------------------+

table2
+-----+---------------------+
| fid |       dtStart       |
+-----+---------------------+
| 198 | 2018-02-01 12:57:50 |
| 197 | 2020-11-18 10:14:31 |
+-----+---------------------+

table3
+-----+---------------------+
| fid |       dtStart       |
+-----+---------------------+
| 197 | 2018-01-10 14:58:19 |
+-----+---------------------+

table4
+-----+---------------------+
| fid |       dtStart       |
+-----+---------------------+
| 198 | 2020-03-01 09:40:09 |
| 197 | 2020-03-04 08:10:59 |
+-----+---------------------+

output
+-----+---------------------+-------------+
| id  |       dtStart       | TableOrigin |
+-----+---------------------+-------------+
| 198 | 2020-03-01 09:40:09 | NULL        |
| 197 | 2020-11-18 10:14:31 | table2      |
+-----+---------------------+-------------+

Questioner
Cicciopasticcio
Viewed
0
forpas 2020-12-04 18:51:16

Although the code works fine in MySql, it does not work in MariaDB.
As a workaround you can repeat COALESCE() in your CASE expression:

WITH ranked_entity AS (
 SELECT
    table5.id,
    GREATEST(
        COALESCE(table1.dtEvaluationStart, '0000-00-00 00:00:00'), 
        COALESCE(table2.dtStart, '0000-00-00 00:00:00'), 
        COALESCE(table3.dtEvaluationStart, '0000-00-00 00:00:00'),
        COALESCE(table4.dtStart, '0000-00-00 00:00:00')) as dtStart,
    CASE GREATEST(
        COALESCE(table1.dtEvaluationStart, '0000-00-00 00:00:00'), 
        COALESCE(table2.dtStart, '0000-00-00 00:00:00'), 
        COALESCE(table3.dtEvaluationStart, '0000-00-00 00:00:00'),
        COALESCE(table4.dtStart, '0000-00-00 00:00:00')
        )
    WHEN COALESCE(table1.dtEvaluationStart, '0000-00-00 00:00:00') THEN 'table1'
    WHEN COALESCE(table2.dtStart, '0000-00-00 00:00:00') THEN 'table2'
    WHEN COALESCE(table3.dtEvaluationStart, '0000-00-00 00:00:00') THEN 'table3'
    WHEN COALESCE(table4.dtStart, '0000-00-00 00:00:00') THEN 'table4'
    END AS TableOrigin,
    ROW_NUMBER() OVER (PARTITION BY table5.id ORDER BY table4.dtStart DESC) AS rn
 FROM table5 
    LEFT JOIN table1 ON table5.id = table1.fid 
    LEFT JOIN table2 ON table5.id = table2.fid
    LEFT JOIN table3 ON table5.id = table3.fid 
    LEFT JOIN table4 ON table5.id = table4.fid
)
SELECT * FROM ranked_entity WHERE rn = 1;

See the demo.
Results:

>  id | dtStart             | TableOrigin | rn
> --: | :------------------ | :---------- | -:
> 197 | 2020-11-18 10:14:31 | table2      |  1
> 198 | 2020-03-01 09:40:09 | table4      |  1