Closed
Description
Wrong SQL query
This F# query:
let intervals = query {
for x in db.GetTable<Operation>() do
select ("username", // Tuple
Sql.DateDiff(
Sql.DateParts.Minute,
Nullable (Sql.Ext
.Lag(x.time, Sql.Nulls.None)
.Over()
.PartitionBy(x.user)
.OrderBy(x.time)
.ToValue()),
Nullable x.time)) }
query {
for (u,i) in intervals do
where (5 <? i && i ?<= 20)
count }produces
SELECT
DateDiff(minute, LAG([x].[eventTime]) OVER(PARTITION BY [x].[eventUser]
ORDER BY [x].[eventTime]), [x].[eventTime])
FROM
[Process_IndexedEvents] [x]
Query Execution Time (AfterExecute): 00:00:00.7769836
Total Execution Time (Completed): 00:00:01.4955837. Rows Count: 25140.Rows Count: 25140 - means second query is executed locally?
The difference between (AfterExecute): 00:00:00.7769836 and (Completed): 00:00:01.4955837 is a time to transmit 25140 rows back from server?
Expression test code generated:
//---------------------------------------------------------------------------------------------------
// This code was generated by LinqToDB.
//---------------------------------------------------------------------------------------------------
using System;
using System.Linq;
using System.Linq.Expressions;
using LinqToDB;
using NUnit.Framework;
namespace T10.T11
{
[System.SerializableAttribute()]
[LinqToDB.Mapping.TableAttribute("Process_IndexedEvents")]
[Microsoft.FSharp.Core.CompilationMappingAttribute((Microsoft.FSharp.Core.SourceConstructFlags)2)]
class T0 : IEquatable<T10.T11.T24>, System.Collections.IStructuralEquatable
{
[System.ComponentModel.DescriptionAttribute("eventTime")]
[LinqToDB.Mapping.ColumnAttribute("eventTime")]
[LinqToDB.Mapping.NotNullAttribute()]
[Microsoft.FSharp.Core.CompilationMappingAttribute((Microsoft.FSharp.Core.SourceConstructFlags)4, (Int32)3)]
public DateTime P22 { get; set; }
[System.ComponentModel.DescriptionAttribute("eventUser")]
[LinqToDB.Mapping.ColumnAttribute("eventUser")]
[LinqToDB.Mapping.NotNullAttribute()]
[Microsoft.FSharp.Core.CompilationMappingAttribute((Microsoft.FSharp.Core.SourceConstructFlags)4, (Int32)4)]
public string P23 { get; set; }
}
}
namespace Tests.UserTests
{
[TestFixture]
public class UserTest : TestBase
{
[Test, DataContextSource]
public void Test(string context)
{
// Table([Process_Indep25edEvents]).Select(p25 => new AnonymousObject`2(Item1 = p25.p5, Item2 = DateDiff(Minute, Convert(Sql.Ep25t.Lag(p25.p4, None).Over().PartitionBy(new [] {p25.p5}).OrderBy(p25.p4).ToValue()), Convert(p25.p4))))
using (var db = GetDataContext(context))
{
var quey = db.GetTable<T10.T11.T24>()
.Select(
p25 => new Microsoft.FSharp.Linq.RuntimeHelpers.AnonymousObject<string,int?>(p25.P23, LinqToDB.Sql.DateDiff(
Minute,
(DateTime?).Ext
.Lag<DateTime>(
p25.P22,
None).Over().PartitionBy(
new [] { p25.P23 }).OrderBy<DateTime>(
p25.P22).ToValue(),
(DateTime?)p25.P22)));
}
}
}
}Correct SQL query
With no tuple everything is fine:
let intervals = query {
for x in db.GetTable<Operation>() do
select (Sql.DateDiff( // No Tuple
Sql.DateParts.Minute,
Nullable (Sql.Ext
.Lag(x.time, Sql.Nulls.None)
.Over()
.PartitionBy(x.user)
.OrderBy(x.time)
.ToValue()),
Nullable x.time)) }
query {
for i in intervals do
where (5 <? i && i ?<= 20)
count }produces
SELECT
Count(*)
FROM
(
SELECT
DateDiff(minute, LAG([x].[eventTime]) OVER(PARTITION BY [x].[eventUser]
ORDER BY [x].[eventTime]), [x].[eventTime]) as [c1]
FROM
[Process_IndexedEvents] [x]
) [i]
WHERE
5 < [i].[c1] AND [i].[c1] <= 20
Query Execution Time (AfterExecute): 00:00:01.8216351
Total Execution Time (Completed): 00:00:01.8219922. Rows Count: 1.Almost no difference between AfterExecute and Completed means everything was executed on server?
Reason
I guess based on my old ticket #1763
We have to force subqueries creation when analytic functions are in projection. Where clause is one of the way to do that.
This might be the issue?
Do any workaround exist?