NUMBERs, JOINs y ORs…

2 marzo \02\UTC 2017

Desde la versión 14, Teradata implementa el tipo “Oracle” NUMBER. Y lo hace a su imagen y semejanza: con el almacenamiento en forma de exponente y mantisa. El motivo es, principalmente, facilitar las migraciones desde Bases de Datos Oracle a Teradata (de las que he visto unas cuantas y he participado en otras). Anteriormente, al no disponer Teradata de un tipo análogo al NUMBER de Oracle, había que elegir entre los numéricos “nativos”, con las dificultades que ello conlleva y la propensión a errores por elecciones incorrectas.

El tipo en sí presenta ciertas ventajas y varias particularidades, como el espacio de almacenamiento variable (de 2 a 18 bytes), la posibilidad de variar escala y precisión sin modificar las filas o la precisión de 18 dígitos (o incluso más) en las operaciones con ellos. Por otra parte, es un tipo en coma flotante (floating point) que puede no ser todo lo exacto que necesitamos y puede llevar a pequeñas incorrecciones en los cálculos.

Así que, en teoría, no hay ningún problema para utilizar NUMBER(n, m) en Teradata tal como lo hacemos en Oracle, donde es el tipo numérico por excelencia.

Pero a veces las cosas no son lo que parecen:

 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                        15.10.02.01
VERSION                        15.10.02.03
LANGUAGE SUPPORT MODE          Standard

 BTEQ -- Enter your SQL request or BTEQ command:


CREATE MULTISET TABLE TESTDB.TEST1 (
   ID_C CHAR(2) NOT NULL,
   ID_N NUMBER
)
PRIMARY INDEX (ID_C);


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


 BTEQ -- Enter your SQL request or BTEQ command:


INSERT INTO TESTDB.TEST1 VALUES ('98', 999999999999998);


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO TESTDB.TEST1 VALUES ('99', 999999999999999);


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:


CREATE MULTISET TABLE TESTDB.TEST2 (
   ID_C CHAR(2) NOT NULL,
   ID_N NUMBER(15, 0),
   C_TXT CHAR(6)
)
PRIMARY INDEX (ID_C);


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


 BTEQ -- Enter your SQL request or BTEQ command:


INSERT INTO TESTDB.TEST2 VALUES ('98', 999999999999998, 'OCHO');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO TESTDB.TEST2 VALUES ('99', 999999999999999, 'NUEVE');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:


SELECT c.C_TXT
      ,a.ID_C
      ,a.ID_N
  FROM ( SELECT ID_C
               ,ID_N
          FROM TESTDB.TEST1
         WHERE ( ID_C = '98' AND
                 ID_N = 999999999999998 )
            OR ( ID_C = '99' AND
                 ID_N = 999999999999999 )
       ) a
  LEFT JOIN TESTDB.TEST2 c
         ON ( a.ID_C = c.ID_C
          AND a.ID_N = c.ID_N )
;


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

C_TXT   ID_C                                      ID_N
------  ----  ----------------------------------------
(null)  99                             999999999999999
OCHO    98                             999999999999998

 BTEQ -- Enter your SQL request or BTEQ command:


SELECT c.C_TXT
      ,a.ID_C
      ,a.ID_N
  FROM ( SELECT ID_C
               ,ID_N
          FROM TESTDB.TEST1
         WHERE ( ID_C = '99' AND
                 ID_N = 999999999999999 )
            OR ( ID_C = '98' AND
                 ID_N = 999999999999998 )
       ) a
  LEFT JOIN TESTDB.TEST2 c
         ON ( a.ID_C = c.ID_C
          AND a.ID_N = c.ID_N )
;


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

C_TXT   ID_C                                      ID_N
------  ----  ----------------------------------------
(null)  98                             999999999999998
NUEVE   99                             999999999999999

 BTEQ -- Enter your SQL request or BTEQ command:

Como podemos ver, la misma query da resultados diferentes sólo con cambiar el orden de los filtros en la parte WHERE … OR de la subselect “a”.

¡Pero eso es imposible! Todos sabemos que el ‘OR’ es conmutativo.

Además, ambos resultados son erróneos.

¿Y qué puede causar este comportamiento? Pues, presumiblemente, los resultados intermedios de la subselect “a” varían en cuanto al tipo elegido según lleguen las primeras filas como 999999999999998 ó 999999999999999. El tipo definido para ID_N en TEST1 es NUMBER, sin especificar escala ni precisión, mientras que en TEST2 es NUMBER(15, 0). Aunque externamente “NUMBER es NUMBER”, en algún lugar se producen diferencias que hacen que el JOIN no funcione como se espera.

Por eso, si estamos en lo cierto, al hacer un CAST explícito sobre las columnas NUMBER en la subselect todo debería funcionar OK:

SELECT c.C_TXT
      ,a.ID_C
      ,a.ID_N
  FROM ( SELECT ID_C
               ,ID_N (NUMBER(15,0))  --CAST!!
          FROM TESTDB.TEST1
         WHERE ( ID_C = '98' AND
                 ID_N = 999999999999998 )
            OR ( ID_C = '99' AND
                 ID_N = 999999999999999 )
       ) a
  LEFT JOIN TESTDB.TEST2 c
         ON ( a.ID_C = c.ID_C
          AND a.ID_N = c.ID_N )
;


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

C_TXT   ID_C              ID_N
------  ----  ----------------
NUEVE   99     999999999999999
OCHO    98     999999999999998

 BTEQ -- Enter your SQL request or BTEQ command:


SELECT c.C_TXT
      ,a.ID_C
      ,a.ID_N
  FROM ( SELECT ID_C
               ,ID_N (NUMBER(15,0))  --CAST!!
          FROM TESTDB.TEST1
         WHERE ( ID_C = '99' AND
                 ID_N = 999999999999999 )
            OR ( ID_C = '98' AND
                 ID_N = 999999999999998 )
       ) a
  LEFT JOIN TESTDB.TEST2 c
         ON ( a.ID_C = c.ID_C
          AND a.ID_N = c.ID_N )
;


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

C_TXT   ID_C              ID_N
------  ----  ----------------
NUEVE   99     999999999999999
OCHO    98     999999999999998

 BTEQ -- Enter your SQL request or BTEQ command:

En efecto, vemos que con CAST explícito todo vuelve a la normalidad y la SELECT da el resultado esperado (y correcto esta vez).

Claro que esto no deja de ser un “workaround“. Supongo que alguien tiene trabajo en Rancho Bernardo.

Por otra parte, siempre se debería especificar escala y precisión en los tipos NUMBER (en Oracle también).

Saludos.

Carlos.