Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-3772

join of columnstore table to innodb table with binary column truncates latter

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 1.2.5
    • Fix Version/s: Icebox
    • Component/s: MariaDB Server
    • Labels:
    • Environment:
      docker container; image: mariadb/columnstore 421441d6c144
      Server version: 10.3.16-MariaDB-log Columnstore 1.2.5-1

      Description

      If table a using Innodb is joined with table b using columnstore, and a has a binary column, that column is not handled correctly when a and b are joined (on an integer column, for example).
      (see bottom for command-only listing)

      MariaDB [(none)]> create database x;
      MariaDB [(none)]> use x;
      MariaDB [x]> -- create one table with a 16-byte binary column using innodb
      MariaDB [x]> create table a(uuid binary(16), id integer) engine innodb;
      MariaDB [x]> insert into a values(unhex("AABBCCDDEEFF00112233445566778899"), 1);
      MariaDB [x]> insert into a values(unhex("AABBCC00DDEEFF001122334455667788"), 1);
      MariaDB [x]> -- create second table with simple columns using columnstore
      MariaDB [x]> create table b(id integer, val float(23)) engine columnstore;
      MariaDB [x]> insert into b values (1, 3.5);
      MariaDB [x]> -- verify insert
      MariaDB [x]> select hex(uuid), id from a;
      +----------------------------------+------+
      | hex(uuid)                        | id   |
      +----------------------------------+------+
      | AABBCCDDEEFF00112233445566778899 |    1 |
      | AABBCC00DDEEFF001122334466778899 |    1 |
      +----------------------------------+------+
      MariaDB [x]> -- verify insert
      MariaDB [x]> select id, val from b;
      +------+------+
      | id   | val  |
      +------+------+
      |    1 |  3.5 |
      +------+------+
      MariaDB [x]> -- join two tables on id column
      MariaDB [x]> select a.id, hex(a.uuid), b.val from b join a on b.id=a.id;
      +------+--------------+------+
      | id   | hex(a.uuid)  | val  |
      +------+--------------+------+
      |    1 | AABBCCDDEEFF |  3.5 |
      |    1 | AABBCC       |  3.5 |
      +------+--------------+------+
      ^-----^------- binary values truncated at first 0x00
       
      Commands only:
       
      create database x;
      use x;
      -- create one table with a 16-byte binary column using innodb
      create table a(uuid binary(16), id integer) engine innodb;
      insert into a values(unhex("AABBCCDDEEFF00112233445566778899"), 1);
      insert into a values(unhex("AABBCC00DDEEFF001122334455667788"), 1);
      -- create second table with simple columns using columnstore
      create table b(id integer, val float(23)) engine columnstore;
      insert into b values (1, 3.5);
      -- verify insert
      select hex(uuid), id from a;
      -- verify insert
      select id, val from b;
      -- join two tables on id column
      select a.id, hex(a.uuid), b.val from b join a on b.id=a.id;
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            jbrzusto John Brzustowski
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.