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

Please add a warning when max_sort_length is reached.

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35275 Unexpected WARN_SORTING_ON_TRUNCATED_LENGTH or assertion failure in diagnostics area

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35307 Unexpected error WARN_SORTING_ON_TRUNCATED_LENGTH or assertion failure in diagnostics area #2

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            jeanfrancois.gagne Jean-François Gagné created issue -
            oleg.smirnov Oleg Smirnov made changes -
            Field Original Value New Value
            Assignee Oleg Smirnov [ JIRAUSER50405 ]
            oleg.smirnov Oleg Smirnov made changes -
            Affects Version/s 10.5.13 [ 26026 ]
            Affects Version/s 10.6.5 [ 26034 ]
            Issue Type Bug [ 1 ] Task [ 3 ]
            serg Sergei Golubchik made changes -
            Priority Minor [ 4 ] Major [ 3 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 11.3 [ 28565 ]
            oleg.smirnov Oleg Smirnov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            oleg.smirnov Oleg Smirnov added a comment - - edited

            psergei can you please review bb-11.3-mdev-27277?

            oleg.smirnov Oleg Smirnov added a comment - - edited psergei can you please review bb-11.3-mdev-27277?
            oleg.smirnov Oleg Smirnov made changes -
            Assignee Oleg Smirnov [ JIRAUSER50405 ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 11.4 [ 29301 ]
            Fix Version/s 11.3 [ 28565 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 11.5 [ 29506 ]
            Fix Version/s 11.4 [ 29301 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 11.6 [ 29515 ]
            Fix Version/s 11.5 [ 29506 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Issue Type Task [ 3 ] New Feature [ 2 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 11.7 [ 29815 ]
            Fix Version/s 11.6 [ 29515 ]
            oleg.smirnov Oleg Smirnov made changes -
            Assignee Sergei Petrunia [ psergey ] Michael Widenius [ monty ]
            oleg.smirnov Oleg Smirnov added a comment -

            I believe monty can review this.

            oleg.smirnov Oleg Smirnov added a comment - I believe monty can review this.
            oleg.smirnov Oleg Smirnov added a comment -

            Rebased on 11.6 and opened a pull request for easier reviewing.

            oleg.smirnov Oleg Smirnov added a comment - Rebased on 11.6 and opened a pull request for easier reviewing.
            bar Alexander Barkov made changes -
            Assignee Michael Widenius [ monty ] Alexander Barkov [ bar ]
            bar Alexander Barkov added a comment -

            The patch
            https://github.com/MariaDB/server/commit/de91966b06120b563e50ab737e30fda345f5dd9c
            is OK to push

            bar Alexander Barkov added a comment - The patch https://github.com/MariaDB/server/commit/de91966b06120b563e50ab737e30fda345f5dd9c is OK to push
            bar Alexander Barkov made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Oleg Smirnov [ JIRAUSER50405 ]
            oleg.smirnov Oleg Smirnov added a comment -

            The branch bb-11.7-MDEV-27277 is ready for testing.

            oleg.smirnov Oleg Smirnov added a comment - The branch bb-11.7- MDEV-27277 is ready for testing.
            psergei Sergei Petrunia added a comment -

            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;
            psergei Sergei Petrunia added a comment - 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;
            oleg.smirnov Oleg Smirnov added a comment -

            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.

            oleg.smirnov Oleg Smirnov added a comment - 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.
            oleg.smirnov Oleg Smirnov made changes -
            Status Stalled [ 10000 ] In Testing [ 10301 ]
            oleg.smirnov Oleg Smirnov made changes -
            Assignee Oleg Smirnov [ JIRAUSER50405 ] Lena Startseva [ JIRAUSER50478 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels Preview_11.7
            lstartseva Lena Startseva added a comment -

            Testing done. Ok to push

            lstartseva Lena Startseva added a comment - Testing done. Ok to push
            lstartseva Lena Startseva made changes -
            Assignee Lena Startseva [ JIRAUSER50478 ] Oleg Smirnov [ JIRAUSER50405 ]
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            oleg.smirnov Oleg Smirnov added a comment -

            Pushed into 11.7 (commit)

            oleg.smirnov Oleg Smirnov added a comment - Pushed into 11.7 ( commit )
            oleg.smirnov Oleg Smirnov made changes -
            Fix Version/s 11.7.1 [ 29913 ]
            Fix Version/s 11.7 [ 29815 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -

            People

              oleg.smirnov Oleg Smirnov
              jeanfrancois.gagne Jean-François Gagné
              Votes:
              0 Vote for this issue
              Watchers:
              9 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.