Using stored View in SELECT query now loses ORDER BY sequence
Really hope someone can help me out, i've looked all over and can't seem to find any reasoning for the issue i'm seeing.
Basically, i've updated my development machine as it was running slow (OSX El Capitan) and installed latest mysql .dmg install (5.7.9), previously i was running (5.5.x).
The live server runs on CentOS (MySql 5.5.42) - and is fine.
However, when i'm coding for development machine i've noticed that SELECT queries which use a View as part of the source are no longer carrying through the default ORDER BY as specified in the View.
The SELECT query has no ORDER BY or GROUP BY statements so i'm not sure what's going on, and this all works fine on the live server (5.5.42).
Any ideas anyone? This has me stumped and i don't know if it's a bug within MySQL 5.7.9, or a change to how the engine works now.
I've added the view and query details below, and would be really grateful if anyone could offer some advice.
VIEW construct:-
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_wostages_open`
AS SELECT
`v_wo_open`.`WOID` AS `WOID`,
`v_wo_open`.`DropID` AS `DropID`,
`v_wo_open`.`FullWO` AS `FullWO`,
`v_wo_open`.`PurchaseOrder` AS `PurchaseOrder`,
`v_wo_open`.`CustomerInvRef` AS `CustomerInvRef`,
`v_wo_open`.`DrawingID` AS `DrawingID`,
`v_wo_open`.`CustomerInv` AS `CustomerInv`,
`v_wo_open`.`DrawingNumber` AS `DrawingNumber`,
`v_wo_open`.`PPS` AS `PPS`,
`v_wo_open`.`Route` AS `Route`,
`v_wo_open`.`QtyReq` AS `QtyReq`,
`v_wo_open`.`FormFactor` AS `FormFactor`,
`v_wo_open`.`DateReq` AS `DateReq`,
`v_wo_open`.`Est_WIPCompletion` AS `Est_WIPCompletion`,
`v_wo_open`.`QtyInWIP` AS `QtyInWIP`,
`v_wo_open`.`TravPrint` AS `TravPrint`,
`v_wo_open`.`Cur_WIPPerformance` AS `Cur_WIPPerformance`,
`v_wo_open`.`Awaiting` AS `Awaiting`,
`v_wo_open`.`Priority` AS `Priority`,
`v_wo_open`.`Planned` AS `Planned`,
`tblwodrops_stages`.`deptCode` AS `deptCode`,
`tblwodrops_stages`.`ProcessName` AS `ProcessName`,
`tblwodrops_stages`.`splitEntry` AS `splitEntry`,
`tblwodrops_stages`.`WIP` AS `WIP`,
`tblwodrops_stages`.`Available` AS `Available`,
`tblwodrops_stages`.`WODropStageID` AS `WODropStageID`,
`tblwodrops_stages`.`Processed` AS `Processed`,
`tblwodrops_stages`.`Losses` AS `Losses`,
`tblwodrops_stages`.`ProcessOrder` AS `ProcessOrder`,
`tblwodrops_stages`.`StartProcDate` AS `StartProcDate`,
`tblwodrops_stages`.`LastProcDate` AS `LastProcDate`,
`tblwodrops_stages`.`User` AS `User`,
`tblwodrops_stages`.`WIP_EstShts` AS `WIP_EstShts`,
`tblwodrops_stages`.`WIP_EstPcs` AS `WIP_EstPcs`,
`tblwodrops_stages`.`WIP_SpecialInstr` AS `WIP_SpecialInstr`,
`tblwodrops_stages`.`ChkInDate` AS `ChkInDate`,
`tblwodrops_stages`.`ChkOutDate` AS `ChkOutDate`,
`tblwodrops_stages`.`ChkInUser` AS `ChkInUser`,
`tblwodrops_stages`.`ChkOutUser` AS `ChkOutUser`,if((`tblwodrops_stages`.`FormFactor` = 'Pcs'),`tblwodrops_stages`.`Available`,(`tblwodrops_stages`.`Available` / `v_wo_open`.`PPS`)) AS `AvailableFF`,
`tblwodrops_stages`.`FormFactor` AS `StagesFormFactor`,if((`v_wo_open`.`Planned` <> 0),date_format(`tblwodrops_stages`.`WIP_EstDate`,'%Y-%m-%d'),date_format(`v_wo_open`.`DateReq`,'%Y-%m-%d')) AS `WIP_EstDate`,if((`tblwodrops_stages`.`WIP_EstDate` < curdate()),'red',if((`tblwodrops_stages`.`WIP_EstDate` = curdate()),'amber','green')) AS `TF_Display`
FROM (`v_wo_open` left join `tblwodrops_stages` on((`v_wo_open`.`DropID` = `tblwodrops_stages`.`DropID`))) where ((((`tblwodrops_stages`.`Processed` = 0) and (`tblwodrops_stages`.`Available` = 0)) or (`tblwodrops_stages`.`Available` > 0)) and ((`tblwodrops_stages`.`WIP` = 'Y') or (`tblwodrops_stages`.`splitEntry` <> 0)))
ORDER BY `v_wo_open`.`Priority` desc,if((`v_wo_open`.`Priority` > 0),0,`v_wo_open`.`Planned`),if((`v_wo_open`.`Planned` <> 0),`tblwodrops_stages`.`WIP_EstDate`,`v_wo_open`.`DateReq`),`tblwodrops_stages`.`WIP_SpecialInstr` desc;
SELECT Query:-
select v_wostages_open.*, tbldrawings.CurAW, tblvars.varcol7 as WIP_Dept
FROM v_wostages_open
LEFT JOIN tbldrawings ON v_wostages_open.drawingid = tbldrawings.drawingid
LEFT JOIN tblvars ON tblvars.VarCol1 = v_wostages_open.ProcessName
WHERE (WIP='y' AND Available > 0 AND ProcessName <> 'Load Batch') AND AvailableFF > 0 and tblvars.varGroup = 'processes' AND NOT ((Route LIKE 'LPS005%' OR Route LIKE 'LPSEMP%' OR Route = 'LPS007 LFS') AND Planned = 0);