Legacy MsSQL - legacy offset select sql command bug #2446

New Issue

suwro · 2024-04-22T16:14:43Z

suwro commented

2024-04-22 16:14:43 +00:00

Recently all my sql query eroors - because sql server is 2003 and won't be able to upgrade-it.

is fine just my mssql understands TOP 1 instead

OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

so I had to use LEGACY OFFSET

orm, err = xorm.NewEngineWithParams(" mssql", u.String(), map[string]string{"USE_LEGACY_LIMIT_OFFSET": "true"})
orm.ShowSQL(true)

_, err = orm.Table("tmp").Select("a,b").Desc("b").Get(&t1)

The generated/executed query is

SELECT TOP 1 FROM tmp ORDER BY [b] DESC

It seems the select part is missed when legacy limit offset is set to true!

it should be SELECT TOP 1 a,b FROM ...

Recently all my sql query eroors - because sql server is 2003 and won't be able to upgrade-it. is fine just my mssql understands **TOP 1** instead **OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY** so I had to use LEGACY OFFSET ``` orm, err = xorm.NewEngineWithParams(" mssql", u.String(), map[string]string{"USE_LEGACY_LIMIT_OFFSET": "true"}) orm.ShowSQL(true) _, err = orm.Table("tmp").Select("a,b").Desc("b").Get(&t1) ``` The generated/executed query is ```SELECT TOP 1 FROM tmp ORDER BY [b] DESC``` It seems the select part is missed when legacy limit offset is set to true! it should be ``` SELECT TOP 1 a,b FROM ...```

suwro commented

2024-04-22 16:32:53 +00:00

found where the problem is:

func (statement *Statement) writeMssqlLegacySelect(buf *builder.BytesWriter, columnStr string)

from legacy_select.go

found where the problem is: **func** (statement *Statement) **writeMssqlLegacySelect**(buf *builder.BytesWriter, columnStr string) from **legacy_select.go**

suwro commented

2024-04-22 16:41:52 +00:00

Solved it - please update the code...

legacy_select.go, insert in line 24 statement.writeStrings(" ",columnStr),

// write mssql legacy query sql
func (statement *Statement) writeMssqlLegacySelect(buf *builder.BytesWriter, columnStr string) error {
	return statement.writeMultiple(buf,
		statement.writeStrings("SELECT"),
		statement.writeTop,
		statement.writeDistinct,
		statement.writeStrings(" ",columnStr),
		statement.writeFrom,
		statement.writeWhereWithMssqlPagination,
		statement.writeGroupBy,
		statement.writeHaving,
		statement.writeOrderBys,
		statement.writeForUpdate,
	)
}

Solved it - please update the code... legacy_select.go, insert in line 24 statement.writeStrings(" ",columnStr), ``` // write mssql legacy query sql func (statement *Statement) writeMssqlLegacySelect(buf *builder.BytesWriter, columnStr string) error { return statement.writeMultiple(buf, statement.writeStrings("SELECT"), statement.writeTop, statement.writeDistinct, statement.writeStrings(" ",columnStr), statement.writeFrom, statement.writeWhereWithMssqlPagination, statement.writeGroupBy, statement.writeHaving, statement.writeOrderBys, statement.writeForUpdate, ) } ```

lunny commented

2024-04-23 03:13:38 +00:00

Please send a PR to fix it. Thank you in advance.

lunny added the

kind

bug

label 2024-04-23 03:13:45 +00:00

suwro closed this issue

2024-04-23 07:46:39 +00:00

suwro reopened this issue

2024-04-23 07:47:56 +00:00

suwro referenced this issue