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

Server crashes when optimizing SQL with ORDER BY

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.6.7, 10.6.10, 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
    • Optimizer, Server
    • CentOS Linux release 7.9.2009 (Core)
      Linux sql1 3.10.0-1160.76.1.el7.x86_64 #1 SMP Wed Aug 10 16:21:17 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

    Description

      Hello.

      MariaDB crashes with the following scenario:
      Default installation on centos7 from mariadb yum repo.

      CREATE DATABASE d;
      USE d;
      CREATE TABLE `vohgajoo` (
        `ohtighit` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `heipeeri` datetime DEFAULT NULL,
        `yingaexe` datetime DEFAULT NULL,
        `itahweit` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
        `ooghohva` varchar(12) DEFAULT NULL,
        `iedacoox` tinytext DEFAULT NULL,
        `meingeic` enum('m','f','t') DEFAULT NULL,
        `quauxeej` date DEFAULT NULL,
        `ahmaepao` varchar(16) DEFAULT NULL,
        `chiavahn` float DEFAULT NULL,
        `eebiavit` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`eebiavit`)),
        `theibaej` bit(1) NOT NULL DEFAULT b'0',
        PRIMARY KEY (`ohtighit`),
        KEY `itahweit` (`itahweit`(12)),
        KEY `ooghohva` (`ooghohva`)
      ) ENGINE=InnoDB AUTO_INCREMENT=381603 DEFAULT CHARSET=utf8mb4;
      CREATE TABLE `imaewaey` (
        `angooyie` int(11) NOT NULL,
        `binaeshe` int(11) NOT NULL,
        `itizahra` varchar(5) NOT NULL DEFAULT 'en',
        `aithixis` int(11) NOT NULL,
        `yingaexe` datetime NOT NULL,
        PRIMARY KEY (`angooyie`,`binaeshe`,`itizahra`),
        KEY `hieghieg` (`angooyie`,`binaeshe`,`aithixis`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
       
      (SELECT bx.itahweit, v.aithixis/2 aithixis, 2 angooyie, JSON_UNQUOTE(JSON_EXTRACT(eebiavit, '$.claumexf')) claumexf FROM d.vohgajoo bx LEFT JOIN d.imaewaey v ON v.itizahra='en' AND v.angooyie=2 AND v.binaeshe=bx.ohtighit WHERE bx.ohtighit IN (4985) AND bx.theibaej=1 ORDER BY bx.meingeic IN ('f','t') DESC, JSON_EXTRACT(bx.eebiavit, '$.balmrusb') DESC LIMIT 1) ORDER BY angooyie=16, aithixis DESC;
      

      Attachments

        1. gdb.txt
          37 kB
        2. mariadb.log
          10 kB

        Issue Links

          Activity

            oleg.smirnov Oleg Smirnov added a comment -

            sanja, what do you think about the second question: "There are more calls to LEX::wrap_unit_into_derived() from other places". Are they free of such an issue?

            oleg.smirnov Oleg Smirnov added a comment - sanja , what do you think about the second question: "There are more calls to LEX::wrap_unit_into_derived() from other places". Are they free of such an issue?
            oleg.smirnov Oleg Smirnov added a comment -

            The fix is pushed to 10.4 but the question above is still actual.

            oleg.smirnov Oleg Smirnov added a comment - The fix is pushed to 10.4 but the question above is still actual.
            alice Alice Sherepa added a comment -

            please also check the test case from MDEV-32872 before closing:

            CREATE TEMPORARY TABLE TableA (A INT, B INT, PRIMARY KEY (A));
            CREATE TEMPORARY TABLE TableB (A VARCHAR(100), B INT);
             
            (
                SELECT
                    TableA.A AS AA, TableB.A AS BA
                FROM TableA
                    LEFT JOIN TableB ON TableB.B = TableA.B
                GROUP BY TableA.A, TableB.A
                ORDER BY CAST(CAST(TableB.A AS DECIMAL) AS UNSIGNED)
            )
            ORDER BY CAST(CAST(BA AS DECIMAL) AS UNSIGNED);
            

            alice Alice Sherepa added a comment - please also check the test case from MDEV-32872 before closing: CREATE TEMPORARY TABLE TableA (A INT , B INT , PRIMARY KEY (A)); CREATE TEMPORARY TABLE TableB (A VARCHAR (100), B INT ); ( SELECT TableA.A AS AA, TableB.A AS BA FROM TableA LEFT JOIN TableB ON TableB.B = TableA.B GROUP BY TableA.A, TableB.A ORDER BY CAST ( CAST (TableB.A AS DECIMAL ) AS UNSIGNED) ) ORDER BY CAST ( CAST (BA AS DECIMAL ) AS UNSIGNED);
            oleg.smirnov Oleg Smirnov added a comment -

            @alice, there is no crash on 10.4 with the pushed patch, but the user is complaining on 10.6. Once the fix is merged upstream we can tell certainly if it's the same bug or not.

            oleg.smirnov Oleg Smirnov added a comment - @alice, there is no crash on 10.4 with the pushed patch, but the user is complaining on 10.6. Once the fix is merged upstream we can tell certainly if it's the same bug or not.

            Works well on 10.6.17, no crashes. Thanks all.

            ykos Yevgeny Kosarzhevsky added a comment - Works well on 10.6.17, no crashes. Thanks all.

            People

              oleg.smirnov Oleg Smirnov
              ykos Yevgeny Kosarzhevsky
              Votes:
              0 Vote for this issue
              Watchers:
              8 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.