MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure

Changing the way how a cursor is opened to fetch its structure only,
e.g. for a cursor FOR loop record variable.

The old methods with setting thd->lex->limit_rows_examined to an Item_uint(0)
was not reliable and could push these messages into diagnostics area:

  The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0)

The new method should be more reliable, as it completely prevents the call
of do_select() in JOIN::exec_inner() during the cursor structure discovery,
so the execution of the cursor SELECT query returns immediately after the
preparation step (when the result row structure becomes known),
without even entering the code that fetches the result rows.

9 files changed, 193 insertions, 36 deletions

diff --git a/mysql-test/suite/compat/oracle/r/sp-cursor.result b/mysql-test/suite/compat/oracle/r/sp-cursor.result
index 03211509f8b..e539f38e307 100644
--- a/mysql-test/suite/compat/oracle/r/sp-cursor.result
+++ b/mysql-test/suite/compat/oracle/r/sp-cursor.result
@@ -949,3 +949,31 @@ def					c%FOUND	3	1	0	Y	32896	0	63
 def					c%ROWCOUNT	8	21	1	Y	32896	0	63
 c%ISOPEN	c%NOTFOUND	c%FOUND	c%ROWCOUNT
 1	NULL	NULL	0
+#
+# MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
+#
+CREATE TABLE t1
+(
+JOBN varchar(18) NOT NULL,
+pk int(11) NOT NULL,
+PRIMARY KEY (pk),
+KEY (JOBN)
+);
+CREATE PROCEDURE p1
+AS
+lS NUMBER(10) :=0;
+CURSOR cBPD IS SELECT * FROM t1 WHERE JOBN='x';
+BEGIN
+FOR lbpd IN cBPD LOOP
+lS:=lS+1;
+END LOOP;
+EXCEPTION
+WHEN OTHERS THEN
+BEGIN
+SELECT SQLERRM;
+END;
+END;
+$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/r/sp-package.result b/mysql-test/suite/compat/oracle/r/sp-package.result
index 4c8fac26d2d..9a53b04d4ad 100644
--- a/mysql-test/suite/compat/oracle/r/sp-package.result
+++ b/mysql-test/suite/compat/oracle/r/sp-package.result
@@ -2869,3 +2869,51 @@ msg
 This is p01
 DROP PACKAGE pkg1;
 DROP TABLE t1;
+#
+# MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
+#
+CREATE TABLE t1 (
+CTR varchar(2) NOT NULL,
+COR varchar(3) NOT NULL,
+DATE datetime NOT NULL,
+CHAN varchar(4) NOT NULL,
+CNO varchar(20) NOT NULL,
+JOBN varchar(18) NOT NULL,
+C1 varchar(30) DEFAULT NULL,
+C2 varchar(30) DEFAULT NULL,
+TIME datetime DEFAULT NULL,
+AMT decimal(12,2) DEFAULT NULL,
+DT datetime NOT NULL,
+pk int(11) NOT NULL,
+PRIMARY KEY (pk),
+KEY Indx1 (JOBN)
+);
+CREATE PACKAGE xyz IS
+PROCEDURE xyz123(ctr IN VARCHAR2,Jn IN VARCHAR2,R OUT VARCHAR2);
+END;
+$$
+CREATE OR REPLACE PACKAGE BODY xyz IS
+PROCEDURE xyz123(
+ctr IN VARCHAR2,
+Jn IN VARCHAR2,
+R OUT VARCHAR2)
+AS
+lS NUMBER(10) :=0;
+CURSOR cBPD IS
+SELECT CTR, COR, DATE, CHAN, CNO, C1, C2, TIME, AMT
+FROM t1 WHERE JOBN=Jn;
+BEGIN
+FOR lbpd IN cBPD
+LOOP
+lS:=lS+1;
+END LOOP;
+EXCEPTION
+WHEN OTHERS THEN
+BEGIN
+SELECT SQLERRM;
+END;
+END;
+END $$
+CALL xyz.xyz123(17,18,@R);
+DROP PACKAGE xyz;
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-cursor.test b/mysql-test/suite/compat/oracle/t/sp-cursor.test
index 5a8b7b69f67..03b8b016ee0 100644
--- a/mysql-test/suite/compat/oracle/t/sp-cursor.test
+++ b/mysql-test/suite/compat/oracle/t/sp-cursor.test
@@ -952,3 +952,38 @@ $$
 DELIMITER ;$$
 --enable_ps_protocol
 --disable_metadata
