Union all problem

I test UNION on V5.3 and V5.3.1

create table tt1 (fid int, f2 TEXT ENCODING DICT(8));
create table tt2 (fid int, f2 TEXT ENCODING DICT(8));

select fid from tt1
UNION all
select fid from tt2
---- it is OK!

select * from tt1
UNION all
select * from tt2
— Subqueries of a UNION must have exact same data types. ??

what’s the probleam ?

Hi @ggking,

Nice to have you also in the Community forum.

I checked out, and it looks to be a bug on text datatypes; while the datatypes are precisely the same, the system believes they aren’t.

Have you tried to run

select f2 from tt1 union all select f2 from tt2

I guess you will get the same error.

but if you change the DDL in this way

create table tt1 (fid int, f2 TEXT ENCODING DICT(8));
create table tt2 (fid int, f2 TEXT, shared dictionary (f2) on tt1(f2));

the query

select * from tt1
UNION all
select * from tt2

Would work flawlessly.

It must use “shared dictionary”…
Do you have a road map for not using a dictionary for “union” ?

Hi,

we have just issued a patch that changes the error message, pointing the users to the use of shared dictionaries, but I don’t know when a real patch will land to correct this issue.

Anyway I will warn you when the issue will be fixed.