SQL> select coalesce(123, 'asd') from rdb$database;
COALESCE
===========
123
SQL> select coalesce('asd', 123) from rdb$database;
COALESCE
===========
asd
SQL> select coalesce('asd', true) from rdb$database;
COALESCE
========
asd
SQL> select coalesce(true, 'asd') from rdb$database;
COALESCE
========
TRUE
SQL> select coalesce(123, true) from rdb$database;
Statement failed, SQLSTATE = HY004
SQL error code = -104
-Datatypes are not comparable in expression COALESCE
SQL> select coalesce(true, 123) from rdb$database;
Statement failed, SQLSTATE = HY004
SQL error code = -104
-Datatypes are not comparable in expression COALESCE
Everything is expected so far. Now execute this:
SQL> select coalesce('asd', true, 123) from rdb$database;
COALESCE
===========
asd
SQL> select coalesce('asd', 123, true) from rdb$database;
COALESCE
===========
asd
SQL> select coalesce(true, 'asd', 123) from rdb$database;
COALESCE
===========
TRUE
SQL> select coalesce(true, 123, 'asd') from rdb$database;
COALESCE
===========
TRUE
SQL> select coalesce(123, 'asd', true) from rdb$database;
COALESCE
===========
123
SQL> select coalesce(123, true, 'asd') from rdb$database;
Statement failed, SQLSTATE = HY004
SQL error code = -104
-Datatypes are not comparable in expression COALESCE
The last error is not expected and this is wrong accordingly to the rules inside DataTypeUtil::makeFromList():
// The output type is figured out as based on this order:
// 1) If any datatype is blob, returns blob;
// 2) If any datatype is a) varying or b) any text/cstring and another datatype, returns varying;
// 3) If any datatype is text or cstring, returns text;
The expected result is to return '123' and this patch fixes this issue (expected errors in the pre-check above are still raised).
Pre-check:
Everything is expected so far. Now execute this:
The last error is not expected and this is wrong accordingly to the rules inside DataTypeUtil::makeFromList():
The expected result is to return '123' and this patch fixes this issue (expected errors in the pre-check above are still raised).