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;

11 files changed, 1096 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 #
diff --git a/sql/item.cc b/sql/item.cc
index 8ea6366e6c4..5d230b4ea66 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -764,6 +764,29 @@ bool Item_field::collect_item_field_processor(void *arg)
 }
 
 
+void Item_ident::undeclared_spvar_error() const
+{
+  /*
+    We assume this is an unknown SP variable, possibly a ROW variable.
+    Print the leftmost name in the error:
+      SET var=a;      -> a
+      SET var=a.b;    -> a
+      SET var=a.b.c;  -> a
+  */
+  my_error(ER_SP_UNDECLARED_VAR, MYF(0),  db_name.str    ? db_name.str :
+                                          table_name.str ? table_name.str :
+                                          field_name.str);
+}
+
+bool Item_field::unknown_splocal_processor(void *arg)
+{
+  DBUG_ENTER("Item_field::unknown_splocal_processor");
+  DBUG_ASSERT(type() == FIELD_ITEM);
+  undeclared_spvar_error();
+  DBUG_RETURN(true);
+}
+
+
 bool Item_field::add_field_to_set_processor(void *arg)
 {
   DBUG_ENTER("Item_field::add_field_to_set_processor");
diff --git a/sql/item.h b/sql/item.h
index c6e1bd19a22..800945f3242 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1926,6 +1926,7 @@ public:
   virtual bool cleanup_excluding_const_fields_processor (void *arg)
   { return cleanup_processor(arg); }
   virtual bool collect_item_field_processor(void *arg) { return 0; }
+  virtual bool unknown_splocal_processor(void *arg) { return 0; }
   virtual bool collect_outer_ref_processor(void *arg) {return 0; }
   virtual bool check_inner_refs_processor(void *arg) { return 0; }
   virtual bool find_item_in_field_list_processor(void *arg) { return 0; }
@@ -3272,6 +3273,8 @@ protected:
   LEX_CSTRING orig_table_name;
   LEX_CSTRING orig_field_name;
 
+  void undeclared_spvar_error() const;
+
 public:
   Name_resolution_context *context;
   LEX_CSTRING db_name;
@@ -3488,6 +3491,7 @@ public:
   Item *get_tmp_table_item(THD *thd);
   bool find_not_null_fields(table_map allowed);
   bool collect_item_field_processor(void * arg);
+  bool unknown_splocal_processor(void *arg);
   bool add_field_to_set_processor(void * arg);
   bool find_item_in_field_list_processor(void *arg);
   bool register_field_in_read_map(void *arg);
@@ -6589,6 +6593,7 @@ private:
   */
   bool read_only;
 public:
+  bool unknown_splocal_processor(void *arg) { return false; }
   bool check_vcol_func_processor(void *arg);
 };
 
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 10218f392c7..23ff906e3cf 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -661,6 +661,40 @@ bool Item_subselect::is_expensive()
 }
 
 
+bool Item_subselect::unknown_splocal_processor(void *argument)
+{
+  SELECT_LEX *sl= unit->first_select();
+  if (sl->top_join_list.elements)
+    return 0;
+  if (sl->tvc && sl->tvc->walk_values(&Item::unknown_splocal_processor,
+                                      false, argument))
+    return true;
+  for (SELECT_LEX *lex= unit->first_select(); lex; lex= lex->next_select())
+  {
+    /*
+      TODO: walk through GROUP BY and ORDER yet eventually.
+      This will require checking aliases in SELECT list:
+        SELECT 1 AS a GROUP BY a;
+        SELECT 1 AS a ORDER BY a;
+    */
+    List_iterator<Item> li(lex->item_list);
+    Item *item;
+    if (lex->where && (lex->where)->walk(&Item::unknown_splocal_processor,
+                                         false, argument))
+      return true;
+    if (lex->having && (lex->having)->walk(&Item::unknown_splocal_processor,
+                                           false, argument))
+      return true;
+    while ((item=li++))
+    {
+      if (item->walk(&Item::unknown_splocal_processor, false, argument))
+        return true;
+    }
+  }
+  return false;
+}
+
+
 bool Item_subselect::walk(Item_processor processor, bool walk_subquery,
                           void *argument)
 {
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index dc8417495c5..8f6e4836ac7 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -234,6 +234,7 @@ public:
   virtual void reset_value_registration() {}
   enum_parsing_place place() { return parsing_place; }
   bool walk(Item_processor processor, bool walk_subquery, void *arg);
+  bool unknown_splocal_processor(void *arg);
   bool mark_as_eliminated_processor(void *arg);
   bool eliminate_subselect_processor(void *arg);
   bool set_fake_select_as_master_processor(void *arg);
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index afeb3837a36..49c8229c277 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -5168,6 +5168,9 @@ sp_head::set_local_variable(THD *thd, sp_pcontext *spcont,
   if (!(val= adjust_assignment_source(thd, val, spv->default_value)))
     return true;
 
+  if (val->walk(&Item::unknown_splocal_processor, false, NULL))
+    return true;
+
   sp_instr_set *sp_set= new (thd->mem_root)
                         sp_instr_set(instructions(), spcont, rh,
                                      spv->offset, val, lex,
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 999563275f4..37f0f80e8a0 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -23,6 +23,36 @@
 #include "sql_parse.h"
 #include "sql_cte.h"
 
+
+/**
+  @brief
+    Walk through all VALUES items.
+  @param
+     @param processor      - the processor to call for each Item
+     @param walk_qubquery  - if should dive into subquery items
+     @param argument       - the argument to pass recursively
+  @retval
+    true   on error
+    false  on success
+*/
+bool table_value_constr::walk_values(Item_processor processor,
+                                     bool walk_subquery,
+                                     void *argument)
+{
+  List_iterator_fast<List_item> list_item_it(lists_of_values);
+  while (List_item *list= list_item_it++)
+  {
+    List_iterator_fast<Item> item_it(*list);
+    while (Item *item= item_it++)
+    {
+       if (item->walk(&Item::unknown_splocal_processor, false, argument))
+         return true;
+    }
+  }
+  return false;
+}
+
+
 /**
   @brief
     Fix fields for TVC values
diff --git a/sql/sql_tvc.h b/sql/sql_tvc.h
index 594a77af65c..b5b8b979f43 100644
--- a/sql/sql_tvc.h
+++ b/sql/sql_tvc.h
@@ -18,6 +18,7 @@
 #include "sql_type.h"
 
 typedef List<Item> List_item;
+typedef bool (Item::*Item_processor) (void *arg);
 class select_result;
 class Explain_select;
 class Explain_query;
@@ -65,6 +66,7 @@ public:
   bool exec(SELECT_LEX *sl);
 
   void print(THD *thd_arg, String *str, enum_query_type query_type);
+  bool walk_values(Item_processor processor, bool walk_subquery, void *arg);
 };
 
 st_select_lex *wrap_tvc_with_tail(THD *thd, st_select_lex *tvc_sl);