KB16635: Count metrics may return incorrect results when temporary tables are used in a Teradata warehouse instead of derived tables

Placeholder •

This KB addresses an issue seen when incorrect results are returned for count metrics when temporary tables are used in a Teradata warehouse instead of derived tables.

SYMPTOM:
A project exists with a Teradata warehouse. In this project, a user creates a Count metric. The Count function's 'Distinct' parameter is set to false, so that the metric will count all of the rows in the base table, as shown below:

For testing purposes, the attribute A refers to a table 'source,' into which exactly 100 rows have been inserted. Thus, the count metric is expected to return 100. Instead, it produces 46, the same value as a count distinct metric, as shown below:

However, if the VLDB property 'Intermediate Table Type' is changed to 'Derived Table' (the default for Teradata), then the metric returns the expected value, as shown below:

1. Perform cross-tabbing]
Tables Fallback Table Type Permanent Table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Tables Drop Temp Table Method Drop after final pass Tables Table Creation Type Implicit Table Query Optimizations Sub Query Type Use Temporary Table, falling back to IN (SELECT COL) for correlated subquery Joins Full Outer Join Support Support Select/Insert Distinct/Group By option (when no aggregation and not table key) Use GROUP BY Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different Where Query Optimizations Set Operator Optimization Disabled Joins Join Type Join 92 Select/Insert UNION Multiple Insert Use UNIONSQL with Intermediate TablesSQL with Derived Tables


create volatile table ZZDA00, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZDA00
select a11.a_id WJXBFS1
from source a11

create volatile table ZZOT01, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZOT01
select pa11.WJXBFS1 WJXBFS1
from ZZDA00 pa11
where pa11.WJXBFS1 is not null

create volatile table ZZMD02, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZMD02
select count(*) WJXBFS1
from ZZOT01 pa11

create volatile table ZZOT03, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZOT03
select distinct pa11.WJXBFS1 WJXBFS1
from ZZDA00 pa11
where pa11.WJXBFS1 is not null

create volatile table ZZDT04, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZDT04
select count(*) WJXBFS1
from ZZOT03 pa11

select pa11.WJXBFS1 WJXBFS1,
   pa12.WJXBFS1 WJXBFS2
from ZZMD02 pa11
   cross join ZZDT04 pa12

drop table ZZDA00
drop table ZZOT01
drop table ZZMD02
drop table ZZOT03
drop table ZZDT04


select pa11.WJXBFS1 WJXBFS1,
   pa12.WJXBFS1 WJXBFS2
from (select count(*) WJXBFS1
   from (select pa11.WJXBFS1 WJXBFS1
      from (select a11.a_id WJXBFS1
         from source a11
         ) pa11
      where pa11.WJXBFS1 is not null
      ) pa11
   ) pa11
   cross join (select count(*) WJXBFS1
   from (select distinct pa11.WJXBFS1 WJXBFS1
      from (select a11.a_id WJXBFS1
         from source a11
         ) pa11
      where pa11.WJXBFS1 is not null
      ) pa11
   ) pa12


create volatile table ZZDA00, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZDA00
select a11.a_id WJXBFS1
from source a11

create volatile table ZZOT01, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZOT01
select pa11.WJXBFS1 WJXBFS1
from ZZDA00 pa11
where pa11.WJXBFS1 is not null

create volatile table ZZMD02, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZMD02
select count(*) WJXBFS1
from ZZOT01 pa11

create volatile table ZZOT03, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZOT03
select distinct pa11.WJXBFS1 WJXBFS1
from ZZDA00 pa11
where pa11.WJXBFS1 is not null

create volatile table ZZDT04, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZDT04
select count(*) WJXBFS1
from ZZOT03 pa11

select pa11.WJXBFS1 WJXBFS1,
   pa12.WJXBFS1 WJXBFS2
from ZZMD02 pa11
   cross join ZZDT04 pa12

drop table ZZDA00
drop table ZZOT01
drop table ZZMD02
drop table ZZOT03
drop table ZZDT04


select pa11.WJXBFS1 WJXBFS1,
   pa12.WJXBFS1 WJXBFS2
from (select count(*) WJXBFS1
   from (select pa11.WJXBFS1 WJXBFS1
      from (select a11.a_id WJXBFS1
         from source a11
         ) pa11
      where pa11.WJXBFS1 is not null
      ) pa11
   ) pa11
   cross join (select count(*) WJXBFS1
   from (select distinct pa11.WJXBFS1 WJXBFS1
      from (select a11.a_id WJXBFS1
         from source a11
         ) pa11
      where pa11.WJXBFS1 is not null
      ) pa11
   ) pa12


