A somewhat unexpected behavior has been observed while investigating different aspects of the data type coercion. By coercion I mean an implicit casting to the "common" data type that happens when a few operands have different data types but the expression requires some predefined data type to be chosen for the resulting value. In Firebird, common data type determination is used in two cases: (a) describing the select list expressions of the union and (b) describing the resulting data type of the CASE/COALESCE functions.
In old versions, different data types couldn't be used as arguments in these cases, throwing the famous error "data type unknown", see this simple test case:
In old versions, different data types couldn't be used as arguments in these cases, throwing the famous error "data type unknown", see this simple test case:
Starting with Firebird 2.0, the common data type determination logic has been added, so that heterogeneous expressions are now allowed and the aforementioned test case works as expected. The implementation follows the SQL specification, subclause 9.3 - "Result of data type combinations". In particular, case [3a] declares:select 1 from rdb$databaseunionselect '1' from rdb$database
If any of the data types in DTS is character string, then:This means that in the above heterogeneous example, the resulting value will be a character string and the non-conforming expressions will be implicitly casted to become strings. And it looks sensible, as otherwise (if a numeric would be chosen as the common data type) the following example would cause the data type conversion error:
i) All data types in DTS shall be character string, and all of them shall have the same character
repertoire. The character set of the result is the character set of the data type in DTS that has the character encoding form with the highest precedence.
However, there's a hidden issue which remained unnoticed for a few recent years.select 1 from rdb$databaseunionselect 'a' from rdb$database
