|
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;
|
|
|
> 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()
|
|
Patch: https://github.com/MariaDB/server/commit/eb612e42020b7409cd37f7ccb41ba61c70a8fb8b
|
|
ok to push
|
|
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
|
|