thenBy on optional column blows up

[psfblair]

The query expression:

    query { 
        for calendarEntry in dataContext.``[main].[calendar_entries]`` do
        where (calendarEntry.timestamp.Value >= todayTimestamp)
        sortBy calendarEntry.timestamp
        thenBy calendarEntry.title
        select calendarEntry  } |> Seq.toList

Note that both timestamp and title are optional columns.

The result:

Unhandled Exception: System.Exception: CreateQuery, e = value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlOrderedQueryable`1[FSharp.Data.Sql.Common.SqlEntity]).ThenBy(calendarEntry => calendarEntry.GetColumnOption("title"))
at FSharp.Data.Sql.Runtime.QueryImplementation+-cctor@112-1.Invoke (System.String message) [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.PrintfImpl+StringPrintfEnv`1[System.Linq.IQueryable].Finalize () [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.PrintfImpl+Final1@224[Microsoft.FSharp.Core.Unit,System.String,System.Linq.IQueryable,System.Linq.Expressions.Expression].Invoke (Microsoft.FSharp.Core.FSharpFunc`2 env, System.Linq.Expressions.Expression a) [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.OptimizedClosures+Invoke@3028[System.Linq.Expressions.Expression,System.Linq.IQueryable,Microsoft.FSharp.Core.FSharpFunc`2[Microsoft.FSharp.Core.Unit,Microsoft.FSharp.Core.PrintfImpl+PrintfEnv`3[Microsoft.FSharp.Core.Unit,System.String,System.Linq.IQueryable]]].Invoke (System.Linq.Expressions.Expression u) [0x00000] in <filename unknown>:0 
at FSharp.Data.Sql.Runtime.QueryImplementation+-cctor@111.System-Linq-IQueryProvider-CreateQuery (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
at System.Linq.Queryable.ThenBy[SqlEntity,FSharpOption`1] (IOrderedQueryable`1 source, System.Linq.Expressions.Expression`1 keySelector) [0x00000] in <filename unknown>:0 
at (wrapper dynamic-method) object:lambda_method (System.Runtime.CompilerServices.Closure,Microsoft.FSharp.Core.Unit)
at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&)
at System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00000] in <filename unknown>:0 
[ERROR] FATAL UNHANDLED EXCEPTION: System.Exception: CreateQuery, e = value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlOrderedQueryable`1[FSharp.Data.Sql.Common.SqlEntity]).ThenBy(calendarEntry => calendarEntry.GetColumnOption("title"))
at FSharp.Data.Sql.Runtime.QueryImplementation+-cctor@112-1.Invoke (System.String message) [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.PrintfImpl+StringPrintfEnv`1[System.Linq.IQueryable].Finalize () [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.PrintfImpl+Final1@224[Microsoft.FSharp.Core.Unit,System.String,System.Linq.IQueryable,System.Linq.Expressions.Expression].Invoke (Microsoft.FSharp.Core.FSharpFunc`2 env, System.Linq.Expressions.Expression a) [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.OptimizedClosures+Invoke@3028[System.Linq.Expressions.Expression,System.Linq.IQueryable,Microsoft.FSharp.Core.FSharpFunc`2[Microsoft.FSharp.Core.Unit,Microsoft.FSharp.Core.PrintfImpl+PrintfEnv`3[Microsoft.FSharp.Core.Unit,System.String,System.Linq.IQueryable]]].Invoke (System.Linq.Expressions.Expression u) [0x00000] in <filename unknown>:0 
at FSharp.Data.Sql.Runtime.QueryImplementation+-cctor@111.System-Linq-IQueryProvider-CreateQuery (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
at System.Linq.Queryable.ThenBy[SqlEntity,FSharpOption`1] (IOrderedQueryable`1 source, System.Linq.Expressions.Expression`1 keySelector) [0x00000] in <filename unknown>:0 
at (wrapper dynamic-method) object:lambda_method (System.Runtime.CompilerServices.Closure,Microsoft.FSharp.Core.Unit)
at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&)
at System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00000] in <filename unknown>:0 

[pezipink]

I cannot replicate this in the latest version since the big fixes for #75, do you have the latest? I might need a schema to replicate this. What database vendor are you using? I have tried your query using optional values and it is working fine, both with and without using .Value on the sortBy / thenBy.

It appears from the stack trace that is isn't even getting as far as attempting to generate SQL, and it is very odd to see Microsoft.FSharp.Core.PrintfImpl+StringPrintfEnv`1 in there ?

Do you have the same issue if you replace thenBy with sortBy? (you don't have to use thenBy, it makes no difference in the SQL provider). Does it work ok with just one? Do the option types make a difference?

I see you are using Mono, perhaps it is platform dependent, I will try and replicate on a mono build later today.

[psfblair]

Just tried with the latest. It works with a second sortBy, but not with thenBy. Here is the SQLite table:

CREATE TABLE calendar_entries(
    id INTEGER PRIMARY KEY NOT NULL,
    date TEXT,
    time TEXT,
    timestamp INTEGER, 
    title TEXT,
    location TEXT,
    description TEXT,
    rsvp TEXT,
    email INTEGER NOT NULL REFERENCES emails (id)
);

If you need the parent table, it's:

CREATE TABLE emails(
    id INTEGER PRIMARY KEY NOT NULL,
    date TEXT,
    timestamp INTEGER, 
    sender TEXT,
    intro TEXT
);

This query passes:

let rows =  query { 
    for calendarEntry in dataContext.``[main].[calendar_entries]`` do
    sortBy calendarEntry.timestamp
    sortBy calendarEntry.title
    select calendarEntry  } |> Seq.toList

This query fails with the exception given above:

let rows =  query { 
    for calendarEntry in dataContext.``[main].[calendar_entries]`` do
    sortBy calendarEntry.timestamp
    sortBy calendarEntry.title
    select calendarEntry  } |> Seq.toList

[pezipink]

your queries are identical, but I'll assume the second one is supposed to have a thenBy after the sortBy :) I will see if i can replicate this tomorrow, apologies for the inconvenience and thanks for the report.

[pezipink]

I am unable to replicate this in .NET, it is working just fine for me. I do not have immediate access to a mono system, and as there is an easy workaround I will postpone looking at this until the weekend, if i have some time then. Thanks :)

[pezipink]

(unless someone else would like to look at this!)

[colinbull]

Closing this as I believe it is fixed on Mono now, certainly on the v1 branch anyway.