Record doesn't show if selecting earlier date interval

1 vote
3 answers
93 views
                          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).  
                        
Asked by Klemkas (13 rep)
May 25, 2017, 06:27 AM
Last activity: May 29, 2017, 11:44 AM
Record doesn't show if selecting earlier date interval

Related Questions
