MySql is randomly retrieving different values using the same query?

Question

This query sometimes randomly gives me null values in some rows and sometimes it doesn't. It all started happening when I changed the inner joins for left joins.

CREATE temporary TABLE IF NOT EXISTS surveys_temp AS 
  (SELECT SN.id_rep, 
          Coalesce(( Sum(CASE 
                           WHEN SN.score < 7 THEN -100 
                           WHEN SN.score >= 7 
                                AND score < 9 THEN 0 
                           WHEN SN.score >= 9 THEN 100 
                         end) / Count(score) ), 0) AS NRS, 
          SW.wtr 
   FROM   surveys SN 
          INNER JOIN (SELECT id_rep, 
                             Coalesce(( Sum(CASE 
                                              WHEN score < 7 THEN -100 
                                              WHEN score >= 7 
                                                   AND score < 9 THEN 0 
                                              WHEN score >= 9 THEN 100 
                                            end) / Count(score) ), 0) AS WTR 
                      FROM   surveys 
                      WHERE  survey_type = 'WTR' 
                      GROUP  BY id_rep) SW 
                  ON SW.id_rep = SN.id_rep 
   WHERE  SN.survey_type = 'NRS' 
   GROUP  BY SN.id_rep); 

CREATE temporary TABLE IF NOT EXISTS orders_temp AS 
  (SELECT id_rep, 
          Sum(Cast(ordernumber AS DECIMAL(2, 0))) AS Orders 
   FROM   orders 
   GROUP  BY id_rep); 

CREATE temporary TABLE IF NOT EXISTS chats_temp AS 
  (SELECT id_rep, 
          Time_format(Sec_to_time(Cast(Cast(Sum(response_time * -1)/ Count( 
                                  id_session) AS 
                                  DECIMAL(5, 2 
                                              )) AS CHAR(6 
                                  ))), '%H : %i : %s')AS response_time 
   FROM   chats 
   WHERE  chat_type = 1 
   GROUP  BY id_rep 
   ORDER  BY id_rep); 

SELECT R.rep_name, 
       Count(DISTINCT R.id_session)                AS Chats, 
       O.orders, 
       Concat(Cast((o.orders/Count(DISTINCT r.id_session)) * 100 AS DECIMAL(5, 2 
              )), '%' 
       )                                           AS CONVERSION, 
       Coalesce(Cast(s.nrs AS DECIMAL(5, 2)), '0') AS NRS, 
       Coalesce(Cast(s.wtr AS DECIMAL(5, 2)), '0') AS WTR, 
       C.response_time 
FROM   reps R 
       LEFT JOIN surveys_temp AS S 
              ON S.id_rep = R.id_rep 
       LEFT JOIN orders_temp AS O 
              ON O.id_rep = R.id_rep 
       LEFT JOIN chats_temp AS C 
              ON c.id_rep = R.id_rep 
WHERE  R.rep_country IN( 'D.R', 'U.S' ) 
GROUP  BY R.rep_name 
ORDER  BY R.rep_name;

did you try to order the results and see if its truly different data? or just different showing? `ORDER BY` — John Ruddell, Mar 18 '14 at 05:12

score 3 · Accepted Answer · edited May 23 '17 at 12:00

3

A problem here is that you are violating the Single Value Rule in that you are grouping only by R.Rep_Name yet selecting other fields without introducing them via an aggregate function, e.g. columns O.Orders and C.response_time (and the derived columns via Coalesce and Concat)

Because of this violation, it is likely that not all values of the non-grouped, non-aggregated columns have the same values per Rep_Name group, and the result is indeterminate. More here

Edit(from comments below)

data looks a little better, but some rep_names are repeating

This appears to confirm the non-aggregated columns were not unique. So:

either the columns can't be shown in the context of rep_name groups,
or you have more discrete groups than you thought
or you will need to qualify the non-distinct columns with an aggregate such as Count(O.Orders), AVG(C.response_time) etc and then change the column headings as such (e.g. "TotalOrders", "AverageResponseTime" etc)

edited May 23 '17 at 12:00

Community

1
1

answered Mar 18 '14 at 05:12

StuartLC

104,537
17
209
285

I'm sorry StuartLC, but can you explain a little better?, I don't understand. – user3044096 Mar 18 '14 at 05:16
1

I've included a couple of links. Unfortunately, MySql allows this. Other RDBMS systems like Oracle and Sql-Server won't allow you to GROUP if you violate the single value rule. – StuartLC Mar 18 '14 at 05:18
1

Ohh i see what you say now, you mean I should add the columns that are not being used for an aggr function into the group by – user3044096 Mar 18 '14 at 05:20
I did what you said, and now data looks a little better, but some rep_names are repeating – user3044096 Mar 18 '14 at 05:24
Indeed, so the data in the non-aggregated columns wasn't unique. So here's your dilemmna: either the columns can't be shown in the context of `rep_name` groups, or you have more groups than you thought, or you will need to qualify the non-distinct columns with an aggregate such as `Count(O.Orders)`, `AVG(C.response_time)` etc and then change the column headings as such (e.g. "TotalOrders", "AverageResponseTime" etc) – StuartLC Mar 18 '14 at 05:28

MySql is randomly retrieving different values using the same query?

1 Answers1