Mysql – Record doesn’t show if selecting earlier date interval

dateMySQLrecord

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).  

Best Answer

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