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

"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)