SQL Server Wrapper HV000: Token error: 'Incorrect syntax near 'offset'

[glib-0]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I'm locally hosting Supabase and have a wrapper for a remote SQL Server 2022 instance. When I try to view the table in the table editor, the connection establishes fine - I can see the request for the row count and response with the correct value, but the request for the actual data returns this error: HV000: Token error: 'Incorrect syntax near 'offset.

Running select * from private.<table> returns nothing.

To Reproduce

• Remote MS SQL Server 2022 instance. Please note that I have no control over this database and don't fully know its configuration.
• SQL Server Authentication
• My connection string is the following format: Server=<url>\<instance>,<port>;Database=<dbname>;TrustServerCertificate=true;Integrated Security=false;Encrypt=false;User ID=<user>;Password=<password>;Packet Size=4096;Network Library=dbmssocn;
• I don't have access to the sa user, just a user that has only read access.
• I can connect to and query the database with the same credentials and connection settings with no problem in SSMS.
• I see that the payload in the request is performing the following query: select * from private.<table> limit 100 offset 0;. Queries with OFFSET work fine - it's not on an Azure SQL Database, it's just SQL Server 2022 so it should work fine via the wrapper. However, T-SQL doesn't have a LIMIT keyword. Missed in translation?

Expected behavior

The table preview/queries on the table should return data.

Screenshots

System information

• OS: Windows 11
• Supabase in Docker installed via Supabase CLI, Studio image 20240422-5cf8f30

[encima]

Hi @glib-0

Thanks for opening! I can reproduce this so I will transfer this issue to the wrappers repo.

[burmecia]

It is strange to me, the limit and offset clause in pg should be translated to sql like offset 0 rows fetch next 10 rows only in this code. Can you paste your sql used in pg? And can you also try run the same sql in Sql Editor or other client tools like psql?

[encima]

SQL used: select * from test_table; This was run in the SQL Editor and in psql, yep
Tested with a locally running SQL Server container

[glib-0]

I think it's because the ORDER BY clause in the wrapper appears to be optional. It's mandatory when you use OFFSET...FETCH in T-SQL. If I do SELECT * FROM <table> AS _wrappers_tbl OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY on the SQL Server database I get the same error message:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'OFFSET'.

If I apply sorting to the table in the Studio, it actually works and returns the data as expected.

[burmecia]

It's mandatory when you use OFFSET...FETCH in T-SQL.

Thanks for that explanation, yes I think that's the reason. OFFSET...FETCH must come with ORDER BY in T-SQL, but in PG it is not mandatory. We should report an error when the ORDER BY is not present with LIMIT...OFFSET.