I'm testing Infinidb v.4.0 and finding some strange results in my query. I'm loading about 500K rows in to the database and the table looks like this:
create table tradesummary(
acctId varchar(15) default null,
acctType tinyint default null,
symbol varchar(10) default null,
wrapper varchar(4) default null,
tradeDate date default null,
lastTradeSeqNum int default null,
numberOfLongTrades smallint default null,
numberOfShortTrades smallint default null,
turnover decimal(12,0) default null,
spreadPnL decimal(12,0) default null,
assetType varchar(20) default null,
month tinyint default null,
year smallint default null
I've seen a few different issues:
1. If I run the following query,
select acctId, wrapper, count(*) from tradesummary group by acctId, wrapper, I get a series of rows back. However, if I select a column(s) for a particular acctId, I get an empty set back.
2. On some occasions, I see NULL & _CpTf_ for columns however there is no null row for acctId in my input file. The data is currently loaded into the db using LOAD DATA INFILE.
3. When I ran truncate table and loaded the data again, I got index corruption and couldn't select the column in the where clause however could see the data when the column is selected using another key. Dropping the table and recreating it seemed to address that issue.
Happy to share further information and any help is much appreciated.