[teiid-issues] [JBoss JIRA] (TEIID-4259) "TEIID30259 Cannot introduce new expressions" when using formattimestamp

[Bram Gadeyne (JIRA)]

Bram Gadeyne created TEIID-4259:
-----------------------------------

             Summary: "TEIID30259 Cannot introduce new expressions" when using formattimestamp
                 Key: TEIID-4259
                 URL: https://issues.jboss.org/browse/TEIID-4259
             Project: Teiid
          Issue Type: Bug
    Affects Versions: 8.11.3
            Reporter: Bram Gadeyne
            Assignee: Steven Hawkins


A certain query executes fine when using the normal timestamp column (w.valuetime). When this is changed to formattimestamp(w.valuetime, 'dd/MM/yyyy HH:mm') we get an exception complaining about a case statement that is part of the select a few columns after the particular valuetime column.

14:00:00,055 WARN  [org.teiid.PROCESSOR] (Worker17615_QueryProcessorQueue95374) 7OLMH/JCvk3W TEIID30020 Processing exception for request 7OLMH/JCvk3W.48 'TEIID30259 Cannot introduce new expressions [CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END] in duplicate removal.'. Originally QueryPlannerException RuleAssignOutputElements.java:407. Enable more detailed logging to see the entire stacktrace.

This was the query that caused the problem:

select w.patientid as Patientid,
formattimestamp(w.valuetime,'yyyy-MM-dd HH:mm') as t2,
w.valuetime as Tijdstip,
max(w.bed) as Bed, max(w.Naam) as Naam,
case when convert(max(w.opname), date) = timestampadd(SQL_TSI_DAY, -1, convert(now() , date)) then 'Gisteren'
        else
        case when convert(max(w.opname), date) =  convert(now(), date) then 'Vannacht' else
        '' end end as Opname,
case when count(w.MondZorgAantal) is null then 'Niet gemeten' else 'OK' end as Comfort,
case when count(w.EvaluatieMond) > 0 then 'OK' else 'Ontbreekt' end as NRS
from
        (
        select pi.patientid as Patientid, null as ValueTime, pi.bedabbr as Bed, concat(pi.PatientLastName, concat(', ',pi.PatientFirstName)) as Naam, pi.AdmTime as opname, null as MondZorgAantal, null as EvaluatieMond, patientStatusID
                from prod_PV_PatientInfo pi
                where pi.PatientStatusID = 1 and PatGroupAbbr like 'PICU%' and cast(pi.admTime as date) <> cast(now() as date)
        union
        select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname,
                v.c as MondZorgAantal, null as EvaluatieMond, v.PatientStatusID from
                        (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID
                        from prod_PV_DerivedVariables obs1
                        join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) <> cast(now() as date) and PatGroupAbbr like 'PICU%'
                        where obs1.variableid = 30010003
                                and cast(valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date))
                        group by obs1.patientid,  obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v
        union
        select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname, null as MondZorgAantal, v.c  as EvaluatieMond, v.PatientStatusID from
                        (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID
                        from prod_PV_ObservationVariables obs1
                        join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) < cast(now() as date) and PatGroupAbbr like 'PICU%'
                        where obs1.variableid = 15005534
                                and cast(obs1.valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date))
                        group by obs1.patientid,  obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v
        ) w
group by w.patientid, w.ValueTime
order by max(w.bed)



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)