[teiid-issues] [JBoss JIRA] (TEIID-4654) MSSQL pagination with select distinct

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4654?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-4654:
----------------------------------
          Component/s: JDBC Connector
        Fix Version/s: 9.2
    Affects Version/s: 9.0


So this is not quite a pagination case in that you are just applying a user limit and not an order by?

The full exception message that I'm seeing is "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."

It looks like we'll have to fall back on the row_number handling or use an inline view to allow this.

> MSSQL pagination with select distinct
> -------------------------------------
>
>                 Key: TEIID-4654
>                 URL: https://issues.jboss.org/browse/TEIID-4654
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 9.0
>            Reporter: Mark Tawk
>            Assignee: Steven Hawkins
>             Fix For: 9.2
>
>
> I'm using Teiid 9.0.0 with mssql translator on a sql server 2012 database
> It seems sql server 2012 does not allow pagination with row fetch when used in select distinct query; i'm getting the following exception:
> TEIID30504 dataModel_ptVDB: 145 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT DISTINCT g_0."Customer_ID" AS c_0, g_0."CalculatedField" AS c_1 FROM "data"."red4"."Customerview" g_0 ORDER BY @@version OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY]



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)