MDEV-15575 different results when using CTE and big_tables=1.

This bug happened due to a defect of the implementation of the handler
function ha_delete_all_rows() for the ARIA engine.
The function maria_delete_all_rows() truncated the table, but it didn't
touch the write cache, so the cache's write offset was not reset.
In the scenario like in the function st_select_lex_unit::exec_recursive
when first all records were deleted from the table and then several new
records were added some metadata became inconsistent with the state of
the cache. As a result the table scan function could not read records
at the end of the table.
The same defect could be found in the implementation of ha_delete_all_rows()
for the MYISAM engine mi_delete_all_rows().

Additionally made late instantiation for the temporary table used to store
rows that were used for each new iteration when executing a recursive CTE.

1 files changed, 12 insertions, 0 deletions

diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index c3a5ed5cff0..373d6ca97cd 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -2097,3 +2097,15 @@ WITH RECURSIVE cte AS
 	UNION
 	SELECT @c:=@c+1 FROM cte WHERE @c<3)
 SELECT * FROM cte;
+
+--echo #
+--echo # MDEV-15575: using recursive cte with big_tables enabled
+--echo #
+
+set big_tables=1;
+
+with recursive qn as
+(select 123 as a union all select 1+a from qn where a<130)
+select * from qn;
+
+set big_tables=default;