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

in/like/equal(=) returns different result

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 5.5.2
    • 6.1.1
    • None
    • None
    • 2021-8, 2021-9

    Description

      User reported that they are getting the different result when using in or like. Here is the example and table definition.

      MariaDB > SELECT Call_Date,Hub_Site,Site_Code,Call_Direction FROM tb1 WHERE Site_Code IN ('stp') ORDER BY Call_Date DESC LIMIT 1\G;
      *************************** 1. row ***************************
      Call_Date: 2021-05-12 16:44:56
      Hub_Site: _ai
      Site_Code: stp
      Call_Direction: Outbound
       
      MariaDB > SELECT Call_Date,Hub_Site,Site_Code,Call_Direction FROM tb1 WHERE Site_Code LIKE 'stp' ORDER BY Call_Date DESC LIMIT 1\G;
      *************************** 1. row ***************************
      Call_Date: 2021-05-26 16:48:36
      Hub_Site: _ai
      Site_Code: stp
      Call_Direction: Outbound
       
      Create Table: CREATE TABLE `tb1` (
        `CallID` bigint(25) NOT NULL,
        `pkid` varchar(120) NOT NULL DEFAULT ' ',
        `Hub_Site` varchar(5) DEFAULT '',
        `Site_Code` varchar(5) DEFAULT '',
        `Call_Direction` varchar(25) DEFAULT '',
        `Call_Date` datetime DEFAULT NULL
      ) ENGINE=Columnstore DEFAULT CHARSET=latin1
      

      When checking length of that

      MariaDB> SELECT MIN(lengthb(Site_Code)), MAX(lengthb(Site_Code)) FROM tb1\G;
      *************************** 1. row ***************************
      MIN(lengthb(Site_Code)): 3
      MAX(lengthb(Site_Code)): 3
      1 row in set (0.504 sec)
      

      User created the same table and imported data once again, but it did happen sometime after.
      This also happened another table as below. FYI, tb2 table has the same structure as tb1 above.

      MariaDB > SELECT Call_Date,Site_Code,code_dial FROM tb2 WHERE code_dial = '9' ORDER BY Call_Date DESC LIMIT 5\G;
      *************************** 1. row ***************************
      Call_Date: 2021-05-15 14:51:18
      Site_Code: ahk
      code_dial: 9
       
      MariaDB > SELECT Call_Date,Site_Code,code_dial FROM tb2 WHERE code_dial LIKE '9' ORDER BY Call_Date DESC LIMIT 5\G;
      *************************** 1. row ***************************
      Call_Date: 2021-06-01 23:49:56
      Site_Code: asc
      code_dial: 9
      

      Attachments

        1. z-custom-my.cnf
          0.3 kB
        2. server.cnf
          1 kB
        3. mysql-clients.cnf
          0.2 kB
        4. columnstore.cnf
          0.5 kB
        5. client.cnf
          0.3 kB
        6. mariadb-enterprise.cnf
          1 kB
        7. repro.sql
          1 kB
        8. repro.sql
          2 kB

        Activity

          People

            dleeyh Daniel Lee (Inactive)
            allen.lee@mariadb.com Allen Lee (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            6 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.