Archives of the TeradataForum

Message Posted: Mon, 12 May 2008 @ 17:51:46 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi -

I am noticing an issue in Teradata, when I do a left join with a view and do a select based on a field in the view being null. This field has a default value defined.

Here is the set up -

TABLE1 has 1 column, COLUMN1, and 3 rows.

TABLE2 has 2 columns (COLUMN1, COLUMN2) and 1 row.

A view on TABLE2 defines a default value for COLUMN2.

When TABLE1 is joined with TABLE2 on COLUMN1 where COLUMN2 is null, it should return 2 rows, but returns NONE. The group by shows that COLUMN2 has 2 nulls.

     CREATE SET TABLE TABLE1 ,NO FALLBACK ,
           NO BEFORE JOURNAL,
           NO AFTER JOURNAL,
           CHECKSUM = DEFAULT
           (
            column1 INTEGER
           )
     PRIMARY INDEX ( column1 );


     insert into TABLE1 values (1);
     insert into TABLE1 values (2);
     insert into TABLE1 values (3);


     CREATE SET TABLE TABLE2 ,NO FALLBACK ,
           NO BEFORE JOURNAL,
           NO AFTER JOURNAL,
           CHECKSUM = DEFAULT
           (
            column1 INTEGER,
            column2 VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC
           )
     PRIMARY INDEX ( column1 );


     insert into TABLE2 values (1, 'One');


     CREATE VIEW VIEW_tbl2
     AS
     SELECT
          column1,
          'Default' Column2
     FROM TABLE2;


     SEL * FROM VENTR_DEV.VIEW_tbl2;


     SELECT TB2.column2, count (*)
     FROM TABLE1 TB1 left outer join VIEW_tbl2 TB2
     on TB1.column1 = TB2.column1
     Group By 1 <<--- Shows 2 rows where Column2 is null


     Select *
     from TABLE1 TB1
       left outer join
     VIEW_tbl2 TB2 on
     TB1.Column1 = TB2.Column1
     where TB2.Column2 is NULL; << --- Returns none when selected.

My problem is why the last SQL does not return any rows. My hypothesis is that since the view has a default defined, it assumes this field will have default value for all rows in the join, even though this is a left join and NOT ALL rows may have a match. Regardless, I think this is not ideal. Can the members confirm if this is not an acceptable behavior or a bug? Any workarounds (short of not using defaults in the view?)

Thanks.

	<Prev	Next>		<<First	<Prev	Next>	Last>>