Hi,

> My name is Ilir Gashi and I am Research Student at the Centre for
> Software
> Reliability, City University, London. I found a (possible) bug in
> Firebird
> 1.0 (I know it is an old release but I do not have a newer one
> installed
> and in any case I am only using the server for research purposes).
>
> The script is as follows:
>
> CREATE TABLE ta (c1 CHAR(1));
> CREATE TABLE tb (c1 CHAR(1));
> CREATE TABLE tc (c1 SMALLINT);
>
> INSERT INTO ta VALUES('a');
> INSERT INTO tb VALUES('b');
> INSERT INTO tc VALUES(1);
>
> SELECT c1 AS a,NULL AS b,NULL AS c FROM ta
> UNION ALL
> SELECT NULL,c1,NULL FROM tb
> UNION ALL
> SELECT NULL,NULL,c1 FROM tc;
>
> SQL error code = -104
> Invalid command
> Data type unknown
>
> The same query with the same tables in MS SQL Server 7 returns the
> results:
>
> a b c
> ---- ---- ------
> a NULL NULL
> NULL b NULL
> NULL NULL 1
>
> (3 row(s) affected)
>
> PostgreSQL 7.2 and Oracle 8.0.5 return the same (or similar) error
> message
> to Firebird 1.0. So i have disagreements in the results retured by the
> servers.
>
> Is this a bug, or is this the intended behaviour in Firebird.

AFAIK, Firebird 2.0 will have an improvement in that area, at least
that's what the roadmap says (watch the wrap).
http://firebird.sourceforge.net/index.php?op=devel&sub=engine&id=roadmap

Search for "Automatic casting in unions"


The following should work with Firebird 1.x


SELECT c1 AS a, cast(NULL as char(1)) AS b, cast(NULL as smallint) AS c
FROM ta
UNION ALL
SELECT cast(NULL as char(1)), c1, cast(NULL as smallint) FROM tb
UNION ALL
SELECT NULL, NULL, c1 FROM tc;

> P.S. I am not sure whether this is the correct place to send this
> report.
> If it is not then sorry for the inconvenience but can you give me a
> pointer
> as to where I should report this.

This forum is fine for support questions. A list of Firebird-related
forums is available here (watch the wrap, again):
http://www.ibphoenix.com/main.nfs?a=ibphoenix&s=1107200626:763361&page=ibp_groups


--
HTH,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database and MS SQL Server
Upscene Productions
http://www.upscene.com