[jira] [Commented] (CALCITE-3344) In JDBC adapter, generate "SELECT TOP(n)" for MSSQL 2008 and earlier, and for Sybase ASE

["Erin Drummond (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3344?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16984656#comment-16984656 ] 

Erin Drummond commented on CALCITE-3344:
----------------------------------------

Apologies if this is the wrong place to post this - but this fix does not cover one edge case. It is possible for the following to be true:
 * There is a Microsoft SQL Server 2012 _server_ (which supports FETCH and OFFSET)
 * It contains a database that is only set at the SQL Server 2008 [compatibility level|https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15] (compatibility level 10)

In this situation, the MssqlSqlDialect class is reported a databaseMajorVersion() of 11 which indicates that FETCH and OFFSET are supported - however attempting to query the database that is of the SQL Server 2008 compatibility level using a query like 'select * from table limit 10' results in an error as Calcite attempts to use FETCH instead of TOP

I'm not sure what the best way to solve this is - configuration hints to the MssqlServerDialect? Or should it execute a query to determine the compatibility level of the database being queried instead of relying on the databaseMajorVersion?

I know you'll say "why cant you just change the compatibility level" - unfortunately i'm in an "Enterprise" environment with incumbent IT overlords that refuse to change anything

> In JDBC adapter, generate "SELECT TOP(n)" for MSSQL 2008 and earlier, and for Sybase ASE
> ----------------------------------------------------------------------------------------
>
>                 Key: CALCITE-3344
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3344
>             Project: Calcite
>          Issue Type: Bug
>          Components: jdbc-adapter
>            Reporter: Julian Hyde
>            Assignee: Julian Hyde
>            Priority: Major
>             Fix For: 1.22.0
>
>
> In JDBC adapter, generate "SELECT TOP\(n)" for Microsoft SQL Server (MSSQL) 2008 and earlier, because it doesn't support "FETCH".
> For MSSQL 2012 and higher, continue to generate "FETCH NEXT n ROWS".
> I have started work here: [https://github.com/julianhyde/calcite/tree/3344-top-n]



--
This message was sent by Atlassian Jira
(v8.3.4#803005)