Select @@Version is 5.5.58
Select @@mysql_version is 7.1.3
So little sudo code is as follow:
from msisdn_batch mb
inner join location_history_test lh
on lh.msisdn=mb.msisdn and mb.id between batch_start and batch_end
inner join ref_location_cellsites cs
on lh.lat=cs.lat and lh.lng=cs.lng
and day(lh.timestamp) = 21 – day(current_timestamp)
inner join ref_subdistrict sd
on cs.pro_code=sd.pro_code and cs.amp_code=sd.amp_code and cs.tam_code =sd.tam_code
group by mb.msisdn,sd.tam_code
order by mb.id,min(lh.timestamp) asc;
FOR visit IN COLLECT(visit_history_query) LOOP
So, above join query is passed into for loop. We have ordered by id from msisdn_batch table which also has unique msisdn attached to each id. So, we expect results to be order by msisdn. Before id we were ordering my msisdn but was not working. So, we then introduced id column in table so that order may work. But its not working i.e. if we print msisdn within for loop then every time its different order we get.
Do note that msisdn is long char string like ‘222245980f1113519c8c3ac7l1cj0b8e397e9184a145057j14af48e26ajd1fg8’. Just in case if this has to do something with Memsql internal engine implementation.