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

LIMIT ROWS EXAMINED throws error in Debug build only

Details

    Description

      On a merge from 10.2 to 10.3, I had to adjust the MDEV-14836 test case, because the Debug build so that an error will be returned upon exceeding the limit. But, the RelWithDebInfo build would not issue any error, only a warning.

      In order to prevent non-debug builds from failing on the CI system, I will temporarily apply the following change to work around this bug:

      diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test
      index dd2b723e6ee..22baad77a69 100644
      --- a/mysql-test/main/information_schema.test
      +++ b/mysql-test/main/information_schema.test
      @@ -20,6 +20,8 @@
       --source include/default_optimizer_switch.inc
       --source include/default_charset.inc
       
      +--source include/maybe_debug.inc
      +
       set global sql_mode="";
       set local sql_mode="";
       
      @@ -1930,9 +1932,14 @@ SELECT SCHEMA_NAME from information_schema.schemata where schema_name=REPEAT('a'
       --echo # Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED
       --echo #
       
      +if ($have_debug) {
       --error ER_UNKNOWN_ERROR
       SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10;
       SHOW WARNINGS;
      +}
      +if (!$have_debug) {
      +SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10;
      +}
       
       --echo #
       --echo # End of 10.2 Test
      

      This change should be reverted as part of fixing the bug.

      Attachments

        Issue Links

          Activity

            The error report seems to be related to the use of the view INFORMATION_SCHEMA.COLUMNS. On a merge of the work-around to 10.4, I will use seq_1_to_100, because the warning message would include an estimate "at least N rows" that differs between Debug and RelWithDebInfo builds. However, when I use seq_1_to_100, no error will be returned.

            My guess is that some (all?) implementations of INFORMATION_SCHEMA views will return an error if sending a record is rejected, and starting with 10.3, this error would be propagated to the execution of the SELECT statement.

            marko Marko Mäkelä added a comment - The error report seems to be related to the use of the view INFORMATION_SCHEMA.COLUMNS . On a merge of the work-around to 10.4, I will use seq_1_to_100 , because the warning message would include an estimate "at least N rows" that differs between Debug and RelWithDebInfo builds. However, when I use seq_1_to_100 , no error will be returned. My guess is that some (all?) implementations of INFORMATION_SCHEMA views will return an error if sending a record is rejected, and starting with 10.3, this error would be propagated to the execution of the SELECT statement.

            I found out that in 10.4 c86114f5948667cebbe7196238face06266d8572 the limit is not being enforced in aggregate functions:

            SELECT SUM(seq) FROM seq_1_to_100 LIMIT ROWS EXAMINED 10;
            

            would compute the sum (5050) even though it involves examining all 100 rows. But, I think that it is a separate bug.
            Similarly, COUNT(*) would return the count, but maybe for that one a shortcut is available for this storage engine? In InnoDB, it would have to read individual rows, and also that would fail to enforce the limit:

            --source include/have_innodb.inc
            --source include/have_sequence.inc
            CREATE TABLE t1 (a SERIAL) ENGINE=InnoDB SELECT * FROM seq_1_to_100;
            SELECT COUNT(*) FROM t1;
            DROP TABLE t1;
            

            marko Marko Mäkelä added a comment - I found out that in 10.4 c86114f5948667cebbe7196238face06266d8572 the limit is not being enforced in aggregate functions: SELECT SUM (seq) FROM seq_1_to_100 LIMIT ROWS EXAMINED 10; would compute the sum (5050) even though it involves examining all 100 rows. But, I think that it is a separate bug. Similarly, COUNT(*) would return the count, but maybe for that one a shortcut is available for this storage engine? In InnoDB, it would have to read individual rows, and also that would fail to enforce the limit: --source include/have_innodb.inc --source include/have_sequence.inc CREATE TABLE t1 (a SERIAL) ENGINE=InnoDB SELECT * FROM seq_1_to_100; SELECT COUNT (*) FROM t1; DROP TABLE t1;

            > the limit is not being enforced in aggregate functions

            Not all aggregate functions:

            MariaDB [test]> SELECT AVG(seq) FROM seq_1_to_100 LIMIT ROWS EXAMINED 10;
            Empty set, 1 warning (0.001 sec)
             
            MariaDB [test]> show warnings;
            +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
            | Level   | Code | Message                                                                                                                                          |
            +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
            | Warning | 1931 | Query execution was interrupted. The query examined at least 11 rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete |
            +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
            1 row in set (0.001 sec)
            

            And here is the difference in processing:

            MariaDB [test]> ANALYZE FORMAT=JSON SELECT SUM(seq) FROM seq_1_to_100 LIMIT ROWS EXAMINED 10;
            +------------------------------------------------------------------------------------------------------------------------+
            | ANALYZE                                                                                                                |
            +------------------------------------------------------------------------------------------------------------------------+
            | {
              "query_block": {
                "select_id": 1,
                "table": {
                  "message": "Storage engine handles GROUP BY"
                }
              }
            } |
            +------------------------------------------------------------------------------------------------------------------------+
            1 row in set (0.000 sec)
            

            MariaDB [test]> ANALYZE FORMAT=JSON SELECT AVG(seq) FROM seq_1_to_100 LIMIT ROWS EXAMINED 10;
            +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | ANALYZE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
            +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_total_time_ms": 0.114760814,
                "table": {
                  "table_name": "seq_1_to_100",
                  "access_type": "index",
                  "key": "PRIMARY",
                  "key_length": "8",
                  "used_key_parts": ["seq"],
                  "r_loops": 1,
                  "rows": 100,
                  "r_rows": 10,
                  "r_table_time_ms": 0.007942549,
                  "r_other_time_ms": 0.095653855,
                  "filtered": 100,
                  "r_filtered": 100,
                  "using_index": true
                }
              }
            } |
            +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            1 row in set, 1 warning (0.001 sec)
            

            elenst Elena Stepanova added a comment - > the limit is not being enforced in aggregate functions Not all aggregate functions: MariaDB [test]> SELECT AVG (seq) FROM seq_1_to_100 LIMIT ROWS EXAMINED 10; Empty set , 1 warning (0.001 sec)   MariaDB [test]> show warnings; + ---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | + ---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1931 | Query execution was interrupted. The query examined at least 11 rows , which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete | + ---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec) And here is the difference in processing: MariaDB [test]> ANALYZE FORMAT=JSON SELECT SUM (seq) FROM seq_1_to_100 LIMIT ROWS EXAMINED 10; + ------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | + ------------------------------------------------------------------------------------------------------------------------+ | { "query_block" : { "select_id" : 1, "table" : { "message" : "Storage engine handles GROUP BY" } } } | + ------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [test]> ANALYZE FORMAT=JSON SELECT AVG (seq) FROM seq_1_to_100 LIMIT ROWS EXAMINED 10; + ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | + ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block" : { "select_id" : 1, "r_loops" : 1, "r_total_time_ms" : 0.114760814, "table" : { "table_name" : "seq_1_to_100" , "access_type" : "index" , "key" : "PRIMARY" , "key_length" : "8" , "used_key_parts" : [ "seq" ], "r_loops" : 1, "rows" : 100, "r_rows" : 10, "r_table_time_ms" : 0.007942549, "r_other_time_ms" : 0.095653855, "filtered" : 100, "r_filtered" : 100, "using_index" : true } } } | + ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set , 1 warning (0.001 sec)

            Yes, technically the sequence engine does not examine any rows for SUM()

            serg Sergei Golubchik added a comment - Yes, technically the sequence engine does not examine any rows for SUM()
            rucha174 Rucha Deodhar added a comment - Patch: https://github.com/MariaDB/server/commit/eb612e42020b7409cd37f7ccb41ba61c70a8fb8b

            ok to push

            serg Sergei Golubchik added a comment - ok to push
            alice Alice Sherepa added a comment -

            http://buildbot.askmonty.org/buildbot/builders/winx64-debug/builds/21470/steps/test/logs/stdio

            main.information_schema 'debug,innodb'   w1 [ fail ]
                    Test ended at 2020-10-20 14:27:23
             
            CURRENT_TEST: main.information_schema
            --- D:/winx64-debug/build/src/mysql-test/main/information_schema,debug.result~	2020-10-20 14:27:18.342794400 +0000
            +++ D:\winx64-debug\build\src\mysql-test\main\information_schema,debug.reject	2020-10-20 14:27:23.014928400 +0000
            @@ -2208,11 +2208,9 @@
             # Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED
             #
             SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10;
            -ERROR HY000: Unknown error
            -SHOW WARNINGS;
            -Level	Code	Message
            -Error	1105	Unknown error
            -Warning	1931	Query execution was interrupted. The query examined at least 11 rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete
            +TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
            +Warnings:
            +Warning	1931	Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete
             #
             # End of 10.2 Test
             #
             
            mysqltest: Result length mismatch
            

            alice Alice Sherepa added a comment - http://buildbot.askmonty.org/buildbot/builders/winx64-debug/builds/21470/steps/test/logs/stdio main.information_schema 'debug,innodb' w1 [ fail ] Test ended at 2020-10-20 14:27:23   CURRENT_TEST: main.information_schema --- D:/winx64-debug/build/src/mysql-test/main/information_schema,debug.result~ 2020-10-20 14:27:18.342794400 +0000 +++ D:\winx64-debug\build\src\mysql-test\main\information_schema,debug.reject 2020-10-20 14:27:23.014928400 +0000 @@ -2208,11 +2208,9 @@ # Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED # SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10; -ERROR HY000: Unknown error -SHOW WARNINGS; -Level Code Message -Error 1105 Unknown error -Warning 1931 Query execution was interrupted. The query examined at least 11 rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION +Warnings: +Warning 1931 Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete # # End of 10.2 Test #   mysqltest: Result length mismatch

            People

              rucha174 Rucha Deodhar
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.