COALESCE in postgresql conditional displaying seemingly undocumented behavior?-CodePudding

I have looked at the COALESCE documentation and it mentions the typical case of using COALESCE to make default/situational parameters, e.g.

COALESCE(discount, 5)

which evaluates to 5 if discount is not defined as something else.

However, I have seen it used where COALESCE actually evaluated all the arguments, despite the documentation explicitly saying it stops evaluating arguments after the first non-null argument.

Here is an example similar to what I encountered, say you have a table like this:

id | wind | rain | snow
1  | null |   2  |  3
2  |   5  | null |  6
3  | null |   7  |  2

Then you run

SELECT *
FROM weather_table
WHERE
    COALESCE(wind, rain, snow) >= 5

You would expect this to only select rows with wind >= 5, right? NO! It selects all rows with either wind, rain or snow more than 5. Which in this case is 2 rows, specifically these two:

2  |   5  | null |  6
3  | null |   7  |  2

Honestly, pretty cool functionality, but it really irks me that I couldn't find any example of this online or in the documentation.

Can anyone tell me what's going on? Am I missing something?

CodePudding user response：

You would expect this to only select rows with wind >= 5, right?

No, I expect it to select rows with what the Coalesce function returns.

The Coalesce function delivers the value of the first non-null parameter. You had Coalesce(wind,rain,snow). The first row had (null,2,3), so coalesce returned 2. The second row had (5,null,6) so returned 5. The third row had (null,7,2) so returned 7.

The last two rows meet the condition >=5, so 2 rows are retrieved.

Notice that the value for snow was never returned in your example, because either wind or rain always had a value.

CodePudding user response：

After writing out the question so clear, I realized what was going on myself. But I want to answer it here in case anyone else is confused.

Turns out the reason is the COALESCE function is run once for each row, which I suppose I could have known. Then it all makes sense.

It checks for each row, do I have non-null wind, if it is >= 5 I add this row to the result, if not I check if rain is non-null, and so on.

Notably though, if my table was had been like this:

id | wind | rain | snow
1  |   0  |   2  |  3
2  |   5  |   0  |  6
3  |   0  |   7  |  2

The command would have worked like I thought, and the COALESCE function completely useless, would have picked only that one row

2  |   5  |   0  |  6

equal to SELECT * FROM weather_table WHERE wind >= 5.

It only works if there are columns which are null (0 <> null).