Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7465

aggregate functions fail with LIMIT EXAMINED ROWS

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Not a Bug
        • Affects Version/s: None
        • Fix Version/s: N/A
        • Component/s: Optimizer

          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.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                f_razzoli Federico Razzoli added a comment -

                I just corrected a wrong info in the bug description.

                Show
                f_razzoli Federico Razzoli added a comment - I just corrected a wrong info in the bug description.
                Hide
                Permalink
                serg Sergei Golubchik added a comment -

                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.
                Show
                serg Sergei Golubchik added a comment - 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.

                  People

                  • Assignee:
                    serg Sergei Golubchik
                    Reporter:
                    f_razzoli Federico Razzoli
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: