Bug #98464 doc recommends invalid query SELECT COUNT(*) WHERE id > 100;
Submitted: 3 Feb 2020 13:43 Modified: 11 Mar 2020 9:52
Reporter: Charon ME Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: documentation, sql_calc_found_rows

[3 Feb 2020 13:43] Charon ME 
Description:
example in https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows recommends calling SELECT COUNT(*) WHERE id > 100; instead of the deprecated SELECT FOUND_ROWS(); (notice the missing FROM clause)

How to repeat:
browse to https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows and read the SQL_CALC_FOUND_ROWS deprecation note

Suggested fix:
replace SELECT COUNT(*) WHERE id > 100; with SELECT COUNT(*) FROM tbl_name WHERE id > 100; in the documentation
[3 Feb 2020 14:08] MySQL Verification Team 
Hello Charon ME,

Thank you for the report and feedback.

regards,
Umesh
[11 Feb 2020 13:59] Paul DuBois 
Posted by developer:
 
Fixed. Thank you for spotting this!
[11 Mar 2020 9:52] Charon ME 
thanks for the fix. Anyway as a side note, I just noticed another problem with this part of the manual: the suggested usage of count(*) will not work as expected when the original query uses group by (while SQL_CALC_FOUND_ROWS worked fine even in those cases). So far I used SELECT COUNT(*) FROM (SELECT ...original query...) tmp but I worry the optimizations promised for using count(*) will not entirely work in this case and it even might be unusable for huge tables