July 12, 2011

Data type coercion vs comparison rules

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:
select 1 from rdb$database
union
select '1' from rdb$database
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:
If any of the data types in DTS is character string, then:

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.
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:
select 1 from rdb$database
union
select 'a' from rdb$database
However, there's a hidden issue which remained unnoticed for a few recent years.

July 10, 2011

Reincarnation of the developer journal

The former Firebird Development Journal that was hosted at the official Firebird site and occasionally maintained by myself now strikes back in a new personal incarnation on the Blogger service with a hope to share more Firebird related information along with other personal blogs maintained by the project members (see the sidebar for the links). Please don't expect me posting frequently but I'll do my best to write about the things I consider worth mentioning.