V3.9.9 - DISTINCT doesn't work when used in a Paged Records Query Type

Wappler General Bugs
, , ,
1

Wappler Version : 3.9.9
Operating System : MacOS
Server Model: ASP.NET
Database Type: MSSQL
Hosting Type: IIS

Expected behavior

The Server Action is expected to Fetch DISTINCT Paged Records from the database tables.

Actual behavior

The Server Action fails when DISTINCT is used in a Paged Records Query Type. The query fails with the MSSQL error (in the developers tools) - Incorrect Syntax near the word DISTINCT.

How to reproduce

  1. Create a Paged Records Server Action
  2. Add Select DISTINCT option to the Query
  3. Server Action fails when the Paged Query is run
3

Which version of MSSQL do you use?

4

Hi @patrick, I’m using MSSQL 2019 version.

5

I’ve updated the way it handles paged recordsets for mssql, works only for mssql 2012 or later. Just replace the files in the Bin folder with the ones from the zip file.

DMXzone.ServerConnect.DatabaseConnector.zip (27.1 KB)

6

Hi Patrick,

The DISTINCT query is working correctly now after replacing the files. Thank you for providing an updated code in resolving this issue.

One thing I have noticed during my testing is that if I change the Paged Query Limit manually to say 2, then the Query fails when navigating to Next Page. I’m not sure whether a Paged Query with DISTINCT is designed to work with default $GET_limit param only or it’s going to fail when navigating to Next Page if records are more than the default page limit of 25 no.

7

I’ve update the code to use OFFSET/FETCH, but I see now that there are some limitations to it.

https://www.dofactory.com/sql/order-by-offset-fetch

Seems it can only be used in combination with ORDER BY. Didn’t see that before in other articles. So I probably have to make some other changes.

The old code used a syntax like:

SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY id)  AS row, * FROM table) AS tbl 
WHERE row BETWEEN @offset AND @limit

New code looks like:

SELECT * FROM table ORDER BY id OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY
8

I was already using it with ORDER BY clause, so it didn’t raise any flag for this requirement. The code is working correctly for the default @limit param, so, I won’t change it manually to a different number.

Thanks for looking into this, Patrick. Your support and assistance is greatly appreciated.

9

While creating our Paged Custom Query Extension, we faced the same issue.
What we are doing is using ORDER BY (SELECT 1) when there is no order by selected in the query for MSSQL.

You might have a better solution, so not sure how helpful this is.