+
+
+--echo #
+--echo # MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
+--echo #
+
+CREATE TABLE t1
+(
+  JOBN varchar(18) NOT NULL,
+  pk int(11) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY (JOBN)
+);
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  lS NUMBER(10) :=0;
+  CURSOR cBPD IS SELECT * FROM t1 WHERE JOBN='x';
+BEGIN
+  FOR lbpd IN cBPD LOOP
+    lS:=lS+1;
+  END LOOP;
+EXCEPTION
+  WHEN OTHERS THEN
+    BEGIN
+      SELECT SQLERRM;
+    END;
+END;
+$$
+DELIMITER ;$$
+
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-package.test b/mysql-test/suite/compat/oracle/t/sp-package.test
index e61dd37467c..04357a7487a 100644
--- a/mysql-test/suite/compat/oracle/t/sp-package.test
+++ b/mysql-test/suite/compat/oracle/t/sp-package.test
@@ -2624,3 +2624,60 @@ DELIMITER ;$$
 CALL pkg1.p00;
 DROP PACKAGE pkg1;
 DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
+--echo #
+
+CREATE TABLE t1 (
+  CTR varchar(2) NOT NULL,
+  COR varchar(3) NOT NULL,
+  DATE datetime NOT NULL,
+  CHAN varchar(4) NOT NULL,
+  CNO varchar(20) NOT NULL,
+  JOBN varchar(18) NOT NULL,
+  C1 varchar(30) DEFAULT NULL,
+  C2 varchar(30) DEFAULT NULL,
+  TIME datetime DEFAULT NULL,
+  AMT decimal(12,2) DEFAULT NULL,
+  DT datetime NOT NULL,
+  pk int(11) NOT NULL,
+  PRIMARY KEY (pk),
+   KEY Indx1 (JOBN)
+);
+
+DELIMITER $$;
+
+CREATE PACKAGE xyz IS
+  PROCEDURE xyz123(ctr IN VARCHAR2,Jn IN VARCHAR2,R OUT VARCHAR2);
+END;
+$$
+
+CREATE OR REPLACE PACKAGE BODY xyz IS
+  PROCEDURE xyz123(
+    ctr IN VARCHAR2,
+    Jn IN VARCHAR2,
+    R OUT VARCHAR2)
+  AS
+    lS NUMBER(10) :=0;
+    CURSOR cBPD IS
+      SELECT CTR, COR, DATE, CHAN, CNO, C1, C2, TIME, AMT
+      FROM t1 WHERE JOBN=Jn;
+  BEGIN
+    FOR lbpd IN cBPD
+    LOOP
+      lS:=lS+1;
+    END LOOP;
+  EXCEPTION
+    WHEN OTHERS THEN
+    BEGIN
+      SELECT SQLERRM;
+    END;
+  END;
+END $$
+DELIMITER ;$$
+
+CALL xyz.xyz123(17,18,@R);
+DROP PACKAGE xyz;
+DROP TABLE t1;
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index c1c938dd9e7..c8b9576fe88 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -4470,9 +4470,9 @@ sp_instr_cursor_copy_struct::exec_core(THD *thd, uint *nextp)
   */
   if (!row->arguments())
   {
-    sp_cursor tmp(thd, &m_lex_keeper);
+    sp_cursor tmp(thd, &m_lex_keeper, true);
     // Open the cursor without copying data
-    if (!(ret= tmp.open_view_structure_only(thd)))
+    if (!(ret= tmp.open(thd)))
     {
       Row_definition_list defs;
       if (!(ret= tmp.export_structure(thd, &defs)))
diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc
index 24777abe1c3..3e77d8c357b 100644
--- a/sql/sp_rcontext.cc
+++ b/sql/sp_rcontext.cc
@@ -748,33 +748,6 @@ int sp_cursor::open(THD *thd)
 }
 
 
-/**
-  Open the cursor, but do not copy data.
-  This method is used to fetch the cursor structure
-  to cursor%ROWTYPE routine variables.
-  Data copying is suppressed by setting thd->lex->limit_rows_examined to 0.
-*/
-int sp_cursor::open_view_structure_only(THD *thd)
-{
-  int res;
-  int thd_no_errors_save= thd->no_errors;
-  Item *limit_rows_examined= thd->lex->limit_rows_examined; // No data copying
-  if (!(thd->lex->limit_rows_examined= new (thd->mem_root) Item_uint(thd, 0)))
-    return -1;
-  thd->no_errors= true; // Suppress ER_QUERY_EXCEEDED_ROWS_EXAMINED_LIMIT
-  DBUG_ASSERT(!thd->killed);
-  res= open(thd);
-  /*
-    The query possibly exited on LIMIT ROWS EXAMINED and set thd->killed.
-    Reset it now.
-  */
-  thd->reset_killed();
-  thd->no_errors= thd_no_errors_save;
-  thd->lex->limit_rows_examined= limit_rows_examined;
-  return res;
-}
-
-
 int sp_cursor::close(THD *thd)
 {
   if (! server_side_cursor)
diff --git a/sql/sql_class.h b/sql/sql_class.h
index acd48b07900..1a7eb943193 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -5037,6 +5037,14 @@ public:
     Currently all intercepting classes derive from select_result_interceptor.
   */
   virtual bool is_result_interceptor()=0;
+
+  /*
+    This method is used to distinguish an normal SELECT from the cursor
+    structure discovery for cursor%ROWTYPE routine variables.
+    If this method returns "true", then a SELECT execution performs only
+    all preparation stages, but does not fetch any rows.
+  */
+  virtual bool view_structure_only() const { return false; }
 };
 
 
@@ -5156,9 +5164,13 @@ private:
   {
     List<sp_variable> *spvar_list;
     uint field_count;
+    bool m_view_structure_only;
     bool send_data_to_variable_list(List<sp_variable> &vars, List<Item> &items);
   public:
-    Select_fetch_into_spvars(THD *thd_arg): select_result_interceptor(thd_arg) {}
+    Select_fetch_into_spvars(THD *thd_arg, bool view_structure_only)
+     :select_result_interceptor(thd_arg),
+      m_view_structure_only(view_structure_only)
+    {}
     void reset(THD *thd_arg)
     {
       select_result_interceptor::reset(thd_arg);
@@ -5171,16 +5183,17 @@ private:
     virtual bool send_eof() { return FALSE; }
     virtual int send_data(List<Item> &items);
     virtual int prepare(List<Item> &list, SELECT_LEX_UNIT *u);
+    virtual bool view_structure_only() const { return m_view_structure_only; }
 };
 
 public:
   sp_cursor()
-   :result(NULL),
+   :result(NULL, false),
     m_lex_keeper(NULL),
     server_side_cursor(NULL)
   { }
-  sp_cursor(THD *thd_arg, sp_lex_keeper *lex_keeper)
-   :result(thd_arg),
+  sp_cursor(THD *thd_arg, sp_lex_keeper *lex_keeper, bool view_structure_only)
+   :result(thd_arg, view_structure_only),
     m_lex_keeper(lex_keeper),
     server_side_cursor(NULL)
   {}
@@ -5192,8 +5205,6 @@ public:
 
   int open(THD *thd);
 
-  int open_view_structure_only(THD *thd);
-
   int close(THD *thd);
 
   my_bool is_open()
diff --git a/sql/sql_cursor.cc b/sql/sql_cursor.cc
index 2a200d279b5..4f3b15c3254 100644
--- a/sql/sql_cursor.cc
+++ b/sql/sql_cursor.cc
@@ -92,6 +92,11 @@ public:
     if (materialized_cursor)
       materialized_cursor->on_table_fill_finished();
   }
+
+  bool view_structure_only() const
+  {
+    return result->view_structure_only();
+  }
 };
 
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index f3e69ba2ead..08bf2a897fc 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4025,7 +4025,7 @@ void JOIN::exec_inner()
                  procedure ? procedure_fields_list : *fields,
                  Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF);
 
-  error= do_select(this, procedure);
+  error= result->view_structure_only() ? false : do_select(this, procedure);
   /* Accumulate the counts from all join iterations of all join parts. */
   thd->inc_examined_row_count(join_examined_rows);
   DBUG_PRINT("counts", ("thd->examined_row_count: %lu",