Incorrect answerset.

Database
Enthusiast

Incorrect answerset.

When selecting from table where col1 is = 0
records where col1 is = ' ' are being returned. Column is Char(1)
Is this normal?

create table dsb.zero_null
(col1 char(1)
,col2 smallint
,col3 smallint)
primary index(col1,col2,col3);

insert into dsb.zero_null
values(,1,2);
insert into dsb.zero_null
values('0',3,4);
insert into dsb.zero_null
values(0,5,6);
insert into dsb.zero_null
values(' ',7,8);
insert into dsb.zero_null
values('',9,10);

sel * from dsb.zero_null;
col1 col2 col3
? 1 2
0 3 4
5 6
7 8
9 10
*********************
sel * from dsb.zero_null
where col1 = 0;
col1 col2 col3
9 10
0 3 4
5 6
7 8
*********************

select * from dsb.zero_null
where col1 = '0'
col1 col2 col3
0 3 4

Thanks,
R.Glass
1 REPLY
Enthusiast

Re: Incorrect answerset.

You might want to read the topic "Implicit Type Conversion of Character Operands" in the Teradata SQL reference manual. When comparing a numeric value to a character value, both operands are first converted to FLOAT. In the conversion from character to numeric, leading blanks in the character value are interpreted as 0.