mysql - Coalesce 函数如何处理数据类型

Question

我是 REDSHIFT 中 COALESCE 功能的新手。我在 mysql 和 Redshift 中运行了以下四个查询。

第一个和第二个查询在 mysql 和 redshift 中均按预期执行。但对于第三个和第四个查询，我在 mysql 和 Redshift 中得到两个不同的结果。这是如何表现的？

1. 选择 COALESCE(null,null,1) -> 1

2. 选择 COALESCE(null,null,'John') -> 1

3. 选择 COALESCE(null,null,1,'John') -> (Redshift : 错误 , mysql:1 )

4. 选择 COALESCE(null,null,'John',1) -> (Redshift:错误, mysql:John )

此外，这个查询应该在 mysql 中给出错误，但它已经成功了感谢任何帮助

Answer 1

Amazon Redshift Database Developer Guide claim :

An NVL expression is identical to a COALESCE expression. NVL and COALESCE are synonyms.

Syntax

NVL | COALESCE ( expression, expression, ... ) 

An NVL or COALESCE expression returns the value of the first expression in the list that is not null. If all expressions are null, the result is null. When a non-null value is found, the remaining expressions in the list are not evaluated.

This type of expression is useful when you want to return a backup value for something when the preferred value is missing or null. For example, a query might return one of three phone numbers (cell, home, or work, in that order), whichever is found first in the table (not null).

如果您收到错误，这可能意味着返回值的数据类型与记录集字段或任何其他必须接受返回值的结构的数据类型不匹配。

PS。会显示错误消息吗？