Description:
following rather long query has been running fine on our web server in version 4.1.14. after upgrading to 5.0.15 while maintaining the same databases and tables we are unable to run the same code. error says ERROR 1054 (42S22): Unknown column 'A.NID' in 'on clause'
create temporary table tempprojects(projectid char(6), title char(255), nidid double, sonid double, conopid double, areid double, busid double, conopordid double, deplid double, ereid double, maintid double, ordid double, pplid double, progid double, techid double, odpid double, nidrev char(255), nidrevdate char(10), sonrev char(255), sonrevdate char(10), conoprev char(255), conoprevdate char(10), arerev char(255), arerevdate char(10), busrev char(255), busrevdate char(10), conopordrev char(255), conopordrevdate char(10), deplrev char(255), deplrevdate char(10), ererev char(255), ererevdate char(10), maintrev char(255), maintrevdate char(10), ordrev char(255), ordrevdate char(10), pplrev char(255), pplrevdate char(10), progrev char(255), progrevdate char(10), techrev char(255), techrevdate char(10), odprev char(255), odprevdate char(10)) (select substring('04-001 | OSIP Requirements Management System',1,6) as projectid, substring('04-001 | OSIP Requirements Management System',10,length('04-001 | OSIP Requirements Management System')-9) as title, B."OBJECT ID" as nidid, C."OBJECT ID" as sonid, D."OBJECT ID" as conopid, E."OBJECT ID" as areid, F."OBJECT ID" as busid, G."OBJECT ID" as conopordid, H."OBJECT ID" as deplid, I."OBJECT ID" as ereid, J."OBJECT ID" as maintid, K."OBJECT ID" as ordid, L."OBJECT ID" as pplid, M."OBJECT ID" as progid, N."OBJECT ID" as techid, O."OBJECT ID" as odpid, B."DOCUMENT REVISION" as nidrev, date_format(B."DOCUMENT REVISION DATE",'%m/%d/%Y') as nidrevdate, C."DOCUMENT REVISION" as sonrev, date_format(C."DOCUMENT REVISION DATE",'%m/%d/%Y') as sonrevdate, D."DOCUMENT REVISION" as conoprev, date_format(D."DOCUMENT REVISION DATE",'%m/%d/%Y') as conoprevdate, E."DOCUMENT REVISION" as arerev, date_format(E."DOCUMENT REVISION DATE",'%m/%d/%Y') as arerevdate, F."DOCUMENT REVISION" as busrev, date_format(F."DOCUMENT REVISION DATE",'%m/%d/%Y') as busrevdate, G."DOCUMENT REVISION" as conopordrev, date_format(G."DOCUMENT REVISION DATE",'%m/%d/%Y') as conopordrevdate, H."DOCUMENT REVISION" as deplrev, date_format(H."DOCUMENT REVISION DATE",'%m/%d/%Y') as deplrevdate, I."DOCUMENT REVISION" as ererev, date_format(I."DOCUMENT REVISION DATE",'%m/%d/%Y') as ererevdate, J."DOCUMENT REVISION" as maintrev, date_format(J."DOCUMENT REVISION DATE",'%m/%d/%Y') as maintrevdate, K."DOCUMENT REVISION" as ordrev, date_format(K."DOCUMENT REVISION DATE",'%m/%d/%Y') as ordrevdate, L."DOCUMENT REVISION" as pplrev, date_format(L."DOCUMENT REVISION DATE",'%m/%d/%Y') as pplrevdate, M."DOCUMENT REVISION" as progrev, date_format(M."DOCUMENT REVISION DATE",'%m/%d/%Y') as progrevdate, N."DOCUMENT REVISION" as techrev, date_format(N."DOCUMENT REVISION DATE",'%m/%d/%Y') as techrevdate, O."DOCUMENT REVISION" as odprev, date_format(O."DOCUMENT REVISION DATE",'%m/%d/%Y') as odprevdate, temp3."TYPE OF NEED" as type_of_need, temp3."MISSION PRIORITY" as mission_priority, temp3."IWT LEAD" as iwt_lead, temp3."IWT MEMBERS" as iwt_members, temp3."GATE MEETING NOTES" as gate_meeting_notes, temp3."PROJECT ISSUES" as project_issues, temp3."PROJECT ACTIONS" as project_actions, temp3."REVIEW TEAM MEETING NOTES" as review_team_meeting_notes, temp3."OVERSIGHT BODY NOTES" as oversight_body_notes from "temp", temp3 left join "CORE DOCUMENTS1" B on (B."DOCUMENT TITLE" = temp."NID" and B."STATE" like 'Latest%' and B."STATUS"='Complete') left join "CORE DOCUMENTS1" C on (C."DOCUMENT TITLE" = temp."SON" and C."STATE" like 'Latest%' and C."STATUS"='Complete') left join "CORE DOCUMENTS1" D on (D."DOCUMENT TITLE" = temp."CONOP" and D."STATE" like 'Latest%' and D."STATUS"='Complete') left join "CORE DOCUMENTS1" E on (E."DOCUMENT TITLE" = temp."ARE" and E."STATE" like 'Latest%' and E."STATUS"='Complete') left join "CORE DOCUMENTS1" F on (F."DOCUMENT TITLE" = temp."BUS" and F."STATE" like 'Latest%' and F."STATUS"='Complete') left join "CORE DOCUMENTS1" G on (G."DOCUMENT TITLE" = temp."CONOPORD" and G."STATE" like 'Latest%' and G."STATUS"='Complete') left join "CORE DOCUMENTS1" H on (H."DOCUMENT TITLE" = temp."DEPL" and H."STATE" like 'Latest%' and H."STATUS"='Complete') left join "CORE DOCUMENTS1" I on (I."DOCUMENT TITLE" = temp."ERE" and I."STATE" like 'Latest%' and I."STATUS"='Complete') left join "CORE DOCUMENTS1" J on (J."DOCUMENT TITLE" = temp."MAINT" and J."STATE" like 'Latest%' and J."STATUS"='Complete') left join "CORE DOCUMENTS1" K on (K."DOCUMENT TITLE" = temp."ORD" and K."STATE" like 'Latest%' and K."STATUS"='Complete') left join "CORE DOCUMENTS1" L on (L."DOCUMENT TITLE" = temp."PPL" and L."STATE" like 'Latest%' and L."STATUS"='Complete') left join "CORE DOCUMENTS1" M on (M."DOCUMENT TITLE" = temp."PROG" and M."STATE" like 'Latest%' and M."STATUS"='Complete') left join "CORE DOCUMENTS1" N on (N."DOCUMENT TITLE" = temp."TECH" and N."STATE" like 'Latest%' and N."STATUS"='Complete') left join "CORE DOCUMENTS1" O on (O."DOCUMENT TITLE" = temp."ODP" and O."STATE" like 'Latest%' and O."STATUS"='Complete') where temp."tablename" = 'CORE DOCUMENTS1' and temp."NAME" = temp3."NAME" )
How to repeat:
check sql format support/consistency from version 4.x to 5.x
Description: following rather long query has been running fine on our web server in version 4.1.14. after upgrading to 5.0.15 while maintaining the same databases and tables we are unable to run the same code. error says ERROR 1054 (42S22): Unknown column 'A.NID' in 'on clause' create temporary table tempprojects(projectid char(6), title char(255), nidid double, sonid double, conopid double, areid double, busid double, conopordid double, deplid double, ereid double, maintid double, ordid double, pplid double, progid double, techid double, odpid double, nidrev char(255), nidrevdate char(10), sonrev char(255), sonrevdate char(10), conoprev char(255), conoprevdate char(10), arerev char(255), arerevdate char(10), busrev char(255), busrevdate char(10), conopordrev char(255), conopordrevdate char(10), deplrev char(255), deplrevdate char(10), ererev char(255), ererevdate char(10), maintrev char(255), maintrevdate char(10), ordrev char(255), ordrevdate char(10), pplrev char(255), pplrevdate char(10), progrev char(255), progrevdate char(10), techrev char(255), techrevdate char(10), odprev char(255), odprevdate char(10)) (select substring('04-001 | OSIP Requirements Management System',1,6) as projectid, substring('04-001 | OSIP Requirements Management System',10,length('04-001 | OSIP Requirements Management System')-9) as title, B."OBJECT ID" as nidid, C."OBJECT ID" as sonid, D."OBJECT ID" as conopid, E."OBJECT ID" as areid, F."OBJECT ID" as busid, G."OBJECT ID" as conopordid, H."OBJECT ID" as deplid, I."OBJECT ID" as ereid, J."OBJECT ID" as maintid, K."OBJECT ID" as ordid, L."OBJECT ID" as pplid, M."OBJECT ID" as progid, N."OBJECT ID" as techid, O."OBJECT ID" as odpid, B."DOCUMENT REVISION" as nidrev, date_format(B."DOCUMENT REVISION DATE",'%m/%d/%Y') as nidrevdate, C."DOCUMENT REVISION" as sonrev, date_format(C."DOCUMENT REVISION DATE",'%m/%d/%Y') as sonrevdate, D."DOCUMENT REVISION" as conoprev, date_format(D."DOCUMENT REVISION DATE",'%m/%d/%Y') as conoprevdate, E."DOCUMENT REVISION" as arerev, date_format(E."DOCUMENT REVISION DATE",'%m/%d/%Y') as arerevdate, F."DOCUMENT REVISION" as busrev, date_format(F."DOCUMENT REVISION DATE",'%m/%d/%Y') as busrevdate, G."DOCUMENT REVISION" as conopordrev, date_format(G."DOCUMENT REVISION DATE",'%m/%d/%Y') as conopordrevdate, H."DOCUMENT REVISION" as deplrev, date_format(H."DOCUMENT REVISION DATE",'%m/%d/%Y') as deplrevdate, I."DOCUMENT REVISION" as ererev, date_format(I."DOCUMENT REVISION DATE",'%m/%d/%Y') as ererevdate, J."DOCUMENT REVISION" as maintrev, date_format(J."DOCUMENT REVISION DATE",'%m/%d/%Y') as maintrevdate, K."DOCUMENT REVISION" as ordrev, date_format(K."DOCUMENT REVISION DATE",'%m/%d/%Y') as ordrevdate, L."DOCUMENT REVISION" as pplrev, date_format(L."DOCUMENT REVISION DATE",'%m/%d/%Y') as pplrevdate, M."DOCUMENT REVISION" as progrev, date_format(M."DOCUMENT REVISION DATE",'%m/%d/%Y') as progrevdate, N."DOCUMENT REVISION" as techrev, date_format(N."DOCUMENT REVISION DATE",'%m/%d/%Y') as techrevdate, O."DOCUMENT REVISION" as odprev, date_format(O."DOCUMENT REVISION DATE",'%m/%d/%Y') as odprevdate, temp3."TYPE OF NEED" as type_of_need, temp3."MISSION PRIORITY" as mission_priority, temp3."IWT LEAD" as iwt_lead, temp3."IWT MEMBERS" as iwt_members, temp3."GATE MEETING NOTES" as gate_meeting_notes, temp3."PROJECT ISSUES" as project_issues, temp3."PROJECT ACTIONS" as project_actions, temp3."REVIEW TEAM MEETING NOTES" as review_team_meeting_notes, temp3."OVERSIGHT BODY NOTES" as oversight_body_notes from "temp", temp3 left join "CORE DOCUMENTS1" B on (B."DOCUMENT TITLE" = temp."NID" and B."STATE" like 'Latest%' and B."STATUS"='Complete') left join "CORE DOCUMENTS1" C on (C."DOCUMENT TITLE" = temp."SON" and C."STATE" like 'Latest%' and C."STATUS"='Complete') left join "CORE DOCUMENTS1" D on (D."DOCUMENT TITLE" = temp."CONOP" and D."STATE" like 'Latest%' and D."STATUS"='Complete') left join "CORE DOCUMENTS1" E on (E."DOCUMENT TITLE" = temp."ARE" and E."STATE" like 'Latest%' and E."STATUS"='Complete') left join "CORE DOCUMENTS1" F on (F."DOCUMENT TITLE" = temp."BUS" and F."STATE" like 'Latest%' and F."STATUS"='Complete') left join "CORE DOCUMENTS1" G on (G."DOCUMENT TITLE" = temp."CONOPORD" and G."STATE" like 'Latest%' and G."STATUS"='Complete') left join "CORE DOCUMENTS1" H on (H."DOCUMENT TITLE" = temp."DEPL" and H."STATE" like 'Latest%' and H."STATUS"='Complete') left join "CORE DOCUMENTS1" I on (I."DOCUMENT TITLE" = temp."ERE" and I."STATE" like 'Latest%' and I."STATUS"='Complete') left join "CORE DOCUMENTS1" J on (J."DOCUMENT TITLE" = temp."MAINT" and J."STATE" like 'Latest%' and J."STATUS"='Complete') left join "CORE DOCUMENTS1" K on (K."DOCUMENT TITLE" = temp."ORD" and K."STATE" like 'Latest%' and K."STATUS"='Complete') left join "CORE DOCUMENTS1" L on (L."DOCUMENT TITLE" = temp."PPL" and L."STATE" like 'Latest%' and L."STATUS"='Complete') left join "CORE DOCUMENTS1" M on (M."DOCUMENT TITLE" = temp."PROG" and M."STATE" like 'Latest%' and M."STATUS"='Complete') left join "CORE DOCUMENTS1" N on (N."DOCUMENT TITLE" = temp."TECH" and N."STATE" like 'Latest%' and N."STATUS"='Complete') left join "CORE DOCUMENTS1" O on (O."DOCUMENT TITLE" = temp."ODP" and O."STATE" like 'Latest%' and O."STATUS"='Complete') where temp."tablename" = 'CORE DOCUMENTS1' and temp."NAME" = temp3."NAME" ) How to repeat: check sql format support/consistency from version 4.x to 5.x