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

Compressed varchar values lost on joins when sorting on columns from joined table(s)

Details

    Description

      When (inner or outer) joining a table with compressed varchar column(s) to another table and sorting on column(s) of the joined table, compressed values are replaced with NULLs in returned result set. We heuristically assume that minimum varchar length to compress is 100, values shorter than that are not compressed and returned correctly.

      A minimal reproducible test case (reproduced in 10.11.4 and 10.6.14, probably affects previous releases as well):

      Server version: 10.11.4-MariaDB MariaDB Server
      		 
       
      		 
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
      		 
       
      		 
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
      		 
       
      		 
      MariaDB [db]> CREATE TABLE t1 (
      		 
          ->   id  int(10) unsigned not null,
      		 
          ->   txt varchar(5000) COMPRESSED NOT NULL DEFAULT '',
      		 
          ->   PRIMARY KEY (id)
      		 
          -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;
      		 
       
      		 
      Query OK, 0 rows affected (0.018 sec)
      		 
       
      		 
      MariaDB [db]> CREATE TABLE t2 (
      		 
          ->   id int(10) unsigned not null,
      		 
          ->   n1 bigint(20) NOT NULL,
      		 
          ->   n2 bigint(20) NOT NULL,
      		 
          ->   n3 bigint(20) NOT NULL,
      		 
          ->   PRIMARY KEY (id)
      		 
          -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;
      		 
      Query OK, 0 rows affected (0.009 sec)
      		 
       
      		 
      MariaDB [db]> INSERT INTO t1 VALUES
      		 
          -> (1, 'short string < 100 chars'),
      		 
          -> (2, 'long string =  99 chars                                                                            '),
      		 
          -> (3, 'long string = 100 chars                                                                            !'),
      		 
          -> (4, 'long string = 101 chars                                                                             !');
      		 
      Query OK, 4 rows affected (0.001 sec)
      		 
      Records: 4  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [db]> INSERT INTO t2 VALUES
      		 
          -> (1, 24, 1, 1),
      		 
          -> (2, 99, 2, 2),
      		 
          -> (3, 100, 3, 3),
      		 
          -> (4, 101, 4, 4)
      		 
          -> ;
      		 
      Query OK, 4 rows affected (0.001 sec)
      		 
      Records: 4  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [db]> SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id;
      		 
      +-------------------------------------------------------------------------------------------------------+------+------+------+------+
      		 
      | txt                                                                                                   | id   | n1   | n2   | n3   |
      		 
      +-------------------------------------------------------------------------------------------------------+------+------+------+------+
      		 
      | short string < 100 chars                                                                              |    1 |   24 |    1 |    1 |
      		 
      | long string =  99 chars                                                                               |    2 |   99 |    2 |    2 |
      		 
      | long string = 100 chars                                                                            !  |    3 |  100 |    3 |    3 |
      		 
      | long string = 101 chars                                                                             ! |    4 |  101 |    4 |    4 |
      		 
      +-------------------------------------------------------------------------------------------------------+------+------+------+------+
      		 
      4 rows in set (0.000 sec)
      		 
       
      		 
      MariaDB [db]> SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id ORDER BY v.n1;
      		 
      +-----------------------------------------------------------------------------------------------------+------+------+------+------+
      		 
      | txt                                                                                                 | id   | n1   | n2   | n3   |
      		 
      +-----------------------------------------------------------------------------------------------------+------+------+------+------+
      		 
      | short string < 100 chars                                                                            |    1 |   24 |    1 |    1 |
      		 
      | long string =  99 chars                                                                             |    2 |   99 |    2 |    2 |
      		 
      |                                                                                                     |    3 |  100 |    3 |    3 |
      		 
      |                                                                                                     |    4 |  101 |    4 |    4 |
      		 
      +-----------------------------------------------------------------------------------------------------+------+------+------+------+
      		 
      4 rows in set (0.001 sec)
      		 
      MariaDB [db]> SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id;
      		 
      +-------------------------------------------------------------------------------------------------------+----+-----+----+----+
      		 
      | txt                                                                                                   | id | n1  | n2 | n3 |
      		 
      +-------------------------------------------------------------------------------------------------------+----+-----+----+----+
      		 
      | short string < 100 chars                                                                              |  1 |  24 |  1 |  1 |
      		 
      | long string =  99 chars                                                                               |  2 |  99 |  2 |  2 |
      		 
      | long string = 100 chars                                                                            !  |  3 | 100 |  3 |  3 |
      		 
      | long string = 101 chars                                                                             ! |  4 | 101 |  4 |  4 |
      		 
      +-------------------------------------------------------------------------------------------------------+----+-----+----+----+
      		 
      4 rows in set (0.002 sec)
      		 
       
      		 
      MariaDB [db]> SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id ORDER BY v.n1;
      		 
      +-----------------------------------------------------------------------------------------------------+----+-----+----+----+
      		 
      | txt                                                                                                 | id | n1  | n2 | n3 |
      		 
      +-----------------------------------------------------------------------------------------------------+----+-----+----+----+
      		 
      | short string < 100 chars                                                                            |  1 |  24 |  1 |  1 |
      		 
      | long string =  99 chars                                                                             |  2 |  99 |  2 |  2 |
      		 
      |                                                                                                     |  3 | 100 |  3 |  3 |
      		 
      |                                                                                                     |  4 | 101 |  4 |  4 |
      		 
      +-----------------------------------------------------------------------------------------------------+----+-----+----+----+
      		 
      4 rows in set (0.001 sec)

      DDL and queries to reproduce:

      CREATE TABLE t1 (
      		 
        id  int(10) unsigned not null,
      		 
        txt varchar(5000) COMPRESSED NOT NULL DEFAULT '',
      		 
        PRIMARY KEY (id)
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;
      		 
       
      		 
      CREATE TABLE t2 (
      		 
        id int(10) unsigned not null,
      		 
        n1 bigint(20) NOT NULL,
      		 
        n2 bigint(20) NOT NULL,
      		 
        n3 bigint(20) NOT NULL,
      		 
        PRIMARY KEY (id)
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;
      		 
       
      		 
       
      		 
      INSERT INTO t1 VALUES 
      (1, 'short string < 100 chars'), 
      (2, 'long string =  99 chars                                                                            '),
      		 
      (3, 'long string = 100 chars                                                                            !'),
      		 
      (4, 'long string = 101 chars                                                                             !');
      		 
       
      		 
      INSERT INTO t2 VALUES
      		 
      (1, 24, 1, 1),
      		 
      (2, 99, 2, 2),
      		 
      (3, 100, 3, 3),
      		 
      (4, 101, 4, 4)
      		 
      ;
      		 
       
      		 
      SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id;
      		 
      SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id ORDER BY v.n1;
      		 
      SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id;
      		 
      SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id ORDER BY v.n1;

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24797 Column Compression - ERROR 1265 (01000): Data truncated for column

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            vladzakh Vladimir "Bob" Zakharychev created issue -
            vladzakh Vladimir "Bob" Zakharychev made changes -
            Field Original Value New Value
            Description When (inner or outer) joining a table with compressed varchar column(s) to another table and sorting on column(s) of the joined table, compressed values are replaced with NULLs in returned result set. We heuristically assume that minimum varchar length to compress is 100, values shorter than that are not compressed and returned correctly.

            A minimal reproducible test case (reproduced in 10.11.4 and 10.6.14, probably affects previous releases as well):
            {code:sql}
            Server version: 10.11.4-MariaDB MariaDB Server

            Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

            Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

            MariaDB [db]> CREATE TABLE t1 (
                -> id int(10) unsigned not null,
                -> txt varchar(5000) COMPRESSED NOT NULL DEFAULT '',
                -> PRIMARY KEY (id)
                -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;

            Query OK, 0 rows affected (0.018 sec)

            MariaDB [db]> CREATE TABLE t2 (
                -> id int(10) unsigned not null,
                -> n1 bigint(20) NOT NULL,
                -> n2 bigint(20) NOT NULL,
                -> n3 bigint(20) NOT NULL,
                -> PRIMARY KEY (id)
                -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;
            Query OK, 0 rows affected (0.009 sec)

            MariaDB [db]> INSERT INTO t1 VALUES
                -> (1, 'short string < 100 chars'),
                -> (2, 'long string = 99 chars '),
                -> (3, 'long string = 100 chars !'),
                -> (4, 'long string = 101 chars !');
            Query OK, 4 rows affected (0.001 sec)
            Records: 4 Duplicates: 0 Warnings: 0

            MariaDB [db]> INSERT INTO t2 VALUES
                -> (1, 24, 1, 1),
                -> (2, 99, 2, 2),
                -> (3, 100, 3, 3),
                -> (4, 101, 4, 4)
                -> ;
            Query OK, 4 rows affected (0.001 sec)
            Records: 4 Duplicates: 0 Warnings: 0

            MariaDB [db]> SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id;
            +-------------------------------------------------------------------------------------------------------+------+------+------+------+
            | txt | id | n1 | n2 | n3 |
            +-------------------------------------------------------------------------------------------------------+------+------+------+------+
            | short string < 100 chars | 1 | 24 | 1 | 1 |
            | long string = 99 chars | 2 | 99 | 2 | 2 |
            | long string = 100 chars ! | 3 | 100 | 3 | 3 |
            | long string = 101 chars ! | 4 | 101 | 4 | 4 |
            +-------------------------------------------------------------------------------------------------------+------+------+------+------+
            4 rows in set (0.000 sec)

            MariaDB [db]> SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id ORDER BY v.n1;
            +-----------------------------------------------------------------------------------------------------+------+------+------+------+
            | txt | id | n1 | n2 | n3 |
            +-----------------------------------------------------------------------------------------------------+------+------+------+------+
            | short string < 100 chars | 1 | 24 | 1 | 1 |
            | long string = 99 chars | 2 | 99 | 2 | 2 |
            | | 3 | 100 | 3 | 3 |
            | | 4 | 101 | 4 | 4 |
            +-----------------------------------------------------------------------------------------------------+------+------+------+------+
            4 rows in set (0.001 sec)
            MariaDB [db]> SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id;
            +-------------------------------------------------------------------------------------------------------+----+-----+----+----+
            | txt | id | n1 | n2 | n3 |
            +-------------------------------------------------------------------------------------------------------+----+-----+----+----+
            | short string < 100 chars | 1 | 24 | 1 | 1 |
            | long string = 99 chars | 2 | 99 | 2 | 2 |
            | long string = 100 chars ! | 3 | 100 | 3 | 3 |
            | long string = 101 chars ! | 4 | 101 | 4 | 4 |
            +-------------------------------------------------------------------------------------------------------+----+-----+----+----+
            4 rows in set (0.002 sec)

            MariaDB [db]> SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id ORDER BY v.n1;
            +-----------------------------------------------------------------------------------------------------+----+-----+----+----+
            | txt | id | n1 | n2 | n3 |
            +-----------------------------------------------------------------------------------------------------+----+-----+----+----+
            | short string < 100 chars | 1 | 24 | 1 | 1 |
            | long string = 99 chars | 2 | 99 | 2 | 2 |
            | | 3 | 100 | 3 | 3 |
            | | 4 | 101 | 4 | 4 |
            +-----------------------------------------------------------------------------------------------------+----+-----+----+----+
            4 rows in set (0.001 sec)
            {code}
            		When (inner or outer) joining a table with compressed varchar column(s) to another table and sorting on column(s) of the joined table, compressed values are replaced with NULLs in returned result set. We heuristically assume that minimum varchar length to compress is 100, values shorter than that are not compressed and returned correctly.

            A minimal reproducible test case (reproduced in 10.11.4 and 10.6.14, probably affects previous releases as well):
            {code:sql}
            Server version: 10.11.4-MariaDB MariaDB Server

            Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

            Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

            MariaDB [db]> CREATE TABLE t1 (
                -> id int(10) unsigned not null,
                -> txt varchar(5000) COMPRESSED NOT NULL DEFAULT '',
                -> PRIMARY KEY (id)
                -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;

            Query OK, 0 rows affected (0.018 sec)

            MariaDB [db]> CREATE TABLE t2 (
                -> id int(10) unsigned not null,
                -> n1 bigint(20) NOT NULL,
                -> n2 bigint(20) NOT NULL,
                -> n3 bigint(20) NOT NULL,
                -> PRIMARY KEY (id)
                -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;
            Query OK, 0 rows affected (0.009 sec)

            MariaDB [db]> INSERT INTO t1 VALUES
                -> (1, 'short string < 100 chars'),
                -> (2, 'long string = 99 chars '),
                -> (3, 'long string = 100 chars !'),
                -> (4, 'long string = 101 chars !');
            Query OK, 4 rows affected (0.001 sec)
            Records: 4 Duplicates: 0 Warnings: 0

            MariaDB [db]> INSERT INTO t2 VALUES
                -> (1, 24, 1, 1),
                -> (2, 99, 2, 2),
                -> (3, 100, 3, 3),
                -> (4, 101, 4, 4)
                -> ;
            Query OK, 4 rows affected (0.001 sec)
            Records: 4 Duplicates: 0 Warnings: 0

            MariaDB [db]> SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id;
            +-------------------------------------------------------------------------------------------------------+------+------+------+------+
            | txt | id | n1 | n2 | n3 |
            +-------------------------------------------------------------------------------------------------------+------+------+------+------+
            | short string < 100 chars | 1 | 24 | 1 | 1 |
            | long string = 99 chars | 2 | 99 | 2 | 2 |
            | long string = 100 chars ! | 3 | 100 | 3 | 3 |
            | long string = 101 chars ! | 4 | 101 | 4 | 4 |
            +-------------------------------------------------------------------------------------------------------+------+------+------+------+
            4 rows in set (0.000 sec)

            MariaDB [db]> SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id ORDER BY v.n1;
            +-----------------------------------------------------------------------------------------------------+------+------+------+------+
            | txt | id | n1 | n2 | n3 |
            +-----------------------------------------------------------------------------------------------------+------+------+------+------+
            | short string < 100 chars | 1 | 24 | 1 | 1 |
            | long string = 99 chars | 2 | 99 | 2 | 2 |
            | | 3 | 100 | 3 | 3 |
            | | 4 | 101 | 4 | 4 |
            +-----------------------------------------------------------------------------------------------------+------+------+------+------+
            4 rows in set (0.001 sec)
            MariaDB [db]> SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id;
            +-------------------------------------------------------------------------------------------------------+----+-----+----+----+
            | txt | id | n1 | n2 | n3 |
            +-------------------------------------------------------------------------------------------------------+----+-----+----+----+
            | short string < 100 chars | 1 | 24 | 1 | 1 |
            | long string = 99 chars | 2 | 99 | 2 | 2 |
            | long string = 100 chars ! | 3 | 100 | 3 | 3 |
            | long string = 101 chars ! | 4 | 101 | 4 | 4 |
            +-------------------------------------------------------------------------------------------------------+----+-----+----+----+
            4 rows in set (0.002 sec)

            MariaDB [db]> SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id ORDER BY v.n1;
            +-----------------------------------------------------------------------------------------------------+----+-----+----+----+
            | txt | id | n1 | n2 | n3 |
            +-----------------------------------------------------------------------------------------------------+----+-----+----+----+
            | short string < 100 chars | 1 | 24 | 1 | 1 |
            | long string = 99 chars | 2 | 99 | 2 | 2 |
            | | 3 | 100 | 3 | 3 |
            | | 4 | 101 | 4 | 4 |
            +-----------------------------------------------------------------------------------------------------+----+-----+----+----+
            4 rows in set (0.001 sec)
            {code}

            DDL and queries to reproduce:
            {code:sql}
            CREATE TABLE t1 (
              id int(10) unsigned not null,
              txt varchar(5000) COMPRESSED NOT NULL DEFAULT '',
              PRIMARY KEY (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;

            CREATE TABLE t2 (
              id int(10) unsigned not null,
              n1 bigint(20) NOT NULL,
              n2 bigint(20) NOT NULL,
              n3 bigint(20) NOT NULL,
              PRIMARY KEY (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;


            INSERT INTO t1 VALUES
            (1, 'short string < 100 chars'),
            (2, 'long string = 99 chars '),
            (3, 'long string = 100 chars !'),
            (4, 'long string = 101 chars !');

            INSERT INTO t2 VALUES
            (1, 24, 1, 1),
            (2, 99, 2, 2),
            (3, 100, 3, 3),
            (4, 101, 4, 4)
            ;

            SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id;
            SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id ORDER BY v.n1;
            SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id;
            SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id ORDER BY v.n1;
            {code}
            danblack Daniel Black made changes -
            danblack Daniel Black made changes -
            Affects Version/s 10.4.30 [ 28912 ]
            danblack Daniel Black made changes -
            Fix Version/s 10.4 [ 22408 ]
            danblack Daniel Black made changes -
            Component/s Data types [ 13906 ]
            danblack Daniel Black made changes -
            Assignee Alexander Barkov [ bar ]
            danblack Daniel Black made changes -
            Attachment MDEV-31724.test [ 71053 ]
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Oleksandr Byelkin [ sanja ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2023-08-15 04:16:47.0 2023-08-15 04:16:47.011
            bar Alexander Barkov made changes -
            Component/s Character Sets [ 10801 ]
            Fix Version/s 10.4.32 [ 29300 ]
            Fix Version/s 10.5.23 [ 29012 ]
            Fix Version/s 10.6.16 [ 29014 ]
            Fix Version/s 10.9.9 [ 29016 ]
            Fix Version/s 10.10.7 [ 29018 ]
            Fix Version/s 10.11.6 [ 29020 ]
            Fix Version/s 11.0.4 [ 29021 ]
            Fix Version/s 11.1.2 [ 28921 ]
            Fix Version/s 11.2.1 [ 29034 ]
            Fix Version/s 10.8.8 [ 28518 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.9.9 [ 29016 ]

            People

              bar Alexander Barkov
              vladzakh Vladimir "Bob" Zakharychev
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.