Description:
When trying to left join two views together you do not get accurate results back. In the right table I do not see any null values. All I get back is what seems to be an actual inner join but no left join. I have listed the two view creations below along with the third view that does the left join. This is a workaround as their are no subqueries allowed in the from clause for views at this time.
CREATE ALGORITHM=UNDEFINED DEFINER=`josh`@`%` SQL SECURITY DEFINER VIEW `bidlinelistview` AS
select
`bid`.`BidId` AS `BidId`,
`bid`.`BidDate` AS `BidDate`,
`product`.`Name` AS `ProductName`,
`product`.`Price` AS `Price`,
`producttype`.`Name` AS `ProductTypeName`,
`edgetreatment`.`Name` AS `EdgeName`,
`edgetreatment`.`Price` AS `EdgePrice`,
`edgetreatment`.`Length` AS `Length`,
`bidlineitem`.`Quantity` AS `Quantity`,
`bidlineitem`.`Comments` AS `Comments`,
`bidlineitem`.`Seams` AS `Seams`,
`bidlineitem`.`BowlCutouts` AS `BowlCutouts`,
`bidlineitem`.`ApronCutouts` AS `ApronCutouts`,
`bidlineitem`.`EdgeLength` AS `EdgeLength`,
`bidlineitem`.`BidLineItemId` AS `BidLineItemId`,
`bidlineitem`.`Floor` AS `Floor`,
`bidlineitem`.`WorkOrder` AS `WorkOrder`,
`edgetreatment`.`EdgeTreatmentId` AS `EdgeTreatmentId`,
`product`.`ProductTypeId` AS `ProductTypeId`,
`product`.`ProductId` AS `ProductId`
from
((((`bidlineitem` join `bid` on((`bidlineitem`.`BidId` = `bid`.`BidId`))) join `product` on((`bidlineitem`.`ProductId` = `product`.`ProductId`))) join `producttype` on((`product`.`ProductTypeId` = `producttype`.`ProductTypeId`))) join `edgetreatment` on((`bidlineitem`.`EdgeTreatmentId` = `edgetreatment`.`EdgeTreatmentId`)));
CREATE ALGORITHM=UNDEFINED DEFINER=`josh`@`%` SQL SECURITY DEFINER VIEW `bidlinecolorview` AS
select
`thickness`.`Value` AS `ThickValue`,
`color`.`Name` AS `ColorName`,
`colorgroup`.`Name` AS `ColorGroupName`,
`productcolor`.`Price` AS `ColorPrice`,
`productcolor`.`Entered` AS `Entered`,
`bidlinecolor`.`BidLineItemId` AS `BidLineItemId`,
`productcolor`.`ColorId` AS `ColorId`,
`productcolor`.`ProductColorId` AS `ProductColorId`
from
(((((`productcolor` join `color` on((`productcolor`.`ColorId` = `color`.`ColorId`))) join `colorgroup` on((`productcolor`.`ColorGroupId` = `colorgroup`.`ColorGroupId`))) join `thickness` on((`productcolor`.`ThicknessId` = `thickness`.`ThicknessId`))) join `bidlinecolor` on((`productcolor`.`ProductColorId` = `bidlinecolor`.`ProductColorId`))) join `product` on((`productcolor`.`ProductId` = `product`.`ProductId`)));
CREATE ALGORITHM=UNDEFINED DEFINER=`josh`@`%` SQL SECURITY DEFINER VIEW `bidlineview` AS (
select
`bidlinelistview`.`BidId` AS `BidId`,
`bidlinelistview`.`BidDate` AS `BidDate`,
`bidlinelistview`.`ProductName` AS `ProductName`,
`bidlinelistview`.`Price` AS `Price`,
`bidlinelistview`.`ProductTypeName` AS `ProductTypeName`,
`bidlinelistview`.`EdgeName` AS `EdgeName`,
`bidlinelistview`.`EdgePrice` AS `EdgePrice`,
`bidlinelistview`.`Length` AS `Length`,
`bidlinelistview`.`Quantity` AS `Quantity`,
`bidlinelistview`.`Comments` AS `Comments`,
`bidlinelistview`.`Seams` AS `Seams`,
`bidlinelistview`.`BowlCutouts` AS `BowlCutouts`,
`bidlinelistview`.`ApronCutouts` AS `ApronCutouts`,
`bidlinelistview`.`EdgeLength` AS `EdgeLength`,
`bidlinelistview`.`BidLineItemId` AS `BidLineItemId`,
`bidlinelistview`.`Floor` AS `Floor`,
`bidlinelistview`.`WorkOrder` AS `WorkOrder`,
`bidlinelistview`.`EdgeTreatmentId` AS `EdgeTreatmentId`,
`bidlinelistview`.`ProductTypeId` AS `ProductTypeId`,
`bidlinelistview`.`ProductId` AS `ProductId`,
`bidlinecolorview`.`ThickValue` AS `ThickValue`,
`bidlinecolorview`.`ColorName` AS `ColorName`,
`bidlinecolorview`.`ColorGroupName` AS `ColorGroupName`,
`bidlinecolorview`.`ColorPrice` AS `ColorPrice`,
`bidlinecolorview`.`Entered` AS `Entered`,
`bidlinecolorview`.`ColorId` AS `ColorId`,
`bidlinecolorview`.`ProductColorId` AS `ProductColorId`
from
(`bidlinelistview` left join `bidlinecolorview` on((`bidlinelistview`.`BidLineItemId` = `bidlinecolorview`.`BidLineItemId`))));
How to repeat:
Create the tables listed above with data and then join them together and it doesn't work
Suggested fix:
allow subqueries in the from clause for views.