MSSQL: fetch 0 rows doesn't work: Invalid usage of the option NEXT in the FETCH statement.

[gm42]

What version of Go are you using (go version)?

go version go1.9 linux/amd64

Which database and its version are you using?

MSSQL 2012+

What did you do?

Please provide a complete runnable program to reproduce your issue.

package main

import (
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mssql"
)

var db *gorm.DB

func init() {
    var err error
    db, err = gorm.Open("mssql", "sqlserver://gorm:LoremIpsum86@localhost:1433?database=gorm")
    if err != nil {
        panic(err)
    }
    db.LogMode(true)
}

type Result struct {
    TableCatalog string
    TableSchema string
    TableName string
    TableType string
}

// change this to 1 to see error go away
const nRows = 0

func main() {
     var result Result
     err := db.Raw(fmt.Sprintf("SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME DESC OFFSET %d ROWS FETCH NEXT 0 ROWS ONLY", nRows)).Scan(&result).Error    
    if err != nil {
        panic(err)
    } else {
        fmt.Println("success")
    }
}

Rationale

It is not possible to fetch 0 rows from what I can see with MSSQL 2012 and 2014. The fix to #1205 is not enough, apparently.

If you try to fetch 1 row, no error is returned.

The error is:

Invalid usage of the option NEXT in the FETCH statement.

If you try with MSSQL 2014+ instead:

The number of rows provided for a FETCH clause must be greater then zero.

(Test here: http://rextester.com/BSLOL96705)

[gm42]

See also: https://github.com/sequelize/sequelize/issues/4404

The problem seems to be that an ORDER BY is always necessary. Also, this functionality (FETCH) is specific to recent versions of MSSQL.

See also: https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx#Anchor_2

[jinzhu]

Sorry for this, but only MSSQL 2017+ are officially supported, as this is the only version that supports docker image and could be tested with our CI https://app.wercker.com/Jinzhu/gorm/runs

Thank you for your report.

[gm42]

For the records, this error can also be caused by invalid escape sequences.