Teradata Implicit conversion from number to varchar for a primary index column will cause Full table scan(FTS) but will not return any data and results in error.

Teradata: Implicit Datatype Conversion

Implicit conversion from number to varchar for a primary index column will cause in Full table scan(FTS) but will not return any data and results in error.

Reason: With different datatypes, even same value may go to different AMPs and during implicit conversion teradata will typcast the column to float thats the reason it not filtering rows using PI.

Detail Explanation

Table Creation : Creating a table with PI as VARCHAR column. 

CREATE MULTISET TABLE tutorial_db.employee_test ,NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
    (
     emp_no INTEGER,
     emp_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
     job_title VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
     manager_id INTEGER,
     hire_date DATE FORMAT 'yyyy-mm-dd',
     salary DECIMAL(18,2),
     commission DECIMAL(18,2),
     dept_no INTEGER)
PRIMARY INDEX ( emp_name );

 INSERT INTO tutorial_db.employee_test SELECT * FROM tutorial_db.employee;

Querying Table PI : Querying table using the PI column value. 

SELECT * FROM tutorial_db.employee_test WHERE emp_name='PRADEEP';

Output:

 emp_no  emp_name  job_title      manager_id   hire_date       salary     commission  dept_no
-------  --------  ------------  -----------  ----------  -----------  -------------  -------
1000245  PRADEEP   PRESIDENT               ?  1981-11-17      5000.00              ?      100

Explain Plan : Explain plan of a query fetching data using the PI column value. Optimiser is directly using primary index to fetch record as shown in the explain plan. 

EXPLAIN SELECT * FROM tutorial_db.employee_test WHERE emp_name='PRADEEP';

Explanation
--------------------------------------------------------------------------------
  1) First, we do a single-AMP RETRIEVE step from
     tutorial_db.employee_test by way of the primary index
     "tutorial_db.employee_test.emp_name = 'PRADEEP'" with no
     residual conditions into Spool 1 (one-amp), which is built locally
     on that AMP.  The size of Spool 1 is estimated with low confidence
     to be 2 rows (368 bytes).  The estimated time for this step is
     0.00 seconds.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.00 seconds.

Insert Numerical Record : Inserting a new record with INTEGER in the PI column. 

INSERT INTO tutorial_db.employee_test VALUES(105555, 3, 'ABC', null,current_date, 3333, null, 200);

Checking Data: Querying table to check newly inserted data. 

SELECT * FROM tutorial_db.employee_test;

Output:
     emp_no  emp_name    job_title         manager_id   hire_date     salary   commission  dept_no
-----------  ----------  ---------------  -----------  ----------  ---------  -----------  -------
    1000245  PRADEEP     PRESIDENT                  ?  1981-11-17    5000.00            ?      100
    1000310  WARD        SALESMAN             1000258  1981-02-22    1250.00       500.00      300
    1000294  SMITH       LDC                  1000292  1980-12-17     800.00            ?      200
    1000326  ADAMS       LDC                  1000288  1987-07-13    1100.00            ?      200
    1000288  SCOTT       SYSTEM ANALYST       1000276  1987-07-13    3000.00            ?      200
      105555     3        ABC                        ?  2016-11-21    3333.00            ?      200 
    1000346  MILLER      LDC                  1000262  1982-01-23    1300.00            ?      100
    1000312  MARTIN      SALESMAN             1000258  1981-09-28    1250.00      1400.00      300
    1000262  CLARK       MANAGER              1000245  1981-06-09    2450.00            ?      100
    1000276  JONES       MANAGER              1000245  1981-04-02    2975.00            ?      200
    1000299  ALLEN       SALESMAN             1000258  1981-02-20    1600.00       300.00      300
    1000258  BLAKE       SENIOR MANAGER       1000245  1981-05-01    2850.00            ?      300
    1000292  FORD        SYSTEM ANALYST       1000276  1981-12-03    3000.00            ?      200
    1000315  TURNER      SALESMAN             1000258  1981-09-08    1500.00          .00      300
    1000336  JAMES       LDC                  1000258  1981-12-03     950.00            ?      300

Querying New Data : Query table using newly inserted record with INTEGER in the PI column. 

SELECT * FROM tutorial_db.employee_test WHERE emp_name=3;

Output: Query will fail with the below error.
*** Failure 2621 Bad character in format or data of employee_test.emp_name.

Explain Plan : Explain plan of a query fetching data using the newly inserted INTEGER as PI value. Optimiser is not using primary index in this case and it is typecasting the PI column to FLOAT considering the passed argument. 

EXPLAIN SELECT * FROM tutorial_db.employee_test WHERE emp_name=3;

Explanation
--------------------------------------------------------------------------------
  1) First, we lock a distinct tutorial_db."pseudo table" for read on a
     RowHash to prevent global deadlock for
     tutorial_db.employee.
  2) Next, we lock tutorial_db.employee_test for read.
  3) We do an all-AMPs RETRIEVE step from tutorial_db.employee
     by way of an all-rows scan with a condition of (
     "(tutorial_db.employee_test.emp_name (FLOAT, FORMAT'-9.99999999999999E-999'))
      = 3.00000000000000E 000") into Spool 1 
     (group_amps), which is built locally on the AMPs.  The size of
     Spool 1 is estimated with low confidence to be 2 rows (368 bytes).
     The estimated time for this step is 0.01 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.01 seconds.

Insert Numerical Record as Character: Inserting a new record number record as VARCHAR in the PI column. 

INSERT INTO tutorial_db.employee_test VALUES(105555, '3', 'ABC', null, current_date, 3333, null, 200);

Querying New Data : Query table using newly inserted record with number as character. 

SELECT * FROM tutorial_db.employee_test WHERE emp_name=3;

Output: Query will also fail with the below error.
 *** Failure 2621 Bad character in format or data of employee_test.emp_name.

Explain Plan : Explain plan of a query fetching data using the newly inserted record(number as VARCHAR) as PI value. Optimiser is not using primary index in this case as well and it is typecasting the PI column to FLOAT considering the passed argument. 

EXPLAIN SELECT * FROM tutorial_db.employee_test WHERE emp_name=3;

Explanation
--------------------------------------------------------------------------------
  1) First, we lock a distinct tutorial_db."pseudo table" for read on a
     RowHash to prevent global deadlock for
     tutorial_db.employee.
  2) Next, we lock tutorial_db.employee for read.
  3) We do an all-AMPs RETRIEVE step from tutorial_db.employee
     by way of an all-rows scan with a condition of (
      "(tutorial_db.employee.emp_name (FLOAT, FORMAT '-9.99999999999999E-999'))
     = 3.00000000000000E 000") into Spool 1 
     (group_amps), which is built locally on the AMPs.  The size of
     Spool 1 is estimated with low confidence to be 2 rows (368 bytes).
     The estimated time for this step is 0.01 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.01 seconds.


Hashing : Compare Hashing for a column casting it as NUMBER and VARCHAR. With different datatypes,even same value may go to different AMPs. Check below examples.