‘Bug’ en CREATE TABLE AS SELECT + USI.

Mi compañero F. y yo hemos encontrado un comportamiento extraño en Teradata que tiene toda la pinta de ser un ‘bug‘.

Al crear una tabla con CREATE TABLE AS SELECT que además tiene un USI (Unique Secondary Index) se produce la eliminación sin errores de las filas que son duplicados de dicho USI sin que se produzca un error (sé lo que estás pensando, pero esto ocurre aunque la tabla sea MULTISET).

Pero esto sólo ocurre en determinadas circunstancias: el USI contiene una columna generada en un CASE con funciones y otra que es concatenación de varias columnas, y además es el NUPI (Non-Unique Primary Index) de la tabla.

Aquí está el caso de prueba:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT * FROM DBC.DBCINFO;

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

InfoKey                        InfoData
------------------------------ -------------------------
RELEASE                        14.10.06.06
LANGUAGE SUPPORT MODE          Standard
VERSION                        14.10.06.06
 

 BTEQ -- Enter your SQL request or BTEQ command:
SHOW TABLE TEST_CASE_SOURCE;

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

--------------------------------------------------------------------
CREATE MULTISET TABLE TEST_CASE_SOURCE ,
    NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
    (
      COLUMN00 CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COLUMN01 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COLUMN02 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COLUMN03 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COLUMN04 CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COLUMN_N VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( COLUMN04 );


 BTEQ -- Enter your SQL request or BTEQ command:
SELECT COUNT(1) FROM TEST_CASE_SOURCE;


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

  Count(1)
-----------
    3736103

Creamos la tabla nueva con las condiciones descritas arriba:

 BTEQ -- Enter your SQL request or BTEQ command:
DROP TABLE TEST_CASE_DEST ;

*** Failure 3807 Object 'TEST_CASE_DEST' does not exist.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
CREATE MULTISET TABLE TEST_CASE_DEST
AS
(
  SELECT
    COLUMN00,
  (COLUMN01||COLUMN02||COLUMN03||'0'||COLUMN04) (CHAR(16)) AS COLUMNPI,
  CASE
      WHEN COLUMN_N IS NULL THEN CAST('000' AS CHAR(3))
      ELSE TD_SYSFNLIB.LPAD(TRIM(COLUMN_N),3,'0')
  END AS COLUMN_N
  FROM TEST_CASE_SOURCE
)WITH DATA
PRIMARY INDEX ( COLUMNPI )
UNIQUE INDEX(COLUMNPI, COLUMN_N, COLUMN00)
;


 *** Table has been created.
 *** Total elapsed time was 7 seconds.

Podemos ver que la tabla se ha creado sin errores, pero:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT COUNT(1) FROM TEST_CASE_DEST;


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

  Count(1)
-----------
    3735548

La tabla creada tiene menos filas que la tabla original (3735548 en vez de 3736103).

La cosa cambia si la tabla es creada como NOPI (NO Primary Index):

 BTEQ -- Enter your SQL request or BTEQ command:
DROP TABLE TEST_CASE_DEST ;


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


 BTEQ -- Enter your SQL request or BTEQ command:
CREATE MULTISET TABLE TEST_CASE_DEST
AS
(
  SELECT
    COLUMN00,
  (COLUMN01||COLUMN02||COLUMN03||'0'||COLUMN04) (CHAR(16)) AS COLUMNPI,
  CASE
      WHEN COLUMN_N IS NULL THEN CAST('000' AS CHAR(3))
      ELSE TD_SYSFNLIB.LPAD(TRIM(COLUMN_N),3,'0')
  END AS COLUMN_N
  FROM TEST_CASE_SOURCE
)WITH DATA
NO PRIMARY INDEX
UNIQUE INDEX(COLUMNPI, COLUMN_N, COLUMN00)
;

*** Failure 2803 Secondary index uniqueness violation in target table.
                Statement# 1, Info =0
 *** Total elapsed time was 6 seconds.

Aquí el error esperado sí se produce.

Pero hay más: Si creamos la misma tabla con CREATE TABLE AS SELECT … WITH NO DATA y hacemos luego el INSERT … SELECT el error aparece también:

 BTEQ -- Enter your SQL request or BTEQ command:
DROP TABLE TEST_CASE_DEST ;

*** Failure 3807 Object 'TEST_CASE_DEST' does not exist.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.  


 BTEQ -- Enter your SQL request or BTEQ command:
CREATE MULTISET TABLE TEST_CASE_DEST
AS
(
  SELECT
    COLUMN00,
  (COLUMN01||COLUMN02||COLUMN03||'0'||COLUMN04) (CHAR(16)) AS COLUMNPI,
  CASE
      WHEN COLUMN_N IS NULL THEN CAST('000' AS CHAR(3))
      ELSE TD_SYSFNLIB.LPAD(TRIM(COLUMN_N),3,'0')
  END AS COLUMN_N
  FROM TEST_CASE_SOURCE
)WITH NO DATA

Es decir, la misma SELECT utilizada en la creación de la tabla -que funcionó sin errores en el primer caso- utilizada ahora con WITH NO DATA y vuelta a utilizar en un INSERT … SELECT posterior vuelve a dar el error esperado (que es el funcionamiento correcto).

Saludos.

Carlos.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: