Description:
Most likely after WL#1074: Add descending indexes support" several queries in the ps tests return result where the rows are in arbitrary order when using a storage engine where the order of rows are not guaranteed without ORDER BY.
How to repeat:
mtr ndb.ps_7ndb w4 [ fail ]
@@ -374,18 +374,18 @@ select a,b FROM t1 where a is not NULL AND b is not NULL group by a - @arg00 ; a b
+4 four
1 one 2 two 3 three -4 four prepare stmt1 from ' select a,b FROM t1 where a is not NULL AND b is not NULL group by a - ? ' ; execute stmt1 using @arg00 ; a b -1 one 2 two -3 three
+1 one
4 four
+3 three
set @arg00='two' ; select a,b FROM t1 where a is not NULL AND b is not NULL having b <> @arg00 order by a ; @@ -459,17 +459,17 @@ and b is not null group by substr(b,@arg02) having sum(a) <> @arg03 ; sum(a) @arg00 -3 b 1 b 4 b
+3 b
prepare stmt1 from ' select sum(a), ? from t1 where a > ? and b is not null group by substr(b,?) having sum(a) <> ? '; execute stmt1 using @arg00, @arg01, @arg02, @arg03; sum(a) ?
+4 b
3 b 1 b -4 b test_sequence ------ join tests ------ select first.a as a1, second.a as a2 @@ -1051,10 +1051,10 @@ execute stmt1 using @arg00; sum(a) + 200 ? 210 1 -204 1
+202 1
201 1 203 1 -202 1
+204 1
set @Oporto='Oporto' ; set @Lisboa='Lisboa' ; set @0=0 ; @@ -1072,13 +1072,13 @@ group by b ; the_sum the_town 204 Oporto -201 Oporto 203 Oporto 202 Oporto
+201 Oporto
204 Lisboa -201 Lisboa -203 Lisboa 202 Lisboa
+203 Lisboa
+201 Lisboa
prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1 group by b union distinct @@ -1086,14 +1086,14 @@ group by b ' ; execute stmt1 using @Oporto, @Lisboa; the_sum the_town -204 Oporto
+202 Oporto
201 Oporto 203 Oporto -202 Oporto -204 Lisboa
+204 Oporto
+202 Lisboa
201 Lisboa
+204 Lisboa
203 Lisboa -202 Lisboa select sum(a) + 200 as the_sum, @Oporto as the_town from t1 where a > @1 group by b @@ -1116,9 +1116,9 @@ group by b ' ; execute stmt1 using @Oporto, @1, @Lisboa, @2; the_sum the_town
+202 Oporto
204 Oporto 203 Oporto -202 Oporto 204 Lisboa 203 Lisboa select sum(a) + 200 as the_sum, @Oporto as the_town from t1
Suggested fix:
Add sorted_result before the queries which now running quires which have no guaranteed order. Previously the queries were always run in a way which caused the resultset order to be guaranteed.
