MySQL UNION ALL gives unwanted value with lead lag

Question

I have a query which is resulting correct values individually. I am seeing where act_in_sec <0 should result no values and that is correct and the problem only arises when i am doing a union all with the same query but different machines. I am not sure what is going wrong.

    Select Y.machine, Y.start_time, Y.end_time, TIMEDIFF(Y.end_time,Y.start_time) as act,
    TIME_TO_SEC(TIMEDIFF(Y.end_time,Y.start_time)) act_in_sec
    FROM
    (
    select X.machine, X.ts as start_time, X.next_ts as end_time
    from (
    select
    *
    , @prev AS next_ts
    , @prev := ts
    from
    (select * from machinestatushistory where machine = 'XXX23-4-XX' order by ts desc) AA
    , (select @prev:=NULL) vars where machine = 'XXX23-4-XX'
    order by ts desc
    ) X WHERE X.MACHINE = 'XXX23-4-XX'

I am doing UNION ALL as below.

select * from (
Select Y.machine, Y.start_time, Y.end_time, TIMEDIFF(Y.end_time,Y.start_time) as act,
TIME_TO_SEC(TIMEDIFF(Y.end_time,Y.start_time)) act_in_sec
FROM
(
select X.machine, X.ts as start_time, X.next_ts as end_time
from (
select
*
, @prev AS next_ts
, @prev := ts
from
(select * from machinestatushistory where machine = 'XXX23-4-XX' order by ts desc) AA
, (select @prev:=NULL) vars where machine = 'XXX23-4-XX'
order by ts desc
) X WHERE X.MACHINE = 'XXX23-4-XX'
)Y

UNION ALL

Select A.machine, A.start_time, A.end_time, TIMEDIFF(A.end_time,A.start_time) as act,
TIME_TO_SEC(TIMEDIFF(A.end_time,A.start_time)) act_in_sec
FROM
(
select B.machine, B.ts as start_time, B.next_ts as end_time
from (
select
*
, @prev AS next_ts
, @prev := ts
from
(select * from machinestatushistory where machine = 'XXX23-42-XX' order by ts desc) AA
, (select @prev:=NULL) vars where machine = 'XXX23-42-XX'
order by ts desc
) B WHERE B.MACHINE = 'XXX23-42-XX'
)A
)W where act_in_sec<0

I am getting wrong values only from the machine which is in the second select, no matter how i order the query.

union all keeps duplicate also in 2 queries result, if you dont want duplicates use union instead. — prashant sindhu, Apr 06 '18 at 07:03

score 1 · Accepted Answer · answered Apr 06 '18 at 07:16

might not be your problem at all but @prev does not get initialised between the two statements for example

select t.id,t.username , @prev:=@prev + 1 prev,0 as prev1
from users t,(select @prev:=0) p
union all
select t.id,t.username , @prev:=@prev + 1,@prev1:=@prev1+1 as prev1
from users t,(select @prev:=0,@prev1:=0) p1;

+----+----------+------+-------+
| id | username | prev | prev1 |
+----+----------+------+-------+
|  1 | John     |    1 |     0 |
|  2 | Jane     |    2 |     0 |
|  3 | Ali      |    3 |     0 |
|  6 | Bruce    |    4 |     0 |
|  7 | Martha   |    5 |     0 |
|  8 | Sidney   |    6 |     0 |
| 10 | charlie  |    7 |     0 |
| 12 | Elisa    |    8 |     0 |
| 14 | Samantha |    9 |     0 |
| 15 | Hannah   |   10 |     0 |
| 16 | Hannah   |   11 |     0 |
| 17 | Kevin    |   12 |     0 |
| 18 | Kevin    |   13 |     0 |
| 19 | Ruth     |   14 |     0 |
|  1 | John     |   15 |     1 |
|  2 | Jane     |   16 |     2 |
|  3 | Ali      |   17 |     3 |
|  6 | Bruce    |   18 |     4 |
|  7 | Martha   |   19 |     5 |
|  8 | Sidney   |   20 |     6 |
| 10 | charlie  |   21 |     7 |
| 12 | Elisa    |   22 |     8 |
| 14 | Samantha |   23 |     9 |
| 15 | Hannah   |   24 |    10 |
| 16 | Hannah   |   25 |    11 |
| 17 | Kevin    |   26 |    12 |
| 18 | Kevin    |   27 |    13 |
| 19 | Ruth     |   28 |    14 |
+----+----------+------+-------+
28 rows in set (0.00 sec)

s_mj · Answer 2 · 2018-04-06T09:52:22.130

Found a different way to do this. Using case and order by machine - setting the first value of next machine as NULL and then difference.

select * from (Select Y.machine, Y.start_time, Y.end_time, TIMEDIFF(Y.end_time,Y.start_time) as act,
TIME_TO_SEC(TIMEDIFF(Y.end_time,Y.start_time)) act_in_sec
FROM
(
select X.machine, X.ts as start_time, X.next_ts as end_time
from (
select

*
, case when machine=@prevMac
then @prev 
else NULL end AS next_ts
, @prevMac := machine
, @prev := ts
from
(select * from machinestatushistory where machine in ('XXX23-4-XX','XXX23-42-XX') order by machine,ts desc) AA
, (select @prev:=NULL, @prevMac:=NULL) vars where machine in ('XXX23-4-XX','XXX23-42-XX')
order by machine,ts desc
) X WHERE X.MACHINE in ('XXX23-4-XX','XXX23-42-XX')
)Y

) W where act_in_sec >=0  order by start_time desc

MySQL UNION ALL gives unwanted value with lead lag

2 Answers2