Icon View Incident Report

Serious Serious
Reported By: Ole Willy Tuv
Reported On: 3/28/2007
For: Version 1.02 Build 1
# 2349 UNION/INTERSECT/EXCEPT Column Type Promotion Not Being Handled Properly

It seems that EDB picks the column types from the first table in the union as the data types for the resulting columns. This approach has implications, such as silent data loss since EDB currently truncates data overflow in assignments, as seen by querying the resulting "test" table.

create table t1
(
 col1 char(1),
 col2 char(1),
 col3 smallint,
 col4 integer
);

create table t2
(
 col1 varchar(2),
 col2 varchar(2),
 col3 decimal(18,2),
 col4 float
);

create table t3
(
 col1 clob,
 col2 char(3),
 col3 integer,
 col4 double precision
);

insert into t1 values ('a','a',32767,2147483647);
insert into t2 values ('ab','ab',123456789012345.67,1.7E308);
insert into t3 values ('abc','abc',2147483647,1.7E308);

create table test as
(
 select * from t1
 union all
 select * from t2
 union all
 select * from t3
)
with data;

select
 name column_name,
 type data_type,
 "length",
 scale
from information.tablecolumns
where tablename = 'test'



Resolution Resolution
Fixed Problem on 5/10/2007 in version 1.03 build 1


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image