MySQL query help - 'count' as variable not working as expected

Question

I have the following (fairly complex) query:

SELECT
  @idx := 
    CASE 
      WHEN @prev_paper = paper_id
        THEN @idx +1
      ELSE 1 
    END AS idx,
@prev_paper := t1.paper_id AS paper_id,
@cnt := (SELECT COUNT(DISTINCT(organization)) as dcnt from authors A INNER JOIN authors__papers AP on AP.author_id = A.author_id where AP.is_contact_author < 1 AND paper_id = @prev_paper GROUP BY paper_id) as org_count,
IF(@cnt > 1, GROUP_CONCAT('{', @idx, '}', first_name, last_name), GROUP_CONCAT(first_name, last_name)) AS names
FROM (
SELECT 
AP.paper_id as paper_id, A.organization, A.first_name, A.last_name, A.country
FROM authors__papers AP
INNER JOIN authors A ON A.author_id = AP.author_id
WHERE AP.is_contact_author <1
) AS t1, (
SELECT @prev_paper :=  '', @idx :=0
) AS t2
GROUP BY paper_id, organization
ORDER BY paper_id, organization

And it outputs results as follows:

idx paper_id    org_count   names
1   5002        2           MarioIannazzo,EduardAlarcon
2   5002        2           {2}VikramPassi,{2}HimadriPandey,{2}MaxLemme
1   5003        1           {1}JiaSun
1   5004        1           Juan A.Leñero-Bardallo,AngelRodríguez-Vázquez,RicardoCarmona-Galán
1   5005        3           AlexandreVernhet,JeanCoignus
2   5005        3           {2}GerardGhibaudo
3   5005        3           {3}Jean-LucOgier,{3}GiulioTorrente,{3}DavidRoy
1   5006        1           {1}JerodMason,{1}PaulDicarlo,{1}HanchingFuh,{1}DavidWhitefield,{1}FlorinelBalteanu
1   5007        3           SivkhengKor,DavidSchwartz,JanosVeres,PingMei
2   5007        3           {2}ChristerKarlsson,{2}PerBroms
3   5007        3           {3}Tse NgaNg
...

As you can see, 'org_count' (@cnt) is not working as expected. '@idx' is not appended to the names sometimes when it should be because it's > 1 (like 5002) and is sometimes when it is not expected to be because it is = 1 (like 5003, 5006 ...). This should look like:

idx paper_id    org_count   names
1   5002        2           {1}MarioIannazzo,{1}EduardAlarcon
2   5002        2           {2}VikramPassi,{2}HimadriPandey,{2}MaxLemme
1   5003        1           JiaSun
1   5004        1           Juan A.Leñero-Bardallo,AngelRodríguez-Vázquez,RicardoCarmona-Galán
1   5005        3           {1}AlexandreVernhet,{1}JeanCoignus
2   5005        3           {2}GerardGhibaudo
3   5005        3           {3}Jean-LucOgier,{3}GiulioTorrente,{3}DavidRoy
1   5006        1           JerodMason,PaulDicarlo,HanchingFuh,DavidWhitefield,FlorinelBalteanu
1   5007        3           {1}SivkhengKor,{1}DavidSchwartz,{1}JanosVeres,{1}PingMei
2   5007        3           {2}ChristerKarlsson,{2}PerBroms
3   5007        3           {3}Tse NgaNg
    ...

It's like something seems off by 1, but I cannot for the life of me figure out what or why. Any help is appreciated!

For one thing you are not safely using variables. See Baron's [Required Reading](http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/) blog if you ever use them. I have almost given up caring to point it out to people like [here](http://stackoverflow.com/questions/38407605/sql-ranking-multiple-groups/38407642#comment64239393_38407642) . "Hey I got the right results get out of my face" ... "Yeah you're right and I am wrong" ... so off to music I go — Drew, Jul 17 '16 at 15:25
And they are very time consuming to write for people like [1](http://stackoverflow.com/a/37943846) and [2](http://stackoverflow.com/a/38000571) . Spending a few hours and still getting it wrong is not uncommon. So it is not that people don't care to help, they just don't have it in them half the time (or they are hacks and don't know any better). The reward system on the stack is slanted toward answering easy lollipop questions that people understand. Variable answers are not among them. — Drew, Jul 17 '16 at 15:44

score 1 · Answer 1 · edited Jan 08 '22 at 16:07

I can't really tell what you want the query to do, but I think I know the problem. MySQL does not guarantee the order of evaluations of expressions in a select clause. So, variables are being assigned in some expressions and then used in others -- but the order of evaluation is unclear.

My problem in understand the query is based on things like the first column of the query is @prev_paper, but the first column of the results is labeled id.

The trick to using variables correctly is to put all the logic in a single expression.

score 1 · Answer 2 · answered Jul 17 '16 at 15:48

1

Marking as answered. @Drew provided the link to Required Reading in his comment will hopefully lead to the solution.

answered Jul 17 '16 at 15:48

D Durham

1,243
2
20
42

MySQL query help - 'count' as variable not working as expected

2 Answers2