MySQL Bugs: #104502: Different results for SQL_CALC_FOUND

Bug #104502	Different results for SQL_CALC_FOUND_ROWS
Submitted:	2 Aug 2021 8:21	Modified:	26 Oct 2021 21:24
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Shell General / Core Client	Severity:	S3 (Non-critical)
Version:	8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Client version: 8.0.26
Server version: 8.0.22
Session: X Protocol (but also happens with a Classic session)

When running these:
SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT 1 UNION ALL SELECT 2) a; SELECT FOUND_ROWS();

SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT 1 UNION ALL SELECT 2) a;
SELECT FOUND_ROWS();

The results are different.
The first has both statements on a single line.

With MySQL Client the results are the same for both.

How to repeat:
8.0.22 127.0.0.1:18022+   test  SQL  SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT 1 UNION ALL SELECT 2) a; SELECT FOUND_ROWS();
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set, 1 warning (0.0015 sec)
Warning (code 1287): SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
+--------------+
| FOUND_ROWS() |
+--------------+
|            2 |
+--------------+
1 row in set, 1 warning (0.0009 sec)
Warning (code 1287): FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
8.0.22 127.0.0.1:18022+   test  SQL  SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT 1 UNION ALL SELECT 2) a;
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set, 1 warning (0.0012 sec)
Warning (code 1287): SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
8.0.22 127.0.0.1:18022+   test  SQL  SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            1 |
+--------------+
1 row in set, 1 warning (0.0010 sec)
Warning (code 1287): FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.

Suggested fix:
While SQL_CALC_FOUND_ROWS is deprecated (for good reasons) it will be around for some time and MySQL Shell should be able to handle this correctly.

I assume MySQL Shell sends a statement to the server to get errors and/or other information that clears/resets the value. Would be good to not do this if the statement has SQL_CALC_FOUND_ROWS in it.

With MySQL Client. This happens with and without warnings enabled on the client.

mysql> \W
Show warnings enabled.
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT 1 UNION ALL SELECT 2) a; SELECT FOUND_ROWS();
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set, 1 warning (0.00 sec)

Warning (Code 1287): SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
+--------------+
| FOUND_ROWS() |
+--------------+
|            2 |
+--------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1287): FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT 1 UNION ALL SELECT 2) a;
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set, 1 warning (0.00 sec)

Warning (Code 1287): SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            2 |
+--------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1287): FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
mysql>

Hello Daniël,

Thank you for the report and feedback.

regards,
Umesh

That happens because warnings are fetched and shown by default. You can disable that with \w or \option --persist showWarnings=false

Hello Alfredo,

Disabling warnings doesn't help.

8.0.26 127.0.0.1:18026+   SQL  \w
Show warnings disabled.
8.0.26 127.0.0.1:18026+   SQL  SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT 1 UNION ALL SELECT 2) a; SELECT FOUND_ROWS();
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set, 1 warning (0.0006 sec)
+--------------+
| FOUND_ROWS() |
+--------------+
|            2 |
+--------------+
1 row in set, 1 warning (0.0003 sec)
8.0.26 127.0.0.1:18026+   SQL  SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT 1 UNION ALL SELECT 2) a;
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set, 1 warning (0.0004 sec)
8.0.26 127.0.0.1:18026+   SQL  SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            1 |
+--------------+
1 row in set, 1 warning (0.0003 sec)

Posted by developer:
 
Thanks for bug report.

This is a duplicate of https://bugs.mysql.com/bug.php?id=104955 (Bug#33362817)

Rootcause of described behaviour is that `%sysvar:version%` value from prompt should be cached during connection, but isn't.[1]

[1] https://github.com/mysql/mysql-shell/blob/master/samples/prompt/README.prompt#L134-L138

Duplicate of https://bugs.mysql.com/bug.php?id=104955