create volatile table ZZDA00, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZDA00
select a11.a_id WJXBFS1
from source a11

create volatile table ZZOT01, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZOT01
select pa11.WJXBFS1 WJXBFS1
from ZZDA00 pa11
where pa11.WJXBFS1 is not null

create volatile table ZZMD02, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZMD02
select count(*) WJXBFS1
from ZZOT01 pa11

create volatile table ZZOT03, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZOT03
select distinct pa11.WJXBFS1 WJXBFS1
from ZZDA00 pa11
where pa11.WJXBFS1 is not null

create volatile table ZZDT04, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZDT04
select count(*) WJXBFS1
from ZZOT03 pa11

select pa11.WJXBFS1 WJXBFS1,
   pa12.WJXBFS1 WJXBFS2
from ZZMD02 pa11
   cross join ZZDT04 pa12

drop table ZZDA00
drop table ZZOT01
drop table ZZMD02
drop table ZZOT03
drop table ZZDT04


select pa11.WJXBFS1 WJXBFS1,
   pa12.WJXBFS1 WJXBFS2
from (select count(*) WJXBFS1
   from (select pa11.WJXBFS1 WJXBFS1
      from (select a11.a_id WJXBFS1
         from source a11
         ) pa11
      where pa11.WJXBFS1 is not null
      ) pa11
   ) pa11
   cross join (select count(*) WJXBFS1
   from (select distinct pa11.WJXBFS1 WJXBFS1
      from (select a11.a_id WJXBFS1
         from source a11
         ) pa11
      where pa11.WJXBFS1 is not null
      ) pa11
   ) pa12


create volatile table ZZDA00, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZDA00
select a11.a_id WJXBFS1
from source a11

create volatile table ZZOT01, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZOT01
select pa11.WJXBFS1 WJXBFS1
from ZZDA00 pa11
where pa11.WJXBFS1 is not null

create volatile table ZZMD02, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZMD02
select count(*) WJXBFS1
from ZZOT01 pa11

create volatile table ZZOT03, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZOT03
select distinct pa11.WJXBFS1 WJXBFS1
from ZZDA00 pa11
where pa11.WJXBFS1 is not null

create volatile table ZZDT04, no fallback, no log(
   WJXBFS1 INTEGER) on commit preserve rows

;insert into ZZDT04
select count(*) WJXBFS1
from ZZOT03 pa11

select pa11.WJXBFS1 WJXBFS1,
   pa12.WJXBFS1 WJXBFS2
from ZZMD02 pa11
   cross join ZZDT04 pa12

drop table ZZDA00
drop table ZZOT01
drop table ZZMD02
drop table ZZOT03
drop table ZZDT04


select pa11.WJXBFS1 WJXBFS1,
   pa12.WJXBFS1 WJXBFS2
from (select count(*) WJXBFS1
   from (select pa11.WJXBFS1 WJXBFS1
      from (select a11.a_id WJXBFS1
         from source a11
         ) pa11
      where pa11.WJXBFS1 is not null
      ) pa11
   ) pa11
   cross join (select count(*) WJXBFS1
   from (select distinct pa11.WJXBFS1 WJXBFS1
      from (select a11.a_id WJXBFS1
         from source a11
         ) pa11
      where pa11.WJXBFS1 is not null
      ) pa11
   ) pa12

Note: To produce this result, the database-instance-level VLDB property 'Count(Column) Support' has been set to 'Use Count(*),' instead of the default setting 'Use Count(Column).' This forces the Strategy SQL Generation Engine to use an intermediate result set to evaluate the Count function.

With the default 'Use Count(Column),' the SQL is much simpler and returns the correct result.


select count(a11.a_id) WJXBFS1,
   count(distinct a11.a_id) WJXBFS2
from source a11

CAUSE:
This behavior is a side effect of Teradata connection modes.

The Teradata driver supports two different connection modes: Teradata mode and ANSI mode. In Teradata mode, tables are created with a property called 'Set,' which means that the table will be treated as a set of distinct elements, instead of a collection of rows that may be repeated.

In the first intermediate table above, ZZDA00, all of the a_id values from the source table are inserted. Many of these values are repeated. With a Teradata-mode connection, only the unique values will be inserted, meaning that this table will have 46 rows instead of the expected 100. Counting the rows of this table naturally returns 46.

If an ANSI-mode connection is used, ZZDA00 will contain 100 rows after the 'insert into' pass, including duplicates. Thus, the Count (non-distinct) metric can return the expected value of 100, while the count distinct metric still returns 46 (because the intermediate table ZZOT03 is populated with an explicit 'select distinct').

Setting the 'Intermediate Table Type' VLDB property to 'Derived Table' produces the expected result because the space-optimization employed in Teradata-mode connections applies only to physical tables created in the warehouse. Selecting the attribute IDs in a derived table always includes duplicate rows (unless the 'distinct' keyword is included).

Therefore, this behavior occurs under the following specific conditions:

The warehouse is Teradata.
The 'Intermediate Table Type' VLDB property is set to 'True Temporary Table' or 'Permanent Table.'
The Count metric requires an intermediate pass to collect the values to be counted. This can occur for two reasons:

The 'Count(Column) Support' VLDB property has been set, at database instance level, to 'Use Count(*)'
The attribute being counted has a compound key.

The warehouse DSN is defined to use a Teradata-mode connection.

If any of these conditions is not met, then the issue should not occur.

ACTION:
The issue may be resolved in one of the following ways. It is not necessary to use both methods simultaneously.

Set the warehouse DSN to use an ANSI-mode connection. This option is reached by clicking on the Options button in the Teradata driver's editing panel in the Microsoft ODBC Administrator, as shown below:

In UNIX environments, the file odbc.ini must be edited manually so that the SessionMode parameter is ANSI.
```
SessionMode=ANSI
```
Set the 'Intermediate Table Type' VLDB property to 'Derived Table', as shown below:

Note that the default intermediate table type for Teradata is 'Derived Table,' so this issue should not occur unless the default has been changed.

Note: This issue is not specific to either Strategy or Teradata versions. Under the above-listed conditions, the Strategy SQL Generation Engine will produce SQL of the above form in all current Strategy versions. The behavior for Teradata-mode connections is consistent across multiple Teradata versions as well.
Note: For more information regarding derived and volatile tables, refer to the following Strategy Knowledge Base technical notes:

Comment

0 comments

Details

Knowledge Article

Published:

May 16, 2017

Last Updated:

May 16, 2017

create volatile table ZZDA00, no fallback, no log( WJXBFS1 INTEGER) on commit preserve rows ;insert into ZZDA00 select a11.a_id WJXBFS1 from source a11 create volatile table ZZOT01, no fallback, no log( WJXBFS1 INTEGER) on commit preserve rows ;insert into ZZOT01 select pa11.WJXBFS1 WJXBFS1 from ZZDA00 pa11 where pa11.WJXBFS1 is not null create volatile table ZZMD02, no fallback, no log( WJXBFS1 INTEGER) on commit preserve rows ;insert into ZZMD02 select count(*) WJXBFS1 from ZZOT01 pa11 create volatile table ZZOT03, no fallback, no log( WJXBFS1 INTEGER) on commit preserve rows ;insert into ZZOT03 select distinct pa11.WJXBFS1 WJXBFS1 from ZZDA00 pa11 where pa11.WJXBFS1 is not null create volatile table ZZDT04, no fallback, no log( WJXBFS1 INTEGER) on commit preserve rows ;insert into ZZDT04 select count(*) WJXBFS1 from ZZOT03 pa11 select pa11.WJXBFS1 WJXBFS1, pa12.WJXBFS1 WJXBFS2 from ZZMD02 pa11 cross join ZZDT04 pa12 drop table ZZDA00 drop table ZZOT01 drop table ZZMD02 drop table ZZOT03 drop table ZZDT04

select pa11.WJXBFS1 WJXBFS1, pa12.WJXBFS1 WJXBFS2 from (select count(*) WJXBFS1 from (select pa11.WJXBFS1 WJXBFS1 from (select a11.a_id WJXBFS1 from source a11 ) pa11 where pa11.WJXBFS1 is not null ) pa11 ) pa11 cross join (select count(*) WJXBFS1 from (select distinct pa11.WJXBFS1 WJXBFS1 from (select a11.a_id WJXBFS1 from source a11 ) pa11 where pa11.WJXBFS1 is not null ) pa11 ) pa12