Wrong ordering of join clauses

2 posts, 0 answers
  1. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 27 Jun 2014 Link to this post

    Using NorthwindEF. For sake of brevity I'm using a meaningless query for reproducing the issue:
    var db = new NorthwindModel();
    var q1 = db.Products.AsQueryable();
    var q2 = db.Products.AsQueryable();
    short quant = 1;
    q1 = q1.Where(p =>
    (
        from y in db.Regions
        where y.RegionID == p.ProductID
        join od in db.OrderDetails on new { Quantity = quant, Id = p.ProductID, } equals new { od.Quantity, Id = od.OrderID, } into odg
        from od in odg.DefaultIfEmpty()
        where od.Discount > 0
        select 1
    ).Any());
    q2 = q2.Where(p =>
    (
        from y in db.Regions
        where y.RegionID == p.ProductID
        join od in db.OrderDetails on new { Quantity = quant, Id = p.ProductID, } equals new { od.Quantity, Id = od.OrderID, } into odg
        from od in odg.DefaultIfEmpty()
        where od.Discount > 0
        select 1
    ).Any());
     
    var q = from d1 in q1
            join d2 in q2 on d1.ProductID equals d2.ProductID
            select d1.ProductName + d2.ProductName;

    Side note: while q1 and q2 are essentially the same OA fails to generate the sql command if I use the same instance.
    The generated SQL is:
    SELECT DISTINCT a.[ProductName] AS COL1, d.[ProductName] AS COL2, 1, 1
    FROM [Products] a
    LEFT JOIN [Order Details] AS b ON (? = b.[Quantity] AND a.[ProductID] = b.[OrderID])
    LEFT JOIN [Order Details] AS c ON (? = c.[Quantity] AND d.[ProductID] = c.[OrderID])
    JOIN [Products] AS d ON (a.[ProductID] = d.[ProductID])
    CROSS JOIN [Region] AS e
    CROSS JOIN [Region] AS f
    WHERE e.[RegionID] = a.[ProductID]
    AND b.[Discount] > 0
    AND f.[RegionID] = d.[ProductID]
    AND c.[Discount] > 0

    The OrderDetails extents in the subquery are moved to the outer FROM clause but not in the right order: line 4 references alias 'd' declared in line 5. Ideally I'd expect the following SQL:
    SELECT DISTINCT a.[ProductName] AS COL1, d.[ProductName] AS COL2, 1, 1
    FROM [Products] a
    JOIN [Products] AS d ON (a.[ProductID] = d.[ProductID])
    WHERE EXISTS
    (
        SELECT 1
        FROM [Region] e
        LEFT JOIN [Order Details] AS b ON (? = b.[Quantity] AND a.[ProductID] = b.[ProductID])
        WHERE e.[RegionID] = a.[ProductID]
              AND b.[Discount] > 0
    )
    AND EXISTS
    (
        SELECT 1
        FROM [Region] f
        LEFT JOIN [Order Details] AS c ON (? = c.[Quantity] AND d.[ProductID] = c.[ProductID])
        WHERE f.[RegionID] = d.[ProductID]
              AND c.[Discount] > 0
    )

    with extents NOT moved outside to the top level join clause (also see my post here why this fails in some cases: http://www.telerik.com/forums/linq-subquery-errors), or if they must, then at least they should be in the proper order, like this:
    SELECT DISTINCT a.[ProductName] AS COL1, d.[ProductName] AS COL2, 1, 1
    FROM [Products] a
    LEFT JOIN [Order Details] AS b ON (? = b.[Quantity] AND a.[ProductID] = b.[OrderID])
    JOIN [Products] AS d ON (a.[ProductID] = d.[ProductID])
    LEFT JOIN [Order Details] AS c ON (? = c.[Quantity] AND d.[ProductID] = c.[OrderID])
    CROSS JOIN [Region] AS e
    CROSS JOIN [Region] AS f
    WHERE e.[RegionID] = a.[ProductID]
    AND b.[Discount] > 0
    AND f.[RegionID] = d.[ProductID]
    AND c.[Discount] > 0




  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 01 Jul 2014 Link to this post

    Hi Greg,

    with the correction made for the other forum post by you the queries will now processed correctly and produce the CROSS JOIN form. I've verified this and added a respective test.

    Regards,
    Thomas
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  3. DevCraft banner
Back to Top