Relating to this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=1514&HighLight=1

It looks like this issue is back. I know that our version is at least as new as that fix, as we have had other issues fixed that are working properly, so I don't think library versions are the issue.

Just to recap, I am creating a FieldCompareSetPredicate using my SelfServicing project. Like the previous post, I am specifying both maxNumberOfItemsToReturn and a RelationCollection. I am also specifying an order by clause (hence the maxNumberOfItemsToReturn), but the TOP statement is not being emitted in the SQL. Any ideas, Franz?

COuld you post your code which fails and also if possible the query which is generated ?

I'd prefer not to post that information yet, but I would be willing to email it to you.

If all you need to know is the overload I'm using and what is being passed, it's

SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareSetPredicate(IEntityFieldCore entityField, IEntityFieldCore setField, SetOperator operatorToUse, IPredicate filter, IRelationCollection relations, string objectAlias, long maxNumberOfItemsToReturn, ISortExpression sorter)

entityField is the field on my entity to match setField is the field on the subquery's table to select operatorToUse is In filter is a valid predicate for the subquery relations is a valid RelationCollection for the subquery objectAlias is string.Empty (there was no overload that did not require this parameter unless I gave up another parameter) maxNumberOfItemsToReturn is 5 sorter is a valid sort expression for the subquery

The resulting SQL is correct (ie all elements such as relations, table names, and clauses--other than select--are as they should be) except for the fact that TOP 5 is not included in the SQL.

You can mail the code snippet to support AT llblgen.com if you want to. I'll see if I can reproduce it with the info you provided here.

I'll mail it if you think you need it, but I don't think it's going to provide you with much if anything more than what I posted above.

What's the runtimeLibrary version you are using?

I can reproduce it with this query:

[Test]
public void FieldCompareSetTestWithRowLimit()
{
    RelationCollection relations = new RelationCollection();
    relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderId);
    PredicateExpression filter = new PredicateExpression(new FieldCompareSetPredicate(
        CustomerFields.CustomerId, OrderFields.CustomerId, SetOperator.In,
        (OrderDetailsFields.ProductId == 1), relations, string.Empty, 5, new SortExpression(OrderFields.OrderDate | SortOperator.Ascending)));
    CustomerCollection customers = new CustomerCollection();
    customers.GetMulti(filter);                                     
}

When I specify 1 as the limit, top is indeed emitted, when I specify 2 or higher it's not. Looking into it.

Ok, this is actually by design.

here we go * to be able have TOP in a query with relations, you have to have DISTINCT as well. Explanation: select TOP 5 c.* from customers c inner join orders o on c.customerid = o.customerid where c.country = 'germany'

this will give 5 duplicate rows, not 5 distinct customers. TOP is therefore useless in this query, or better: it doesn't give proper results. What's needed is DISTINCT, this query therefore gives the proper results: select DISTINCT TOP 5 c.* from customers c inner join orders o on c.customerid = o.customerid where c.country = 'germany'

The query mechanism switches to client side filtering if DISTINCT and TOP are required but DISTINCT can't be emitted. Though for a SUBquery that's not useful of course, though unavoidable.

