1

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).  
  • I'd suggest to use std JOIN syntax: FROM data INNER JOIN workers ON data.user = workers.full_name and It'd helps if you provide table schema and some sample data – McNets May 25 '17 at 7:12
  • I tried to use INNER JOIN as you suggested, but the result is the same, missing one record. – Klemkas May 25 '17 at 8:42
  • I'm very unskilled with databases, i cannot grasp the principle on how joins work, so i use what i understand, simple CRUD operations. I just tested two queries SELECT 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. – Klemkas May 25 '17 at 8:57
2

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

  • It's a total mystery now. I tried removing sum, count and group by, used this query SELECT kategorija, subkategorija, detales as komentarai from duomenys INNER JOIN darbuotojai ON duomenys.useris=darbuotojai.pilnas_vardas AND data BETWEEN '2017-05-19' AND '2017-05-24' AND useris='some user' ORDER BY id ASC And the record in question does NOT appear! If i change the first date to '2017-05-20' or 21 or 22 or 23, it is shown! If it's 19, it's not. Is has to be something with dates.. – Klemkas May 25 '17 at 9:03
  • Ok, it was my stupid mistake, the record appeared on the second page. So removing these functions helped to show it, i'll keep trying further. – Klemkas May 25 '17 at 10:01
  • You are right, there is a mistake in my query. It's funny that this mistake emerged after 6 months of using this software. Now i'll have to find a way to correct it somehow... Oh boy... :/ This query was so sophisticated and hard to write.. – Klemkas May 25 '17 at 10:10
  • I think using a view you can simplify the query. First you compute the aggregate values, and then you add the column 'comment' to each row. – mboada May 25 '17 at 21:19
0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.