Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Column name ending with "From" generates invalid SQL #250

Closed
corydeppen opened this Issue Dec 16, 2012 · 1 comment

Comments

Projects
None yet
2 participants

I've noticed something odd when I try to return an ordered list from a query that contains a column name ending with "From" (e.g. ActiveFrom). I've included the SQL that is generated when I try to sort on the ActiveFrom column.

WITH __Data AS (
    SELECT [dbo].[PromoPosts].[Id],[dbo].[PromoPosts].[ActiveFrom],[dbo].[PromoPosts].[ActiveTo],[dbo].[PromoPosts].[Created],[dbo].[PromoPosts].[Updated] 
    from [dbo].[PromoPosts] 
    ORDER BY [dbo].[PromoPosts].[Active, ROW_NUMBER() OVER(ORDER BY [dbo].[PromoPosts].[Id]) AS [_#_]
From])
SELECT [Id],[ActiveFrom],[ActiveTo],[Created],[Updated],[Active FROM __Data WHERE [_#_] BETWEEN 1 AND 25

It seems like it's parsing the From and injecting SQL in its place. When I sort on a different column that does not end with "From" the query executes as expected. This throw an exception "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. Incorrect syntax near the keyword 'BETWEEN'."

The following is what the generated SQL looks like when sorting on the ActiveTo column.

WITH __Data AS (
    SELECT [dbo].[PromoPosts].[Id],[dbo].[PromoPosts].[ActiveFrom],[dbo].[PromoPosts].[ActiveTo],[dbo].[PromoPosts].[Created],[dbo].[PromoPosts].[Updated],ROW_NUMBER() OVER(ORDER BY [dbo].[PromoPosts].[ActiveTo]) AS [_#_]
    from [dbo].[PromoPosts])
SELECT [Id],[ActiveFrom],[ActiveTo],[Created],[Updated]
FROM __Data 
WHERE [_#_] BETWEEN 1 AND 25
Owner

markrendle commented Dec 16, 2012

Looks like a problem in the SQL query paging code. I'll get it fixed.

@ghost ghost assigned markrendle Jan 23, 2013

@markrendle markrendle added a commit that referenced this issue May 21, 2013

@markrendle markrendle Fixes issue #250 98e303e

@markrendle markrendle closed this May 21, 2013

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment