Bug #16592 Left Join On Views
Submitted: 18 Jan 2006 2:50 Modified: 18 Feb 2006 13:53
Reporter: Joshua Wilson Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.18-standard OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[18 Jan 2006 2:50] Joshua Wilson
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.
[18 Jan 2006 13:53] Valeriy Kravchuk
Thank you for a problem report. Please, add the SHOW CREATE TABLE results for the base tables used in your views and some test data to show the problem. You may upload a dump of your test data using the File tab.

By the way, are you sure you are interpreting LEFT JOIN results in a proper way? Please, reread the manual on this topic. Describe the results you supposed to get, as well the real results you are getting.
[19 Feb 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".