[MDEV-7465] aggregate functions fail with LIMIT EXAMINED ROWS Created: 2015-01-14  Updated: 2015-02-18  Resolved: 2015-02-18

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: limit_examined_rows


 Description   

With the following query, I expected to see an AVG from a sample of 5000 rows, but it fails:

MariaDB [test]> SELECT AVG(id) FROM t LIMIT ROWS EXAMINED 5000;
 
Empty set, 1 warning (0.00 sec)
 
 
 
Warning (Code 1931): Query execution was interrupted. The query examined at least 5001 rows, which exceeds LIMIT ROWS EXAMINED (5000). The query result may be incomplete.

The same happens with every aggregate function.

I'm not sure if this is a bug or a request.

 Comments   
Comment by Federico Razzoli [ 2015-01-14 ]

I just corrected a wrong info in the bug description.

Comment by Sergei Golubchik [ 2015-02-18 ]

This is the intentional behavior of LIMIT ROWS EXAMINED. The documentation describes it as (emphasis is mine):

The effects of terminating the query because of LIMIT ROWS EXAMINED are as follows:

  • The result of the query is a subset of the complete query, depending on when the query engine detected that the limit was reached. The result may be empty if no result rows could be computed before reaching the limit.
  • A warning is generated of the form: "Query execution was interrupted. The query examined at least 100 rows, which exceeds LIMIT ROWS EXAMINED (20). The query result may be incomplete."
  • If the query contains a GROUP BY clause, the last group where the limit was reached will be discarded.
Generated at Thu Feb 08 07:19:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.