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?
Thanks,
Guoxun
--
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.
