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).
FROM data INNER JOIN workers ON data.user = workers.full_name
and It'd helps if you provide table schema and some sample dataSELECT id, useris, kategorija, subkategorija, valandos, detales, data, skyrius from duomenys,darbuotojai WHERE duomenys.user=darbuotojai.full_name AND data BETWEEN '2017-05-19' AND '2017-06-24' AND user='some user' ORDER BY comment ASC
and it returns 8 results, but if i use above query, it returns 7 results.