Bad SQL produced when selecting a field that was also used in the orderBy of a let subquery #280

Closed
CezarCretu opened this Issue Jul 17, 2014 · 3 comments

Projects

None yet

4 participants

@CezarCretu
Contributor

I've encoutered an error using the current version from master.
I have the following table:

[Table("orders", Schema = "public")]
public partial class Order
{
    public long Id { get; set; }

    [Required]
    public string Category { get; set; }

    public DateTime Timestamp { get; set; }
}

When executing this query (examples have been simplified):

from ordersGrouped in db.Orders.GroupBy(x => x.Category)
 let lastOrder = ordersGrouped.OrderByDescending(x => x.Timestamp)
                                                 .Select(x => x.Timestamp)
                                                  .FirstOrDefault()
 select new
                   {
                         LastOrderDate = lastOrder
                    }

I get an EntityCommandException ({"ERROR: 42804: CASE types integer and timestamp without time zone cannot be matched"}) (although the error has nothing to do with timestamp in particular, it's just that 1 gets selected every time instead of the actual value). The SQL produced is here.

Selecting the whole Order entity instead of just the timestamp works (can't even select the whole thing in the let part and then select just the timestamp in the projection, that would break it again), changing the query so that I order by one field and select another also works.

Another method that works is splitting the let in two parts, as such:

from ordersGrouped in db.Orders.GroupBy(x => x.Category)
 let categoryOrders = ordersGrouped.OrderByDescending(x => x.Timestamp)
 let lastOrder = categoryOrders.Select(x => x.Timestamp).FirstOrDefault()
 select new
              {
                     LastOrderDate = lastOrder
               }
@Emill
Member
Emill commented Jul 17, 2014

Thanks. I'll check that out.

The issue is a NewInstance(Column(Element(Limit(Sort(Project(...))))))

@Emill Emill added a commit to Emill/Npgsql that referenced this issue Jul 17, 2014
@Emill Emill Fix bug #280. A DbElementExpression can have a Limit(Sort(Project(...…
…))) as child expression. Then the column in the projection must be projected all the way up.
448346d
@Emill
Member
Emill commented Jul 17, 2014

Could you test #281 and see if it works?

@CezarCretu
Contributor

It works in both my simplified variants and the original query. Thank you.

@Emill Emill closed this Jul 21, 2014
@franciscojunior franciscojunior added this to the 2.2 milestone Jul 22, 2014
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment