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

wrong string comparisation after dataimport and extents

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.1.5, 1.1.6, 1.2.5
    • 1.1.0
    • None
    • None
    • 2019-06

    Description

      To Reproduce

      Copy attached export-fc.csv to /usr/local/mariadb/columnstore/mysql/db

      DROP TABLE IF EXISTS t1;
       
      CREATE TABLE t1 (
      `s1` varchar(2) DEFAULT NULL
      ) ENGINE=Columnstore DEFAULT CHARSET=utf8;
       
      LOAD DATA INFILE '/usr/local/mariadb/columnstore/mysql/db/export-fc.csv'
      INTO TABLE t1
      CHARACTER SET 'utf8'
      FIELDS TERMINATED BY ';' ENCLOSED BY '\"'
      LINES TERMINATED BY '\n';
      

      Comparisation result in where clause is everytime 0

      MariaDB [test]> select COUNT(*) from t1 where s1 = '60';                      
      +----------+
      | COUNT(*) |
      +----------+
      |        0 |
      +----------+
      1 row in set (0.00 sec)
      

      It works with converting

      MariaDB [test]> select COUNT(*) from t1 where convert(s1,CHAR(2)) = '60';
      +----------+
      | COUNT(*) |
      +----------+
      |    65572 |
      +----------+
      1 row in set (0.41 sec)
      

      Interesting is, if we create random strings into an innodb table and inserting into a columnstore table, the opposite is the case

      Testcase:

      drop table if exists `t1`;
      drop table if exists `t2`;
       
      CREATE TABLE `t1` (
      `s1` varchar(2) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      CREATE TABLE `t2` (
      `s1` varchar(2) DEFAULT NULL
      ) ENGINE=Columnstore DEFAULT CHARSET=utf8;
       
      DROP PROCEDURE IF EXISTS InsertRandt1;
       
      DELIMITER $$
      CREATE PROCEDURE InsertRandt1(IN NumRows INT, IN MinVal INT, IN MaxVal INT)
          BEGIN
              DECLARE i INT;
              SET i = 1;
              START TRANSACTION;
              WHILE i <= NumRows DO
                  INSERT INTO t1 VALUES (lpad(MinVal + CEIL(RAND() * (MaxVal - MinVal)),2,'0' ));
                  SET i = i + 1;
              END WHILE;
              COMMIT;
          END$$
      DELIMITER ;
       
      call InsertRandt1(5000000, 1,100);
       
      insert into `t2` select * from `t1`;
      

      MariaDB [test]> select COUNT(*) from t1 where s1 = '60';
      +----------+
      | COUNT(*) |
      +----------+
      |    50385 |
      +----------+
      1 row in set (1.39 sec)
       
      MariaDB [test]> select COUNT(*) from t1 where s1 = convert(s1,CHAR(2)) = '60';
      +----------+
      | COUNT(*) |
      +----------+
      |        0 |
      +----------+
      1 row in set (1.97 sec)
      
      

      Attachments

        Issue Links

          Activity

            People

              dleeyh Daniel Lee (Inactive)
              Richard Richard Stracke
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.