Invalid Date Error

Database

Invalid Date Error

SQL throwing error as "Select Failed 2665: invalid date" for the SQL example as below 

SEL  APP.date_id
FROM DATABASE_NAME.COLUMN1 APP
            INNER JOIN     (
                                        SELECT
                                            fact.*                                            , Time_Alias_Name
                                        FROM DATABASE_NAME.TABLE01 FACT
                                        JOIN (
                                                    SELECT    
                                                            Time_Sid,
                                                            Time_Alias_Name
                                                    FROM DATABASE_NAME02.Dim_Time
                                                    WHERE Time_Hier_Name = 'Hierarchy'                                                    
                                                    
                                                    --AND Time_Name_Parent LIKE ANY                                                     --('Jan%','Feb%','Mar%','Apr%','May%','Jun%','Jul%','Aug%','Sep%','Oct%','Nov%','Dec%')                                        )tim
                                        ON fact.Time_Sid = tim.time_sid            )fact    
            ON APP.Column02 = fact.Column02
            AND APP.Column03 = fact.Column03
            AND APP.Column04 = fact.Column04
            AND APP.date_id = fact.Time_Alias_Name
            AND fact.Column05 = 500040
            
            
            LEFT JOIN DATABASE_NAME.TABLE03 GL
            ON GL.COID = APP.COID
            AND Trunc(APP.Column06) >= GL.TABLE03    
            
            WHERE 
            (Trunc(APP.Date_ID) BETWEEN Trunc(Trunc(Current_Date, 'MM') - 120,'MM')     AND Current_Date -1) 

But SQL works fine if i add group by on column 

 

SEL  APP.date_id
FROM DATABASE_NAME.COLUMN1 APP
            INNER JOIN     (
                                        SELECT
                                            fact.*                                            , Time_Alias_Name
                                        FROM DATABASE_NAME.TABLE01 FACT
                                        JOIN (
                                                    SELECT    
                                                            Time_Sid,
                                                            Time_Alias_Name
                                                    FROM DATABASE_NAME02.Dim_Time
                                                    WHERE Time_Hier_Name = 'Hierarchy'                                                    
                                                    
                                                    --AND Time_Name_Parent LIKE ANY                                                     --('Jan%','Feb%','Mar%','Apr%','May%','Jun%','Jul%','Aug%','Sep%','Oct%','Nov%','Dec%')                                        )tim
                                        ON fact.Time_Sid = tim.time_sid            )fact    
            ON APP.Column02 = fact.Column02
            AND APP.Column03 = fact.Column03
            AND APP.Column04 = fact.Column04
            AND APP.date_id = fact.Time_Alias_Name
            AND fact.Column05 = 500040
            
            
            LEFT JOIN DATABASE_NAME.TABLE03 GL
            ON GL.COID = APP.COID
            AND Trunc(APP.Column06) >= GL.TABLE03    
            
            WHERE 
            (Trunc(APP.Date_ID) BETWEEN Trunc(Trunc(Current_Date, 'MM') - 120,'MM')     AND Current_Date -1) 
            group by 1

 SQL also works fine for below 

SEL  APP.date_id
FROM DATABASE_NAME.COLUMN1 APP
            INNER JOIN     (
                                        SELECT
                                            fact.*                                            , cast(Time_Alias_Name as date format 'yyyy-mm-dd') as time_alias
                                        FROM DATABASE_NAME.TABLE01 FACT
                                        JOIN (
                                                    SELECT    
                                                            Time_Sid,
                                                            Time_Alias_Name
                                                    FROM DATABASE_NAME02.Dim_Time
                                                    WHERE Time_Hier_Name = 'Hierarchy'                                                    
                                                    
                                                    --AND Time_Name_Parent LIKE ANY                                                     --('Jan%','Feb%','Mar%','Apr%','May%','Jun%','Jul%','Aug%','Sep%','Oct%','Nov%','Dec%')                                        )tim
                                        ON fact.Time_Sid = tim.time_sid            )fact    
            ON APP.Column02 = fact.Column02
            AND APP.Column03 = fact.Column03
            AND APP.Column04 = fact.Column04
            AND trunc(date_id) = trunc(fact.Time_Alias)
            AND fact.Column05 = 500040
            
            
            LEFT JOIN DATABASE_NAME.TABLE03 GL
            ON GL.COID = APP.COID
            AND Trunc(APP.Column06) >= GL.TABLE03    
            
            WHERE 
            (Trunc(APP.Date_ID) BETWEEN Trunc(Trunc(Current_Date, 'MM') - 120,'MM')     AND Current_Date -1) 
            group by 1

 Details : 

3rd SQL i posted above, i converted "Time_alias_Name" column is VARCHAR(20) & date_id is DATE. So converted them as DATE in TRUNC() 

My query here is, how SQL 02 works in that case if the data types are different & returning proper date values. Is it an BUG on Teradata ??? 

 

Regards,

Vels

4 REPLIES 4
Teradata Employee

Re: Invalid Date Error

At a guess, EXPLAIN will show the inner joins being done in a dfferent order for the first one, and for some time dimension rows Time_Alias_Name is not a valid YYYY-MM-DD string.

 

If anything is a "bug" it would be the first one, where the optimizer likely assumes that an implicit CAST of Time_Alias_Name to DATE will succeed for all rows, not just rows matching on Time_Sid. But I would view that as more of a data modeling issue - using the same column to store different types of data. 

Re: Invalid Date Error

@Fred  It is an hierarchical table (Dim_Time) - it holds value of Date, Text (like Quarter, Month, YTD, QTD etc.,). Hence, it isn't an data model issue. 

Teradata to behave weird is an issue. 

  1. With Group By ~ SQL Works
  2. With additional logic in inner query (the commented part i have done) ~ SQL Works
  3. With Distinct on SELECT ~ SQL Works 
  4. But SQL 01 doesn't work 

Hence, it is an BUG i suspect than an issue with data modeling. 

FYI - same SQL was working fine for few months. Recently started getting this error(so had to update SQL with alternative options). 

 

Regards,

Vels

Teradata Employee

Re: Invalid Date Error

You coded a comparison between unlike types. Rather than fail it outright, the optimizer attempted to insert an implicit cast from character to date so it could try to execute the query anyway.

You are, of course, free to consider this a bug and open an incident with Teradata support.

 

Re: Invalid Date Error

Thanks @Fred  - Sure, will open an incident with Teradata Support regarding the same.