• to be able to have DISTINCT in a query which contains an ORDER BY clause, the fields in the ORDER BY clause have to appear in the select list. Explanation: this is a requirement for sqlserver (and most other dbs as well, I'm not sure about mysql). Imagine this subquery, which is actually the query generated by my C# snippet above: (replaced the parameter with hard value, of course '1' was a parameter in the real query)

SELECT  [dbo].[Customers].[CustomerID] AS [CustomerId], 
    [dbo].[Customers].[CompanyName], 
    [dbo].[Customers].[ContactName], 
    [dbo].[Customers].[ContactTitle], 
    [dbo].[Customers].[Address], 
    [dbo].[Customers].[City], 
    [dbo].[Customers].[Region], 
    [dbo].[Customers].[PostalCode], 
    [dbo].[Customers].[Country],
    [dbo].[Customers].[Phone], 
    [dbo].[Customers].[Fax] 
FROM    [dbo].[Customers]  
WHERE 
(( 
    [dbo].[Customers].[CustomerID] IN 
    (   
        SELECT [dbo].[Orders].[CustomerID] AS [CustomerId] 
        FROM ( [dbo].[Orders]  INNER JOIN [dbo].[Order Details] 
            ON  [dbo].[Orders].[OrderID]=[dbo].[Order Details].[OrderID]) 
        WHERE [dbo].[Order Details].[ProductID] = 1 
        ORDER BY [dbo].[Orders].[OrderDate] ASC
    )
))

When I modify the subquery to:

SELECT DISTINCT TOP 5 [dbo].[Orders].[CustomerID] AS [CustomerId] 
FROM ( [dbo].[Orders]  INNER JOIN [dbo].[Order Details] 
    ON  [dbo].[Orders].[OrderID]=[dbo].[Order Details].[OrderID]) 
WHERE [dbo].[Order Details].[ProductID] = 1 
ORDER BY [dbo].[Orders].[OrderDate] ASC

it doesn't work, I get an error:

Server: Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

So in other words: it can't be done.

OK, I can buy that, and it COULD be done with another subselect. However, I am now having problems doing one without a RelationCollection. It is forcing a distinct to be emitted, which doesn't allow me to sort the list. Again, I could do another subselect to presort them, but that shouldn't be necessary if I'm not using any relations.

I can think of the following workaround:

Create a database view of the inner select, map it to an entity, use the Designer to define a relation between this entity and the original entity to fetch (Customers in the previous example)

Fetch Customers, joining the newly created Entity, and passing the filter you want.

Thanks, but I don't really consider creating a view for a single subselect to be a legitimate workaround. I'm looking more for insight into why distinct would be required in a circumstance where there are no relations and (consequently) no danger of a cartesian product.

Franz, do you have any insight here?

AdamRobinson wrote:

Thanks, but I don't really consider creating a view for a single subselect to be a legitimate workaround. I'm looking more for insight into why distinct would be required in a circumstance where there are no relations and (consequently) no danger of a cartesian product.

You specified relations, which caused the query engine to check whether TOP is useful. If you don't specify relations, the problem isn't there. So I'm not really sure if I understand what you're particular looking for...

In the second query I'm talking about, there are no relations (I am specifying null for the IRelationCollection parameter), but distinct is still included, which doesn't allow me to sort.

AdamRobinson wrote:

In the second query I'm talking about, there are no relations (I am specifying null for the IRelationCollection parameter), but distinct is still included, which doesn't allow me to sort.

You specified one query:

entityField is the field on my entity to match setField is the field on the subquery's table to select operatorToUse is In filter is a valid predicate for the subquery relations is a valid RelationCollection for the subquery objectAlias is string.Empty (there was no overload that did not require this parameter unless I gave up another parameter) maxNumberOfItemsToReturn is 5 sorter is a valid sort expression for the subquery

I then see a post where you have problems when you don't specify a relationcollection. What are the specifics for that query exactly?

A subquery allows duplicates if the limit is 0 or 1, and doesn't if it's higher than that. I think that causes the DISTINCT to appear in the subquery...

(edit): what I'm getting at is a query definition so I can reproduce it here. That's the most effective, otherwise I have to slip in 'guess mode' and that's often more time consuming...

public FieldCompareSetPredicate(SD.LLBLGen.Pro.ORMSupportClasses.IEntityField field, SD.LLBLGen.Pro.ORMSupportClasses.IEntityField setField, SD.LLBLGen.Pro.ORMSupportClasses.SetOperator operatorToUse, SD.LLBLGen.Pro.ORMSupportClasses.IPredicate filter, SD.LLBLGen.Pro.ORMSupportClasses.IRelationCollection relations, string objectAlias, long maxNumberOfItemsToReturn, SD.LLBLGen.Pro.ORMSupportClasses.ISortExpression sorter) Member of SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareSetPredicate

Parameters: field: field to compare to the set results setField: field to base the set on operatorToUse: SetOperator.In filter: my predicate relations: null objectAlias: string.Empty maxNumberOfItemsToReturn: 3 sorter: my descending sort expression

It indeed looks like a bug. The subquery creation call passes false for allowDuplicates solely based on the fact if a limit has been passed in, which isn't correct in all cases, like the one you suggest. As distinct isn't necessary in subquery results, it can be troubling when a special set of parameters are passed in, e.g. no relations, a sort on a different field in the entity.

I'll look into it.

Found a normal query which also fails.

/// <summary>
/// This test uses a sort which is outside the selectlist, and DISTINCT shouldn't be emitted into the query.
/// </summary>
[Test]
public void DynamicListWithMisplacedSortTest()
{
    ResultsetFields fields = new ResultsetFields(1);
    fields.DefineField(OrderFieldIndex.OrderId, 0, "OrderId");
    
    SortExpression sorter = new SortExpression(OrderFields.OrderDate | SortOperator.Descending);
    DataTable results = new DataTable();
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.FetchTypedList(fields, results, null, 5, sorter, false);
    }
    Assert.AreEqual(5, results.Rows.Count);
}

The error is in the DQE, which tests if relations AND sorters are specified. If so, it THEN tests if the order by fields are in the select list and bases the emitting of DISTINCT on that result. Because no relations are specified it never checks it and it thus goes wrong.

I'll fix this today and upload a new build.

A fix is now available. (runtime libs)

Thanks!