Description:
union all truncates floating point fields
here is my sql statement:
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
(select DL.entry,DL.device,C.id,C.name,C.mark c,M.value m,ifnull(PT.value,0.) p,0. a,0. f,0. l,0.+0. t,0.+0. P,0. C,0. N
from DevLot DL
left join Entry E on E.Lot=DL.entry
left join Cntp C on C.ID=E.Cntp
left join Device D on D.ID=DL.device
left join Model M on M.ID=D.modl
left join Brand B on B.ID=M.brand
left join DevPart DP on DL.entry=DP.entry and DL.device=DP.device
left join PartType PT on PT.ID=DP.part
where (DL.entry=1 and DL.device in(1)))
union all
(select DL.entry,DL.device,C.id,C.name,C.mark c,M.value m,0. p,0. a,0. f,RL.value*(unix_timestamp(ifnull(DT.tmax,now()))-unix_timestamp(DT.tmin))/3600. l,(unix_timestamp(ifnull(DT.tmax,now()))-unix_timestamp(DT.tmin))/3600. t,0. P,0. C,0. N
from DevLot DL
left join Entry E on E.Lot=DL.entry
left join Cntp C on C.ID=E.Cntp
left join Device D on D.ID=DL.device
left join Model M on M.ID=D.modl
left join Brand B on B.ID=M.brand
left join DevTime DT on DL.entry=DT.entry and DL.device=DT.device
left join Operator O on O.id=DT.operator
left join Role RL on RL.ID=O.role
left join workplace WP on WP.ID=DT.workplace
where (DL.entry=1 and DL.device in(1)))
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
the result
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
flux>source test.sql;
+-------+--------+------+-------+------+------+---+---+---+---+---+---+---+---+
| entry | device | id | name | c | m | p | a | f | l | t | P | C | N |
+-------+--------+------+-------+------+------+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | Codec | 0 | 1000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | 1 | 1 | Codec | 0 | 1000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------+--------+------+-------+------+------+---+---+---+---+---+---+---+---+
2 rows in set (0.00 sec)
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
while only one side:
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
(select DL.entry,DL.device,C.id,C.name,C.mark c,M.value m,0. p,0. a,0. f,RL.value*(unix_timestamp(ifnull(DT.tmax,now()))-unix_timestamp(DT.tmin))/3600. l,(unix_timestamp(ifnull(DT.tmax,now()))-unix_timestamp(DT.tmin))/3600. t,0. P,0. C,0. N
from DevLot DL
left join Entry E on E.Lot=DL.entry
left join Cntp C on C.ID=E.Cntp
left join Device D on D.ID=DL.device
left join Model M on M.ID=D.modl
left join Brand B on B.ID=M.brand
left join DevTime DT on DL.entry=DT.entry and DL.device=DT.device
left join Operator O on O.id=DT.operator
left join Role RL on RL.ID=O.role
left join workplace WP on WP.ID=DT.workplace
where (DL.entry=1 and DL.device in(1)))
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
returns :
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
flux>source test.sql;
+------+--------+----+-------+---+------+---+---+---+------+------+---+---+---+
|entry | device | id | name | c | m | p | a | f | l | t | P | C | N |
+------+--------+----+-------+---+------+---+---+---+------+------+---+---+---+
| 1 | 1 | 1 | Codec | 0 | 1000 | 0 | 0 | 0 | 0 | 0.01 | 0 | 0 | 0 |
+------+--------+----+-------+---+------+---+---+---+------+------+---+---+---+
1 row in set (0.00 sec)
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
note field "t" has the value "0.01" which is not there in the union statement.
Mircea.
How to repeat:
straight union on plain table
(select * from a where val>2 union select * from a where val<2;)
doesn't display this problem
it may be related to outer joined tables.
Suggested fix:
union should work as expected.
