Getting different row count with Mysql 8.0.18 and 8.0.27 version-CodePudding

Can anyone help me with solution for below query result. I am getting different row count with version 8.0.18 and 8.0.27. Please let me know if any configuration changes needed.

(Note : I have commented some of the values with ***. Kindly ignore that)

Mysql 8.0.18:

mysql>  SELECT REQ.REQ_ID REQUEST_ID,REQ.CURRENT_STATE_ID,STAPP.APPROVER_TYPE_ID,REQ.REQUEST_TYPE, 'YES' APPROVER FROM WF_REQUESTS REQ  LEFT JOIN WF_STATE_APPROVERS STAPP ON REQ.CURRENT_STATE_ID=STAPP.STATE_ID WHERE REQ.APP_CODE=102 AND REQ.COMPANY_CODE=101  AND   REQ.PROCESS_STATUS='PENDING' AND ((STAPP.APPROVER_TYPE_ID = (SELECT ID from WF_APPROVER_TYPES WHERE NAME = 'FINGROUP') ) OR(REQ.LOCATION_CODE = '0000' AND  STAPP.APPROVER_TYPE_ID = (SELECT ID from WF_APPROVER_TYPES WHERE NAME = 'RGM') ) ) UNION SELECT a.REQUEST_ID, a.CURRENT_STATE_ID, a.APPROVER_TYPE_ID,a.REQUEST_TYPE, CASE WHEN  a.APPROVER_NAME ='FINGROUP' OR a.APPROVER_NAME ='RGM'  THEN 'YES' ELSE 'NO' END AS APPROVER FROM (SELECT  REQ.REQ_ID REQUEST_ID,REQ.CURRENT_STATE_ID, STAPP.APPROVER_TYPE_ID, REQ.REQUEST_TYPE, WAT.NAME APPROVER_NAME FROM WF_REQUESTS REQ LEFT JOIN WF_STATE_APPROVERS STAPP ON REQ.CURRENT_STATE_ID = STAPP.STATE_ID LEFT JOIN WF_APPROVER_TYPES WAT ON WAT.ID=STAPP.APPROVER_TYPE_ID WHERE REQ.APP_CODE = 102 AND REQ.COMPANY_CODE = 101 AND REQ.PROCESS_STATUS = 'PENDING')a LIMIT 10 OFFSET 0;

 ------------ ------------------ ------------------ -------------- ---------- 
| REQUEST_ID | CURRENT_STATE_ID | APPROVER_TYPE_ID | REQUEST_TYPE | APPROVER |
 ------------ ------------------ ------------------ -------------- ---------- 
|      30131 |                2 |                4 | ***          | YES      |
|      32375 |               42 |                4 | ***          | YES      |
|     330180 |               62 |                4 | ***          | YES      |
|     330178 |               62 |                4 | ***          | YES      |
|     330174 |               62 |                4 | ***          | YES      |
|     329227 |               62 |                4 | ***          | YES      |
|     327863 |               62 |                4 | ***          | YES      |
|     324671 |               62 |                4 | ***          | YES      |
|     353554 |               62 |                4 | ***          | YES      |
|     343647 |               62 |                4 | ***          | YES      |
 ------------ ------------------ ------------------ -------------- ---------- 
10 rows in set (1.42 sec)

mysql> SELECT FOUND_ROWS() count;
 ------- 
| count |
 ------- 
| 11975 |
 ------- 
1 row in set, 1 warning (0.00 sec)

Mysql 8.0.27:

mysql>  SELECT REQ.REQ_ID REQUEST_ID,REQ.CURRENT_STATE_ID,STAPP.APPROVER_TYPE_ID,REQ.REQUEST_TYPE, 'YES' APPROVER FROM WF_REQUESTS REQ  LEFT JOIN WF_STATE_APPROVERS STAPP ON REQ.CURRENT_STATE_ID=STAPP.STATE_ID WHERE REQ.APP_CODE=102 AND REQ.COMPANY_CODE=101  AND   REQ.PROCESS_STATUS='PENDING' AND ((STAPP.APPROVER_TYPE_ID = (SELECT ID from WF_APPROVER_TYPES WHERE NAME = 'FINGROUP') ) OR(REQ.LOCATION_CODE = '0000' AND  STAPP.APPROVER_TYPE_ID = (SELECT ID from WF_APPROVER_TYPES WHERE NAME = 'RGM') ) ) UNION SELECT a.REQUEST_ID, a.CURRENT_STATE_ID, a.APPROVER_TYPE_ID,a.REQUEST_TYPE, CASE WHEN  a.APPROVER_NAME ='FINGROUP' OR a.APPROVER_NAME ='RGM'  THEN 'YES' ELSE 'NO' END AS APPROVER FROM (SELECT  REQ.REQ_ID REQUEST_ID,REQ.CURRENT_STATE_ID, STAPP.APPROVER_TYPE_ID, REQ.REQUEST_TYPE, WAT.NAME APPROVER_NAME FROM WF_REQUESTS REQ LEFT JOIN WF_STATE_APPROVERS STAPP ON REQ.CURRENT_STATE_ID = STAPP.STATE_ID LEFT JOIN WF_APPROVER_TYPES WAT ON WAT.ID=STAPP.APPROVER_TYPE_ID WHERE REQ.APP_CODE = 102 AND REQ.COMPANY_CODE = 101 AND REQ.PROCESS_STATUS = 'PENDING')a LIMIT 10 OFFSET 0;

 ------------ ------------------ ------------------ -------------- ---------- 
| REQUEST_ID | CURRENT_STATE_ID | APPROVER_TYPE_ID | REQUEST_TYPE | APPROVER |
 ------------ ------------------ ------------------ -------------- ---------- 
|      30131 |                2 |                4 | ***          | YES      |
|      32375 |               42 |                4 | ***          | YES      |
|      39103 |                2 |                4 | ***          | YES      |
|      39102 |                2 |                4 | ***          | YES      |
|      39101 |                2 |                4 | ***          | YES      |
|      39099 |                2 |                4 | ***          | YES      |
|      39081 |                2 |                4 | ***          | YES      |
|      39019 |                2 |                4 | ***          | YES      |
|      39018 |                2 |                4 | ***          | YES      |
|      30852 |                2 |                4 | ***          | YES      |
 ------------ ------------------ ------------------ -------------- ---------- 
10 rows in set (0.12 sec)

mysql> SELECT FOUND_ROWS() count;
 ------- 
| count |
 ------- 
|    10 |
 ------- 
1 row in set, 1 warning (0.00 sec)

CodePudding user response：

The FOUND_ROWS() information function has been deprecated as of version 8.0.17, q.v. the documentation:

The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17;

It appears that the behavior of FOUND_ROWS() in your version 8.0.18 is to return the size of what the entire result set would be, sans the LIMIT clause.

The documentation offers a workaround:

As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows.