SP For Collect Loop not honoring order by clause

Help
1

Hi,
I am Abdul from Thailand. We are having problem with Stored Procedure. We have Query having multiple tables with group by and order by clause. We are using this query in For Collect loop. Problem is this for loop is not honoring order by clause when we call procedure using call command in memsql. In for loop we are basically inserting data in table got from order by clause query. For each execution order of data is different. Seems like for loop not honoring order by clause.
If we run same query outside SP then order by works perfectly. So, whatsoever no issue in query. We desperately require help.

MemSql version : 5.5.58

2

What does
select @@memsql_version
show?

And can you please give some code to show the repro?

4

Select @@Version is 5.5.58
Select @@mysql_version is 7.1.3

So little sudo code is as follow:

Select concat(sd.pro_code,sd.amp_code,sd.tam_code),sd.tam_code,mb.msisdn,min(lh.timestamp)
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
			 
			 
			 ....
			 ..
			 .
			 .
			 ...
			 END 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.

5

The ORDER BY is supposed to get honored, I believe. This may be a bug. I’ll pass it on to the dev team.

E.g.

delimiter //
create or replace procedure p() as
declare 
  q query(a int);
begin
  drop temporary table if exists t;
  create temporary table t(a int);
  for i in 1..10 loop
     insert into t values(rand()*100);
  end loop;
  q = select a from t order by a;
  for r in collect(q) loop
    echo select r.a as a;
  end loop;
end //
delimiter ;

then

call p();
+------+
| a    |
+------+
|   30 |
+------+
1 row in set (0.13 sec)

+------+
| a    |
+------+
|   31 |
+------+
1 row in set (0.13 sec)

+------+
| a    |
+------+
|   38 |
+------+
1 row in set (0.13 sec)
...

(everything is in ascending order).

6

Yeah very simple query works for order by even if you go by string column type. Problem occurs with our join query. That should honor order by as well as per expectation.