Invalid query with NTEXT columns

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

    Posted 09 Dec 2014 Link to this post

    Using the Northwind schema the following query (just for sake of simplicity I reuse the same tables in the query, my production query is much more complex):
    using( var db = new NorthwindEntities() )
    {
        var q1 = db.Categories.Where(p => db.Regions.Any(r => r.RegionID == p.CategoryID));
        var q2 = db.Categories.Where(p => db.Regions.Any(r => r.RegionID == p.CategoryID));
        var q = from p1 in q1
              join p2 in q2 on p1.CategoryID equals p2.CategoryID
              select new { Desc1 = p1.Description, Desc2 = p2.Description };
        var res = q.ToList();
    }
    generates the following SQL:
    SELECT DISTINCT a.[Description] AS COL1, b.[Description] AS COL2, c.[RegionID], d.[RegionID]
    FROM [Categories] a
    JOIN [Categories] AS b ON (a.[CategoryID] = b.[CategoryID])
    CROSS JOIN [Region] AS c
    CROSS JOIN [Region] AS d
    WHERE c.[RegionID] = a.[CategoryID] AND d.[RegionID] = b.[CategoryID]
    however this fails because the select list contains an NTEXT column which cannot be used with DISTINCT (MSSQL: error 421, Oracle: ORA-00932). I'd expect the following query to be generated:
    SELECT a.Description, b.Description
    FROM
    (
       SELECT *
       FROM Categories c
       WHERE EXISTS (SELECT 1 FROM Region r WHERE r.RegionID = c.CategoryID)
    ) a
    INNER JOIN
    (
       SELECT *
       FROM Categories c
       WHERE EXISTS (SELECT 1 FROM Region r WHERE r.RegionID = c.CategoryID)
    ) b
    ON a.CategoryID=b.CategoryID
    A possible workaround is to replace Any() with Count()>0 however this still generates a semantically invalid query if q1 and q2 are left-joined. I've already described this latter issue in http://www.telerik.com/forums/wrong-sql-generated-with-joined-subqueries
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 11 Dec 2014 Link to this post

    Hi Greg,

    thanks for reporting this issue with us. There is unfortunately no easy fix, just a very specific work-around to prevent this issue appearing in the application:

     var q1 = db.Categoris.Where(p => db.Regions.Any(r => r.Id == p.Id) || (1==0));
     var q2 = db.Categoris.Where(p => db.Regions.Any(r => r.Id == p.Id) || (1==0));
     var q = from p1 in q1
                  join p2 in q2 on p1.Id equals p2.Id
                  select new { Desc1 = p1.Description, Desc2 = p2.Description };
     var res = q.ToList();

    By appending the useless (1==0) condition the internal representation is shifted to an OR node and that's why the runtime does not append the distinct any longer. 

    I will file a bug report for this; thanks for reporting.

    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