System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 0a32885109ae9460e38faf34aa7d3383c54077ca
Title: IQueryable.Concat() throws "ORDER BY clause should come after UNION ALL not before"
Status: Closed Type: Code_Defect
Severity: Minor Priority: Blocker
Subsystem: LINQ Resolution: Fixed
Last Modified: 2015-03-04 19:27:12
Version Found In: 1.0.94
User Comments:
anonymous added on 2014-11-04 09:14:01:

I try concatenating 2 queries:

var files1 = context.Files.Where(f => f.ParentFolder.SubFolders.Any()).OrderByDescending(f => f.Size).Take(10); var files2 = context.Files.Where(f => !f.ParentFolder.SubFolders.Any()).OrderByDescending(f => f.Size).Take(10); foreach(var file in files1.Concat(files2)) [...]

Throws SQLiteException: SQL logic error or missing database ORDER BY clause should come after UNION ALL not before

at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) at System.Data.SQLite.SQLiteCommand.BuildNextCommand() at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

Since I'm only interrested of the 10 largest files of both groups I see no chance in reordering my query.

But I have to admit that for this query its not important that SQLite is doing the concatenation so in this case a fallback to IEnumerable works well: foreach(var file in files1.AsEnumerable().Concat(files2)) [...]


mistachkin added on 2014-11-05 19:00:00:
Blocking 1.0.95.0 until the root cause is known.

mistachkin added on 2014-11-08 02:14:30:
Do you have an example schema (and entity model) that demonstrates the issue?

mistachkin added on 2014-11-08 02:24:55:
This is also an issue with the LIMIT and OFFSET clauses.

mistachkin added on 2014-11-08 03:50:57:
I was able to reproduce the issue locally.

mistachkin added on 2014-11-08 06:44:16:
There is now a candidate fix for this issue on the tkt-0a32885109 branch:

  [/timeline?r=tkt-0a32885109]

Basically, the fix is to disallow the left SELECT statement of a compound
operator from having an ORDER BY, LIMIT, or OFFSET clause.

Please let us know if this clears up the issue for you.