Strange error while inserting null in character key columns

Database

Strange error while inserting null in character key columns

I am facing something strange.
I am creating two tables with one foreign key between them on a varchar column :
CREATE TABLE A(
IKEY INTEGER NOT NULL,
REF_KEY VARCHAR(10),
CONSTRAINT PK_A PRIMARY KEY (IKEY));

CREATE TABLE B (
CHAR_KEY VARCHAR(10) NOT NULL,
CONSTRAINT PK_B PRIMARY KEY (CHAR_KEY));

ALTER TABLE A
ADD CONSTRAINT FK_CONSTRAINT FOREIGN KEY (REF_KEY)
REFERENCES WITH CHECK OPTION B(CHAR_KEY);

Now I insert some data :
INSERT INTO B(CHAR_KEY) VALUES('A');

Then if I insert a null :
INSERT INTO A(IKEY,REF_KEY) VALUES(1,NULL);

I get :
Error: [Teradata Database] [TeraJDBC 13.00.00.06] [Error 2620] [SQLState HY000] The format or data contains a bad character.
SQLState: HY000
ErrorCode: 2620

If I insert a key :
INSERT INTO A(IKEY,REF_KEY) VALUES(1,'A');

It works.

Now if I remove all from table B and A.
and try the following inserts, they will work :
INSERT INTO B(CHAR_KEY) VALUES('1');
INSERT INTO A(IKEY,REF_KEY) VALUES(1,NULL);

It looks like Teradata is trying to convert Varchar keys when trying to insert a null.
Any thoughts ?
2 REPLIES
Teradata Employee

Re: Strange error while inserting null in character key columns

The data type of a NULL value is INTEGER. That's why the Teradata Database returns Error 2620 The format or data contains a bad character.

For example:

select type(null);

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Type(Null)
---------------------------------------
INTEGER

The workaround for this issue is to cast the NULL to be a VARCHAR, so that it is compatible with the foreign key.

INSERT INTO A(IKEY,REF_KEY) VALUES(2,cast(null as varchar(10)));
--> Works as expected: successful insert

Re: Strange error while inserting null in character key columns

Null is not supposed to be typed. I think Teradata is the sole database to type it.
Worse the proposed solution give me an error :
Error: [Teradata Database] [TeraJDBC 13.00.00.06] [Error 3610] [SQLState HY000] Internal error: Please do not resubmit the last request. SubCode, CrashCode: 0, 3701
SQLState: HY000
ErrorCode: 3610