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

Wrong result ('pNuLl_' instead of NULL) in the TEXT column when LEFT OUTER JOIN is used with WHERE condition

    XMLWordPrintable

Details

    Description

      Consider the following simple test case:

      MariaDB [test]> create table A (id bigint, vc varchar(100), t text, fk_b bigint) engine = Columnstore;
      Query OK, 0 rows affected (10.585 sec)
       
      MariaDB [test]> create table B (id bigint, vc varchar(100)) engine = Columnstore;
      Query OK, 0 rows affected (9.859 sec)
       
      MariaDB [test]> insert into A values(11, 'abc', 'XYZ', 2);
      Query OK, 1 row affected (2.188 sec)
       
      MariaDB [test]> insert into B values (1, 'bla'), (2, 'doh');
      Query OK, 2 rows affected (1.672 sec)
      Records: 2  Duplicates: 0  Warnings: 0
      

      This queries produce correct and expected results:

      MariaDB [test]> select A.t from B left outer join A on A.fk_b = B.id;
      +------+
      | t    |
      +------+
      | NULL |
      | XYZ  |
      +------+
      2 rows in set (0.321 sec)
       
      MariaDB [test]> select left(A.vc,20) from B left outer join A on A.fk_b = B.id where B.id = 1;
      +---------------+
      | left(A.vc,20) |
      +---------------+
      | NULL          |
      +---------------+
      1 row in set (0.370 sec)
      

      But if we SELECT the TEXT column with WHERE clause, we get some weird 'pNuLl_' string:

      MariaDB [test]> select left(A.t,20) from B left outer join A on A.fk_b = B.id where B.id = 1;
      +----------------------+
      | left(A.t,20)         |
      +----------------------+
      | pNuLl_               |
      +----------------------+
      1 row in set (0.037 sec)
      

      This is NOT the case with InnoDB:

      MariaDB [test]> alter table A engine = InnoDB;
      Query OK, 1 row affected (6.289 sec)               
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> alter table B engine = InnoDB;
      Query OK, 2 rows affected (6.121 sec)              
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select left(A.t,20) from B left outer join A on A.fk_b = B.id where B.id = 1;
      +--------------+
      | left(A.t,20) |
      +--------------+
      | NULL         |
      +--------------+
      1 row in set (0.104 sec)
      

      Attachments

        Activity

          People

            sergey.zefirov Sergey Zefirov
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.