MDEV-4978 - Server cursor is broken with blobs in the select list,

            ORDER BY does not work

Use "dynamic" row format (instead of "block") for MARIA internal
temporary tables created for cursors.

With "block" row format MARIA may shuffle rows, with "dynamic" row
format records are inserted sequentially (there are no gaps in data
file while we fill temporary tables).

This is needed to preserve row order when scanning materialized cursors.

1 files changed, 31 insertions, 0 deletions

diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 3590dc66187..ecb12408654 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -9271,4 +9271,35 @@ drop procedure p2;
 
 drop table t1;
 
+--echo #
+--echo # MDEV-4978 - Server cursor is broken with blobs in the select list,
+--echo #             ORDER BY does not work
+--echo #
+CREATE TABLE t1(a INT, b BLOB);
+INSERT INTO t1 VALUES(1,REPEAT('a',4835)),(2,'b'),(3,'c'),(4,'d'),(5,REPEAT('e',805)),(6,'f');
+
+DELIMITER |;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE done INT DEFAULT 0;
+  DECLARE v1 INT;
+  DECLARE v2 BLOB;
+  DECLARE c1 CURSOR FOR SELECT * FROM t1 ORDER BY a;
+  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
+  OPEN c1;
+  REPEAT
+    FETCH c1 INTO v1, v2;
+    IF NOT done THEN
+      SELECT v1;
+    END IF;
+  UNTIL done END REPEAT;
+  CLOSE c1;
+END|
+DELIMITER ;|
+
+CALL p1;
+
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
 --echo # End of 5.5 test