On Monday, March 13, 2017 at 7:55:45 AM UTC-7, [email protected] 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.
