Mysql count query results in empty set-CodePudding

I am executing the following query and getting an empty result set back. What could be the problem?

SELECT COUNT(*) 
FROM STATION_METRICS 
WHERE metric_label = 'Pressure' 
AND station_id = 1 
AND metric_timestamp >= '2022-11-29' 
AND metric_timestamp <= '2022-12-06' 
ORDER BY metric_timestamp ASC 
LIMIT 18446744073709551615 OFFSET 50;

It confuses me because when I execute the same query but without count(*) like this

SELECT * 
FROM STATION_METRICS 
WHERE metric_label = 'Pressure' 
AND station_id = 1 
AND metric_timestamp >= '2022-11-29' 
AND metric_timestamp <= '2022-12-06' 
ORDER BY metric_timestamp ASC 
LIMIT 18446744073709551615 OFFSET 50;

it returns rows normally, but the count does not. I am using limit with large number as a workaround since offset cannot be used alone. Also, when I remove limit and offset, count works normally as expected.

CodePudding user response：

It does not make much sense to be using OFFSET with a top level count query. The COUNT(*) will return a single record containing the count over the table, given the limit and sort. But then, your inclusion of OFFSET will force the cursor past the single record result set to nothing.

For your first count query, most likely you should just be running:

SELECT COUNT(*)
FROM STATION_METRICS
WHERE metric_label = 'Pressure' AND
      station_id = 1 AND
      metric_timestamp >= '2022-11-29' AND metric_timestamp < '2022-12-07';