MDEV-14347 CREATE PROCEDURE returns no error when using an unknown variable

CREATE PROCEDURE did not detect unknown SP variables in assignments like this:

  SET var=a_long_var_name_with_a_typo;

The error happened only during the SP execution time, and only of the control
flow reaches the erroneous statement.

Fixing most expressions to detect unknown identifiers.
This includes simple subqueries without tables:

- Query specification: SELECT list, WHERE,
  HAVING (inside aggregate functions) clauses, e.g.
    SET var= (SELECT unknown_ident+1);
    SET var= (SELECT 1 WHERE unknown_identifier);
    SET var= (SELECT 1 HAVING SUM(unknown_identifier);

- Table value constructor: VALUES clause, e.g.:
    SET var= (VALUES(unknown_ident));

Note, in some more complex subquery cases unknown variables are still not detected
(this will be fixed separately):

- Derived tables:
  SET a=(SELECT unknown_ident FROM (SELECT 1 AS alias) t1);
  SET res=(SELECT * FROM t1 LEFT OUTER JOIN (SELECT unknown_ident) t2 USING (c1));

- CTE:
  SET a=(WITH cte1 (a) AS (SELECT unknown_ident) SELECT * FROM cte1);
  SET a=(WITH cte1 (a,b) AS (VALUES (unknown,2),(3,4)) SELECT * FROM cte1);
  SET a=(WITH cte1 (a,b) AS (VALUES (1,2),(3,4)) SELECT unknown_ident FROM cte1);

- SELECT .. GROUP BY unknown_identifier
- SELECT .. ORDER BY unknown_identifier
- HAVING with an unknown identifier outside of any aggregate functions:
  SELECT .. HAVING unknown_identifier;

4 files changed, 998 insertions, 19 deletions

diff --git a/mysql-test/main/sp-error.result b/mysql-test/main/sp-error.result
index b7d9a3a5cc8..703af07569b 100644
--- a/mysql-test/main/sp-error.result
+++ b/mysql-test/main/sp-error.result
@@ -443,9 +443,7 @@ create procedure nodb.bug3339() begin end|
 ERROR 42000: Unknown database 'nodb'
 create procedure bug2653_1(a int, out b int)
 set b = aa|
-call bug2653_1(1, @b)|
-ERROR 42S22: Unknown column 'aa' in 'field list'
-drop procedure bug2653_1|
+ERROR 42000: Undeclared variable: aa
 create procedure bug2653_2(a int, out b int)
 begin
 if aa < 0 then
@@ -1205,9 +1203,7 @@ set selectstr = concat(selectstr,
 c.operatorid,
 'in (',conditionstr, ')');
 end|
-call bug15091();
-ERROR 42S02: Unknown table 'c' in field list
-drop procedure bug15091;
+ERROR 42000: Undeclared variable: c
 drop function if exists bug16896;
 create aggregate function bug16896() returns int return 1;
 ERROR HY000: Aggregate specific instruction(FETCH GROUP NEXT ROW) missing from the aggregate function
diff --git a/mysql-test/main/sp-error.test b/mysql-test/main/sp-error.test
index 95a7372a6f2..0a545b99f42 100644
--- a/mysql-test/main/sp-error.test
+++ b/mysql-test/main/sp-error.test
@@ -607,13 +607,10 @@ create procedure nodb.bug3339() begin end|
 #
 # BUG#2653
 #
+--error ER_SP_UNDECLARED_VAR
 create procedure bug2653_1(a int, out b int)
   set b = aa|
 
---error ER_BAD_FIELD_ERROR
-call bug2653_1(1, @b)|
-
-drop procedure bug2653_1|
 
 --error ER_BAD_FIELD_ERROR
 create procedure bug2653_2(a int, out b int)
@@ -1714,6 +1711,7 @@ drop procedure if exists bug15091;
 --enable_warnings
 
 delimiter |;
+--error ER_SP_UNDECLARED_VAR
 create procedure bug15091()
 begin
   declare selectstr varchar(6000) default ' ';
@@ -1726,15 +1724,6 @@ begin
 end|
 delimiter ;|
 
-# The error message used to be:
-#   ERROR 1109 (42S02): Unknown table 'c' in order clause
-# but is now rephrased to something less misleading:
-#   ERROR 1109 (42S02): Unknown table 'c' in field list
---error ER_UNKNOWN_TABLE
-call bug15091();
-
-drop procedure bug15091;
-
 
 #
 # BUG#16896: Stored function: unused AGGREGATE-clause in CREATE FUNCTION
diff --git a/mysql-test/main/sp-vars.result b/mysql-test/main/sp-vars.result
index 236695a6c0f..4952746c4e0 100644
--- a/mysql-test/main/sp-vars.result
+++ b/mysql-test/main/sp-vars.result
@@ -1310,3 +1310,406 @@ t1	CREATE TABLE "t1" (
   "var" char(1) DEFAULT NULL
 )
 DROP TABLE t1;
+#
+# MDEV-14347 CREATE PROCEDURE returns no error when using an unknown variable
+#
+#
+# Simple cases (without subqueries) - the most typical problem:
+# a typo in an SP variable name
+#
+CREATE PROCEDURE p1(a INT)
+BEGIN
+DECLARE res INT DEFAULT 0;
+IF (a < 0) THEN
+SET res= a_long_variable_name_with_a_typo;
+END IF;
+END;
+$$
+ERROR 42000: Undeclared variable: a_long_variable_name_with_a_typo
+CREATE PROCEDURE p1(a INT)
+BEGIN
+DECLARE res INT DEFAULT 0;
+IF (a < 0) THEN
+SET res= 1 + a_long_variable_name_with_a_typo;
+END IF;
+END;
+$$
+ERROR 42000: Undeclared variable: a_long_variable_name_with_a_typo
+#
+# Complex cases with subqueries
+#
+#
+# Maybe a table field identifier (there are some tables) - no error
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(SELECT * FROM t1 LEFT OUTER JOIN t2 USING (c1));
+END;
+$$
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(SELECT * FROM t1 LEFT OUTER JOIN t2 ON (c1=c2));
+END;
+$$
+DROP PROCEDURE p1;
+#
+# One unknown identifier, no tables
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=unknown_ident;
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=unknown_ident1.unknown_ident2;
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident1
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=unknown_ident1.unknown_ident2.unknown_ident3;
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident1
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT unknown_ident);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT unknown_ident FROM dual);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT (SELECT unknown_ident));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT (SELECT unknown_ident FROM dual));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT 1 WHERE unknown_ident);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT 1 WHERE unknown_ident=1);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT 1 LIMIT unknown_ident);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+#
+# GROUP, HAVING, ORDER are not tested yet for unknown identifiers
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT 1 GROUP BY unknown_ident);
+END;
+$$
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(SELECT 1 HAVING unknown_ident);
+END;
+$$
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT 1 ORDER BY unknown_ident);
+END;
+$$
+DROP PROCEDURE p1;
+#
+# HAVING + aggregate_function(unknown_identifier) is a special case
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(SELECT 1 HAVING SUM(unknown_ident));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+#
+# Known indentifier + unknown identifier, no tables
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=a+unknown_ident;
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=a+(SELECT unknown_ident);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=a+(SELECT unknown_ident FROM dual);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT (a+(SELECT unknown_ident)));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT (a+(SELECT unknown_ident FROM dual)));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+#
+# Unknown indentifier + known identifier, no tables
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=unknown_ident+a;
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT unknown_ident)+a;
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT unknown_ident FROM dual)+a;
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT (SELECT unknown_ident)+a);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT (SELECT unknown_ident FROM dual)+a);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+#
+# Maybe a table field indentifier + unknown identifier
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT c1 FROM t1)+unknown_ident;
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT c1 FROM t1)+(SELECT unknown_ident);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT c1 FROM t1)+(SELECT unknown_ident FROM dual);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT ((SELECT c1 FROM t1)+(SELECT unknown_ident)));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT ((SELECT c1 FROM t1)+(SELECT unknown_ident FROM dual)));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+#
+# Unknown indentifier + maybe a table field identifier
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=unknown_ident+(SELECT c1 FROM t1);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT unknown_ident)+(SELECT c1 FROM t1);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT unknown_ident FROM dual)+(SELECT c1 FROM t1);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT (SELECT unknown_ident)+(SELECT c1 FROM t1));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+SET a=(SELECT (SELECT unknown_ident FROM dual)+(SELECT c1 FROM t1));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+#
+# Maybe a table field identifier + maybe a field table identifier
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE a INT;
+-- c2 does not have a table on its level
+-- but it can be a field of a table on the uppder level, i.e. t1
+SET a=(SELECT c1+(SELECT c2) FROM t1);
+END;
+$$
+DROP PROCEDURE p1;
+#
+# TVC - unknown identifier
+#
+CREATE PROCEDURE p1(a INT)
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(VALUES(unknown_ident));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1(a INT)
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(VALUES(1),(unknown_ident));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1(a INT)
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(VALUES((SELECT unknown_ident)));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1(a INT)
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(VALUES(1),((SELECT unknown_ident)));
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1(a INT)
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(VALUES(1) LIMIT unknown_ident);
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+#
+# TVC - ORDER BY - not tested yet for unknown identifiers
+#
+CREATE PROCEDURE p1(a INT)
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(VALUES(1) ORDER BY unknown_ident);
+END;
+$$
+DROP PROCEDURE p1;
+#
+# TVC - maybe a table field identifier - no error
+#
+CREATE PROCEDURE p1(a INT)
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(VALUES((SELECT c1 FROM t1)));
+END;
+$$
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(a INT)
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=(VALUES(1),((SELECT c1 FROM t1)));
+END;
+$$
+DROP PROCEDURE p1;
+#
+# Functions DEFAULT(x) and VALUE(x)
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=DEFAULT(unknown_ident);
+SELECT res;
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE res INT DEFAULT 0;
+SET res=VALUE(unknown_ident);
+SELECT res;
+END;
+$$
+ERROR 42000: Undeclared variable: unknown_ident
+#
+# End of MDEV-14347 CREATE PROCEDURE returns no error when using an unknown variable
+#
diff --git a/mysql-test/main/sp-vars.test b/mysql-test/main/sp-vars.test
index ba8cd4f5ca8..9edf245acbe 100644
--- a/mysql-test/main/sp-vars.test
+++ b/mysql-test/main/sp-vars.test
@@ -1603,3 +1603,594 @@ $$
 DELIMITER ;$$
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
+
+
+
+--echo #
+--echo # MDEV-14347 CREATE PROCEDURE returns no error when using an unknown variable
+--echo #
+
+--echo #
+--echo # Simple cases (without subqueries) - the most typical problem:
+--echo # a typo in an SP variable name
+--echo #
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  IF (a < 0) THEN
+    SET res= a_long_variable_name_with_a_typo;
+  END IF;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  IF (a < 0) THEN
+    SET res= 1 + a_long_variable_name_with_a_typo;
+  END IF;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Complex cases with subqueries
+--echo #
+
+--echo #
+--echo # Maybe a table field identifier (there are some tables) - no error
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(SELECT * FROM t1 LEFT OUTER JOIN t2 USING (c1));
+END;
+$$
+DELIMITER ;$$
+DROP PROCEDURE p1;
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(SELECT * FROM t1 LEFT OUTER JOIN t2 ON (c1=c2));
+END;
+$$
+DELIMITER ;$$
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # One unknown identifier, no tables
+--echo #
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=unknown_ident;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=unknown_ident1.unknown_ident2;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=unknown_ident1.unknown_ident2.unknown_ident3;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT unknown_ident);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT unknown_ident FROM dual);
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT (SELECT unknown_ident));
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT (SELECT unknown_ident FROM dual));
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT 1 WHERE unknown_ident);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT 1 WHERE unknown_ident=1);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT 1 LIMIT unknown_ident);
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # GROUP, HAVING, ORDER are not tested yet for unknown identifiers
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT 1 GROUP BY unknown_ident);
+END;
+$$
+DELIMITER ;$$
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(SELECT 1 HAVING unknown_ident);
+END;
+$$
+DELIMITER ;$$
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT 1 ORDER BY unknown_ident);
+END;
+$$
+DELIMITER ;$$
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # HAVING + aggregate_function(unknown_identifier) is a special case
+--echo #
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(SELECT 1 HAVING SUM(unknown_ident));
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Known indentifier + unknown identifier, no tables
+--echo #
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=a+unknown_ident;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=a+(SELECT unknown_ident);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=a+(SELECT unknown_ident FROM dual);
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT (a+(SELECT unknown_ident)));
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT (a+(SELECT unknown_ident FROM dual)));
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Unknown indentifier + known identifier, no tables
+--echo #
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=unknown_ident+a;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT unknown_ident)+a;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT unknown_ident FROM dual)+a;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT (SELECT unknown_ident)+a);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT (SELECT unknown_ident FROM dual)+a);
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Maybe a table field indentifier + unknown identifier
+--echo #
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT c1 FROM t1)+unknown_ident;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT c1 FROM t1)+(SELECT unknown_ident);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT c1 FROM t1)+(SELECT unknown_ident FROM dual);
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT ((SELECT c1 FROM t1)+(SELECT unknown_ident)));
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT ((SELECT c1 FROM t1)+(SELECT unknown_ident FROM dual)));
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Unknown indentifier + maybe a table field identifier
+--echo #
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=unknown_ident+(SELECT c1 FROM t1);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT unknown_ident)+(SELECT c1 FROM t1);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT unknown_ident FROM dual)+(SELECT c1 FROM t1);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT (SELECT unknown_ident)+(SELECT c1 FROM t1));
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  SET a=(SELECT (SELECT unknown_ident FROM dual)+(SELECT c1 FROM t1));
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Maybe a table field identifier + maybe a field table identifier
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE a INT;
+  -- c2 does not have a table on its level
+  -- but it can be a field of a table on the uppder level, i.e. t1
+  SET a=(SELECT c1+(SELECT c2) FROM t1);
+END;
+$$
+DELIMITER ;$$
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # TVC - unknown identifier
+--echo #
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(VALUES(unknown_ident));
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(VALUES(1),(unknown_ident));
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(VALUES((SELECT unknown_ident)));
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(VALUES(1),((SELECT unknown_ident)));
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(VALUES(1) LIMIT unknown_ident);
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # TVC - ORDER BY - not tested yet for unknown identifiers
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(VALUES(1) ORDER BY unknown_ident);
+END;
+$$
+DELIMITER ;$$
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # TVC - maybe a table field identifier - no error
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(VALUES((SELECT c1 FROM t1)));
+END;
+$$
+DELIMITER ;$$
+DROP PROCEDURE p1;
+
+DELIMITER $$;
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=(VALUES(1),((SELECT c1 FROM t1)));
+END;
+$$
+DELIMITER ;$$
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Functions DEFAULT(x) and VALUE(x)
+--echo #
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=DEFAULT(unknown_ident);
+  SELECT res;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  SET res=VALUE(unknown_ident);
+  SELECT res;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # End of MDEV-14347 CREATE PROCEDURE returns no error when using an unknown variable
+--echo #