Record doesn't show if selecting earlier date interval

Question

i encountered a very strange behaviour on mysql database.

I have this complicated query:

SELECT 
    category, subcategory, sum(hours), count(hours), 
    group_concat(concat(user,': ', comment) separator ' ') as comments 
FROM 
    duomenys, workers 
WHERE 
    duomenys.user=workers.full_name 
    AND date BETWEEN '2017-05-23' AND '2017-05-25' 
    AND user='USERNAME' 
    AND departament='Some departament' 
GROUP BY 
    category, subcategory 
ORDER BY 
    id ASC;

In short it selects records from time interval (concats identical comments and sums hours).

For example:

Category   Subcategory   Hours  Count  Comments
Category1  Subcategory1  2:30   3      Username: User comment.

The problem is, if i increase time interval BETWEEN '2017-05-22' AND '2017-05-25', one record from 2017-05-24 is not displayed. Not sure if it's only this one, a user spotted it missing. I can select any date, or even interval of one day date, it shows ok. But if i select 22 or earlier as starting date, it dissapears from results.

The record is normal, same as others, the query is the same, i cannot spot anything unusual. Same behaviour happens in phpmyadmin if i use this query directly on the database.

Table structures are very simple, no external keys, etc.:

CREATE TABLE IF NOT EXISTS `duomenys` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Ivedimas` datetime NOT NULL,
`Data` date NOT NULL,
`Category` varchar(255) NOT NULL,
`Subcategory` varchar(255) NOT NULL,
`Hours` int(11) NOT NULL,
`Comment` text NOT NULL,
`User` varchar(64) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18871 ;

CREATE TABLE IF NOT EXISTS `workers` (
`name` varchar(20) NOT NULL,
`full_name` varchar(80) NOT NULL,
`skyrius` varchar(80) NOT NULL,
`departament` varchar(80) NOT NULL,
PRIMARY KEY (`vardas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm sorry if names don't match somewhere, i tried to translate fields to english, for easier reading.

EDIT: I thought i found the answer. After carefully examing that record, i found this quotation mark used in the record " instead of “„ like on every other records. But changing the quotation mark didn't help. And it shouldn't, because i use mysql_real_escape_string before inserting into databse. That record looks like this:

SCR-4.1.1-2017 „Scrambled ciklo, scrambled ir daliųjų medžiagų vežimo sertifikatų išdavimo taisyklės“ projekto peržiūra (pakartotinai).  

Answer 1

I think the problem with you query is the use of aggregated functions: sum and count. Using group by category, subcategory shows only rows with different category and subcategory values: only one row for each category and subcategory.

You can add comments column on your group by clause. Another option is to remove group by clause, sum and count functions, but this second option generates a result without aggregates values.

I hope this helps

Miquel Boada

Answer 2

I think using a view you can simplify the query. First you compute the aggregate values, and then you join the new view with your table.

You can use something like

create view vw_cat_subcat_hours_count as SELECT distinct Category, subcategory, sum(hours) as sumHours,count(*) as totalRows from duomenys INNER JOIN workers ON user='user2' group by category, subcategory;

select distinct D.Category, D.Subcategory, totalRows, sumHours, comment from vw_cat_subcat_hours_count VW inner join duomenys D on D.Category=VW.Category and D.Subcategory=VW.subcategory;

You need to add interval time in this query, but I think this can work for your needs!

Good luck

Miquel

Answer 3

I found one more culprit. It might be that my query is good after all, cannot be 100% sure, because it's hard to test. But group_concat function has limit on the output length. Changing group_concat_max_len to a bigger number in my.cnf configuration seems to have fixed the issue. Probably the record in question was just truncated.