MDEV-13523: Group By in a View, called within a Stored Routine causes Error Code 1356 when a non-root user runs the routine for a second time

it is actually test suite for second code chunk fix in MDEV-13439

2 files changed, 73 insertions, 0 deletions

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 81cc41e5ab3..d0b2675ebb2 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -6589,5 +6589,40 @@ use test;
 drop database test_db;
 drop user foo@localhost;
 #
+# MDEV-13523: Group By in a View, called within a Stored Routine
+# causes Error Code 1356 when a non-root user runs the routine for
+# a second time
+#
+CREATE DATABASE bugTest;
+USE bugTest;
+CREATE TABLE `procViewTable` (`id` int(10), `someText` varchar(50) NOT NULL);
+insert  into `procViewTable` values (1,'Test'), (2,'Test 2');
+CREATE USER 'procView'@'%';
+GRANT ALL PRIVILEGES ON `bugTest`.* TO 'procView'@'%';
+CREATE DEFINER=`procView`@`%` VIEW `procViewSimple` AS (
+select * from (
+select `id` from `bugTest`.`procViewTable`
+  ) `innerQuery`
+  group by `innerQuery`.`id`
+);
+connect  con1,localhost,procView,,;
+use bugTest;
+prepare stmt from "SELECT * FROM procViewSimple";
+execute stmt;
+id
+1
+2
+execute stmt;
+id
+1
+2
+disconnect con1;
+connection default;
+drop user procView;
+drop view procViewSimple;
+drop table procViewTable;
+use test;
+drop database bugTest;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index de7edd158e5..e2164e438dc 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -6307,5 +6307,43 @@ drop database test_db;
 drop user foo@localhost;
 
 --echo #
+--echo # MDEV-13523: Group By in a View, called within a Stored Routine
+--echo # causes Error Code 1356 when a non-root user runs the routine for
+--echo # a second time
+--echo #
+
+CREATE DATABASE bugTest;
+USE bugTest;
+
+CREATE TABLE `procViewTable` (`id` int(10), `someText` varchar(50) NOT NULL);
+insert  into `procViewTable` values (1,'Test'), (2,'Test 2');
+
+CREATE USER 'procView'@'%';
+GRANT ALL PRIVILEGES ON `bugTest`.* TO 'procView'@'%';
+
+CREATE DEFINER=`procView`@`%` VIEW `procViewSimple` AS (
+  select * from (
+    select `id` from `bugTest`.`procViewTable`
+  ) `innerQuery`
+  group by `innerQuery`.`id`
+);
+
+--connect (con1,localhost,procView,,)
+use bugTest;
+
+prepare stmt from "SELECT * FROM procViewSimple";
+execute stmt;
+execute stmt;
+
+# Cleanup
+--disconnect con1
+--connection default
+drop user procView;
+drop view procViewSimple;
+drop table procViewTable;
+use test;
+drop database bugTest;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #