Strange MySQL issue with unexpected results

Question

I have a MySQL table for reporting, the engine is innoDB, and there are some sections: / *! 50100 PARTITION BY HASH (YEAR (date) * 12 + MONTH (date)) PARTICLES 48 * /

The table has a date field, a dealId field, a placementId field (and some metric columns).

I have a dealId that is present on the date 2017-09-10:

select count(*) from report_deal_plac where date = "2017-09-10" and dealId = 11983; +----------+ | count(*) | +----------+ | 96 | +----------+ 1 row in set (0.00 sec) 

But when I try to get all dealId for this date, I can not find this dealId:

 SELECT DISTINCT(dealId) FROM report_deal_plac WHERE date = "2017-09-10"; +--------+ | dealId | +--------+ | 1938 | | 3620 | | 5892 | | 6360 | | 6814 | | 8928 | | 9010 | | 9193 | | 9282 | | 9583 | | 9676 | | 10129 | | 10300 | | 10615 | | 10858 | | 11259 | | 11388 | | 11563 | +--------+ 18 rows in set (0.00 sec) 

I tried to use the group if I had a problem with explicit, but this does not help:

 SELECT dealId FROM report_deal_plac WHERE date = "2017-09-10" group by dealId; +--------+ | dealId | +--------+ | 1938 | | 3620 | | 5892 | | 6360 | | 6814 | | 8928 | | 9010 | | 9193 | | 9282 | | 9583 | | 9676 | | 10129 | | 10300 | | 10615 | | 10858 | | 11259 | | 11388 | | 11563 | +--------+ 

Using the where clause doesn't help:

 SELECT DISTINCT(dealId) FROM report_deal_plac WHERE date = "2017-09-10" and dealId = 11983; Empty set (0.00 sec) 

I did a CHECK TABLE which says that everything is in order. I tried the OPTIMIZE table (which instead updated + analysis and had the result OK). But that does not help.

Where can the problem come from?

Here is the table definition:

 CREATE TABLE `report_deal_plac` ( `date` date NOT NULL, `dealId` int(11) NOT NULL, `placementId` int(11) NOT NULL, `impressions` int(11) NOT NULL, `impressionsKept` int(11) NOT NULL, `impressionsResold` int(11) NOT NULL, `dollarRevenue` decimal(12,6) NOT NULL DEFAULT '0.000000', `revenue` decimal(12,6) NOT NULL DEFAULT '0.000000', `netDollarRevenue` decimal(12,6) NOT NULL DEFAULT '0.000000', `netRevenue` decimal(12,6) NOT NULL DEFAULT '0.000000', `impressionsMeasured` int(11) DEFAULT '0', `impressionsViewed` int(11) DEFAULT '0', `impressionsSoldMeasured` int(11) DEFAULT '0', `impressionsSoldViewed` int(11) DEFAULT '0', `clicks` int(11) DEFAULT '0', PRIMARY KEY (`date`,`dealId`,`placementId`), KEY `dealid` (`dealId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH ( YEAR(date) * 12 + MONTH(date)) PARTITIONS 48 */ 

Here are the EXPLAIN PARTITIONS for my request

 explain partitions select DISTINCT(dealId) FROM report_deal_plac WHERE date = '2017-09-10'; +----+-------------+------------------+------------+-------+----------------+--------+---------+------+------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+------------+-------+----------------+--------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | report_deal_plac | p21 | range | PRIMARY,dealid | dealid | 4 | NULL | 533 | Using where; Using index for group-by | +----+-------------+------------------+------------+-------+----------------+--------+---------+------+------+---------------------------------------+ 

UPDATE: for epilogue:

• Using BETWEEN '2017-09-10' AND '2017-09-11' worked (there was no data for the 11th). I have no idea why. (And BETWEEN '2017-09-10' AND '2017-09-10' does not work)

• Using an aggregate function with GROUP BY worked

• SET optimizer_switch = 'index_merge_intersection=off'; Did not do anything

• REPAIR or ALTER TABLE t1 ENGINE = InnoDB; didn't change anything

All the transactions that I received were those where I had only one row for the date in the table.

I fixed the problem by creating another table with the same name and doing INSERT INTO new_table SELECT * FROM old_table , trimming the old table and reinserting it from the new one.

Answer 1

You are missing the COUNT each value. There may be several lines with dealId = 11983

 SELECT dealId, count(*) FROM report_deal_plac WHERE date = "2017-09-10" group by dealId; 

You should see the result you are looking for.