Details
-
New Feature
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
Hi,
this is to report upstream feature request Bug#105911 [1] for MariaDB.
[1]: https://bugs.mysql.com/bug.php?id=105911
This FR is to add a warning when the max_sort_length limit is reached.
As shown below for 10.6.5 (same for 10.5.13), there are no warning when the limit is reached.
Many thanks for looking into this,
Jean-François Gagné
# Create a sandbox.
|
dbdeployer deploy single mariadb_10.6.5
|
|
|
# max_sort_length is 1024 by default.
|
./use -N <<< "show global variables like 'max_sort_length'"
|
max_sort_length 1024
|
|
|
# Initialize the sandbox.
|
./use <<< "
|
CREATE DATABASE test_jfg;
|
CREATE TABLE test_jfg.t (
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
v VARCHAR(5000))"
|
|
|
# Add rows on both sides of the limit.
|
for i in 1020 1024; do
|
for j in 2 4 6 1 3 5; do ./use test_jfg <<< "INSERT INTO t(v) VALUES('$(yes | head -n $i | paste -s -d "")$j')"; done
|
done
|
|
|
# No warnings even if the ORDER BY is not fully honored.
|
mysql [localhost:10605] {msandbox} (test_jfg) > SELECT SUBSTRING(v, 1020) FROM t ORDER BY v;
|
+--------------------+
|
| SUBSTRING(v, 1020) |
|
+--------------------+
|
| y1 |
|
| y2 |
|
| y3 |
|
| y4 |
|
| y5 |
|
| y6 |
|
| yyyyy2 |
|
| yyyyy4 |
|
| yyyyy6 |
|
| yyyyy1 |
|
| yyyyy3 |
|
| yyyyy5 |
|
+--------------------+
|
12 rows in set (0.000 sec)
|
|
|
# I would expect a warning similar to this.
|
mysql [localhost:10605] {msandbox} (test_jfg) > SELECT "1a" + 2;
|
+----------+
|
| "1a" + 2 |
|
+----------+
|
| 3 |
|
+----------+
|
1 row in set, 1 warning (0.000 sec)
|
|
|
mysql [localhost:10605] {msandbox} (test_jfg) > show warnings;
|
+---------+------+----------------------------------------+
|
| Level | Code | Message |
|
+---------+------+----------------------------------------+
|
| Warning | 1292 | Truncated incorrect DOUBLE value: '1a' |
|
+---------+------+----------------------------------------+
|
1 row in set (0.000 sec)
|
Attachments
Issue Links
- causes
-
MDEV-35275 Unexpected WARN_SORTING_ON_TRUNCATED_LENGTH or assertion failure in diagnostics area
-
- Closed
-
-
-
- Closed
-
Activity
Thinking of testing this... So, this affects:
- Queries doing sorting (to handle GROUP BY or ORDER BY)
- Queries with window functions
(it seems window frame bound check uses this.) - Queries doing grouping with the
"use an index to get rows ordered group-after-group" algorithm. - any IN subqueries. (left expression "cache" uses max_sort_length?)
This doesn't affect:
- UNIONs. select blob_column1 from t1 union select blob_column2 from t2
- Grouping when it doesn't involve sorting, like
select count
from t1 group by blob_column order by NULL;
WEIGHT_STRING() may be pretty useful for testing as it produces the array of binary weights which would participate in sorting. I.e, arrays of weights for different strings are compared against each other to determine which string must precedes another in the sorted output. The `max_sort_length` parameters set the maximal size of the buffer into which the string weights are put before their comparison (and sorting). So, if the produced array of weights is larger than `max_sort_length`, the comparison will be made on truncated data and therefore, the results of sorting may be inaccurate. And we expect a warning to be generated in such a case.
So, one way of testing the feature can be writing of SQL statements involving sorting of strings at the same time checking if their weight strings are larger than `max_sort_length`. In every such case a warning must be generated.
Another way of testing suggested by serg is the following.
Start with low `max_sort_length` setting and sort a set of strings. After each cycle increase `max_sort_length` setting and check if the sorting order has changed. If it has, then there must be a warning at the previous execution. However, it's not clear how to verify the number of truncated strings displayed in the warning message.
The branch bb-11.7-
MDEV-27277is ready for testing.