[MCOL-4306] Using EQUALS in a WHERE clause on a specific column returns no results Created: 2020-09-11  Updated: 2021-04-19  Resolved: 2020-09-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 1.5.3
Fix Version/s: 5.4.1

Type: Bug Priority: Blocker
Reporter: Nick (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: 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]>

 Comments   
Comment by Mike Archipley [ 2020-09-15 ]

Hi David,
I am the developer at SAI that discovered this issue. I just wanted to follow up to see if there have been any updates on this. The field that we found to have the issue is a critical field used by our software to allow customers to view their data and we have customers that are unable to view their data because of this so it is pretty high priority on our end.

Regards,
Mike Archipley

Comment by Todd Stoffel (Inactive) [ 2020-09-20 ]

We are going to need the DDL and the version before we can reproduce.

Comment by David Hall (Inactive) [ 2020-09-22 ]

I have re-created the test from the sample data. The bug is reproducible.

Comment by David Hall (Inactive) [ 2020-09-22 ]

Added check for string types. We should not be comparing strings as signed int

Comment by Daniel Lee (Inactive) [ 2020-09-25 ]

Build verified: 5.4.1 ( Drone #759)

Reproduced the issue in 1.5.3-1

MariaDB [mytest]> 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,Hub_Site,Incoming_Trunk,Location_Code,NPA,NXX,Outgoing_Trunk,Site_Code FROM avaya WHERE Call_Date BETWEEN '2020-01-02 00:00:00' AND '2020-01-03 00:00:00' LIMIT 5;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

ANI_Digits Call_Date Call_Direction Call_Duration Minutes Call_Time Dialed_Digits Extension Hub_Site Incoming_Trunk Location_Code NPA NXX Outgoing_Trunk Site_Code

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

  2020-01-03 00:00:00 Incoming 00:00:08 0.13 06:22:52 NULL 4213377 _fl 6088 Foot_Locker_Hub NULL NULL NULL NULL
NULL 2020-01-03 00:00:00 Outbound 00:00:08 0.13 06:22:52 4213004 4213377 _fl NULL Foot_Locker_Hub NULL NULL 6798 NULL
  2020-01-03 00:00:00 Incoming 00:00:00 0.00 06:23:00 NULL 6798 _fl 6088 Foot_Locker_Hub NULL NULL NULL NULL
7179385941 2020-01-03 00:00:00 Incoming 00:02:08 2.13 06:23:52 NULL 4213004 _fl 6767 Foot_Locker_Hub NULL NULL NULL NULL
NULL 2020-01-03 00:00:00 Outbound 00:02:08 2.13 06:23:52 4213004 4213004 _fl NULL Foot_Locker_Hub NULL NULL 6798 NULL

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 rows in set (0.100 sec)

MariaDB [mytest]> 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,Hub_Site,Incoming_Trunk,Location_Code,NPA,NXX,Outgoing_Trunk,Site_Code FROM avaya WHERE Call_Date BETWEEN '2020-01-02 00:00:00' AND '2020-01-03 00:00:00' AND Site_Code='kch' LIMIT 5;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

ANI_Digits Call_Date Call_Direction Call_Duration Minutes Call_Time Dialed_Digits Extension Hub_Site Incoming_Trunk Location_Code NPA NXX Outgoing_Trunk Site_Code

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

NULL 2020-01-02 00:00:00 Outbound 01:18:31 78.52 14:16:29 12024525923 4213011 _fl NULL Foot_Locker_Hub 202 452 6088 kch
4132437092 2020-01-02 00:00:00 Incoming 01:08:44 68.73 14:18:16 NULL 4213103 _fl 6767 Foot_Locker_Hub NULL NULL NULL kch
NULL 2020-01-02 00:00:00 Outbound 01:10:17 70.28 14:34:43 18772089751 4212989 _fl NULL Foot_Locker_Hub 877 208 6768 kch
NULL 2020-01-02 00:00:00 Outbound 00:24:13 24.22 14:51:47 15404641595 4213428 _fl NULL Foot_Locker_Hub 540 464 6088 kch
NULL 2020-01-02 00:00:00 Outbound 00:24:13 24.22 14:51:47 15404641595 4213428 _fl NULL Foot_Locker_Hub 540 464 6088 kch

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 rows in set (0.043 sec)

MariaDB [mytest]> 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,Hub_Site,Incoming_Trunk,Location_Code,NPA,NXX,Outgoing_Trunk,Site_Code FROM avaya WHERE Call_Date BETWEEN '2020-01-02 00:00:00' AND '2020-01-03 00:00:00' AND Site_Code LIKE 'kch' LIMIT 5;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

ANI_Digits Call_Date Call_Direction Call_Duration Minutes Call_Time Dialed_Digits Extension Hub_Site Incoming_Trunk Location_Code NPA NXX Outgoing_Trunk Site_Code

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

NULL 2020-01-02 00:00:00 Outbound 01:18:31 78.52 14:16:29 12024525923 4213011 _fl NULL Foot_Locker_Hub 202 452 6088 kch
4132437092 2020-01-02 00:00:00 Incoming 01:08:44 68.73 14:18:16 NULL 4213103 _fl 6767 Foot_Locker_Hub NULL NULL NULL kch
NULL 2020-01-02 00:00:00 Outbound 01:10:17 70.28 14:34:43 18772089751 4212989 _fl NULL Foot_Locker_Hub 877 208 6768 kch
NULL 2020-01-02 00:00:00 Outbound 00:24:13 24.22 14:51:47 15404641595 4213428 _fl NULL Foot_Locker_Hub 540 464 6088 kch
NULL 2020-01-02 00:00:00 Outbound 00:24:13 24.22 14:51:47 15404641595 4213428 _fl NULL Foot_Locker_Hub 540 464 6088 kch

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 rows in set (0.048 sec)

Generated at Thu Feb 08 02:49:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.