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

NULL gets lost (becomes empty string), SELECT hangs with DESC index on MyISAM/Aria table

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • N/A
    • 10.8.1
    • Server
    • None

    Description

      SET max_statement_time= 2; # Just so that if it hangs, then not forever
      		 
       
      		 
      DROP TABLE IF EXISTS t3;
      		 
      CREATE TABLE t (c CHAR(8), KEY(c DESC)) ENGINE=MyISAM CHARACTER SET utf8mb4;
      		 
      INSERT INTO t VALUES (''),('foo'),(NULL),(''),('bar');
      		 
      SELECT DISTINCT c FROM t;
      		 
      SELECT c FROM t;
      		 
       
      		 
      # Cleanup
      		 
      DROP TABLE t;

      The first query returns three values, without NULL:

      preview-10.8-MDEV-13756-desc-indexes 47c18283

      		 
      SELECT DISTINCT c FROM t;
      		 
      c
      		 
       
      		 
      bar
      		 
      foo

      If count is added, it claims there are 3 empty strings instead of 2.
      The second query, plain SELECT without DISTINCT, hangs (subject to max_statement_time).

      Reproducible with MyISAM and Aria. Not reproducible with InnoDB.
      Reproducible with big5, utf8mb4, utf8mb3. Not reproducible with latin1. I didn't try other charsets.
      Not reproducible without DESC index.

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-13756 Implement descending index: KEY (a DESC, b ASC)

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27347 MyISAM/Aria table is marked as crashed upon updating NULL values in DESC index

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Summary NULL gets lost, SELECT hangs with DESC index on MyISAM/Aria table NULL gets lost (becomes empty string), SELECT hangs with DESC index on MyISAM/Aria table
            elenst Elena Stepanova made changes -
            Description {code:sql}
            SET max_statement_time= 2; # Just so that if it hangs, then not forever

            DROP TABLE IF EXISTS t3;
            CREATE TABLE t (c CHAR(8), KEY(c DESC)) ENGINE=MyISAM CHARACTER SET utf8mb4;
            INSERT INTO t VALUES (''),('foo'),(NULL),(''),('bar');
            SELECT DISTINCT c FROM t;
            SELECT c FROM t;

            # Cleanup
            DROP TABLE t;
            {code}

            The first query returns three values, without NULL:
            {code:sql|title=preview-10.8-MDEV-13756-desc-indexes 47c18283}
            SELECT DISTINCT c FROM t;
            c

            bar
            foo
            {code}
            The second query, plain SELECT without DISTINCT, hangs (subject to max_statement_time).

            Reproducible with MyISAM and Aria. Not reproducible with InnoDB.
            Reproducible with big5, utf8mb4, utf8mb3. Not reproducible with latin1. I didn't try other charsets.
            Not reproducible without DESC index.             		{code:sql}
            SET max_statement_time= 2; # Just so that if it hangs, then not forever

            DROP TABLE IF EXISTS t3;
            CREATE TABLE t (c CHAR(8), KEY(c DESC)) ENGINE=MyISAM CHARACTER SET utf8mb4;
            INSERT INTO t VALUES (''),('foo'),(NULL),(''),('bar');
            SELECT DISTINCT c FROM t;
            SELECT c FROM t;

            # Cleanup
            DROP TABLE t;
            {code}

            The first query returns three values, without NULL:
            {code:sql|title=preview-10.8-MDEV-13756-desc-indexes 47c18283}
            SELECT DISTINCT c FROM t;
            c

            bar
            foo
            {code}
            If count is added, it claims there are 3 empty strings instead of 2.
            The second query, plain SELECT without DISTINCT, hangs (subject to max_statement_time).

            Reproducible with MyISAM and Aria. Not reproducible with InnoDB.
            Reproducible with big5, utf8mb4, utf8mb3. Not reproducible with latin1. I didn't try other charsets.
            Not reproducible without DESC index.
            elenst Elena Stepanova made changes -
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            serg Sergei Golubchik added a comment -

            pushed into preview-10.8-MDEV-13756-desc-indexes

            serg Sergei Golubchik added a comment - pushed into preview-10.8- MDEV-13756 -desc-indexes
            serg Sergei Golubchik made changes -
            Status Stalled [ 10000 ] In Testing [ 10301 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.8.1 [ 26815 ]
            Fix Version/s 10.8 [ 26121 ]
            Resolution Fixed [ 1 ]
            Status In Testing [ 10301 ] Closed [ 6 ]

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.