Thanks Jeremy very much, I saw your change in github. And I will wait for the new release.
Guoxun 在 2017年3月13日星期一 UTC+8下午11:13:33，Jeremy Evans写道： > > On Monday, March 13, 2017 at 7:55:45 AM UTC-7, [email protected] > <javascript:> wrote: >> >> Hi, >> >> I am facing an odd issue when I try to support MSSQL(tinytds) for one >> existing app. From the log, it looks like paginate (or order_by) put its >> clause to the wrong place and make the query fail. Here is the snippet of >> source code and the output: >> >> puts "#{ds.sql}" >> # SELECT [USERS].* FROM [USERS] INNER JOIN [SPACES_DEVELOPERS] ON >> ([SPACES_DEVELOPERS].[USER_ID] = [USERS].[ID]) WHERE >> ([SPACES_DEVELOPERS].[SPACE_ID] = 1) UNION SELECT [USERS].* FROM [USERS] >> INNER JOIN [SPACES_MANAGERS] ON ([SPACES_MANAGERS].[USER_ID] = >> [USERS].[ID]) WHERE ([SPACES_MANAGERS].[SPACE_ID] = 1) UNION SELECT >> [USERS].* FROM [USERS] INNER JOIN [SPACES_AUDITORS] ON >> ([SPACES_AUDITORS].[USER_ID] = [USERS].[ID]) WHERE >> ([SPACES_AUDITORS].[SPACE_ID] = 1) >> >> paginated_dataset = ds.extension(:pagination).paginate(page, >> page_size) >> >> puts "#{paginated_dataset .sql}" >> # SELECT [USERS].* FROM [USERS] INNER JOIN [SPACES_DEVELOPERS] ON >> ([SPACES_DEVELOPERS].[USER_ID] = [USERS].[ID]) WHERE >> ([SPACES_DEVELOPERS].[SPACE_ID] = 1) ORDER BY 1 OFFSET 0 ROWS FETCH NEXT >> 1 ROWS ONLY UNION SELECT [USERS].* FROM [USERS] INNER JOIN >> [SPACES_MANAGERS] ON ([SPACES_MANAGERS].[USER_ID] = [USERS].[ID]) WHERE >> ([SPACES_MANAGERS].[SPACE_ID] = 1) UNION SELECT [USERS].* FROM [USERS] >> INNER JOIN [SPACES_AUDITORS] ON ([SPACES_AUDITORS].[USER_ID] = >> [USERS].[ID]) WHERE ([SPACES_AUDITORS].[SPACE_ID] = 1) >> >> Originally, the app uses MySQL, the sql clause of paginated_dataset is >> "SELECT ... UNION ... INNER JOIN ... UNION ... ORDER BY ... LIMIT 1 >> OFFSET 0", which is right. >> but in tinytds the cluase is "SELECT ... ORDER BY 1 OFFSET 0 ROWS FETCH >> NEXT 1 ROWS ONLY UNION ... UNION ...", which is not a valid clause for >> sql server because "ORDER BY ..." clauses are in the wrong place. "ORDERY >> BY ..." should be at the end of the clause as my understanding. >> >> Do you have any idea about this error, and help me out? >> > > On MySQL, compounds (UNION/INTERSECT/EXTRACT) are placed before the ORDER > BY, on MSSQL, they come after. This appears to be a bug. I'm guessing > other Sequel users have not hit this issue because Sequel's default > behavior for compounds is to wrap them in a subselect. I'll fix this > shortly. Thanks for the report! > > Jeremy > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
