MySQL Reused variable outputs NULL

Question

I am using the following SQL. The column1, column2 giving output like as 122.5014 and 4.2857; But the last column giving output NULL although I was expecting 48.9266.

SQL INPUT:

SELECT (

SELECT @h := ( SUM( (
TIME_TO_SEC( TIMEDIFF(  `stop` ,  `start` ) ) /3600 ) )
)
) AS  `HoursWorked` , (

SELECT @w := ( TIMESTAMPDIFF( 
DAY , MIN(  `start` ) , MAX(  `stop` ) ) /7 )
) AS  `InWeeks` , (

SELECT (
((@w *40) - ( @h ))
)
) AS  `DutyDistance` 
FROM  `work_table`

OUTPUT:

HoursWorked | InWeeks | DutyDistance
------------------------------------
122.5014    | 4.2857  | NULL

score 0 · Answer 1 · answered May 26 '14 at 12:52

Output of user variables is not predictable when calculated with aggregate functions and then used in an expression. And hence, your query did not work.

Example:

mysql> create table tbl_so_q23870035( i int );
Query OK, 0 rows affected (0.48 sec)

mysql> insert into tbl_so_q23870035 values( 2 ), ( 4 ), ( 9 ), ( 6 ), ( 15 );
Query OK, 5 rows affected (0.13 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select @s:=sum(i) as s, @s*2 as s2 from tbl_so_q23870035;
+------+------+
| s    | s2   |
+------+------+
|   36 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> select s, s*2 as s2
    ->   from ( 
    ->          select sum(i) as s from tbl_so_q23870035 
    ->   ) aggregated_data;
+------+------+
| s    | s2   |
+------+------+
|   36 |   72 |
+------+------+
1 row in set (0.00 sec)

Hence, you have to change your query first to calculate HoursWorked, and InWeeks in inner query and then secondly calculate DutyDistance in outer query. No user variable is required.

Example:

select `HoursWorked`, `InWeeks`, ( `InWeeks` * 40 - `HoursWorked` ) AS `DutyDistance` 
from (
    select  SUM( TIME_TO_SEC( TIMEDIFF(  `stop`,  `start` ) ) / 3600 ) AS `HoursWorked`
         ,  TIMESTAMPDIFF( DAY , MIN(  `start` ), MAX(  `stop` ) ) / 7 AS `InWeeks` 
    FROM  `work_table`
) aggregated_data

MySQL Reused variable outputs NULL

1 Answers1