Devart Forums

For some queries, if paginal mode is enabled, the page limit is applied to subqueries.

E.g: In the following query, even though there is a limit of 10000, a limit of 1000 is applied to the subquery.

SELECT GROUP_CONCAT(id)
FROM (
SELECT nr.id /* nr.*, getusername(nr.UsrLst_ID), a.name, a.catlst_id, cl.catagoryname*/
FROM notificationrule AS nr
INNER JOIN areaassign AS aa ON aa.id = nr.AreaAssign_ID
INNER JOIN areas AS a ON a.id = aa.Areas_id
INNER JOIN catlst AS cl ON cl.id = a.catlst_id
WHERE nr.UsrLst_ID = getuseridex('control room') AND a.catlst_id = 10
LIMIT 10000
) AS X;

But in the following similar query, it works fine:
SELECT
GROUP_CONCAT(id)
FROM (SELECT
nr.id /* nr.*, getusername(nr.UsrLst_ID), a.name, a.catlst_id, cl.catagoryname*/
FROM notificationrule AS nr
INNER JOIN areaassign AS aa
ON aa.id = nr.AreaAssign_ID
INNER JOIN areas AS a
ON a.id = aa.Areas_id
INNER JOIN catlst AS cl
ON cl.id = a.catlst_id
WHERE nr.UsrLst_ID = getuseridex('control room')
AND a.catlst_id = 10
LIMIT 10000) AS X;

SELECT
GROUP_CONCAT(id)
FROM (SELECT
*
FROM cameraconfig LIMIT 2000) AS x;

We will review this and will answer you as soon as possible.

Could you please describe it in more detail and provide us the actual and expected results? What happens if you turn off Paginal Mode? A video or screenshots demonstrating the issue would be also of help.

You can send a reply straight to our support system at alexaATdevartDOTcom and supportATdevartDOTcom .

Using the following query:
SELECT
*
FROM (SELECT
contracts.*
FROM contracts
INNER JOIN vehunits vu1
ON vu1.contract_id = contracts.id
WHERE vu1.enddatetime = 0
OR getdatetimenow() < vu1.enddatetime
LIMIT 12345) AS ContractUnits
group by ContractUnits.cmplst_id
LIMIT 5000;

Results with paginal mode enabled (194 rows):


Results with paginal mode disabled (2174 rows):


The same query in workbench returns 2174 rows:


With a subquery limit of 1000 and paginal mode disabled (194 rows):


Interestingly, the same query without the where clause does not suffer from the paginal mode issue.

Thank you for the reply.

We will investigate this issue and will answer you as soon as possible.