[
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)
