Archives of the TeradataForum

Message Posted: Thu, 09 Jul 2009 @ 16:36:03 GMT

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

Hi,

We have run this simple query which inserts 2 rows into a table, and runs 3 separate selects.

We have tried this on a V2R6.1 , V2R6.2 and V12 machines.

The results are the same, the last 2 selects produce the wrong results.

We're told its due to the COLUMNLIST being over VARCHAR(1000).

All executed via SQL Assitant in Teradata mode.

     CREATE SET TABLE colstats.clean_stats_test ,FALLBACK ,
            NO BEFORE JOURNAL,
            NO AFTER JOURNAL,
            CHECKSUM = DEFAULT
            (
             DatabaseName VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
             TableName VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
             StatId INTEGER,
             StatisticType CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
             ColumnName VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
             Frequency SMALLINT NOT NULL,
             CreateTimeStamp TIMESTAMP(0) FORMAT 'yyyy-mm-ddbhh:mi:ss' NOT NULL,
             CommentString VARCHAR(300) CHARACTER SET LATIN NOT CASESPECIFIC,
             ColCounter INTEGER,
             ColumnList VARCHAR(8192) CHARACTER SET LATIN NOT CASESPECIFIC,
             CollectStats CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
       PRIMARY INDEX ( DatabaseName ,TableName ,ColumnName );

     insert into colstats.clean_stats_test
     values
     ('sys_reporting','dbqlobjtbl_tmp',1,'I','procid',1,current_timestamp(0),
     'Systec',1,'procid,collecttimestamp','C');
     insert into colstats.clean_stats_test
     values
     ('sys_reporting','dbqlobjtbl_tmp',1,'I','collecttimestamp',1,current_tim
     estamp(0),'Systec',2,'procid,collecttimestamp','C');

     1)   select databasename, tablename, statid, statistictype, columnname,
     frequency, createtimestamp, commentstring, colcounter, columnlist,
     collectstats
     from colstats.clean_stats_test
     order by 1,2,3, 9,10 ;

        DatabaseName   TableName   StatId   StatisticType
     ColumnName   Frequency   CreateTimeStamp   CommentString
     ColCounter   ColumnList   CollectStats
        sys_reporting   dbqlobjtbl_tmp   1   I   procid         1
     2009-07-09 16:47:59   Systec   1   procid,collecttimestamp   C
        sys_reporting   dbqlobjtbl_tmp   1   I   collecttimestamp
     1   2009-07-09 16:47:59   Systec   2   procid,collecttimestamp
     C

     CORRECT RESULT

     2)   select databasename, tablename, statid, statistictype, columnname,
     frequency, createtimestamp, commentstring, colcounter, columnlist,
     collectstats
     from colstats.clean_stats_test
     order by 1,2,3, 10,9 ;

        DatabaseName   TableName   StatId   StatisticType
     ColumnName   Frequency   CreateTimeStamp   CommentString
     ColCounter   ColumnList   CollectStats
        sys_reporting   dbqlobjtbl_tmp   1   I   collecttimestamp
     1   2009-07-09 16:47:59   Systec   2   procid,collecttimestamp
     C
        sys_reporting   dbqlobjtbl_tmp   1   I   procid
     1   2009-07-09 16:47:59   Systec   1   procid,collecttimestamp
     C

     INCORRECT RESULT

     3)  select databasename, tablename, statid, statistictype, columnname,
     frequency, createtimestamp, commentstring, colcounter, columnlist,
     collectstats
     from colstats.clean_stats_test
     order by 1,2,3, columnlist,colcounter ;

        DatabaseName   TableName   StatId   StatisticType
     ColumnName   Frequency   CreateTimeStamp   CommentString
     ColCounter   ColumnList   CollectStats
        sys_reporting   dbqlobjtbl_tmp   1   I   collecttimestamp
     1   2009-07-09 16:47:59   Systec   2   procid,collecttimestamp
     C
        sys_reporting   dbqlobjtbl_tmp   1   I   procid         1
     2009-07-09 16:47:59   Systec   1   procid,collecttimestamp   C

     INCORRECT RESULT

Anyone else experience this

Trevor Price - Certified Teradata Master V2R5

Vodafone DBA

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