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.
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;
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 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 INTO tutorial_db.employee_test VALUES(105555, 3, 'ABC', null,current_date, 3333, null, 200);
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
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 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 INTO tutorial_db.employee_test VALUES(105555, '3', 'ABC', null, current_date, 3333, null, 200);
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 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.
SELECT HASHAMP(HASHBUCKET(HASHROW(emp_no))),emp_no from tutorial_db.employee order by 2;
HASHAMP(HASHBUCKET(HASHROW(emp_no))) emp_no
------------------------------------ -----------
87 1000245
49 1000258
73 1000262
124 1000276
123 1000288
122 1000292
78 1000294
56 1000299
0 1000310
89 1000312
74 1000315
93 1000326
35 1000336
154 1000346
SELECT HASHAMP(HASHBUCKET(HASHROW(cast(emp_no as varchar(10))))),emp_no from tutorial_db.employee order by 2;
HASHAMP(HASHBUCKET(HASHROW(emp_no))) emp_no
------------------------------------ -----------
129 1000245
172 1000258
112 1000262
149 1000276
133 1000288
136 1000292
126 1000294
176 1000299
108 1000310
136 1000312
124 1000315
73 1000326
183 1000336
69 1000346