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

Using EQUALS in a WHERE clause on a specific column returns no results

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 1.5.3
    • 5.4.1
    • PrimProc
    • None

    Description

      We have a breaking issue with Columnstore. It was working fine earlier today but all the sudden, queries that are executed using equals in the code column are returning 0 results. Here is one of the query examples showing the issue:

      MariaDB [AAA]> SELECT ANI_Digits,Call_Date,Call_Direction,DATE_FORMAT( '2008-12-31 ' + INTERVAL Call_Duration SECOND, '%H:%i:%s' ) as Call_Duration,ROUND( ( Call_Duration / 60.0) , 2 ) AS Minutes, Call_Time,Dialed_Digits,Extension,HSITE,ITrun,Location_Code,NPA,NXX,Outgoing_Trunk,code FROM foo WHERE Call_Date BETWEEN '2019-04-10 12:00:00' AND '2019-04-12 12:18:00' LIMIT 5;
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ANI_Digits Call_Date Call_Direction Call_Duration Minutes Call_Time Dialed_Digits Extension HSITE ITrun Location_Code NPA NXX Outgoing_Trunk code
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      NULL 2019-04-11 12:04:56 Outbound 00:00:04 0.07 12:04:56 4213004 2333410 _fl NULL FAH NULL NULL 8850 nnn
      NULL 2019-04-11 12:04:56 Outbound 00:00:04 0.07 12:04:56 4213004 4213265 _fl NULL FAH NULL NULL 8850 yyy
      2019-04-11 12:05:00 Incoming 00:00:00 0.00 12:05:00 NULL 8850 _fl 6767 FAH NULL NULL NULL NULL
      2019-04-11 12:05:00 Incoming 00:00:00 0.00 12:05:00 NULL 8850 _fl 8850 FAH NULL NULL NULL NULL
      2019-04-11 12:05:03 Incoming 00:00:57 0.95 12:05:03 NULL 4203706 _fl 6888 FAH NULL NULL NULL vvv
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Notice that 3 records contain a value in the code field. When I include the code condition in the query, I get nothing:

      MariaDB [AAA]> SELECT ANI_Digits,Call_Date,Call_Direction,DATE_FORMAT( '2008-12-31 ' + INTERVAL Call_Duration SECOND, '%H:%i:%s' ) as Call_Duration,ROUND( ( Call_Duration / 60.0) , 2 ) AS Minutes, Call_Time,Dialed_Digits,Extension,HSITE,ITrun,Location_Code,NPA,NXX,Outgoing_Trunk,code FROM foo WHERE Call_Date BETWEEN '2019-04-10 12:00:00' AND '2019-04-12 12:18:00' AND code='yyy' LIMIT 5;

      Empty set (0.011 sec)

      I confirmed that there are no extra spaces in the value. Whats odd is it does return results if I use LIKE with no wild cards:

      MariaDB [AAA]> SELECT ANI_Digits,Call_Date,Call_Direction,DATE_FORMAT( '2008-12-31 ' + INTERVAL Call_Duration SECOND, '%H:%i:%s' ) as Call_Duration,ROUND( ( Call_Duration / 60.0) , 2 ) AS Minutes, Call_Time,Dialed_Digits,Extension,HSITE,ITrun,Location_Code,NPA,NXX,Outgoing_Trunk,code FROM foo WHERE Call_Date BETWEEN '2019-04-10 12:00:00' AND '2019-04-12 12:18:00' AND code LIKE 'yyy' LIMIT 5;
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ANI_Digits Call_Date Call_Direction Call_Duration Minutes Call_Time Dialed_Digits Extension HSITE ITrun Location_Code NPA NXX Outgoing_Trunk code
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      NULL 2019-04-11 12:04:56 Outbound 00:00:04 0.07 12:04:56 4213004 4213265 _fl NULL FAH NULL NULL 8850 yyy
      NULL 2019-04-11 12:05:24 Outbound 00:00:36 0.60 12:05:24 4213004 4213004 _fl NULL FAH NULL NULL 8850 yyy
      2019-04-11 12:05:33 Incoming 00:00:27 0.45 12:05:33 NULL 4214179 _fl 6888 FAH NULL NULL NULL yyy
      2019-04-11 12:05:46 Incoming 00:00:14 0.23 12:05:46 NULL 4215923 _fl 6888 FAH NULL NULL NULL yyy
      7172151364 2019-04-11 12:05:54 Incoming 00:05:06 5.10 12:05:54 NULL 4213439 _fl 6767 FAH NULL NULL NULL yyy
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      5 rows in set (0.077 sec)

      I was able to reproduce with the prcedure provided by the customer in the Case.

      MariaDB [foo]> LOAD DATA INFILE 'foo_data'
      -> INTO TABLE foo
      -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
      -> LINES TERMINATED BY '\n';
      Query OK, 7001 rows affected (2.364 sec)

      Records: 7001 Deleted: 0 Skipped: 0 Warnings: 0

      MariaDB [foo]> SELECT code FROM foo WHERE code LIKE 'yyy' LIMIT 1;
      -----------
      code
      -----------
      yyy
      -----------
      1 row in set (0.106 sec)

      MariaDB [foo]> SELECT code FROM foo WHERE code = 'yyy' LIMIT 1;
      Empty set (0.007 sec)
      MariaDB [foo]>

      Attachments

        Activity

          People

            dleeyh Daniel Lee (Inactive)
            nicklamb Nick (Inactive)
            Votes:
            0 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.