Column level attributes not copied to target table in CREATE TABLE AS...

Database
Enthusiast

Column level attributes not copied to target table in CREATE TABLE AS...

Hi all,

CREATE TABLE AS (subquery) syntax behaves different from what is documented in the standard manual (SQL Reference manual-Data Definition Statements-Release V2R6.1 (B035-1144-115A)).

The column level attributes dont seem to copied to the target table as specified in the excerpt below. Compare it to the actual test result also pasted below.

I would like to know if others have experienced this and its a known problem.

EXCERPT:
=========
"....If you do not specify any column descriptors, the system copies only the data types of the selected source columns or the resultant data types of any columns used in a subquery expression to the target table. It does not copy any attributes such as FORMAT, DEFAULT, COMPRESS, and so on unless you specify them explicitly.

A column descriptor is a target table column name followed by a specification of one or more column attributes. If you specify column descriptors in the definition for the target table, then the column attributes they specify replace any contradictory attributes of the selected source column...
.....
.....
In the following illustration of the feature, you first create a table named t. At some later point, you wish to copy the definition for table t and its data to a new table that you name t1. Note that column t.y is nullable, but the subquery definition for table t1 applies the column descriptor NOT NULL to column t1.b, which is an alias of column t.y.
After table t1 has been created, you run a SHOW TABLE statement on it, and the system reports that column t1.b is defined to be non-nullable. This is because it picked up the NOT NULL specification from the column descriptor in the CREATE TABLE … AS statement. Note that the t1 definition also retains its analogous source column attribute DEFAULT 0, just as column t1.a retains the t.x attribute NOT NULL.

CREATE TABLE t
(
x INTEGER NOT NULL,
y INTEGER DEFAULT 0
);

CREATE TABLE t1 (
a,
b NOT NULL) AS (
SELECT *
FROM t)
WITH DATA;

SHOW TABLE t1;

CREATE SET TABLE t1, NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
a INTEGER NOT NULL,
b INTEGER NOT NULL DEFAULT 0
)
PRIMARY INDEX ( a );

...."

ACTUAL TEST RESULT
===================
/***************************************************************************/

BTEQ 08.02.03.03 Thu Jul 26 12:17:22 2007

+---------+---------+---------+---------+---------+---------+---------+----

.logon houston/josephm1,

*** Logon successfully completed.
*** Teradata Database Release is V2R.06.01.01.17
*** Teradata Database Version is 06.01.01.17
*** Transaction Semantics are BTET.
*** Character Set Name is 'ASCII'.

*** Total elapsed time was 2 seconds.

+---------+---------+---------+---------+---------+---------+---------+----

SELECT * FROM DBC.DBCINFO;

*** Query completed. 2 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

InfoKey InfoData
------------------------------ --------------------------------------------
RELEASE V2R.06.01.01.17
VERSION 06.01.01.17

+---------+---------+---------+---------+---------+---------+---------+----

CREATE TABLE t
(
x INTEGER NOT NULL,
y INTEGER DEFAULT 0
);

*** Table has been created.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

CREATE TABLE t1 (
a,
b NOT NULL) AS
(
SELECT *
FROM t
)
WITH DATA;

*** Table has been created.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

SHOW TABLE t1;

*** Text of DDL statement returned.
*** Total elapsed time was 1 second.

---------------------------------------------------------------------------
/****NOTICE THAT THE ATTRIBUTES FOR t.a and t.b WERE NOT COPIED*************/

CREATE SET TABLE JOSEPHM1.t1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NOT LOCAL AFTER JOURNAL,
WITH JOURNAL TABLE = JOSEPHM1.jtab ,
CHECKSUM = DEFAULT
(
a INTEGER,
b INTEGER NOT NULL)
PRIMARY INDEX ( a );

+---------+---------+---------+---------+---------+---------+---------+----

.logoff;
*** You are now logged off from the DBC.
+---------+---------+---------+---------+---------+---------+---------+----
1 REPLY
Teradata Employee

Re: Column level attributes not copied to target table in CREATE TABLE AS...

If you read the entire topic the documentation is inconsistent; the particular section you excerpted is wrong. What you are observing is the intended behavior. Note there is a difference between the "subquery" and "non-subquery" forms:

CREATE TABLE t1a (
a,
b NOT NULL) AS (
SELECT *
FROM t)
WITH DATA;

versus

CREATE TABLE t2a (
a,
b NOT NULL) AS t
WITH DATA;